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 |
|
steel
Posting Yak Master
107 Posts |
 Posted - 2008-09-28 : 02:13:59
|
| hello all,I have a production server on which there is a job which copies a table to another server but once we have changed the table we are getting the error:Inspite of the fact we ahave changed the the table schema on sec server and made the necessary changes in the job also but all in vainCould not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943)please hlp me...... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-28 : 02:42:55
|
| what was job doing?Can you give query used? |
 |
|
|
steel
Posting Yak Master
107 Posts |
Posted - 2008-09-28 : 02:57:49
|
| Actually it is a cursor which copies the data from a table row by row and takes to another server which is a linked server.declare @t varchar(50)declare cursor abc for select * from tbl_abcopen abcfetch next from abc into @twhile @@fetch_status=0begininsert into server2.db_name.dbo.tbl_abc values(@t)fetch next from abc into @tendclose abcdeallocate abcgothis type of exact deployement.............. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-28 : 03:02:09
|
quote:
Originally posted by steel
Actually it is a cursor which copies the data from a table row by row and takes to another server which is a linked server.declare @t varchar(50)declare cursor abc for select * from tbl_abcopen abcfetch next from abc into @twhile @@fetch_status=0begininsert into server2.db_name.dbo.tbl_abc values(@t)fetch next from abc into @tendclose abcdeallocate abcgothis type of exact deployement..............
why use cursor if you want just data to be transferred? why cant you go for set based solution? using OPENROWSET or four part naming convention or even SSIS/DTS? |
|
|
|
steel
Posting Yak Master
107 Posts |
Posted - 2008-09-28 : 03:29:53
|
| no actually it is critical system...and we are using all possible solution....this is one of the weapon from the arsenal.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-28 : 03:34:58
|
quote:
Originally posted by steel
no actually it is critical system...and we are using all possible solution....this is one of the weapon from the arsenal....
didnt get that. but anyways its better to go for set based solution which will be much faster than row by row cursor approach, unless you've any row by row manipulation required. Do you mean performance is not a concern even when system is critical? |
|
|
|
|
|
|
Could not complete cursor