SQL 7 to SQL 2000 Upgrade

Hi,

Can anyone shed any light on why this might have happened?

We currently log ship from a Live database to a Warm Standby using custom
built scripts. (I know that this is no longer necessary in SQL 2000). The
script includes the following lines to check for the existence of a log file
to be applied to the warm standby:

IF EXISTS (Select * from sysobjects where name = '#t')

Drop Table #t

CREATE TABLE #t(x INT)

INSERT INTO #t EXEC master..xp_cmdshell @ExecLine

IF (Select x from #t) = 1

BEGIN

Select @Location = 'D:\TransferIn\Logs\' + @BakMonth + @BakDay + '\' +
@BakMonth + @BakDay + @BakHour + @BakMin + '.BAK'

Restore Log MyDatabase
From Disk = @location
With Standby = 'D:\MSSQL7\BACKUP\UNDO_MyDatabase.DAT'

END

Drop table #t

Where @ExecLine would equal something like this: "IF EXIST
D:\TransferIn\Logs\1009\10091850.BAK  ECHO 1"

The problem is, since the upgrade we get the following error:

Server: Msg 512, Level 16, State 1, Line 36
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

when we do this:

IF (Select x from #t) = 1

because #t now contains:

x
-----------
1
NULL

ie. an extra value (Null) has been inserted.

The fix is trivial, I realise. What I wanted to know was why it has
happened.

Any ideas?

Many thanks,

James Brophy

You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV,
or subscribe to other DevelopMentor lists at http://discuss.develop.com.