February 26, 2003 at 11:35 am
Is it possible to PROGRAMATICALLY copy a stored procedure from one database to another?
Thanks in advance,
Billy
Edited by - bp on 02/26/2003 11:37:55 AM
February 26, 2003 at 12:19 pm
Billy
I think there are several ways. One would be to go through Import wizard, create a job from it and call that job programmatically.
Another way is to use sp_helptext to get source code of the procedure and execute it on another server.
The most elegant way is probably to use SQL-DMO.
Etc.
Michael
February 26, 2003 at 12:34 pm
Thanks Michael.
The sp_helptext method is not an option because the proc is over 8000 chars long (the entire create proc script will not fit in the varchar(8000) I declare for the EXEC statement).
I will look into the sql-dmo option.
Billy
February 27, 2003 at 2:27 am
You could consider creating a DTS Package which transfered the Stored Procedure(s) with a Transfer SQL Objects task. The DTS Package could then be programmatically called with xp_cmdshell.
February 28, 2003 at 3:17 am
I always script my procs in text files and use ISQL to load/update them in one or more DB's. This way I never have to copy procs and also have documentation / backup.
Far away is close at hand in the images of elsewhere.
Anon.
February 28, 2003 at 10:23 am
I do the same thing as David Burrows describes. I script all tables, vies, indexes, triggers, procedures, UDF, UDT, etc, etc. and store it all in VSS.
March 3, 2003 at 9:19 am
The source of code : http://www.SQLServerCentral.com
/*******************************************************************************
Written By : Simon Sabin
Date : 25 October 2002
Description : Copies a stored procedure from one database to another
:
History
Date Change
------------------------------------------------------------------------------
25/10/2002 Created
*******************************************************************************/
USE master
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp_CopyStoredprocedure')
DROP PROCEDURE sp_CopyStoredprocedure
GO
CREATE PROCEDURE dbo.sp_CopyStoredprocedure
(
@procedure sysname
,@sourceDb sysname
,@targetdb sysname
)
AS
DECLARE @lines int
DECLARE @line int
DECLARE @vline varchar(4)
DECLARE @objectid varchar(10)
DECLARE @DECLARE varchar(8000)
DECLARE @SELECT varchar(8000)
DECLARE @EXECUTE varchar(8000)
DECLARE @PRINT varchar(8000)
DECLARE @sql nvarchar(4000)
SET @sql = 'USE ' + @sourcedb + '
SELECT @objectid = id, @lines = count(*)
FROM ' + @sourcedb + '.dbo.syscomments
WHERE id = object_id(@procedure)
GROUP BY id'
EXEC sp_executesql @sql, N'@objectid int OUTPUT, @lines int OUTPUT, @procedure sysname', @objectid OUTPUT, @lines OUTPUT, @procedure
SELECT @objectid , @lines , @procedure, @sql
SET @DECLARE = ''
SET @SELECT = ''
SET @EXECUTE = ''
SET @PRINT = ''
SET @line = 0
WHILE @line <= @lines
BEGIN
SET @vline = @line
SET @DECLARE = @DECLARE + char(10) + 'DECLARE @line' + @vline + ' nvarchar(4000)'
SET @SELECT = @SELECT + char(10) + 'SELECT @line' + @vline + ' = text FROM ' + @sourcedb + '.dbo.syscomments WHERE colid = ' + @vline + ' AND id = ' + @objectid
SET @EXECUTE = @EXECUTE + '+ @line' + @vline
SET @PRINT = @PRINT + char(10) + 'PRINT @line' + @vline
SET @line = @line + 1
END
SET @DECLARE = @DECLARE + CHAR(10)
SET @SELECT = @SELECT + CHAR(10)
SET @EXECUTE = 'USE ' + @targetDb + char(10) + 'EXECUTE (''''' + @EXECUTE + ')'
SET @sql = 'IF EXISTS (SELECT 1 FROM ' + @targetDB + '.dbo.sysobjects WHERE name = @procedure )
DROP PROCEDURE ' + @procedure
exec sp_executesql @sql, N'@procedure sysname ', @procedure
PRINT @DECLARE
PRINT @SELECT
PRINT @EXECUTE
PRINT @PRINT
EXECUTE (@DECLARE + @SELECT + @EXECUTE + @PRINT)
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply