> 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"

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

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:e$Eouft2JHA.140@TK2MSFTNGP03.phx.gbl...
> 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.
>
> --
> 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"
>

> 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@NOyahoo.SPAMcom> wrote in message
> news:e$Eouft2JHA.140@TK2MSFTNGP03.phx.gbl...
>> 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