June 23, 2009 at 8:15 pm
I am trying to do the following in sql job but it givean error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'IMMEDIATE'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'else'.
if exists(select name from sysdatabases where name='test')
begin
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB test,test_Old
GO
ALTER DATABASE test_new SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB test_new,test
Go
ALTER DATABASE test SET MULTI_USER
GO
end
else 0
June 23, 2009 at 8:25 pm
The first GO ends your batch. That is what is causing your problem. Try this:
DECLARE @SQLCmd nvarchar(4000);
set @SQLCmd = '
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB test,test_Old
GO
ALTER DATABASE test_new SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB test_new,test
Go
ALTER DATABASE test SET MULTI_USER
GO
';
if exists(select name from sysdatabases where name='test')
begin
exec (@SQLCmd)
end
else 0
June 23, 2009 at 8:26 pm
The first GO ends your batch. That is what is causing your problem. Try this:
DECLARE @SQLCmd nvarchar(4000);
set @SQLCmd = '
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB test,test_Old
GO
ALTER DATABASE test_new SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB test_new,test
Go
ALTER DATABASE test SET MULTI_USER
GO
';
if exists(select name from sysdatabases where name='test')
begin
exec (@SQLCmd)
end
else 0
June 23, 2009 at 8:34 pm
i get this error
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '0'.
June 23, 2009 at 8:42 pm
Well, its your code. What was else for? If you don't need it, drop it.
June 23, 2009 at 9:24 pm
but still i get error at GO, if that ios removed error is at SP_RENAMEDB
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'go'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'go'.
Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'go'.
or
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'SP_RENAMEDB'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'SP_RENAMEDB'
June 23, 2009 at 9:55 pm
You may want to spend some time reading BOL (Books Online, the SQL Server Help System).
Change SP_RENAMEDB to EXEC SP_RENAMEDB
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply