update query error

khautinh · July 1st, 2004, 09:18 AM

Hi all,

I have SQL server1 and SQL server2 and they are linked server to each other. I am from SQL server1 try the following query
Update server1.db1.DBO.table1
set
    [Salary Standard] = 66798,
    [Peer Group] = 'binh'
where
    position = ' B277'
and I got 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 'Standard'.

Please help. What do I miss?
Thanks a lot

alyeng2000 · July 1st, 2004, 01:10 PM

is there an error if you execute the query with out adding the server1 word

Ahmed Ali
Software Developer

happygv · July 1st, 2004, 09:30 PM

Hi khautinh,

When you want to update something in SERVER1, being connected to that, it is local to you, and you don't have to use SERVER1 reference there.

You can straight away use [b/Update DBO.table1[/b] if you use db1 as your current database. Only when you want to update any table on the remote server, you use that server referencing. But that shouldn't be causing you this error.

It is not a good practice to use SPACEs in the table/column name. It is better to remove the space in there and try updating.

Code:

Update table1
set
    Salary_Standard = 66798,
    Peer_Group = 'binh'
where position = 'B277'

This should work for you.
Cheers!

_________________________
- Vijay G
Strive for Perfection