Update schema and rows in one transaction, SQL Server 2005?

The code is using a server-side cursor, that's what those calls are for. The first set of calls is preparing/opening the cursor. Then fetching rows from the cursor.

Finally closing the cursor. Those sprocs are analogous to the OPEN CURSOR, FETCH NEXT, CLOSE CURSOR T-SQL statements.

The code is using a server-side cursor, that's what those calls are for. The first set of calls is preparing/opening the cursor. Then fetching rows from the cursor.

Finally closing the cursor. Those sprocs are analogous to the OPEN CURSOR, FETCH NEXT, CLOSE CURSOR T-SQL statements. I'd have to take a closer look (which I will), but my guess is there is something going on with the server-side cursor, the encapsulating transaction, and the DDL.

Some more questions: Are you meaning to use server-side cursors in this case? Are the ADO Commands all using the same active connection? Update: I'm not exactly sure what's going on.It looks like you're using server-side cursors so you can use Recordset.Update() to push changes back to the server, in addition to executing generated SQL statements to alter schema and update data in the dynamic table(s).

Using the same connection, inside an explicit transaction. I'm not sure what effect the cursor operations will have on the rest of the transaction, or vice-versa, and to be honest I'm surprised this isn't working. I don't know how large of a change it would be, but I would recommend moving away from the server-side cursors and building the UPDATE statements for your table updates.

Sorry I couldn't be of more help. BTW- I found the following information on the sp_cursor calls: http://jtds.sourceforge.net/apiCursors.html.

Aye, I was reading in the documentation that in order to do transactions I couldn't use a Client-Side Cursor. And I also made sure that they were all using the same active connection as well. Thank you for helping out with this, it's been a weird problem for sure =( – EdgarVerona Sep 12 '08 at 2:31 Good info too about the stored procedures there... I had a feeling it was doing something like that, but I wasn't sure... I'd never actually done a Trace before today, so I've never seen those kinds of procedures being called under the hood.

=) =) – EdgarVerona Sep 12 '08 at 2:38 No worries, you tried the best you could with the information given. I'm scratching my head on it aas well... Aye, I am using Recordset. Update right now for the actual row updates as you said... I'll try using a directly generated Update statement instead and see how that works.

– EdgarVerona Sep 12 '08 at 17:34.

The behavior you describe is allowed. How is the code making the schema changes? Building SQL on the fly and executing through an ADO Command?

Or using ADOX? If you have access to the database server, try running a SQL Profiler trace while testing the scenario you outlined. See if the trace logs any errors/rollbacks.

Oh, it's building ADO commands. I'll try using the trace and report back! – EdgarVerona Sep 11 '08 at 23:40 Okay, used the trace, I didn't see anything unusual though.

=( I posted the SQL statements that were picked up by the Profiler above. – EdgarVerona Sep 12 '08 at 2:19.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.