how to attach db at app startup and detach and end

[macnux]

Hi
i have mdf file i want to attach this file to the sqlserver 2005 and when closing the program the database is detached
i connect to to master database and write the following

Dim path As String
path = My.Application.Info.DirectoryPath

pre_connect.ExecuteNonQuery("sp_attach_db 'xx','" & path & "\xx.mdf','" & path & "\xx_log.ldf';")

it gives me error message says:-
"Directory lookup for the file "C:\Documents and Settings\mokhtar\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplicati on1\bin\Debug\xx.mdf" failed with the operating system error 5(error not found)."

any help

thanks in advance.

[RhinoBull]

how to attach db at app startup and detach and end.

You don't "attach" database - you connect/disconnect to/from it.
What db do you use?

[jmcilhinney]

If you are supplying your own MDF file then you still do as RB says, and each time you connect and disconnect the MDF file is attached and detached. It's your connection string that controls that. See www.connectionstrings.com for the appropriate format for various scenarios. Here's the one for attaching an MDF file on connection:

Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

I think your SQL Server has to be setup to allow User Instances, which SQL Server Express is by default but SQL Server is not. I wouldn't stake my life on that but I think it's the case.

[szlamany]

SQL Server express does allow an "attach on open" - that is unique in and of itself.

Are you using EXPRESS?

[jmcilhinney]

SQL Server express does allow an "attach on open" - that is unique in and of itself.

Are you using EXPRESS?

Are you saying that SQL Server 2005 (other than Express) doesn't allow you to attach an MDF file? I wasn't aware of that. I've never actually tried so it never failed.

[RhinoBull]

I personally have a problem with termin "attach" as it makes no sense what so ever. How can you attach something that is so foreign?
What is it a luggage that you can attach on top of your car's roof?
You connect to database (or data file for that matter). Period. And I could care less how much MS is promoting new terminology.

Sorry guys.

[szlamany]

When you ATTACHDBFILENAME in SQL SERVER EXPRESS you are not specifying a LOGICAL database name - but instead a physical file. That file is both attached and the service is potentially started all in one call.

The local database can now be moved, copied, or e-mailed along with the application. At the new location, no additional configuration is needed to make it work. There are three main features that enable the Application User Instance support in SQL Server Express: the AttachDBFilename option in the connection string, the lack of a requirement to specify the logical database name, and the User Instance option.

All this without authorization rights to the MASTER DB. No prior connection is made.

That's very different then SP_ATTACH_DB called within the context of an open connection to the MASTER (or other appropriate) DB.

Also EXPRESS has AUTO-CLOSE enabled by default - so a DB attached on open is closed and detached on close (in most cases) and the service no longer holds disk-locks against it.

Auto-Close existed in SQL 2000 and is enabled by default in SQL Server Express. This feature releases the file locks on the user databases when there are no active connections to it. Thus, the database is ready to be moved or copied after the application that uses it is closed.

http://technet.microsoft.com/en-us/l.../ms345154.aspx

[RhinoBull]

That is a very bad architecture - are we going back to Access/DBase file based crap? I'm glad we mostly use Oracle.

[szlamany]

I personally have a problem with termin "attach" as it makes no sense what so ever. How can you attach something that is so foreign?
What is it a luggage that you can attach on top of your car's roof?
You connect to database (or data file for that matter). Period. And I could care less how much MS is promoting new terminology.

Sorry guys.

ATTACH makes sense in standard SQL when the service is up and running in a full time mode. That DB has references in the MASTER DB - it's a full time component of the server and tracked in all ways - maintenance plans - backups - etc.

The new model in EXPRESS allows a one-off DB to be attached to a non-running service at "run-time-of-the-client-app" without anything other then a reference to ATTACDBFILENAME in the connection string.

That just makes the whole service aspect and DB attachment aspect go away.

[RhinoBull]

Do you really believe in that architecture. Just don't answer with quotes from Microsoft please. Thanks.

[jmcilhinney]

The ability to attach a database to SQL Server already exists. Every database in SQL Server is an MDF file. You can attach an existing MDF database file to an SQL Server instance in SQL Management Studio by right-clicking the Databases node and selecting Attach. When you create a database in SQL Server all it does is create an MDF file and attach it. That file then stays permanently attached, unless you select it and Detach it. There is nothing foreign about attaching a database to SQL Server.

This new feature of attaching and detaching on an ad hoc basis is probably aimed specifically at ClickOnce deployment. It allows you to distribute an MDF file, complete with schema, with your app with your application and then attach at run time. This avoids the need to run code to access SQL Server and create a database, which ClickOnce installers can't do. It all makes perfect sense.

[szlamany]

Do you really believe in that architecture. Just don't answer with quotes from Microsoft please. Thanks.

I cannot read the mind of the OP - but if they are using EXPRESS and they want to attach on open and detach on close - then isn't that exactly what this new connection string method is all about?

Having a small app make use of the full server engine without the app or the user having to "know-about-the-server" is a huge advantage. No need to have Mgmt Studio installed. No need for "sql admin" rights to the the server process. No need to have access rights to the MASTER DB - or any DB for that matter other than the DB that's being attached on open.

Granted I don't need this type of architecture - as we only do large enterprise installs - but it does make perfect sense to me for a smaller single client app using the server instance installed locally on a workstation.

Having the detach on close makes the .MDF available for standard backup and copies - as opposed to it being open/locked by the server process - forcing sql-backup agents to be needed.

I don't see any huge downside to using this concept.

[macnux]

im using sql express 2005
i use this connection string
"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes ;"

but it gives me error message says:-
"Cannot open database "mydb" requested by the login. The login failed.
Login failed for user 'COMPUTER\username'.

any help?
thanks.

[jmcilhinney]

That was only an example. Do you have a file named 'c:\asd\qwe\mydbfile.mdf'? I'll wager not. You're supposed to put the path of YOUR file in there. Note also that you should avoid hard-coding a path if possible. If your MDF file will be in the same folder as your EXE then you would do something LIKE this:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes

If it's in the Database folder below that you'd do this:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\Database\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes

[macnux]

i successed in this
my problem now is after connecting and try to execute some sql statements on the database it gives me this error message:-

Cannot open database "mydb" requested by the login. The login failed.
Login failed for user 'COMPUTER\username'.

any help?
thanks in advance.

[jmcilhinney]

I think the error message speaks for itself: you haven't provided the appropriate login details. I suggest that you do as I suggested and visit www.connectionstrings.com and see what options you can set in the connection string.

[szlamany]

I just recently made a contribution to http://www.connectionstrings.com/?carrier=sqlserver2005

Trusted Connection from a CE device
Often a Windows CE device is not authenticated and logged in to a domain. To use SSPI or trusted connection / authentication from a CE device, use this connection string.

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

Note that this will only work on a CE device.

and I got listed in the Thank You page

...sorry for going off topic