Cursors, distriubted transactions and two versions of SQL Server
It is normally a great idea to use FAST_FORWARD cursors in TSQL if you must use a cursor because really we shouldn’t be modifying data in the cursor now should we? Seeing that cursors are quite possibly the worst constructs to use in SQL and all.
It has come to my attention that we have a couple of SPs that have cursors that do not specify what type they are. IF these cursors then go and create a Distributed Transaction, and within that transaction update data on a distributed server (via linked server) AND the remote server thinks that it is within a non-read only transaction you will get a rather odd error. The Calling server is SQL 2000 and the remote server is SQL 2008 both enterprise. The SQL 2000 server is on windows 2003 R2 Server, the SQL 2008 server is on Windows 2008. DTC works just fine between the two after some minor tweaks. The Error:
Class: 16 Error #: 8180 at line:376 Message Statement(s) could not be prepared. Class: 15 Error #: 102 at line:376 Message Incorrect syntax near 'Tbl1001'. Class: 15 Error #: 1018 at line:376 Message Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. ---> System.Data.SqlClient.SqlException: Statement(s) could not be prepared.
Incorrect syntax near 'Tbl1001'.
Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
That said, this is not the actual error at all. That code doesn’t even exist in the stored procedure in question. What?!? A Microsoft SQL Server error mislead you? NEVER! Oh, wait, yeah, they do that frequently.. say it ain’t so.
When the code was dissected the actual error was more telling. The real error is
Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.
Ah-ha! Now, we don’t actually update the cursor at this level, so why not use a fast_forward cursor type. This seems to clear up the issue, the SP ran and now I await actual data to see if this will be the fix.
It has come to my attention that we have a couple of SPs that have cursors that do not specify what type they are. IF these cursors then go and create a Distributed Transaction, and within that transaction update data on a distributed server (via linked server) AND the remote server thinks that it is within a non-read only transaction you will get a rather odd error. The Calling server is SQL 2000 and the remote server is SQL 2008 both enterprise. The SQL 2000 server is on windows 2003 R2 Server, the SQL 2008 server is on Windows 2008. DTC works just fine between the two after some minor tweaks. The Error:
Class: 16 Error #: 8180 at line:376 Message Statement(s) could not be prepared. Class: 15 Error #: 102 at line:376 Message Incorrect syntax near 'Tbl1001'. Class: 15 Error #: 1018 at line:376 Message Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. ---> System.Data.SqlClient.SqlException: Statement(s) could not be prepared.
Incorrect syntax near 'Tbl1001'.
Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
That said, this is not the actual error at all. That code doesn’t even exist in the stored procedure in question. What?!? A Microsoft SQL Server error mislead you? NEVER! Oh, wait, yeah, they do that frequently.. say it ain’t so.
When the code was dissected the actual error was more telling. The real error is
Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.
Ah-ha! Now, we don’t actually update the cursor at this level, so why not use a fast_forward cursor type. This seems to clear up the issue, the SP ran and now I await actual data to see if this will be the fix.
posted by TedS at 3:40 PM
0 Comments:
Post a Comment
<< Home