How can we help you today? How can we help you today?

Name of input parameter of stored procedure?

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?
DavidCook
0

Comments

1 comment

Sort by
  • petey
    The input parameter is not named, and is always expected to be in the 1st position.

    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 = Nothing
    petey
    0

Add comment

Please sign in to leave a comment.