Posted by Erland Sommarskog on 05/27/05 01:13

(markydev@gmail.com) writes:
> I'm using sql server 2000 sp4.
> I've 2 databases linked, an instance and my local.
> I'm getting two different errors when trying to update the remote table
> (local server) from the instance.
> There is only one row of data in the table with an identity field.
>
> 1st sql:-
> UPDATE [local].[database].dbo.NUMBERS SET [f 1]=3
>
> This gives me the error:-
> Server: Msg 8180, Level 16, State 1, Line 1
> Statement(s) could not be prepared.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1'.
>
> If I was to remove the space from [f 1] and use [f1] it would work
> fine.
>
> "select [f 1] from [dev001].[fashion Master].dbo.numbers"
> will return the correct value
>
> Any Ideas ?

I've seen this before, and I'm quite sure that I have reported it to
Microsoft. However, it does not seem have been fixed in SQL 2000. The
error is that when the query is submitted to the remote server, the
brackets are missing.

I tried a similar query in SQL 2005, and it worked correctly.

> 2nd sql:-
> UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1

I tried a similar query, but I could not reproduce the problem. Could
you post the CREATE TABLE statement for NUMBERS, including keys and
indexes?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]