Distributed Query question

• Barbara BOyer

  SSC Enthusiast

  Points: 190

  More actions

  April 14, 2004 at 7:51 am

  #110842

  I installed a new server with SQL 2000. I am having a problem updating tables on this server from my other servers. I don't know whether it is something in the SQL settings or the database creation settings. I created a simple 2 column table on the new server. I can insert records into the table from the other servers. However, when I try a remote update with a very simple where clause, I get the following error message:

  Could not open table '"hhdb"."dbo"."test"' from OLE DB provider 'SQLOLEDB'.  The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.

  [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

  We have other remote servers that I do not have this problem with. Does anyone have any suggestions of where I should look for a solution?

  Thanks, Barb

• April 19, 2004 at 8:00 am

  This was removed by the editor as SPAM

• roberthamilton

  Ten Centuries

  Points: 1002

  More actions

  April 19, 2004 at 9:20 am

  #503362

  I vaguely remember seeing something like this a long time ago. I think I had to have an index on the remote table, possibly a unique one?

• tulcanla

  SSCommitted

  Points: 1541

  More actions

  April 19, 2004 at 12:20 pm

  #503385

  try this

   

  before SQL

   write:

   

   

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

   

• Barbara BOyer

  SSC Enthusiast

  Points: 190

  More actions

  April 19, 2004 at 12:31 pm

  #503387

  Thank you so much!  I think it was the ansi_null that did it!!

  Is there somewhere in the database I can set this so it does not have to be put into each script?

   

  Thanks again, Barb

• tulcanla

  SSCommitted

  Points: 1541

  More actions

  April 19, 2004 at 12:44 pm

  #503388

  check this:

  sp_dboption [ [ @dbname = ] 'database' ]

      [ , [ @optname = ] 'option_name' ]

      [ , [ @optvalue = ] 'value' ]

   

  and

   

  DBCC USEROPTIONS