MSSQL Backup with progress

This is an example procedure to make a backup of a SQL server database.

It works synchronous and will return intermediate result messages ("10% completed")

 Procedure TestESQLBackup    
     String sConnectString
     String sDriverId
     
     Integer iNumCols       
     Integer iNoOfMessages  
     Integer iRowCount              
     Integer iMessage
     String  sMessage
     
     Handle hdbc
     Handle hstmt
     String  sSQLQuery
 
 
     Integer iNextSet
     Integer iColumns
     String  sCol1
     String  sCol2
     Integer iFetchResult
     
     Get SQLConnectionId of ghoConnection "MssqlOrderID" to hdbc
     
     If (hdbc <> 0) Begin
         Get SQLOpen of hdbc to hstmt
         If (hstmt <> 0) Begin
             Move "BACKUP DATABASE [OrderDB] TO  DISK = N'C:\Temp\OrderDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'OrderDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 " to sSQLQuery
             //Move "BACKUP DATABASE [OrderDB] TO  DISK = N'C:\Temp\OrderDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'OrderDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD " to sSQLQuery
         
             Send SQLExecDirect of hstmt sSQLQuery
             Repeat
                 Get SQLStmtAttribute of hStmt SQLSTMTATTRIB_COLUMNCOUNT to iNumCols
                 Get SQLStmtAttribute of hStmt SQLSTMTATTRIB_NUMMESSAGES to iNoOfMessages
                 Get SQLStmtAttribute of hstmt SQLSTMTATTRIB_ROWCOUNT    to iRowCount
                 
                 Showln (SFormat("SQLSTMTATTRIB_COLUMNCOUNT returned %1",iNumCols))
                 Showln (SFormat("SQLSTMTATTRIB_NUMMESSAGES returned %1",iNoOfMessages))
                 Showln (SFormat("SQLSTMTATTRIB_ROWCOUNT    returned %1",iRowCount))
                 
                 If (iNoOfMessages > 0) Begin
                     For iMessage from 1 to iNoOfMessages
                         Get SQLGetMessage of hStmt iMessage to sMessage
                         Get RemoveComponentIdentifier of hStmt sMessage to sMessage
                         Showln (Sformat("  SQLGetMessage iMessage %1 returned [%2]",iMessage,sMessage))
                     Loop
                 End
            
                 Get SQLNextResultSet of hstmt to iNextSet
             Until (iNextSet = 0)
             
             Send SQLClose of hStmt
         End
         Send SQLDisconnect of hDbc
     End
     
 End_Procedure

Notes:

• If you specify the STATS=10 option you will get intermediate result messages:

   "10 percent complete", etc

• If you do not specify STATS = 10, SQLExec will only return when the backup is complete.
• The call to SQLNextResultSet is essential for this to work. If you omit that it will not work.

Best regards,

Data Access Worldwide

Martin Moleman

taken from: https://support.dataaccess.com/Forums/showthread.php?62564-How-to-test-if-long-running-SQL-statement-is-finished&p=332822#post332822