May 11, 2015 at 10:50 am
Hello Experts,
I wrote the below script to move the SP from master to another db, but does not seem like working.
Any suggestion, how to fix this??
declare @sql3 nvarchar(max), @name3 sysname = 'NEW_DB'
SET @sql3 = (Select definition from sys.sql_modules where object_id = object_id('usp_Build_DB'));
set @sql3 = N'execute ' + QUOTENAME(@name3) + N'.dbo.sp_executesql N''' + @sql3 + ''''
Thanks.
May 11, 2015 at 11:08 am
What's not working? Why aren't you using parameters to execute sp_executesql? Why aren't you executing the dynamic code or at least printing it?
May 11, 2015 at 12:43 pm
I missed to copy the EXEC( ) statement....Anyways....found something strange..
It does not create any proc, instead throw the below error msg..
Msg 111, Level 15, State 1, Procedure usp_dbaadministration, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
use master
go
declare
@isql varchar(8000),
@dbname varchar(64)
SET @dbname = 'dest_db'
declare c1 cursor for
SELECT modz.definition
FROM sys.sql_modules modz
LEFT OUTER JOIN sys.objects objz
ON modz.object_id = objz.object_id
WHERE type_desc IN ('SQL_STORED_PROCEDURE') and objz.name = 'usp_dbaadministration'
open c1
fetch next from c1 into @isql
While @@fetch_status <> -1
begin
select @isql = 'USE ' + @dbname + ';' + @isql
print @isql
exec(@isql)
fetch next from c1 into @isql
end
close c1
deallocate c1
go
Thanks.
May 11, 2015 at 12:49 pm
That's because you're executing the following in the dynamic code.
USE SomeDB;
CREATE PROCEDURE usp_Something
AS
...
As the error states, the CREATE (or ALTER) statement should be the first (and only) statement in a batch.
You were right before on using sp_executesql to define the correct database.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply