How to Generate Script for a table using SMO when the table is in Transaction

Question

Hi,

 

I am facing a problem when i am trying to generate script for a table.The altered table is in transaction.

 

The following is the sample code:

 

Code Snippet

SqlConnection con = new SqlConnection("server=.;database=TestSMO;uid=sa;pwd=sa2005");

con.Open();

SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadUncommitted);

SqlCommand cmd = new SqlCommand("ALTER TABLE Table1 ADD Col3 varchar(20) null", con);

cmd.Transaction = trans;

cmd.ExecuteNonQuery();

 

// Get the bind token of the transaction related to the supplied connection

//

SqlCommand cmd1 = new SqlCommand("sp_getbindtoken", con, trans);

cmd1.CommandType = CommandType.StoredProcedure;

SqlParameter paramBindToken = new SqlParameter("@token", SqlDbType.VarChar, 255);

paramBindToken.Direction = ParameterDirection.Output;

cmd1.Parameters.Add(paramBindToken);

cmd1.ExecuteNonQuery();

string sBindtoken = paramBindToken.Value.ToString();

string sCmd = "";

if ((sBindtoken != null) && (sBindtoken.Length > 0))

{

// Bind to the transaction of the connection to the current

// connection

//

sCmd = "EXEC sp_bindsession '" + sBindtoken + "'";

//svr.ExecuteImmediate(sCmd, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sCmd.Length);

}

 

ServerConnection svrCon = new ServerConnection(".","sa","sa2005");

Server server = new Server(svrCon);

Database db = server.Databases["TestSMO"];

Console.WriteLine(db.GetTransactionCount(TransactionTypes.Both));

db.ExecuteNonQuery(sCmd,ExecutionTypes.Default);

Table table = db.Tables["Table1"];

ScriptingOptions options = new ScriptingOptions();

options.Default = true;

StringCollection sc = table.Script(options);

foreach (string str in sc)

{

Console.WriteLine(str);

}

trans.Commit();

con.Close();

 

I am able to achive the above using SQL-DMO but with the new SMO i am not able to.Can any please help me how do i generate script for a table which is altered in a transaction.

Answer 1

Hi Manoj,

The following information from Books Online might help -

BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.

http://msdn2.microsoft.com/en-us/library/ms188929.aspx

 

Thanks,

Kuntal

Answer 2

Hi,

 

I am able to resolve this. Thanks for your suggestion.  

 

I will explain you what i am trying to do. I need to generate a table script that is altered in a transaction. If i use the same connection for SMO i am getting an exception some exeception . So i created a new connection and passed to my ServerConnection object and passed this object to Server object. After this i tried to get the bindsession on my first connection  and then executed sp_bindsession on my SMO connection. But still,  when SMO tries to get the properties.It used to stuck.That means my SMO connection is not binded to my previously opened transaction.So, what i did is, i started a transaction on SMO connection and then executed the sp_bindsession on that connection.This solved my problem.

 

But, still i need to understand.How do we join SMO connection to a open transaction with out using sp_bindsession using SMO.Since, sp_bindsession is not supported in future versions of SQL Server.

 

Answer 3

Hi AhanHasi,

I am also looking to do exactly what you did. Did you ever find an answer or can you post me how you used the sp_bindsession to accomplish the same task please?

Cheers
Dewy

---

Dewy