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