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:
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;