P: n/a
|
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 | |
Share this Question
P: n/a
|
(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 | |
P: n/a
|
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 | |
P: n/a
|
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 | | This discussion thread is closed Replies have been disabled for this discussion. | | Question stats - viewed: 8586
- replies: 3
- date asked: Jul 23 '05
|