update sql errors using linked server

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • markydev@gmail.com
    #1

    update sql errors using linked server

    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::Op enRowset
    returned 0x80040e21: [PROPID=DBPROP_B OOKMARKS VALUE=True
    STATUS=DBPROPST ATUS_CONFLICTIN G], [PROPID=DBPROP_C OMMANDTIMEOUT
    VALUE=600 STATUS=DBPROPST ATUS_OK], [PROPID=Unknown PropertyID
    VALUE=True STATUS=DBPROPST ATUS_OK], [PROPID=DBPROP_I RowsetLocate
    VALUE=True STATUS=DBPROPST ATUS_CONFLICTIN G],
    [PROPID=DBPROP_I RowsetChange VA...

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

    Any ideas

    Tags: None
    • Erland Sommarskog
      #2
      Re: update sql errors using linked server

      (markydev@gmail .com) writes:[color=blue]
      > 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 ?[/color]

      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.
      [color=blue]
      > 2nd sql:-
      > UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1[/color]

      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@sommarsk og.se

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

        Comment

        • md
          #3
          Re: update sql errors using linked server

          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:[color=blue]
          > (markydev@gmail .com) writes:[color=green]
          > > 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 ?[/color]
          >
          > 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.
          >[color=green]
          > > 2nd sql:-
          > > UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1[/color]
          >
          > 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@sommarsk og.se
          >
          > Books Online for SQL Server SP3 at
          > http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

            Comment

            • Erland Sommarskog
              #4
              Re: update sql errors using linked server

              md (markydev@gmail .com) writes:[color=blue]
              > 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.[/color]

              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, esquel@sommarsk og.se

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

                Comment

                Previous template Next
                Working...