Clueless in 80040e31 land

[Curt]

Hi

I have the following stored procedure--

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_hdwr]

@Page int,
@Size int,
@color varChar(50)
AS
DECLARE @Start int, @End int
BEGIN TRANSACTION GetDataSet
SET @Start = (((@Page - 1) * @Size) + 1)
IF @@ERROR <> 0
GOTO ErrorHandler
SET @End = (@Start + @Size - 1)
IF @@ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
( Row int IDENTITY(1,1) PRIMARY KEY,
Number NChar(10),
Color VarChar(50),
Thumb VarChar(50) )
IF @@ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
select * from HDWR
where Color = @color
IF @@ERROR <> 0
GOTO ErrorHandler
SELECT *
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)
IF @@ERROR <> 0
GOTO ErrorHandler
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"
cnn.open cString
sql="sp_hdwr " & pgcount & ", 15, 'BC'"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open sql, cnn
'response.write sql%>

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???
Thanks

Curt

 

[Bob Barrows]

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

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.

 

[Curt]

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?

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

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.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows]

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.

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?

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

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.