unusual error when updating linked server table

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

unusual error when updating linked server table

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-10-23 : 13:26:52

I have two sql servers (ie A and B). My primary server is A and B is a linked server I access thru A.

The link server connection to B appears to work fine EXCEPT that I have one (and only one) table that I am unable to update. When I try to update the table I get this very generic and useless error:

Server: Msg 8180, Level 16, State 1, Line 11
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 11
Line 1: Incorrect syntax near 'Days'.

Here is the code I use:

declare @vendorid varchar(2), @customernumber varchar(50), @deliverydays varchar(50), @cutofftime datetime, @cutofftimedays smallint
set @vendorid ='OL'
set @customernumber='0056'
set @deliverydays=''
set @cutofftime='1899-12-30 1:00 AM'
set @cutofftimedays=1

UPDATE NILSRV09_LINKED.NorthWind.dbo.[Vendor Customer XRef]
SET
[Delivery Days] = @deliverydays,
[Cutoff Time] = @cutofftime,
[Cutoff Time Days] = @cutofftimedays
WHERE [vendor customer number] = @customernumber

The field names, sizes etc are all correct; I have manually verified that and when I run this code on server B (without the linked server syntax, of course), it runs with NO problems...

I have tried the usual tests (just set one field, change which field is set, copy the table and try to update that copy, ...) without success.

Has anyone else out there encountered this weird scenario??

Any ideas on how to figure out what the real problem is???

thx in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 14:35:36

Have you tried aliasing the table you want to UPDATE? Like this:

UPDATE A
SET
[Delivery Days] = @deliverydays,
[Cutoff Time] = @cutofftime,
[Cutoff Time Days] = @cutofftimedays
FROM NILSRV09_LINKED.NorthWind.dbo.[Vendor Customer XRef] A
WHERE A.[vendor customer number] = @customernumber

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-10-23 : 14:40:56

tried but I get the same error.

Note:

the problem seems to be related to the spaces in the field names; when I rename the field so that it has no spaces (and modify the code accordingly), the code magically starts to work.

this is odd since I update other tables on the linked server that have spaces in the table names and in the field names.

it just makes no sense...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 14:45:53

If you're using ODBC to link the table, it could be that the ODBC driver doesn't recognize the square brackets as column name delimiters, or the spaces in the column names. ODBC is extremely finicky about a lot of things. If you're using the native SQL Server OLE DB provider then I'm at a loss. If you can switch over to OLE DB see if that clears it up.

You might also want to update your MDAC components and see if that helps:

http://www.microsoft.com/data/download.htm

Edited by - robvolk on 10/23/2002 14:46:36

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-10-23 : 14:50:00

thx, Rob.

am using the native sql OLE provider and already have the latest/greatest mdac drivers.

had already tried using double quotes as column name delimiters but no luck.

as mentioned, this is odd because I have no trouble with other, very similar tables. I have even tried recreating the table from scratch on another database (which is why the code sample I gave shows the NorthWind db).

thanks for the help. let me know if you come up with any sudden inspirations.

sigh. a massive migraine and its not even noon yet...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 14:57:40

It could also be that "Days" is a reserved word ("Day" IS reserved in ODBC and marked as a future reserved word for SQL Server), but that's just a WAG.

Can you change the column names? I'll be honest with you, using spaces is not a good idea even if it is supported.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources