Hi ALL
I want to know that is it possible that i use the .net transaction and inside of that i use tha stored procedure transaction ,nested transaction with .net and stored procedure?
is it like the nested transaction in stored procedure that we use both 2 transactions in stored procedure or not ?
thanks
To use .NET code in SQL you need to enable CLR Integration via the Surface Area Configuration Utility. This need only be done once.
Next you need to compile your .NET assembly to a DLL. This DLL will be called into MS SQL.
From within MS SQL you load the assembly by using the statement:
CREATE ASSEMBLY <ass_name>
FROM '<path to dll>'
CREATE function <function name> (<any parameters>)
returns <any return value>
[with returns null on null input]
external name <ass_name>.[<dll ns.class>].<dll method>
The .NET assembly can be removed once it is pulled into MS SQL since the binary is from that point onwards referenced internally.
|||Shouldn't a transaction be atomic? Why would you use 2 transactions?|||The transaction is atomic. Once you import the CLR code into the assembly (a one time processes) and you define the T-SQL function to call the imported code you have an atomic function.
The only difference is that MS SQL's internal .NET Framework will be running the code, rather than the T-SQL interpretur.
Hope this helps.
Al
|||Thanks, but I was answering on the original question ;-)
|||SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. You should however take a look at the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept. It is not clear if you want to link the .NET transaction with the SQL transaction and this can be done by using the TransactionScope class i.e., if multiple resource managers are involved then transaction automatically becomes a distributed transaction.
No comments:
Post a Comment