August 11, 2006 at 7:41 pm
I have some vbscript (Win2003SP1, IIS 6) that sends a text command to SQL 7 (Win 2000) via ADO using a recordset object, cmd.CommandType = adCmdText, and SQL 7 returns a recordset. A simple example would be "SELECT 20 AS newkey". However, redirecting the same ASP page to SQL 2005 (Win2003SP1) does not return a recordset. No error is raised. Besides the fact that it ought to be coded as a stored procedure, it's a lot of broken code to fix. Anyone else seen this? Any easy fix? Thanks.
August 14, 2006 at 5:16 pm
Further clarification: the command does not return a result set if the Select command is preceeded by any Insert, Update, or Delete command. If preceeded by other T-SQL (DECLARE, SET, etc.) then it works.
August 16, 2006 at 12:49 pm
FYI. The problem is that SQL 2005 seems to be sending back the "# of rows affected" message to ADO which then gets confused, SQL 7 did not do this. [Have not tried on SQL 2000.) The fix is to add SET NOCOUNT ON; at the beginning of the text batch string and now everything works as before.
August 17, 2006 at 5:24 am
Rule number 1
ALWAYS USE
SET NOCOUNT ON
in stored procedures that have contact with ADO
N 56°04'39.16"
E 12°55'05.25"
August 21, 2006 at 4:58 pm
I discovered that the problem can be solved if I set the global database user options to 512 = set nocount on. Upon furthur research, I discovered that the old SQL 7 database had that enabled. However, that is now causing an even bigger problem, SSMS refuses to add, update, or delete any row in any table, with a bogus Optimistic Concurrency Control Error.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply