Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Problem with EXEC in SQL Server 2005 Stored Proc

Author  Topic 

jubjubber
Starting Member

3 Posts

Posted - 2008-07-15 : 20:54:33
I am using SQL Server 2005 with an ASP.NET front end. I have a stored procedure where a query is dynamically generated and contained in a variable @QueryString. At the end of the stored procedure, I run this command:

Exec (@QueryString)

to execute the query. This stored procedure is called by an ASP.NET page and values are returned to a dataset.

The problem is that when the ASP.NET page calls the stored procedure, it takes a long time for data to be returned (30 seconds).

I did a test where I took the query that is contained in @QueryString and instead of calling Exec (@QueryString), I had the query hard coded into the stored procedure itself. Now, when the ASP.NET page calls this stored procedure, values are returned quickly (3 seconds). That is, I commented out the Exec (@QueryString) and instead, have the stored procedure call the hard coded query.

Does anyone know what I am doing wrong? Does anyone know of a problem where using Exec in stored procedures in stored procedures in SQL Server 2005 - and called by ASP.NET pages - causes slow processing? Help! This used to return values quickly when I was running SQL Server 2000.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 20:57:13
dup http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106674


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -