SQL Server Calling a stored procedure from another stored procedure at the command line

I have been playing around with database backup automation scripts and in particular the one at this link:

http://support.microsoft.com/kb/2019698

  • SQL Query: Most recent server entry, by server and disk
  • Recursive Concatenation of Parent Elements
  • SQL Server Management Studio Schema View
  • Verify your SQL Server DML scripts
  • SQL Server - What happens when a row in a table is updated?
  • Date format issue while importing from a flat file to an SQL database
  • I got everything working fine and even added automated compression using 7zip, logging, and with the help of vbscript an email scheduled notification. However, even without all that, you can see this is a bit heavy. Its now easily reaching 400 lines of code.

    I am really not comfortable having all my stuff in one block like this and I want to separate it out. So I can have say a compression file called BackupCompress.sql, and an log file called BackupLogReport.sql all of which would be called from inside the main Backup.sql script.

    The Backup.sql script is in turn run from a Backup.bat file which is set to run in the scheduler.

    All of this works like a charm. But I am at a loss as to how to call BackupCompress.sql from within BackupLogReport.sql and pass in parameters and get a return value.

    In the Backup.bat file I use this command to spin everything up and pass parameters to it:

    SQLCMD -S %SQLDATABASE% -d master -i %BACKUP_FOLDER%\Backup.sql -v Pram1="%Pram1%"
    

    In the Backup.sql file I get those parameters simply by:

    DECLARE @Param1 NVARCHAR(256) = '$(Param)'
    

    from then on as my script runs it uses whatever I want to pass in.

    I tried using standard sql stored procedure logic to call another procedure like this:

    EXEC    BackupCompress.sql
        @AnotherParam = @Param1
    

    I also tried:

    EXECUTE sp_executesql BackupCompress.sql @Param1
    

    Finally I tried:

    SET @cmd = 'SQLCMD -S ' + @@ServerName + ' -d master -i $(BACKUP_FOLDER)\BackupCompress.sql -v Param1 = ' + @Param1
    EXEC xp_cmdshell @cmd, no_output
    

    but it doesn’t work and my files which were being compressed simply don’t get compressed. I get no error message. everything else continues to work fine.

    EDIT: I was getting an error message on the last one but I fixed it – however, I still don’t get my little zip file. I even put print’s into the file to see if it was actually be executed but it does not seem to be.

    EDIT2: Another option I have tried, almost works, but cant figure out how to pass parameters from within the sql file to the other file… As a result it generates an error saying it cant find the file as it’s treating the path as a literal string instead of the variable value I want to pass.

    :!!SQLCMD -S @@ServerName -d master -i @CFG_BACKUP_PATH\BackupCompress.sql -v Param1 = @Param1
    

    One Solution collect form web for “SQL Server Calling a stored procedure from another stored procedure at the command line”

    xp_cmdshell can return values. These values can be captured into a table variable that you could use to “see” the results, and perhaps determine where the problem lies:

    DECLARE @cmd VARCHAR(255);
    DECLARE @Param1 NVARCHAR(256) = '$(Param)';
    DECLARE @Results TABLE
    (
        ResultsText NVARCHAR(MAX)
    );
    
    SET @cmd = 'SQLCMD -S ' + @@ServerName + '-d master -i $(BACKUP_FOLDER)\$(BackupCompress.sql) -v Param1 = ' + @Param1;
    SET @cmd = 'DIR \';
    INSERT INTO @Results (ResultsText)
    EXEC xp_cmdshell @cmd;
    
    SELECT *
    FROM @Results;
    

    You need to ensure xp_cmdshell is enabled for the instance, by executing:

    EXEC sp_configure 'xp_cmdshell',1;

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.