3

I'm using VB6 with SQLOLEDB and MSDASQL. By switching the provider I notice that I'm getting different results with one query.

My query checks some server settings and executes one of two BACKUP statements.

When I'm using SQLOLEDB everything works fine and the backup gets created. If I'm using MSDASQL no error occurs but no backup gets created.

It's the same server, the same user and the same statement.

I've used SQL Profiler and it shows that 100% the same command gets executed. Even if I copy the MSDASQL command and execute it in SSMS the backup gets created, but not with VB6.

My statement:

DECLARE @s varchar(1000), @ver int
SET @ver = 10
SELECT @s = case
WHEN
@ver > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END
EXEC (@s)

But when I'm executing:

DECLARE @s varchar(1000), @ver int
SELECT @s = case
WHEN
10 > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END
EXEC (@s)

(without SET @ver = 10) the backup works.

Could someone explain?

5

OLEDB via ADO has always had the issue where DONE_IN_PROC messages (rows affected) confuse code that is not expecting them. A workaround without app code changes is to specify SET NOCOUNT ON as the first statement in the batch:

SET NOCOUNT ON;
DECLARE @s varchar(1000), @ver int;
SET @ver = 10;
SELECT @s = case
WHEN
@ver > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END;
EXEC (@s)

EDIT:

A TDS protocol DONE, DONEINPROC, or DONEPROC message is returned to the client upon completion of each T-SQL statement and proc. These messages can optionally include a row count (indicated by a DONE_COUNT flag). Higher level APIs like ADO generally ignore these TDS messages except when a row count is included. But ADO has the nasty habit of returning a result set with no columns or rows in this case. Not only may this behavior vary by provider/driver, it is further complicated when informational and error messages are generated but not consumed by the client. This can result in a multi-statement batch that does not run to completion and errors that are not detected.

SET NOCOUNT ON is a convenient way to avoid these issues rather than write code to handle all messages and skip these empty result sets.

I should add that this applies to unmanaged "classic" ADO, not ADO.NET.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.