jweyl (8/6/2012)

---

Hi -

I have some very simple code that kills all the user pids, then switches the db to single use and runs a restore and sync of the logins. We do this on a nightly basis. Last month our environment got upgraded to SQL server 2008 and now the code won't work. I am not sure why? But I am getting the following error out of SQL Server "Incorrect syntax near '*'. [SQLSTATE 42000] (Error 102)" I haven't been able to find anything to help me out.

Here is my simple code:

use master

go

DECLARE @killspid int , @CMD nvarchar (20) DECLARE cur_kill CURSOR FOR SELECT SP.spid, SD.name

FROM SYSPROCESSES SP JOIN SYSDATABASES SD ON SP.dbid = SD.dbid WHERE SD.name = 'MyDBName'

OPEN cur_kill FETCH NEXT FROM cur_kill INTO @killspid ,@CMD WHILE @@FETCH_STATUS = 0 --------------------------

BEGIN SET @CMD = 'KILL ' + CAST ( @killspid as Varchar(3)) EXECUTE sp_executesql @CMD

PRINT CAST ( @killspid as Varchar(3)) + ' SPID KILLED ' FETCH NEXT FROM cur_kill INTO @killspid , @CMD

END CLOSE cur_kill DEALLOCATE cur_kill

go

ALTER DATABASE [MyDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [MyDBName] FROM DISK = N'\\MyServer\LogShipping\MyDBName_backup.bak' WITH FILE = 1,

MOVE N'MyDBName_Data' TO N'E:\Data\MyDBName_Data.MDF',

MOVE N'MyDBName_Log' TO N'E:\Data\MyDBName_Log.LDF',

NOUNLOAD, REPLACE, STATS = 10

GO

Then the DB runs the sp 'exec sp_change_users_login' to sync users on this new server.

Does anyone have any idea why this isn't working? Over the weekend I did a trace on thee jobs and I am reading through the results right now, but that might take a little bit more digging.