Microsoft OLE DB Provider for SQL Server error '80004005' - Unspecified error

Hi,
I have an ASP code that calls a stored procedure. I use a Command
object to execute the stored procedure. The Stored procedure returns
data when executed in Query analyser. But it takes around 1 minute for
this. The stored procedure involves querying a huge amount of data.
When I try to open the ASP page, I get the following error :
Microsoft OLE DB Provider for SQL Server error '80004005'
Unspecified error
Any suggestions?
TIA,
AnishyaHi
Have you checked with SQL Profiler that the procedure is being called? Check
that the login/user you are using has the correct permissions. If this has
timed out then I would expect that a TIMEOUT error message. You may also want
to check http://www.aspfaq.com/show.asp?id=2009
John
"Ani" wrote:
> Hi,
> I have an ASP code that calls a stored procedure. I use a Command
> object to execute the stored procedure. The Stored procedure returns
> data when executed in Query analyser. But it takes around 1 minute for
> this. The stored procedure involves querying a huge amount of data.
> When I try to open the ASP page, I get the following error :
> Microsoft OLE DB Provider for SQL Server error '80004005'
> Unspecified error
> Any suggestions?
> TIA,
> Anishya
>|||Hi,
The procedure is being called in the Profiler. I tried executing the
same stored procedure call in query analyser and saw that it works
fine. The only problem is that it takes around 1 minute. So I am sure
this is why the ASP page gives the error. The stored proc makes the use
of a temporary table. I tried executing the stored proc by using a
permanent table instead of the temporary table. It doesnt take much
time. But doesn't permanent tables involve more performance overheads
than temporary tables? I am trying to find another alternative. Is
there any way that I could increase the timeout period for ASP pages?
Thanks,
Anishya|||Hi
You could be seeing contention on tempdb if a lot of people are
simulataneously creating temporary tables, see
http://support.microsoft.com/kb/328551. You should see if the temporary table
is really necessary or possibly use a table variable as an alternative.
You may also want to change the timeout on your command object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocommandtimeout.asp
John
"Ani" wrote:
> Hi,
> The procedure is being called in the Profiler. I tried executing the
> same stored procedure call in query analyser and saw that it works
> fine. The only problem is that it takes around 1 minute. So I am sure
> this is why the ASP page gives the error. The stored proc makes the use
> of a temporary table. I tried executing the stored proc by using a
> permanent table instead of the temporary table. It doesnt take much
> time. But doesn't permanent tables involve more performance overheads
> than temporary tables? I am trying to find another alternative. Is
> there any way that I could increase the timeout period for ASP pages?
> Thanks,
> Anishya
>