Peroframnce issue with sp_executesql

[David]

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.

[Steve]

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