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