What are the challenges you face when working across database platforms? Take the survey
Options

Name of input parameter of stored procedure?

DavidCookDavidCook Posts: 8 Bronze 2
edited June 24, 2007 11:56PM in SQL Backup Previous Versions
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?

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.