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

Can't INSERT TableName EXEC sqlbackup anymore

viacoboniviacoboni Posts: 13
edited October 13, 2006 12:20PM in SQL Backup Previous Versions
Version 4.0 of SQL Backup returns multiple result sets. Because the second result set has two columns and the first result set has one column, THERE IS NO WAY to take the output of the procedure into a table.

This breaks multiple procedures I've created around sqlbackup.

Please convert the second result set to a one column result. This should be easy to do as the first column of the second result set are constant names.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    What were you previously parsing the output for? If it was to detect errors, you can now use the two output parameters that are returned, that provides the SQL Backup exit code and SQL Server error code (if any).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Petey,

    I appreciate the reply, but there are a host of reasons why I did it this way. Errors are one of them. Suppose using the new error code I get an error code. Wouldn't it be nice to show ALL the messages, including the detailed error? What if I want to see how long it took to backup a database? By returning two incompatible result sets you make that impossible for me to do using the stored procedure.

    Please fix this problem. Thanks.

    Vince
  • Options
    peteypetey Posts: 2,358 New member
    Making the change might break other users' code who expect the 2 datasets.

    Given that the xproc in SQL Backup v3 was essentially calling the command line interface to perform it's work, you could get similar results by using xp_cmdshell to run sqlbackupc e.g.

    xp_cmdshell 'C:\Program Files\Red Gate\SQL Backup\sqlbackupc -sql "BACKUP DATABASE x TO DISK = ''H:\Backups\x.sqb'' " '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Petey,

    OK, how about this? Add another column to the first result set that is compatible with the second column in the second result set. That will allow the output to be stored in a table once again. Nobody else's code should break.

    Whaddya think?
  • Options
    I agree, it would be very helpful if the 2 result sets from the sqlbackup extended stored procedure could be captured into a single table. Or if someone could tell me how I could capture the separate result sets into separate tables that would be helpful as well.
Sign In or Register to comment.