John Porter
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(LoginSQLClie nt.Connect ion);
//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!
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(LoginSQLClie
//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\
//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!
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!
"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 ?
MyDataBase exist at the time of running this command ?
ASKER
Yes it does. I can replace the tsql code "exec sp_attach_db 'MyDataBase','C:\Data\MyDa taBase.mdf ','C:\Data \MyDataBas e_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!
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:
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();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\\MyInsta nce;uid=sa ;pwd=passw ord;databa se=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!
("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\\MyInsta
}
}
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!
ASKER
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??
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??
ASKER
Your explanations make sense but I am still getting same error message...
there where some white spaces in the string.
Open in new window