Options
Name of input parameter of stored procedure?
DavidCook
Posts: 8 Bronze 2
Is the parameter passed into the SQLBackup stored procedure named? I'm attempting to use a product called "Automise" to do a bunch of things, including backup up a DB, and the available actions include running a stored procedure via ADO, but not by a script. The properties of the action require naming the parameter(s) of the stored procedure, and passing appropriate values to them. I can't seem to find in the documentation the name of the input parameter. Does it in fact have a name? (The documentation mentions -I, -U, -P and -SQL parameters, but doesn't name them)
As an example, if I have the following:
exec master..sqlbackup N'-SQL ""BACKUP DATABASE [H2LatrobeTest] TO DISK = [\\LHS-SQL01\SQLBackup\Test\H2LatrobeTest-20070624.sqb] WITH INIT, COMPRESSION = 1""'
I'm assuming everthing from the N to the =1""' inclusive is a text string passed into the stored procedure as a parameter, and that parameter will be named something such as CommandToRun.
Am I close to the mark here?
As an example, if I have the following:
exec master..sqlbackup N'-SQL ""BACKUP DATABASE [H2LatrobeTest] TO DISK = [\\LHS-SQL01\SQLBackup\Test\H2LatrobeTest-20070624.sqb] WITH INIT, COMPRESSION = 1""'
I'm assuming everthing from the N to the =1""' inclusive is a text string passed into the stored procedure as a parameter, and that parameter will be named something such as CommandToRun.
Am I close to the mark here?
Comments
Automise appears to use the sp_procedure_params_rowset function to retrieve the list of parameters. This information does not exist for extended stored procedures e.g. xp_sendmail. The 'Load procedures' option does not load extended stored procedures either, suggesting this action wasn't designed with extended stored procedures in mind.
I've tried using different actions to 'automise' SQL Backup, and the only one I got working satisfactorily was to use the 'Run script' function (Misc). The SQL Server actions all had one quirk or another, and could not return the exitcode/sql error codes correctly (or maybe it's just my inexperience with that app).
Here's a sample script I found workable:
Set conn = CreateObject("ADODB.Connection") conn.ConnectionString = "Driver={SQL Server}; Server=.; Database = master;" conn.Open Set recordset = conn.Execute("EXEC master..sqlbackup '-sql ""BACKUP DATABASE pubs TO DISK = [e:\temp\pubs.sqb] WITH INIT""'") If not recordset.eof Then rsArray = recordset.GetRows() For f = 0 to UBound(rsArray, 2) output = output + rsArray(0, f) + Chr(13) + Chr(10) Next Action.echo(output) End If Set recordset = recordset.NextRecordset If Not recordset.eof Then output = "" rsArray = recordset.GetRows() For f = 0 to UBound(rsArray, 2) select case f case 0: exitcode = rsArray(1, f) case 1: sqlerrorcode = rsArray(1, f) end select output = output + rsArray(0, f) + " = " + rsArray(1, f) + Chr(13) + Chr(10) Next Action.echo(output) End If If exitcode <> 0 or sqlerrorcode <> 0 Then ActionResult = false End If Set recordset = Nothing Set conn = NothingSQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8