Hello,
I needed to look at some .BCP files, so I thought I'd create a small tool for that.
I decided to create a temporary DB, attach empty mdf, ldf files with predefined structure and then insert the BCP files, so I can view the data as DB tables .
Here is the procedure I follow:
1) I drop the existing (if exists) DB ("Drop DBName") - to be prepared for the next set of .BCP files.
2) Then I create a new DB using sp_attach_db,
3) Then I populate the db via "Bulk Insert".
1st time this works just fine, but when I try to execute step 1 (drop DB) after step 3 I get a message that there is still open connection and the DB can't be dropped.
Here I can do two different things:
1) Find the SPID of the open connection to my DB and KILL the process or
2) Instead of killing the process, set the DB to "SINGLE_USER WITH ROLLBACK IMMEDIATE" and then drop the DB.
In both cases I was able to Drop the DB and attach the new one, however the Bulk Insert (via ExecuteNonQuery() ) for some reason closes the current connection and brakes with the "System Error" message.
I simulated this procedure on Query Analyser and on SQL Server Studio Express, but everything worked just fine there.
The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.
To make it short, here is the SQL Query procedure (which worked, though):
USE Master
if exists
(select name
from master.dbo.sysdatabases
where has_dbaccess(name)=1 and name='MyDB')
BEGIN
Drop database MyDB
END
exec sp_who2
-- Before the next command I have to manually restore the .ndf, .ldf files, deleted by the "Drop DB" command
sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'D:\CurrSchema\DB_Patient.ndf',
@.filename2 = N'D:\CurrSchema\DB_Definitions.mdf',
@.filename3 = N'D:\CurrSchema\DB_Log.ldf',
@.filename4 = N'D:\CurrSchema\DB_Data.ndf'
exec sp_who2
-- WAITFOR DELAY '00:00:04'
-- need to wait manually here. Waitfor won't work with "USE DB"
USE MyDB
BULK INSERT CAL
FROM '\\srv\D$\CurrFiles\CAL.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)
BULK INSERT CAP
FROM '\\srv\CurrFiles\CAP.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)
exec sp_who2
Has anybody seen something like this?
Thank you.
Haven't seen it personally. However, I suggest you insert a 'go' between each command to seperate them into batches. Also, be sure to change the database to 'master' for your connection before sending/executing 'drop db'.|||
Yes, Thank you - you were right. I did not need the 'GO', but the change to 'master' was the key. I should have been more careful when using somebody else's code - no matter how encapsulated, it's never context-free - in my case I had to add context switching with 'USE master'.
George.
|||
Hi... you mentioned you had the below source sample...
The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.
Can you please post or sent to billbbellevue@.hotmail.com
Kind regards,
Bill Bezick
