problem with a SP launched from ADODB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

problem with a SP launched from ADODB

Author

Topic

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2009-04-17 : 08:00:29

Hello,

I am used to lauch Stored Procedures in VB(Access) with a few statements like this:

quote:
Set Cnxn = New ADODB.Connection
CnxnToOpen = "DRIVER={SQL Native Client};...."
Cnxn.Open CnxnToOpen
sp = ... some stored procedure ...
Cnxn.Execute sp, , options

I have a Stored Procedure that duplicates a database by a backup and restore method.
It contains mainly such statements:

quote:
duplicateDbSP
...
SET @query = 'BACKUP DATABASE ' + @SourceDB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
...
SET @query = 'RESTORE DATABASE ' + @DestinationDB + ' FROM DISK = ' ...
EXEC (@query)
...

This duplicateDbSP works perfectly well from the MSSMS.
I get a clone from my database quite fast.

However, when I launch this duplicateDbSP Stored Procedure in VB frol ADODB, the restore operation blocks.
Looking in the MSSMS, I can see the newly created database, but it is marked as "(Restoring...)".
This "(Restoring...)" state is endless and I need to delete this new database manually.
Executing the same SP within the MSSMS works perfectly well.

How would you find what happens and how could it be solved?

Thanks for your suggestions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 11:21:05

can you try tracing with profiler and check if there's any difference b/w query fired from application and from query analyser. also are you using any transactions in application?

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2009-04-17 : 15:54:28

quote:
Originally posted by visakh16

can you try tracing with profiler and check if there's any difference b/w query fired from application and from query analyser. also are you using any transactions in application?

visakh16,

Thanks for your suggestions.
I used the Profiler for the first time (sql2005).
I could not see any indication in the traces, for each of the two methods.

However, I checked the existence of the files that should have been created when using ADODB.
All files were created correctly.
I could see the ".bak" file created by the backup step.
I could also see the ".mdf" and ".ldf" files created by the restore.
However, in the MSSMS the new database was indicated as "Restoring...",
and it is impossible to connect to it by any application.

By the way, the properties of the newly created clone cannot be read in MSSMS.
Instead it pops up this error message:

quote:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database 'bectOpComTest02DirTest' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=927&LinkId=20476
BUTTONS:
OK

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2009-04-17 : 16:04:32

visakh16,

I just realised a small difference in the traces.

By ADODB: ApplicationName = "Microsoft Office 2003"
By MSSMS: ApplicationName = "Microsoft SQL Server management Studio - Query"

In both cases: TextData = "cloneDb ...", where cloneDb is the name of my SP.

It is quite normal that "Microsoft Office 2003" is reffered to when using ADODB.
However, would that make a difference somewhere at the end of the process?
Maybe everything but the last step can be carried out from ADODB?
This is very unlikely.

I also remember (99% sure) that this SP did actually clone a db through ADODB when I did the very first tests.
Unfortunately, I cannot remember of any difference in the method.

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2009-04-17 : 16:11:33

visakh16,

I just realised there are two additional records in the trace when using MSSMS.
I can read these information:

quote:
EventClass: SQL:BatchStarting
TextData: update DiscoveryData set TimeOutCount = TimeOutCount+1 where ipAddressDecimal = '...'

quote:
EventClass: SQL:BatchCompleted
TextData: update DiscoveryData set TimeOutCount = TimeOutCount+1 where ipAddressDecimal = '...'

These records appeared when using ADODB.
I don't know if this is an indication.
Would you have an idea?

Would that simply be related to the MSSMS functioning, or would it be related to my problem, or is there no relation?

Thanks

PS: I think this is unrelated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:28:18

does your application have any other statements suceeding the above?

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2009-04-20 : 07:21:30

Thanks a lot visakh16 !
Your last question was very useful.

I now terminate the VBA sub in an another way:

quote:
...
Set rs = Cnxn.Execute(sp, , ADODB.CommandTypeEnum.adCmdText)
t1 = Timer()
While Timer() < t1 + 5
Wend
Cnxn.close

Doing so, this clone procedure works perfectly well.

It seems that the "RESTORE" operation cannot complete succefully if the connexion object in the VBA application is destroyed too early.

Would you have further suggestions to terminate the VBA code in a "more professional way"?

For example, the SP returns a message for every 10% completed.
Would it be possible to retrieve these messages and forward them to the end user?

Or at least, would it be possible to check the RESTORE process in such a way that I could destroy the connexion object at the right time.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources