S
SaltedSnacks
Hello.
We have an ASP 3.0 application that currently works "correctly" on one
server, Server A, and we're testing it on another server, Server B,
which is 64 bit.
The connection string for Server A is:
DRIVER={SQL Server};SERVER=...;DATABASE=...;UID=...;PWD=...
The connection string for Server B is:
PROVIDER=sqloledb;SERVER=...;DATABASE=...;UID=...;PWD=...
(Note: Both servers point to the same database on the same server)
The unexpected behavior occurs after calling .Execute on a command.
Here is some sample code:
Dim DBConn
Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open strDBConnection '(the ones shown above)
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = DBConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_TestProcedure"
objCmd.Parameters.Append objCmd.CreateParameter("@Name", adVarChar,
adParamInput, 50, "Test")
Dim rs
Set rs = objCmd.Execute
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure
@Name varchar(50)
AS
INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate())
SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a
SELECT.
Now... to the issue. On Server A, the variable rs above ends up with
a single open Recordset which is the results of the SELECT statement.
However, on Server B, rs is set to a closed recordset, and
rs.NextRecordset() gets a second recordset of the results of the
SELECT statement.
I understand what's going on. Server B is first returning the number
of rows affected by the INSERT which translates to a closed
recordset. But Server A does not do this.
I would like to know why the default behavior of the
command's .Execute is different on the different servers. Does it
relate to the Provider/Driver settings in the connection string? Does
it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the
start of the stored procedure. But we have many stored procedures,
and if the solution is a change in the connection string, that would
be preferred. Also, whatever the possible solution is, I also looking
to discover *why* it's happening.
Any help would be greatly appreciated.
We have an ASP 3.0 application that currently works "correctly" on one
server, Server A, and we're testing it on another server, Server B,
which is 64 bit.
The connection string for Server A is:
DRIVER={SQL Server};SERVER=...;DATABASE=...;UID=...;PWD=...
The connection string for Server B is:
PROVIDER=sqloledb;SERVER=...;DATABASE=...;UID=...;PWD=...
(Note: Both servers point to the same database on the same server)
The unexpected behavior occurs after calling .Execute on a command.
Here is some sample code:
Dim DBConn
Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open strDBConnection '(the ones shown above)
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = DBConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_TestProcedure"
objCmd.Parameters.Append objCmd.CreateParameter("@Name", adVarChar,
adParamInput, 50, "Test")
Dim rs
Set rs = objCmd.Execute
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure
@Name varchar(50)
AS
INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate())
SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a
SELECT.
Now... to the issue. On Server A, the variable rs above ends up with
a single open Recordset which is the results of the SELECT statement.
However, on Server B, rs is set to a closed recordset, and
rs.NextRecordset() gets a second recordset of the results of the
SELECT statement.
I understand what's going on. Server B is first returning the number
of rows affected by the INSERT which translates to a closed
recordset. But Server A does not do this.
I would like to know why the default behavior of the
command's .Execute is different on the different servers. Does it
relate to the Provider/Driver settings in the connection string? Does
it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the
start of the stored procedure. But we have many stored procedures,
and if the solution is a change in the connection string, that would
be preferred. Also, whatever the possible solution is, I also looking
to discover *why* it's happening.
Any help would be greatly appreciated.