Re: Clueless in 80040e31 land
- From: "Bob Barrows" <reb01501@xxxxxxxxxxxxxxx>
- Date: Fri, 29 May 2009 16:25:36 -0400
The only thing that comes to mind is parameter-sniffing:
http://tinyurl.com/f9r2
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
There may be lock-contention occurring: you would have to turn on the
Locks event in Profiler to see if this is the case.
Curt wrote:
Hi Bob,--
I appreciate your help with this. I did change the sp_ to pr_ on the
procedure name.
Using SQL Profiler was no help. When I do a response.write and copy &
paste to SQL Prof. it works very fast, just like it's supposed to,
returning the set number of records according to the page specified.
I even set the no count on like you suggested, to no avail.
I have several pages on this site programmed the same way, and they
have worked fine for years, and continue to work. It's just any new
ones I've written lately just do not seem to want to work and the DB
times out. I have over 1000 asp pages that has been growing on this
site on the employee side starting back in 1999, and the failing of
any new paging statements is beyond me as to why. The biggest mystery
is why are the pages that have been working for years still work??
With ten years of developing over 1000 asp pages, I have a pretty good
toolkit that I can reference. The stored procedure works flawlessly in
Profiler, but fails when utilized from the web server. My connection
string aside, it really shouldn't make any difference how I connect,
as long as I do, and it has always worked.
I was kind of hoping someone would find some stupid thing like extra
single quote, or something that is difficult to catch, but after many
hours of perusing, I just can't see it. Can anybody?
"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:e$Eouft2JHA.140@xxxxxxxxxxxxxxxxxxxxxxx
Curt wrote:
Hi
I have the following stored procedure--
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_hdwr]
Nothing to do with your problem, but it's a bad practice to use
"sp_" to prefix your stored procedures. "sp_" should be reserved for
system stored procedures. In fact, when asked to execute a procedure
with that prefix, SQL Server will assume it's a system procedure and
will waste time trying to find it in the master database, only
looking in the current database when it fails to find it in master
(this is true even if you fully qualify the procedure in your call).
While the time wasted is small, the more important problem arises if
you inadvertantly give your procedure the same name as an existing
system procedure. You will certainly see unexpected results when you
try to execute your procedure.
@Page int,
@Size int,
@color varChar(50)
AS
This might have some bearing on your problem, but I doubt it:
You should turn on NOCOUNT to avoid sql server sending informational
messages (x rows affected) to the client as closed recordsets,
SET NOCOUNT ON
DECLARE @Start int, @End int<snip>
IF @@ERROR <> 0
GOTO ErrorHandler
If you are using SQL2005, you should really switch to TRY...CATCH
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR
Below is my error message--
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
Here is my ASP code that causes this to happen--
<% pgcount=request.form("pgcount")
if pgcount="" then
pgcount="1"
end if
set cnn=Server.CreateObject("ADODB.Connection")
cString="driver={SQL
Server};server=blahblah;uid=blah;pwd=blahblahblah;database=blah2"
It's about time you got away from the archaic ODBC connection, isn't
it? There's a perfectly fine OLE DB provider available for SQL
Server, and it has worked well for years:
http://www.aspfaq.com/show.asp?id=2126
cnn.open cString
sql="sp_hdwr " & pgcount & ", 15, 'BC'"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open sql, cnn
'response.write sql%>
Rather than use dynamic sql, which leaves you vulnerable to sql
injection, I would prefer this syntax:
set rs=Server.CreateObject("ADODB.Recordset")
cnn.sp_hdwr pgcount,15,"BC", rs
if not rs.eof then
...
Now when I comment out the rs.open and uncomment the response.write,
I can copy and past the sql statement right into Management Studio
and it works flawlessly.
The line referencing the error is the rs.open line so it doesn't
seem to like the SQL statement, even though the copy and pasted sql
statement works flawlessly and as quick as I can press F5 key.
I use this same paging method over and over on several scheduling
pages and it works fine. But it just doesn't seem to want to
function here. (And that is a single quote followed by BC followed
by another single quote, ending up on a double quote.)
Any ideas???
None of my above suggestions would seem to have anything to do with
this problem (I would suggest implementing them anyways, especially
the first one concerning the name of the procedure). I would suggest
using SQL Profiler to try to figure out what is happening.
HTH,
Bob Barrows
.
- References:
- Re: Clueless in 80040e31 land
- From: Curt
- Re: Clueless in 80040e31 land
- Prev by Date: Re: pass stored procedure parameters in asp
- Next by Date: Re: Replace characters in String
- Previous by thread: Re: Clueless in 80040e31 land
- Next by thread: Replace characters in String
- Index(es):
Relevant Pages
|
