* * *

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

ccrause

  • Full Member
  • ***
  • Posts: 129
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: 6918
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 »
Ada's daddy wrote this:"Fools are my theme, let satire be my song."

tudi_x

  • Hero Member
  • *****
  • Posts: 524
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 1.8.4 64b on MX Linux "Horizon"

Thaddy

  • Hero Member
  • *****
  • Posts: 6918
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.
Ada's daddy wrote this:"Fools are my theme, let satire be my song."

Zvoni

  • Jr. Member
  • **
  • Posts: 59
People call me crazy because i'm jumping out of perfectly fine airplanes.
I say you're crazy not to!
--------------------------------------------------------------------------------------------------
For health reasons i try to avoid reading unformatted Code

ccrause

  • Full Member
  • ***
  • Posts: 129
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

  • Full Member
  • ***
  • Posts: 129
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

  • Full Member
  • ***
  • Posts: 129
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.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus