SQL / ADO issue when upgrading to SQL 2000

[TWiSTeD ViBE]

Hi Folks,

We recently upgraded to SQL 2000 from SQL 7. We run a Classic ASP based
website using this server as the backend.

We thought the migration went seamlessly - but it seems that INSERT
statements that also return the identity column ID now don't work properly.
For example (in ASP using ADO):-

---------------------------------------------------

Set rsTest = dbConn.Execute("INSERT INTO tbTable (field1, field2)
VALUES('test', 'test'); SELECT @@identity;").NextRecordset

varNewID = rsTest(0)

rsTest.Close
Set rsTest = Nothing

---------------------------------------------------

We've found a solution that works by setting NOCOUNT ON in the query and
removing the .NextRecordSet from the ADO execution:-

---------------------------------------------------

Set rsTest = dbConn.Execute("SET NOCOUNT ON; INSERT INTO tbTable (field1,
field2) VALUES('test', 'test'); SELECT @@identity;")

varNewID = rsTest(0)

rsTest.Close
Set rsTest = Nothing

---------------------------------------------------

This works but all throughout our site the old method is used and it would
involve a great deal of work to change every occurrence.

Is there a setting or something we can apply in SQL 2000 to fix this issue??

Any help much appreciated!!

Rob

[Bob Barrows]

TWiSTeD ViBE wrote:

> Hi Folks,
>
> We recently upgraded to SQL 2000 from SQL 7. We run a Classic ASP
> based website using this server as the backend.
>
> We thought the migration went seamlessly - but it seems that INSERT
> statements that also return the identity column ID now don't work
> properly. For example (in ASP using ADO):-
>
> ---------------------------------------------------
>
> Set rsTest = dbConn.Execute("INSERT INTO tbTable (field1, field2)
> VALUES('test', 'test'); SELECT @@identity;").NextRecordset
>
> varNewID = rsTest(0)
>
> rsTest.Close
> Set rsTest = Nothing
>
> ---------------------------------------------------

What does "doesn't work" mean? error message? No results?

FWIW, I disapprove of this technique. Why aren't you encapsulating this code
in stored procedures? Are you protecting yourself from SQL Injection?
There's been much discussion on this subject in the past couple of weeks in
the .asp.db newsgroup, so a quick Google search should get you plenty of
info on this matter.

>
> We've found a solution that works by setting NOCOUNT ON in the query
> and removing the .NextRecordSet from the ADO execution:-
>
> ---------------------------------------------------
>
> Set rsTest = dbConn.Execute("SET NOCOUNT ON; INSERT INTO tbTable
> (field1, field2) VALUES('test', 'test'); SELECT @@identity;")
>
> varNewID = rsTest(0)
>
> rsTest.Close
> Set rsTest = Nothing
>
> ---------------------------------------------------
>
> This works but all throughout our site the old method is used and it
> would involve a great deal of work to change every occurrence.
>
> Is there a setting or something we can apply in SQL 2000 to fix this
> issue??
>

There is, but I suggest you don't use it as someday somebody will be
counting on the default behavior. However, from Books Online (BOL):

*********************************************
How to configure user options (Enterprise Manager)
To configure user options
Expand a server group.


Right-click a server, and then click Properties.


Click the Connections tab.


In the Default connection options box, select one or more attributes to
configure the default query-processing options for all connected users.
By default, no user options are configured
*******************************************

Scroll down in the list box to set the nocount option.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
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]

Just wanted to add:
Even if you do shut off nocount for everyone, you are still going to have to
get rid of all those ".NextRecordset"s. You should WANT to do this. Passing
two recordsets across the network when one will do is extremely inefficient
(using a recordset to return a single value is also inefficient, but that's
another rant ...)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
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"

[TWiSTeD ViBE]

Thanks for the help...

I was about to reply with what you've mentioned below - this solution
doesn't really solve the issue.

I also checked the old server and confirmed my thoughts - the NOCOUNT
setting in the server preferences wasn't set there either.

Another weird thing is that some of our other webservers that access the
same backend don't have this issue - which implies it is something specific
to the one connection - however the connection settings havent changed at
all since the migration.

Its all very annoying really!

Any further thoughts?

Rob


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23OeN0%23Z5DHA.1804@TK2MSFTNGP12.phx.gbl...

> Just wanted to add:
> Even if you do shut off nocount for everyone, you are still going to have

to

> get rid of all those ".NextRecordset"s. You should WANT to do this.

Passing

> two recordsets across the network when one will do is extremely

inefficient

> (using a recordset to return a single value is also inefficient, but

that's

> another rant ...)
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> 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]

TWiSTeD ViBE wrote:

> Thanks for the help...
>
> I was about to reply with what you've mentioned below - this solution
> doesn't really solve the issue.
>
> I also checked the old server and confirmed my thoughts - the NOCOUNT
> setting in the server preferences wasn't set there either.

Again, for the sake of efficiency, you should be wanting to go back and add
the "SET NOCOUNT ON" line to all your code and remove the .NextRecordset. I
know it seems like a daunting task (there are search and replace utilities
out there that will help), but it will help the performance of all your
apps.

>
> Another weird thing is that some of our other webservers that access
> the same backend don't have this issue - which implies it is
> something specific to the one connection - however the connection
> settings havent changed at all since the migration.
>
> Its all very annoying really!
>
> Any further thoughts?

Not until you tell us what "not working" means. :-)
I suspectu you are seeing the effects of the new version of MDAC that was
installed along with your SQL Server.


--
Microsoft MVP - ASP/ASP.NET
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]

TWiSTeD ViBE wrote:

> Thanks for the help...
>
> I was about to reply with what you've mentioned below - this solution
> doesn't really solve the issue.
>
> I also checked the old server and confirmed my thoughts - the NOCOUNT
> setting in the server preferences wasn't set there either.

Again, for the sake of efficiency, you should be wanting to go back and add
the "SET NOCOUNT ON" line to all your code and remove the .NextRecordset. I
know it seems like a daunting task (there are search and replace utilities
out there that will help), but it will help the performance of all your
apps.

>
> Another weird thing is that some of our other webservers that access
> the same backend don't have this issue - which implies it is
> something specific to the one connection - however the connection
> settings havent changed at all since the migration.
>
> Its all very annoying really!
>
> Any further thoughts?

Not until you tell us what "not working" means. :-)
I suspectu you are seeing the effects of the new version of MDAC that was
installed along with your SQL Server.


--
Microsoft MVP - ASP/ASP.NET
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"