run_script_remote

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.run_script_remote') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.run_script_remote
GO

CREATE PROCEDURE run_script_remote
     @name VARCHAR(30) = ' ',
     @dbNAme VARCHAR(30) = ' ',
     @source VARCHAR(30) = ' ',
     @sourcePassword VARCHAR(30) = ' ',
     @path VARCHAR(50) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @ext VARCHAR(3) = 'out',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @scriptName VARCHAR(30),
        @myName VARCHAR(30),    
        @scriptFile VARCHAR(35),
        @outFile VARCHAR(35),
        @CMD VARCHAR(255),
	@CMDstatus INT 

/*
   Procedure run_remote_script will run any script
   in the admin subsystem's scripts table that is
   prefixed with 'script_' on any server with an 
   adequate trust relationship to the admin SQL
   Server. The scripts table nameing convention is 
   assumed.
*/
        
IF     (@name = ' ') 
    OR (@dbName = ' ')
    OR (@source = ' ') 
    OR (@path NOT LIKE '\\%')
  RETURN 1

SELECT @myName = 'run_script_remote'
SELECT @scriptName = 'script_' + @name
SELECT @scriptFile = '\' + @name + '.sql'
SELECT @outFile = '\' + @name + '.' + @ext 

/*
   move the script to flat file in a folder
   with read/write permissions granted to
   the login account of the admin SQL Server
   and the source SQL Server 
*/
EXEC hold_semaphore 'textreader'
  TRUNCATE TABLE textreader
    INSERT textreader
    SELECT line 
    FROM scripts 
    WHERE name = @scriptName
    ORDER BY ID
    IF @debug = 'true' SELECT * FROM textreader
    SELECT @CMD = 'bcp admin..textreader out ' + @path + 
                  @scriptFile + ' -c -Usa -P' + @saPassword + 
                  ' -S' + @@SERVERNAME
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
  TRUNCATE TABLE textreader
EXEC release_semaphore 'textreader'  

/*
   run the script on the source SQL Server
*/
SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
              ' -d' + @dbName + ' -i"' + @path + @scriptFile + '" -o' +
              @path + @outFile + " -n"
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) return @CMDstatus

RETURN