February 5, 2008 at 6:04 am
Hi All,
acutally these day i m working on a procedure that will execute on two different servers. Main procedure will run on Server A and will retun a script of creating/droping modificd procedure in a spcific time period. and then from Server B i will execute this varible and create those procedures.
it's working fine upto returning values into varible. but when i type execute it's give me error.. i hv printed the varible and error. plz find blow
here is the code that varible get but will problem
(1 row(s) affected)
(1 row(s) affected)
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempdb]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[tempdb]
GO
CREATE proc [dbo].[s_RestoreLatestBackup]
@dbname varchar(128) ,
@localBackupPath varchar(200) ,
@localDBPath varchar(200) ,
@localLogPath varchar(200) ,
@recipients varchar(128)
as
/*
exec s_TestRestore
@dbname = 'testdb' ,
@localBackupPath = 'c:\TestRestore\' ,
@localDBPath = 'c:\TestRestore\' ,
@localLogPath = 'c:\TestRestore\' ,
@recipients = 'myemailaddress'
*/
declare @cmd varchar(2000) ,
@filename varchar(128) ,
@S varchar(128) ,
@i int ,
@d datetime ,
@sql nvarchar(2000) ,
@StartDate datetime
select @StartDate = getdate()
-- get latest backup filename
select @cmd = 'dir /B ' + @localBackupPath + @dbname + '*.*'
create table a (s varchar(2000))
insert a exec master..xp_cmdshell @cmd
delete a
where s is null
or s not like '%full%'
select @filename = max(s) from a
-- Get files in backup
select @cmd = 'restore filelistonly from disk = ''' + @localBackupPath + @filename + ''''
create table files
(
lname varchar(128),
pname VARCHAR(128),
type varchar(10),
fgroup varchar(128),
size varchar(50),
maxsize varchar(50)
)
insert files
exec (@cmd)
GO
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 30
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 59
Incorrect syntax near 'GO'.
February 5, 2008 at 8:49 am
Its not letting you use the "GO" in your dynamic SQL script. Break the script into 2 pieces. Because your first script is an "IF EXISTS DROP" there is no reason that can't be run seperatly (still first but in a different variable and with a different EXEC command).
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply