Recent

Author Topic: MSSQL ODBC how error - waiting for database restore to finish?  (Read 3234 times)

ccrause

  • Hero Member
  • *****
  • Posts: 845
I'm porting a Delphi program which used Ado components to drop/restore/rename a database.  I'm busy moving this over to Lazarus, where I'm using ODBC to connect to a MSSQL localdb instance. I am trying to restore a database from file, followed by an alter statement to rename the database.  The alter statement raises an error: Database X cannot be opened. It is in the middle of a restore;

My suspicion is that the restore operation runs asynchronous, so that the server is still busy with the restore operation when the next statement is executed.  Not sure how to wait until the restore is completed before executing the next statement - this was not an issue when I used Delphi's Ado components.  Any ideas?

For reference the bit of code causing the error:
Code: Pascal  [Select][+][-]
  1.   SqlTransaction.EndTransaction;
  2.   ODBCConnection.Close();
  3.   ODBCConnection.Params.Values['AUTOCOMMIT'] := '1';
  4.   ODBCConnection.Connected := true;
  5.   SqlTransaction.StartTransaction;
  6.  
  7.   ODBCConnection.ExecuteDirect('restore database '+InternalUnifacName+
  8.                                ' from disk = ''' + UnifacDBFile +
  9.                                ''' with replace , move '''+InternalUnifacName+
  10.                                ''' to ''' + APEDpath + InternalUnifacName+'.mdf'' , move '''+
  11.                                InternalUnifacName+'_log'' to ''' +
  12.                                APEDpath + InternalUnifacName + '_log.ldf''');
  13.   SqlTransaction.Commit;
  14.   Memo1.Lines.Add('Restore completed.');
  15.   ODBCConnection.EndTransaction;
  16.   ODBCConnection.Close();
  17.  
  18.   ODBCConnection.Connected := true;
  19.   ODBCConnection.StartTransaction;
  20.   ODBCConnection.ExecuteDirect('alter database '+InternalUnifacName+' modify name = '+ UnifacTargetName);/code]

Thaddy

  • Hero Member
  • *****
  • Posts: 14204
  • Probably until I exterminate Putin.
Re: MSSQL ODBC how error - waiting for database restore to finish?
« Reply #1 on: May 22, 2018, 12:16:03 pm »
You probably ran into a true hardware failure? Check your disk(s). If it supports S.M.A.R.T. that should be easy.
Also note  restoring is always a - fully! - blocking operation. But you know that.

https://en.wikipedia.org/wiki/S.M.A.R.T.
« Last Edit: May 22, 2018, 12:18:08 pm by Thaddy »
Specialize a type, not a var.

tudi_x

  • Hero Member
  • *****
  • Posts: 532
Re: MSSQL ODBC how error - waiting for database restore to finish?
« Reply #2 on: May 22, 2018, 12:22:02 pm »
i understood the database is local to the app. based on this:
a. perform restore from command line using TProcess
b. parse result. move to SQL based on this result.
Lazarus 2.0.2 64b on Debian LXDE 10

Thaddy

  • Hero Member
  • *****
  • Posts: 14204
  • Probably until I exterminate Putin.
Re: MSSQL ODBC how error - waiting for database restore to finish?
« Reply #3 on: May 22, 2018, 12:29:32 pm »
That's not what the error message indicates..... Sigh. Try to be helpful please. He is a - very - experienced programmer.
Specialize a type, not a var.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

ccrause

  • Hero Member
  • *****
  • Posts: 845
Re: MSSQL ODBC how error - waiting for database restore to finish?
« Reply #5 on: May 22, 2018, 04:06:40 pm »
You probably ran into a true hardware failure? Check your disk(s). If it supports S.M.A.R.T. that should be easy.
Also note  restoring is always a - fully! - blocking operation. But you know that.
I can still run the original Delphi program, which uses exactly the same sql scripts with TADOConnection + TADOCommand, without errors - so no hardware problems.  I therefore conclude that either:
  • I've incorrectly configured/used TODBCConnection and/or TSQLTransaction and/or TSQLQuery, leading to the ExecuteDirect statement returning immediately without waiting for ODBC to signal completion of the statement
  • There is a limitation/quirk somewhere between FPC's (TODBCConnection and friends) handling of sql management statements and MSSQL's ODBC driver behaviour
I have also tried using TSQLQuery.ExecSql command, with a similar error.

Or it's maybe this: https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state

Use "With Recovery"?
I doubt that since the same sql script works both in sqlcmd/osql utility and the old Delphi program.  I've tried adding recovery but get the same error.

ccrause

  • Hero Member
  • *****
  • Posts: 845
Re: MSSQL ODBC how error - waiting for database restore to finish?
« Reply #6 on: May 23, 2018, 12:45:01 pm »
I've worked around the ODBC problem by going the ADODB route for the restore/alter sequence:
Code: Pascal  [Select][+][-]
  1. var
  2.   s: widestring;
  3.   adocon, adocmd: OleVariant;
  4. begin
  5.   adocon := CreateOleObject('ADODB.Connection');
  6.   adocon.Open('Provider=SQLNCLI11;Server=(localdb)\' + LocalInstanceStr +
  7.   ';Trusted_Connection=yes;Initial Catalog=master');
  8.  
  9.   adocmd := CreateOleObject('ADODB.command');
  10.   adocmd.ActiveConnection := adocon;
  11.   s := 'restore database '+InternalUnifacName +
  12.        ' from disk = ''' + UnifacDBFile +
  13.        ''' with replace, move ''' + InternalUnifacName +
  14.        ''' to ''' + APEDpath + InternalUnifacName+'.mdf'' , move ''' +
  15.        InternalUnifacName+'_log'' to ''' +
  16.        APEDpath + InternalUnifacName + '_log.ldf'';';
  17.   adocmd.CommandText := s;
  18.   adocmd.Execute;
  19.  
  20.   s := 'alter database '+InternalUnifacName+' modify name = '+ UnifacTargetName+';';
  21.   adocmd.CommandText := s;
  22.  
  23.   adocmd.Execute;
  24.   adocmd := null;
  25.   adocon := null;

I therefore conclude that either:
  • I've incorrectly configured/used TODBCConnection and/or TSQLTransaction and/or TSQLQuery, leading to the ExecuteDirect statement returning immediately without waiting for ODBC to signal completion of the statement
  • There is a limitation/quirk somewhere between FPC's (TODBCConnection and friends) handling of sql management statements and MSSQL's ODBC driver behaviour
This OLE route via ADODB works, but I would still like to know what is causing my original problem... %)

ccrause

  • Hero Member
  • *****
  • Posts: 845
Re: MSSQL ODBC how error - waiting for database restore to finish?
« Reply #7 on: May 23, 2018, 12:56:35 pm »
i understood the database is local to the app. based on this:
a. perform restore from command line using TProcess
b. parse result. move to SQL based on this result.
This is indeed another option, in other parts of this program I do use TProcess to call various batch file scripts (but these scripts are executing proprietary update sequences by calling various executable files).  My preference is however to keep as much of the code contained inside my program as I possibly can, such as the sql related updates.

 

TinyPortal © 2005-2018