June 12, 2008 at 12:03 pm
So, I jump through hoops to not create cursors, and now it appears that SQL Server is creating them behind the scenes for me...?
Below is the pseudocode that is executing and generating a "Msg 16917 Cursor is not open." error. There are no cursors in triggers on the tables being inserted or anywhere else that I can find. The proc is on a sql2k5 box and the remote db's are sql2k. This does not happen in the test env where all boxes are sql2k5. There are several of the same type of bulk insert statements within the transaction, so I'm guessing that sql2k is creating a cursor behind the scenes...? Any thoughts on what might be causing this or how to avoid it would be appreciated!
Thanks!
Select field1, field2
into #trans
from table1
begin distributed tran
set xact_abort on
insert into sql2ksever.remotedb.dbo.tableA (
field1,
field2,
field3)
select t.field1, t.field2, x.field3
from #trans t inner join othersql2kbox.db1.dbo.tableB x
on
where ....
--more of these inserts that join the temp table to remote
--tables and insert remotely.
if @@ERROR <> 0
rollback tran
else
commit tran
drop table #trans
June 12, 2008 at 4:58 pm
It's not SQL2K.
It's ODBC driver you use for Linked Server connection.
ODBC drivers don't have bulk insert functionality, when you do insert on remote server driver declares cursor and inserts row by row.
Inserting into remote server tables is really bad idea.
Always try to read data from remote servers, not to insert.
If you do quite a chunk of data in most cases it's even faster to bcp it to a file, pass the name of the file to remote server and do bulk insert over there .
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply