Run tsql sp_attach_db SPOC from C#

Saxitalis
Saxitalis used Ask the Experts™
on
Hello Experts,

I have been successfully running tsql code from C# but when I try to run the sp_attach_db stored procedure it fails. The same code works fine when run in SQL Server 2005 Management Studio...

I tried this:

-----------------
                 CN = new SqlConnection(LoginSQLClient.Connection);
                    //Open connection and execute insert query.
                    CN.Open();
                    //Start Transaction
                    SqlTransaction transaction = CN.BeginTransaction();
                    try
                    {
                        //Set insert query
                        string qry = "exec sp_attach_db 'MyDataBase','C:\\Data\\ MyDataBase.mdf','C:\\Data\\ MyDataBase _log.ldf' ";
                       

                        //Initialize SqlCommand object for insert.
                        SqlCommand SqlCom = new SqlCommand(qry, CN);
                        SqlCom.Transaction = transaction;
                        //
                        SqlCom.ExecuteNonQuery();
                        transaction.Commit();
                        CN.Close();
                        CN.Dispose();
                       
                    }//end try
                    catch
                    {
                        transaction.Rollback();
                        CN.Close();
                        CN.Dispose();
                    }
-----------
I get mostly Synatx errors but this synatax works in Management Studio - Can anyone see why this is not working??

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this:

there where some white spaces in the string.
string qry = @"exec sp_attach_db 'MyDataBase','C:\Data\MyDataBase.mdf','C:\Data\MyDataBase_log.ldf'";

Open in new window

Author

Commented:
Thanks zadeveloper - I am no longer getting synatax error msgs but rather get this error:

"CREATE DATABASE statement not allowed within multi-satement transaction"

Any idea what is happening??


Thanks!
does the database:
MyDataBase exist at the time of running this command ?
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
Yes it does. I can replace the tsql code "exec sp_attach_db 'MyDataBase','C:\Data\MyDataBase.mdf','C:\Data\MyDataBase_log.ldf'" with other tsql code like "Update Table a set column a = 'newValue'" and it works fine.

It seem s like the difference is that I am not executing a stored procedure when it works. Could this be the reason for an error msg??

Thanks!
well spotted ! I think you are right

try:

CN = new SqlConnection(LoginSQLClient.Connection);
       //Open connection and execute insert query.
       CN.Open();
       //Start Transaction
       SqlTransaction transaction = CN.BeginTransaction();
       try
       {
           //Set insert query
           string qry = " '','','' ";

           //Initialize SqlCommand object for insert.
           using (SqlCommand SqlCom = new SqlCommand("sp_attach_db", CN))
           {
               SqlCom.CommandType = CommandType.StoredProcedure;

               SqlCom.Parameters.AddWithValue(@"@dbname", @"MyDataBase");
               SqlCom.Parameters.AddWithValue(@"@filename1", @"C:\Data\MyDataBase.mdf");
               SqlCom.Parameters.AddWithValue(@"@filename2", @"C:\Data\MyDataBase_log.ldf");

               SqlCom.Transaction = transaction;

               SqlCom.ExecuteNonQuery();
               transaction.Commit();
           }
       }//end try
       catch
       {
           transaction.Rollback();
       }

Open in new window

You will need to change the commandtype line to use the correct6 command type.

see the line:


SqlCom.CommandType = CommandType.StoredProcedure

Open in new window

Author

Commented:
Thanks again zadeveloper... I still can't get this to run-Get the same arror:
 ("CREATE DATABASE statement not allowed within multi-satement transaction")

The params look correct when I take a look at the Sproc in System Databases...
Can you get it to attach a DB using the above code? I'm not sure what I am doing wrong here??...

Here is what my Connection class looks like:
 public class LoginFirstSQLClient
    {
        public static string Connection
        {
            get { return "server=localhost\\MyInstance;uid=sa;pwd=password;database=Master"; }
        }
    }

Again, If I attach MyDatabase via Management Studio to localhost\\MyInstance (and change the Connection class to access MyDatabase instead of Master) , And run TSQL code through C# it works fine.

Any idea what I am not getting right??

Thanks!

Author

Commented:
I read somewhere that sp_attach_db is just a wrapper around the CREATE DATABASE command. The error returned here is referring to CREATE DATABASE...

I wonder if there is another system stored procedure that will attach a database. Can't you somehow use ALTER DATABASE to move and then re-attach a database??

Author

Commented:
Your explanations make sense but I am still getting same error message...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial