Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Difficulty executing multiple SQL cmds

Author  Topic 

EricJones
Starting Member

1 Post

Posted - 2007-08-27 : 14:24:48
I am probably missing something simple here but...

I have a CLR Stored Procedure where I am attempting to execute multiple SQL commands. Here is what happens.


Case One
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL Query.
8. Execute Query via ExecuteReader and get SQLReader.
9. Read query result set.
10. Close QueryReader.
11. Set Command Text Property to another SQL Query.
12. Execute Query

This is where I get one of two exceptions.
1. An Exception stating that I cannot have parallel transactions.
or
2. An Exception that I have an open SqlReader that must be closed.


Case Two
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL INSERT command.
8. Execute Insert command.
9. Set Command Text Property to SQL Query command.
10. Execute Query Command via ExecuteReader.

At this point the stored procedure appears to hang.


Could someone please enlighten me if I have missed something obvious while reading the manual, regarding what you can and cannot do?

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 05:22:37
you'll have to show us your code

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-09-13 : 18:24:16
...................ANd
Go to Top of Page
   

- Advertisement -