This is a migrated thread and some comments may be shown as answers.

Peroframnce issue with sp_executesql

1 Answer 27 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
David
Top achievements
Rank 2
David asked on 03 Nov 2010, 11:32 AM
Hi,

I have a SQL statment which when run as a SQL script on the SQL managment studio works in 10scs. When run through Telerik as a report SQL datasource it times out.

when investigating the problem i have found this is todo with sp_executesql.

for example if i run my Script:

sp_executesql N' MySQL'.N'@Paramn varchar(20)',@Param ='Value'

then this takes 3mins.

if i run:

sp_executesql N' Declare @LocalParam as varchar(20)
                            Set @LocalParam = @Param
                            MySQL'.N'@Param',@Param ='Value'

this runs in 10scs.

my problem is when i try and do this in telerik it add the localParam into the sp_executesql Paramiter list like:

sp_executesql N' Declare @LocalParam as varchar(20)
                            Set @LocalParam = @Param
                            MySQL'.N' @LocalParam nvarchar(20) @Param nvarchar(20)',@Param ='Value'

because of this i get LocalParam is already declared if i remove the declare it then adds its own @LocalParam as a nvarchar(4000) and again it times out.

1 Answer, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 05 Nov 2010, 03:44 PM
Hi David,

Currently the SqlDataSource component supports only two methods for retrieving data: calling a SQL select statement or a store procedure. However it seems that the Transact-SQL statement you are trying to execute is neither, so it is not supported. My advice in this case is to create a stored procedure in the database that executes your custom Transact-SQL statement and then specify that stored procedure to the SqlDataSource instead. Check the "How to: Connect to a SQL Server Database Using Stored Procedure" help topic from the online documentation for more info.

Sincerely yours,
Steve
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Tags
General Discussions
Asked by
David
Top achievements
Rank 2
Answers by
Steve
Telerik team
Share this question
or