Using sp_executesql
-
My latest fire has been distinguished and I am sifting through the ashes trying to determine underlying causes. This specific fire was the result of "Time out occurred while waiting for buffer latch type 2" errors. The strange thing is that the errors occurred while executing a parameterized select query from a .NET application. The RCP call ends up being
EXEC sp_executesql N'select a from t where c=@c', N'@c varchar (50)',@c='TEST'
(SQL statement is merely a generic statement for the purpose of example)
Executing this call from Query Analyzer resulted in the aforemention latch wait timeout error. However, executing the analgous statement,
select a from t where c='TEST'
returned a single row in a few hundred milliseconds.
So, what then is the significant difference between using sp_executesql and not using sp_executesql that, potentially, coulld result in latch wait errors.
TIA,
Gordon Pollokoff
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
Gordon that latch type 2 error made me remember that there was a problem in early service packs....while it might not be your specific problem, what version is on your server? select @@version. it had something to do with deleted cache for procedures I think.
this article from MS describes it a bit:
http://support.microsoft.com/kb/309093
If it's not that, let us know and we can look further.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Microsoft SQL Server 2000
- 8.00.760 which is the version for SP3.
And I only read that article 10 or 15 times yesterday while fighting the fire
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
lol sorry for pointing out the obvious...I'll read a bit more on it and see if i find something un-obvious.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply