update sql errors using linked server

markydev

Hi,

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 ?
2nd sql:-
UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1
This gives me the error:-
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"fashion Master"."dbo"."NUMBERS"' 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.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT
VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate
VALUE=True STATUS=DBPROPSTATUS_CONFLICTING],
[PROPID=DBPROP_IRowsetChange VA...

If I was to remove the isnull part, then it will work ok

Any ideas

Jul 23 '05 #1

Erland Sommarskog

(ma******@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, es****@sommarskog.se

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

Jul 23 '05 #2

md

2nd sql:- when adding the indentity field to my table I presumed an
index would be created. This was not the case, so I created this index
and now the sql works ok.
Thanks for the reply
Erland Sommarskog wrote:

(ma******@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, es****@sommarskog.se

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

Jul 23 '05 #3

Erland Sommarskog

md (ma******@gmail.com) writes:

2nd sql:- when adding the indentity field to my table I presumed an
index would be created. This was not the case, so I created this index
and now the sql works ok.

The only case indexes are created implicitly in SQL Server is when you
define a PRIMARY KEY or UNIQUE constraint. IDENTITY columns or FOREIGN
KEY columns do not come with an index, unless you actually create one.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

Jul 23 '05 #4