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.
| 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 One1. 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 QueryThis is where I get one of two exceptions. 1. An Exception stating that I cannot have parallel transactions.or2. An Exception that I have an open SqlReader that must be closed.Case Two1. 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 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-09-13 : 18:24:16
|
| ...................ANd |
|
|
|
|
|
|
Difficulty executing multiple SQL cmds