August 6, 2012 at 2:24 pm
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.
August 6, 2012 at 2:47 pm
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.
I can't find an * (asterick) in your code. Also, why are you manually killing spids when the ALTER DATABASE [MyDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE will terminate all connections to the database allowing your subsequent restore run?
August 6, 2012 at 3:42 pm
the alter db was an extra step I put in after the switch over to SQL Server 2008 when reading about getting exclusive access. There were a few applications that were hanging on after the pids were manually killed.
There isn't an asterisk in the code, which is why this issue is so confusing. I can't figure out why this is happening.
August 7, 2012 at 2:56 am
Your code is running fine i have checked in SQL 2008 as below
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 = 'MyDB'
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
--Sp_who2 'Active'
ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDB] FROM DISK = N'D:\All_File_Fullbackup.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
--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
56 SPID KILLED
57 SPID KILLED
63 percent processed.
100 percent processed.
Processed 168 pages for database 'MyDB', file 'MyDB_Primary' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG1_Dat1' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG1_Dat2' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG2_Dat3' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG2_Dat4' on file 1.
Processed 1 pages for database 'MyDB', file 'MyDB_log' on file 1.
RESTORE DATABASE successfully processed 201 pages in 0.104 seconds (15.099 MB/sec).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply