August 19, 2010 at 6:30 pm
Hello,
I have an app written in VB6 using the SQL 2005 Express DB engine. I am more familiar with Access than SQL, so hopefully I am explaining this correctly.
On a customer computer (deployed copy of app), our software, by default, accesses the SQL databases on the local C drive. When I try copying these same databases to a network drive and have our app point to that new location to open those files, I get the: "An attempt to attach an auto-named database for file [db name] failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." error, and of course the app must close.
I realize there is an instance of this same database name in the server, and on my development machine I can use the SQL Server Management utility to detach this instance. But on a customer's computer, I don't want to have the user do this, and we don't even install the Management Utility. Can you point me to the topic or show me an example of the syntax I would need to "detach" a database through VB code? This should allow me to attach to the database in the new location.
This is the connection string we are using now
DBConnStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"AttachDBFileName=" & sMasterDB & ";Data Source=.\SQLEXPRESS"
I noticed after searching for answers that this is a fairly simple connection string, so feel free to advise on any improvements here as well.
Thanks in advance for any help any of you experts have to offer!
- Scott
August 20, 2010 at 3:23 pm
1) Databases cannot be located at UNC path when attaching SQL Server will not allow it.
2) After installing SQL Server Express on the computer you can just use following connection string, I seen a .Net buddy use it. I didn't like it but it worked nicely for him.
"Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;"
3) If you still wish to attach, try the following...
[font="Courier New"]CREATE DATABASE [DBName] ON
( FILENAME = N'C:\DBName.mdf' ),
( FILENAME = N'C:\DBName.ldf' )
FOR ATTACH
GO[/font]
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 23, 2010 at 1:58 pm
Thanks very much Mohit!
I remember seeing that Trusted_Connection option in the forums. I think that will be fine with our files since they all have very unique names.
Will the same "Attach" syntax that you show here work if I substitute in "Detach"? Or is there a separate command to detach?
Thanks again for your help.
August 23, 2010 at 7:51 pm
A bit different...
EXEC sp_detach_db 'DBName'
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
September 2, 2010 at 3:59 pm
Thanks again for you help with this Mohit.
I am having trouble finding the exact syntax on the sp_detach_db command in VB6. Here is what I am trying in a test app I wrote.
This is the connection string.
DBConnStr = "Provider=SQLNCLI; Server=.\SQLEXPRESS;" & _
"AttachDBFileName=" & sTemp & ";Database=" & sTemp & ";Trusted_Connection=Yes"
dbTemp.Open DBConnStr
This is the detach command.
sSQL = "sp_detach_db '" & sTemp & "'"
dbTemp.Execute sSQL
This is the error I receive.
Cannot detach the database 'C:\WINBIDPRO\MASTER.MDF' because it is currently in use.
I have to open a connection to the database in order to execute an SQL command, so I must have something wrong in my syntax.
What do you think, am I completely clueless! 🙂
Thanks again Mohit
September 2, 2010 at 4:10 pm
scott 52869 (9/2/2010)
Thanks again for you help with this Mohit.I am having trouble finding the exact syntax on the sp_detach_db command in VB6. Here is what I am trying in a test app I wrote.
This is the connection string.
DBConnStr = "Provider=SQLNCLI; Server=.\SQLEXPRESS;" & _
"AttachDBFileName=" & sTemp & ";Database=" & sTemp & ";Trusted_Connection=Yes"
dbTemp.Open DBConnStr
This is the detach command.
sSQL = "sp_detach_db '" & sTemp & "'"
dbTemp.Execute sSQL
This is the error I receive.
Cannot detach the database 'C:\WINBIDPRO\MASTER.MDF' because it is currently in use.
I have to open a connection to the database in order to execute an SQL command, so I must have something wrong in my syntax.
What do you think, am I completely clueless! 🙂
Thanks again Mohit
your syntax is correct; but just need to understand what is going on. Since you are using AttachDBFileName in your connection string, your database by default is detached. So you don't need to remove it to, just copy the mdf/ldf file whereever you wish.
Now if you actually attach the database to local SQL Server using "CREATE DATABASE ... FOR ATTACH" then you can use that "sp_detach_db" by creating connection to master database.
Hope that shed some light...
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
September 2, 2010 at 5:04 pm
We never actually used the "Create Database ... For Attach" command on any of our databases. We use the same "AttachDBFileName" argument in the connection string every time we connect to one of these databases.
I know that if I open my SQL Server Management Studio Express program, it shows the database I have connected to in the Summary - Databases window. If I right click and "Detach" the database I am testing this with, I know I would be able to connect to a different database with the same name on a different drive.
If I don't detach the test database first with the Management software, I get this error:
"An attempt to attach an auto-named database for file Z:\Host C\WinBidPro\Master.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
I'm not too clear on the "auto-named" reference, but I am guessing it knows that the database "name" was created when this file was opened the very first time, because it was copied from a blank template database before it was populated.
I hope I didn't just confuse the issue, but this is pretty much what is happening.
Thanks again for the quick responses.
September 7, 2010 at 2:10 pm
Hi Mohit,
I apologize for taking you down the wrong path on all of this. It appears that my problem isn't "attach/detach" related, it is really a problem with connecting to a database on a network path.
I have noticed that if I copy a database and its log file to another folder, or even another local drive on my computer, I am able to connect to that database with no problem. The problem occurs when I try to connect to a database that has been copied to a network drive. My development machine is a WinXP virtual machine on a Windows 7 host. If I copy a database to a folder in the host OS, I am unable to open it, and I receive the error I listed in the previous post.
It looks like I have more to learn about working with databases stored on a network path. Can you suggest a link that may straighten me out with this subject?
Thanks again Mohit.
- Scott
September 7, 2010 at 6:53 pm
SQL Server will not let you access files over UNC path; it must be local on the server.
If you need to access it remotely better to attached it SQL Server and access it via port 1433. This is possible for SQL Server Express edition, however you will need to enable remote connection for it to work.
Cheers.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply