sp_attachDB procedure for SQL Server Express

Question

0.00/5 (No votes)

See more:

Hi,
I am newbie to SQL Server Express. At the moment I am manually attaching the DBs using SSMS but I need to do it through code.
I have searched a lot over internet but could not really start anything.

I know there is a sp_attach procedure but donot know how to use it and then how to call that through my application's setup. How to use SQLDMO

Could any help be given to me please?

Thanks

Posted 3-Feb-12 21:12pm

Furqan Sehgal

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

strogg · Answer 1 · 2012-02-03T21:20:00

Solution 1

Do not use sp_attach_db(). As MSDN states it may be removed in the future.
Use CREATE DATABASE .... FOR ATTACH

SQL

CREATE DATABASE [C:\xyz.MDF] ON
( FILENAME = N'C:\xyz.mdf' ),
( FILENAME = N'C:\xyz_log.LDF' )
 FOR ATTACH

If you're using SSMS, before hitting the 'OK' button, check for the Script button on top. Click it & it'll show you the underlying script for the action. You can learn from it.

Posted 3-Feb-12 21:20pm

strogg

Comments

Furqan Sehgal 4-Feb-12 11:15am

But I donot know where to write the above code and then how to call it in my vb.net application.

strogg 5-Feb-12 3:51am

Here's one way to do it in vb.net

Dim con As New SqlConnection("Your connection string")
con.Open()
Dim cmd As SqlCommand = con.CreateCommand()
cmd.CommandText = "Your statement to execute"
cmd.ExecuteNonQuery()
con.Close()
You need to import System.Data.SqlClient.
If you do not know anything, this is not the way to learn. Read some concerned book or go through the MSDN and learn before you use.

Wendelius · Answer 2 · 2012-02-03T21:32:00

Solution 2

Depending on your needs, another option to attaching the database by creating it is to attach the database file during the connection using AttachDbFilename parameter.

This is commonly used for example if the SQL Server installation is local and for one user. See: http://www.connectionstrings.com/sql-server-2008[^]

Posted 3-Feb-12 21:32pm

Wendelius

Comments

Furqan Sehgal 4-Feb-12 11:16am

But just giving it in connection string does not attach it to the SQL Server instance

Wendelius 4-Feb-12 11:22am

That's one way of attaching the database, see Connecting to SQL Server Express User Instances (ADO.NET)[^]