Microsoft SQL Server 2005
--
Questions
--
Followers
Top Experts
the <new SqlConnection("context connection=true")> thing inside of MS SQL 2005 triggers and stored procedures (CLR) is really a mess for me. Here I have another problem:
Suppose you have a trigger which opens an SqlConnection like <SqlConnection con = new SqlConnection("context connection=true")>. Easy, no problem.
Now, inside this trigger you might want to call a stored procdure which needs an SqlConnection for it's purposes too. Problem: You can't open this second SqlConnection, because the one which was opened in the trigger is still open (and for some reason I want to keep it opened).
I tried to figure out the connections status in the stored procedure:
if (con.State != ConnectionState.Open)
con.Open();
But con.State is always "close"... And when con.Open() is executed, I receive the known exception (context is alredy in use).
What can you do about that? Maybe there is a way to pass a connection to a stored procedure so it does not have to open one of it's own? Just an idea...
Thanks as always for any help!
Ingmar
SQL 2005 Express, CLR
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
It is actually a support forum, but there is a long, involved and interesting discussion on the subject.
thanks for the link.
Unfortunately those guys did not seem to solve the proplem either...:-(
What are you actually trying to do in the code? There could be a workaround.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
It's just a trigger that collects a bunch of data rows (therefor needs an open connection) and then calls a stored procedure for each row that was found. The stored procedure needs an open connection for it's own purposes.
Of course there is an easy work around: I could close the connection in the trigger, before calling the stored procedure. The reason why I do not want to do that is that I have a lot of stored procedures (and triggers) that are all connected in some way. I'm just too lazy to make sure that I put a con.Close() at every place where it might be neccessary now or in the future. So I hoped I could solve this problem in the beginning of every trigger/stored procedure by checking if the connection is already open...and then just use it.
If you do not mind, I leave the question open for one or two days. Maybe someone else has a great idea. If not I will accept your last posting. Hope that is ok with you.
Thanks for your help! And good night man, I mean Nightman ;-)
Good night, and good luck.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft SQL Server 2005
--
Questions
--
Followers
Top Experts
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.