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")

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

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