Link to home
Start Free TrialLog in
Avatar of John Porter
John PorterFlag for United States of America

asked on

Run tsql sp_attach_db SPOC from C#

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!
Avatar of Jarrod
Jarrod
Flag of South Africa image

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

Avatar of John Porter

ASKER

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 ?
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

ASKER CERTIFIED SOLUTION
Avatar of Jarrod
Jarrod
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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??
Your explanations make sense but I am still getting same error message...