Quoted Identifiers Don't Work with Linked Servers and Update

I believe this is a bug in how update SQL statements are parsed when
executing against a linked server. I execute the following SQL:
update [server].[database].[dbo].[table] set [my column] = 'new value'
Note the space in the column name 'my column'. And I get the following
errors:
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 'column'.
The syntax of that SQL statement is correct. In fact, I can go to the
linked server and execute it there and it works, like this:
update [table] set [my column] = 'new value'
If I change the schema so the column does not have a space in it, then
execute the following SQL statement from the original server with the server
link, it works:
update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
This to me says that there is a parsing bug because, when performing an
update against a linked server, the quoted identifier for the column is not
respected! Note that this works:
select [my column] from [server].[database].[dbo].[table]
So it appears to be a problem parsing the update statement.
Can anyone shed some light on what is going on? Should I use a support
incident to get this fixed? Or is it something I am doing wrong? Thanks!
-CoreyYou don't say what version of sql server your using so it is hard to say but
there are numerous KB's with related subjects on this. Here is one that
seems to fit.
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
If that is not it then I would take a look at the other hits in the KB that
you can find from here:
http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
and enter "linked server identifier".
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> I believe this is a bug in how update SQL statements are parsed when
> executing against a linked server. I execute the following SQL:
> update [server].[database].[dbo].[table] set [my column] = 'new value'
> Note the space in the column name 'my column'. And I get the following
> errors:
> 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 'column'.
> The syntax of that SQL statement is correct. In fact, I can go to the
> linked server and execute it there and it works, like this:
> update [table] set [my column] = 'new value'
> If I change the schema so the column does not have a space in it, then
> execute the following SQL statement from the original server with the
server
> link, it works:
> update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
> This to me says that there is a parsing bug because, when performing an
> update against a linked server, the quoted identifier for the column is
not
> respected! Note that this works:
> select [my column] from [server].[database].[dbo].[table]
> So it appears to be a problem parsing the update statement.
> Can anyone shed some light on what is going on? Should I use a support
> incident to get this fixed? Or is it something I am doing wrong? Thanks!
> -Corey
>
>|||Thanks for the response! I'm using SQL Server 2000 with Service Pack 3.
The problem happens:
1. Using Query Analyzer
2. In my code, which uses the native SQL Server .NET data provider
I have seen and read a lot of articles and, while they discuss the problem,
and while Microsoft claims they have fixed it in other situations, they have
not fixed it in mine. I would be interested if anyone could reproduce the
problem, or could give me information that would lead to a solution of the
problem. Otherwise I'll be forced to use an MSDN support incident.
-Corey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You don't say what version of sql server your using so it is hard to say
but
> there are numerous KB's with related subjects on this. Here is one that
> seems to fit.
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> If that is not it then I would take a look at the other hits in the KB
that
> you can find from here:
> http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> and enter "linked server identifier".
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Young, Corey" <Corey@.Youngspot.com> wrote in message
> news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > I believe this is a bug in how update SQL statements are parsed when
> > executing against a linked server. I execute the following SQL:
> >
> > update [server].[database].[dbo].[table] set [my column] = 'new value'
> >
> > Note the space in the column name 'my column'. And I get the following
> > errors:
> >
> > 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 'column'.
> >
> > The syntax of that SQL statement is correct. In fact, I can go to the
> > linked server and execute it there and it works, like this:
> >
> > update [table] set [my column] = 'new value'
> >
> > If I change the schema so the column does not have a space in it, then
> > execute the following SQL statement from the original server with the
> server
> > link, it works:
> >
> > update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
> >
> > This to me says that there is a parsing bug because, when performing an
> > update against a linked server, the quoted identifier for the column is
> not
> > respected! Note that this works:
> >
> > select [my column] from [server].[database].[dbo].[table]
> >
> > So it appears to be a problem parsing the update statement.
> >
> > Can anyone shed some light on what is going on? Should I use a support
> > incident to get this fixed? Or is it something I am doing wrong?
Thanks!
> >
> > -Corey
> >
> >
> >
>|||OK, it is best to post that extra info up front so that we don't have to
assume anything. I will post this on the private ng and see if anyone else
can confirm this. By the way does it work if you use double quotes instead
of [] ?
--
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks for the response! I'm using SQL Server 2000 with Service Pack 3.
> The problem happens:
> 1. Using Query Analyzer
> 2. In my code, which uses the native SQL Server .NET data provider
> I have seen and read a lot of articles and, while they discuss the
problem,
> and while Microsoft claims they have fixed it in other situations, they
have
> not fixed it in mine. I would be interested if anyone could reproduce the
> problem, or could give me information that would lead to a solution of the
> problem. Otherwise I'll be forced to use an MSDN support incident.
> -Corey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > You don't say what version of sql server your using so it is hard to say
> but
> > there are numerous KB's with related subjects on this. Here is one that
> > seems to fit.
> >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> >
> > If that is not it then I would take a look at the other hits in the KB
> that
> > you can find from here:
> > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> >
> > and enter "linked server identifier".
> >
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > I believe this is a bug in how update SQL statements are parsed when
> > > executing against a linked server. I execute the following SQL:
> > >
> > > update [server].[database].[dbo].[table] set [my column] = 'new value'
> > >
> > > Note the space in the column name 'my column'. And I get the
following
> > > errors:
> > >
> > > 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 'column'.
> > >
> > > The syntax of that SQL statement is correct. In fact, I can go to the
> > > linked server and execute it there and it works, like this:
> > >
> > > update [table] set [my column] = 'new value'
> > >
> > > If I change the schema so the column does not have a space in it, then
> > > execute the following SQL statement from the original server with the
> > server
> > > link, it works:
> > >
> > > update [server].[database].[dbo].[table] set [mycolumn] = 'new value'
> > >
> > > This to me says that there is a parsing bug because, when performing
an
> > > update against a linked server, the quoted identifier for the column
is
> > not
> > > respected! Note that this works:
> > >
> > > select [my column] from [server].[database].[dbo].[table]
> > >
> > > So it appears to be a problem parsing the update statement.
> > >
> > > Can anyone shed some light on what is going on? Should I use a
support
> > > incident to get this fixed? Or is it something I am doing wrong?
> Thanks!
> > >
> > > -Corey
> > >
> > >
> > >
> >
> >
>|||Thanks!
The behavior is the same using either double-quotes or brackets.
-Corey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> OK, it is best to post that extra info up front so that we don't have to
> assume anything. I will post this on the private ng and see if anyone
else
> can confirm this. By the way does it work if you use double quotes
instead
> of [] ?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Young, Corey" <Corey@.Youngspot.com> wrote in message
> news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Thanks for the response! I'm using SQL Server 2000 with Service Pack 3.
> > The problem happens:
> >
> > 1. Using Query Analyzer
> > 2. In my code, which uses the native SQL Server .NET data provider
> >
> > I have seen and read a lot of articles and, while they discuss the
> problem,
> > and while Microsoft claims they have fixed it in other situations, they
> have
> > not fixed it in mine. I would be interested if anyone could reproduce
the
> > problem, or could give me information that would lead to a solution of
the
> > problem. Otherwise I'll be forced to use an MSDN support incident.
> >
> > -Corey
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > You don't say what version of sql server your using so it is hard to
say
> > but
> > > there are numerous KB's with related subjects on this. Here is one
that
> > > seems to fit.
> > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > >
> > > If that is not it then I would take a look at the other hits in the KB
> > that
> > > you can find from here:
> > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > >
> > > and enter "linked server identifier".
> > >
> > >
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > I believe this is a bug in how update SQL statements are parsed when
> > > > executing against a linked server. I execute the following SQL:
> > > >
> > > > update [server].[database].[dbo].[table] set [my column] = 'new
value'
> > > >
> > > > Note the space in the column name 'my column'. And I get the
> following
> > > > errors:
> > > >
> > > > 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 'column'.
> > > >
> > > > The syntax of that SQL statement is correct. In fact, I can go to
the
> > > > linked server and execute it there and it works, like this:
> > > >
> > > > update [table] set [my column] = 'new value'
> > > >
> > > > If I change the schema so the column does not have a space in it,
then
> > > > execute the following SQL statement from the original server with
the
> > > server
> > > > link, it works:
> > > >
> > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
value'
> > > >
> > > > This to me says that there is a parsing bug because, when performing
> an
> > > > update against a linked server, the quoted identifier for the column
> is
> > > not
> > > > respected! Note that this works:
> > > >
> > > > select [my column] from [server].[database].[dbo].[table]
> > > >
> > > > So it appears to be a problem parsing the update statement.
> > > >
> > > > Can anyone shed some light on what is going on? Should I use a
> support
> > > > incident to get this fixed? Or is it something I am doing wrong?
> > Thanks!
> > > >
> > > > -Corey
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Corey,
Here is a reply I got from another MVP and although I don't like the answer
I guess it makes sense as to what is going on.
> My understanding is that, this is a known limitation(?) of OLE DB provider
> for SQL Server. By default, SQL Server OLE DB provider supports two
> interfaces IRowsetUpdate & IRowsetChange whose properties determine
whether
> the underlying rowset can be updated or not. Delimited identifiers with
> certain characters (~ , - ,! ,{ ,% ,} ,^ ,' ,& ,. ,( ,\ , ) ,` ,space) can
> set these property bits to false thereby making the underlying rowset
> non-updateable. This causes any update/insert/delete queries including
> 4-part naming & pass-through against these datasets to fail.
> A workaround may be to avoid direct 4-part naming and pass thru queries
and
> try to do the update directly on the server, say using sp_executeSQL like:
> EXEC server.database.dbo.sp_ExecuteSQL N'
> UPDATE [table] SET [my column] = ''new value'''
>
Since I never use spaces I can't say I have run across this before. Another
option would be to use a stored proc that lives in the other server and call
it to do the update. Good luck. If you still aren't satisfied you can give
MS PSS a call.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:uzPNp%23BnDHA.1284@.TK2MSFTNGP09.phx.gbl...
> Thanks!
> The behavior is the same using either double-quotes or brackets.
> -Corey
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > OK, it is best to post that extra info up front so that we don't have to
> > assume anything. I will post this on the private ng and see if anyone
> else
> > can confirm this. By the way does it work if you use double quotes
> instead
> > of [] ?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > Thanks for the response! I'm using SQL Server 2000 with Service Pack
3.
> > > The problem happens:
> > >
> > > 1. Using Query Analyzer
> > > 2. In my code, which uses the native SQL Server .NET data provider
> > >
> > > I have seen and read a lot of articles and, while they discuss the
> > problem,
> > > and while Microsoft claims they have fixed it in other situations,
they
> > have
> > > not fixed it in mine. I would be interested if anyone could reproduce
> the
> > > problem, or could give me information that would lead to a solution of
> the
> > > problem. Otherwise I'll be forced to use an MSDN support incident.
> > >
> > > -Corey
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > You don't say what version of sql server your using so it is hard to
> say
> > > but
> > > > there are numerous KB's with related subjects on this. Here is one
> that
> > > > seems to fit.
> > > >
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > > >
> > > > If that is not it then I would take a look at the other hits in the
KB
> > > that
> > > > you can find from here:
> > > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > > >
> > > > and enter "linked server identifier".
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > > I believe this is a bug in how update SQL statements are parsed
when
> > > > > executing against a linked server. I execute the following SQL:
> > > > >
> > > > > update [server].[database].[dbo].[table] set [my column] = 'new
> value'
> > > > >
> > > > > Note the space in the column name 'my column'. And I get the
> > following
> > > > > errors:
> > > > >
> > > > > 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 'column'.
> > > > >
> > > > > The syntax of that SQL statement is correct. In fact, I can go to
> the
> > > > > linked server and execute it there and it works, like this:
> > > > >
> > > > > update [table] set [my column] = 'new value'
> > > > >
> > > > > If I change the schema so the column does not have a space in it,
> then
> > > > > execute the following SQL statement from the original server with
> the
> > > > server
> > > > > link, it works:
> > > > >
> > > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
> value'
> > > > >
> > > > > This to me says that there is a parsing bug because, when
performing
> > an
> > > > > update against a linked server, the quoted identifier for the
column
> > is
> > > > not
> > > > > respected! Note that this works:
> > > > >
> > > > > select [my column] from [server].[database].[dbo].[table]
> > > > >
> > > > > So it appears to be a problem parsing the update statement.
> > > > >
> > > > > Can anyone shed some light on what is going on? Should I use a
> > support
> > > > > incident to get this fixed? Or is it something I am doing wrong?
> > > Thanks!
> > > > >
> > > > > -Corey
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks for the help!
I have not seen 4-part naming to be a problem as I am able to do inserts and
updates to tables on the linked server when the table and column names don't
require bracketing or quoting.
The table and column names are highly dynamic, so having a stored procedure
on the linked server is probably not a good option for me. However, I had
not thought of the sp_executesql option, which I will try. What I had
planned on doing (until you came up with the sp_executesql option) was to
name my columns and tables such that they do not have spaces in them, but
rather have the '_' character in them, and then just changing the '_'
character to a space prior to display to the user. I'll try your idea
first.
Will there be any performance consequences vs. normal inserts and updates as
a result of using sp_executesql in the way you have described?
Again, thanks a lot for the help! I really appreciate it.
-Corey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Oo7xj4OnDHA.424@.TK2MSFTNGP10.phx.gbl...
> Corey,
> Here is a reply I got from another MVP and although I don't like the
answer
> I guess it makes sense as to what is going on.
> > My understanding is that, this is a known limitation(?) of OLE DB
provider
> > for SQL Server. By default, SQL Server OLE DB provider supports two
> > interfaces IRowsetUpdate & IRowsetChange whose properties determine
> whether
> > the underlying rowset can be updated or not. Delimited identifiers with
> > certain characters (~ , - ,! ,{ ,% ,} ,^ ,' ,& ,. ,( ,\ , ) ,` ,space)
can
> > set these property bits to false thereby making the underlying rowset
> > non-updateable. This causes any update/insert/delete queries including
> > 4-part naming & pass-through against these datasets to fail.
> >
> > A workaround may be to avoid direct 4-part naming and pass thru queries
> and
> > try to do the update directly on the server, say using sp_executeSQL
like:
> >
> > EXEC server.database.dbo.sp_ExecuteSQL N'
> > UPDATE [table] SET [my column] = ''new value'''
> >
>
> Since I never use spaces I can't say I have run across this before.
Another
> option would be to use a stored proc that lives in the other server and
call
> it to do the update. Good luck. If you still aren't satisfied you can
give
> MS PSS a call.
>
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Young, Corey" <Corey@.Youngspot.com> wrote in message
> news:uzPNp%23BnDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > Thanks!
> >
> > The behavior is the same using either double-quotes or brackets.
> >
> > -Corey
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > > OK, it is best to post that extra info up front so that we don't have
to
> > > assume anything. I will post this on the private ng and see if anyone
> > else
> > > can confirm this. By the way does it work if you use double quotes
> > instead
> > > of [] ?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > > Thanks for the response! I'm using SQL Server 2000 with Service
Pack
> 3.
> > > > The problem happens:
> > > >
> > > > 1. Using Query Analyzer
> > > > 2. In my code, which uses the native SQL Server .NET data provider
> > > >
> > > > I have seen and read a lot of articles and, while they discuss the
> > > problem,
> > > > and while Microsoft claims they have fixed it in other situations,
> they
> > > have
> > > > not fixed it in mine. I would be interested if anyone could
reproduce
> > the
> > > > problem, or could give me information that would lead to a solution
of
> > the
> > > > problem. Otherwise I'll be forced to use an MSDN support incident.
> > > >
> > > > -Corey
> > > >
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > > You don't say what version of sql server your using so it is hard
to
> > say
> > > > but
> > > > > there are numerous KB's with related subjects on this. Here is
one
> > that
> > > > > seems to fit.
> > > > >
> > > > >
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > > > >
> > > > > If that is not it then I would take a look at the other hits in
the
> KB
> > > > that
> > > > > you can find from here:
> > > > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > > > >
> > > > > and enter "linked server identifier".
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > > > I believe this is a bug in how update SQL statements are parsed
> when
> > > > > > executing against a linked server. I execute the following SQL:
> > > > > >
> > > > > > update [server].[database].[dbo].[table] set [my column] = 'new
> > value'
> > > > > >
> > > > > > Note the space in the column name 'my column'. And I get the
> > > following
> > > > > > errors:
> > > > > >
> > > > > > 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 'column'.
> > > > > >
> > > > > > The syntax of that SQL statement is correct. In fact, I can go
to
> > the
> > > > > > linked server and execute it there and it works, like this:
> > > > > >
> > > > > > update [table] set [my column] = 'new value'
> > > > > >
> > > > > > If I change the schema so the column does not have a space in
it,
> > then
> > > > > > execute the following SQL statement from the original server
with
> > the
> > > > > server
> > > > > > link, it works:
> > > > > >
> > > > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
> > value'
> > > > > >
> > > > > > This to me says that there is a parsing bug because, when
> performing
> > > an
> > > > > > update against a linked server, the quoted identifier for the
> column
> > > is
> > > > > not
> > > > > > respected! Note that this works:
> > > > > >
> > > > > > select [my column] from [server].[database].[dbo].[table]
> > > > > >
> > > > > > So it appears to be a problem parsing the update statement.
> > > > > >
> > > > > > Can anyone shed some light on what is going on? Should I use a
> > > support
> > > > > > incident to get this fixed? Or is it something I am doing
wrong?
> > > > Thanks!
> > > > > >
> > > > > > -Corey
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||sp_executesql can be just as effective as adhoc sql (or more so) if you can
properly parameterize the changing values. If you are calling multiple
tables or columns you will still be able to cache the plan and reuse it the
next time you call a similar update.
--
Andrew J. Kelly
SQL Server MVP
"Young, Corey" <Corey@.Youngspot.com> wrote in message
news:eD820LSnDHA.424@.TK2MSFTNGP10.phx.gbl...
> Thanks for the help!
> I have not seen 4-part naming to be a problem as I am able to do inserts
and
> updates to tables on the linked server when the table and column names
don't
> require bracketing or quoting.
> The table and column names are highly dynamic, so having a stored
procedure
> on the linked server is probably not a good option for me. However, I had
> not thought of the sp_executesql option, which I will try. What I had
> planned on doing (until you came up with the sp_executesql option) was to
> name my columns and tables such that they do not have spaces in them, but
> rather have the '_' character in them, and then just changing the '_'
> character to a space prior to display to the user. I'll try your idea
> first.
> Will there be any performance consequences vs. normal inserts and updates
as
> a result of using sp_executesql in the way you have described?
> Again, thanks a lot for the help! I really appreciate it.
> -Corey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Oo7xj4OnDHA.424@.TK2MSFTNGP10.phx.gbl...
> > Corey,
> >
> > Here is a reply I got from another MVP and although I don't like the
> answer
> > I guess it makes sense as to what is going on.
> >
> > > My understanding is that, this is a known limitation(?) of OLE DB
> provider
> > > for SQL Server. By default, SQL Server OLE DB provider supports two
> > > interfaces IRowsetUpdate & IRowsetChange whose properties determine
> > whether
> > > the underlying rowset can be updated or not. Delimited identifiers
with
> > > certain characters (~ , - ,! ,{ ,% ,} ,^ ,' ,& ,. ,( ,\ , ) ,` ,space)
> can
> > > set these property bits to false thereby making the underlying rowset
> > > non-updateable. This causes any update/insert/delete queries including
> > > 4-part naming & pass-through against these datasets to fail.
> > >
> > > A workaround may be to avoid direct 4-part naming and pass thru
queries
> > and
> > > try to do the update directly on the server, say using sp_executeSQL
> like:
> > >
> > > EXEC server.database.dbo.sp_ExecuteSQL N'
> > > UPDATE [table] SET [my column] = ''new value'''
> > >
> >
> >
> > Since I never use spaces I can't say I have run across this before.
> Another
> > option would be to use a stored proc that lives in the other server and
> call
> > it to do the update. Good luck. If you still aren't satisfied you can
> give
> > MS PSS a call.
> >
> >
> > http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
> > http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > news:uzPNp%23BnDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > > Thanks!
> > >
> > > The behavior is the same using either double-quotes or brackets.
> > >
> > > -Corey
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:ucOjyfBnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > > > OK, it is best to post that extra info up front so that we don't
have
> to
> > > > assume anything. I will post this on the private ng and see if
anyone
> > > else
> > > > can confirm this. By the way does it work if you use double quotes
> > > instead
> > > > of [] ?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > news:eW8lOl$mDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > > > Thanks for the response! I'm using SQL Server 2000 with Service
> Pack
> > 3.
> > > > > The problem happens:
> > > > >
> > > > > 1. Using Query Analyzer
> > > > > 2. In my code, which uses the native SQL Server .NET data provider
> > > > >
> > > > > I have seen and read a lot of articles and, while they discuss the
> > > > problem,
> > > > > and while Microsoft claims they have fixed it in other situations,
> > they
> > > > have
> > > > > not fixed it in mine. I would be interested if anyone could
> reproduce
> > > the
> > > > > problem, or could give me information that would lead to a
solution
> of
> > > the
> > > > > problem. Otherwise I'll be forced to use an MSDN support
incident.
> > > > >
> > > > > -Corey
> > > > >
> > > > >
> > > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > > news:%23MDPlH$mDHA.2416@.TK2MSFTNGP10.phx.gbl...
> > > > > > You don't say what version of sql server your using so it is
hard
> to
> > > say
> > > > > but
> > > > > > there are numerous KB's with related subjects on this. Here is
> one
> > > that
> > > > > > seems to fit.
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;218995&Product=sql2k
> > > > > >
> > > > > > If that is not it then I would take a look at the other hits in
> the
> > KB
> > > > > that
> > > > > > you can find from here:
> > > > > > http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto
> > > > > >
> > > > > > and enter "linked server identifier".
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Andrew J. Kelly
> > > > > > SQL Server MVP
> > > > > >
> > > > > >
> > > > > > "Young, Corey" <Corey@.Youngspot.com> wrote in message
> > > > > > news:ub5tcM2mDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > > > > > I believe this is a bug in how update SQL statements are
parsed
> > when
> > > > > > > executing against a linked server. I execute the following
SQL:
> > > > > > >
> > > > > > > update [server].[database].[dbo].[table] set [my column] ='new
> > > value'
> > > > > > >
> > > > > > > Note the space in the column name 'my column'. And I get the
> > > > following
> > > > > > > errors:
> > > > > > >
> > > > > > > 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 'column'.
> > > > > > >
> > > > > > > The syntax of that SQL statement is correct. In fact, I can
go
> to
> > > the
> > > > > > > linked server and execute it there and it works, like this:
> > > > > > >
> > > > > > > update [table] set [my column] = 'new value'
> > > > > > >
> > > > > > > If I change the schema so the column does not have a space in
> it,
> > > then
> > > > > > > execute the following SQL statement from the original server
> with
> > > the
> > > > > > server
> > > > > > > link, it works:
> > > > > > >
> > > > > > > update [server].[database].[dbo].[table] set [mycolumn] = 'new
> > > value'
> > > > > > >
> > > > > > > This to me says that there is a parsing bug because, when
> > performing
> > > > an
> > > > > > > update against a linked server, the quoted identifier for the
> > column
> > > > is
> > > > > > not
> > > > > > > respected! Note that this works:
> > > > > > >
> > > > > > > select [my column] from [server].[database].[dbo].[table]
> > > > > > >
> > > > > > > So it appears to be a problem parsing the update statement.
> > > > > > >
> > > > > > > Can anyone shed some light on what is going on? Should I use
a
> > > > support
> > > > > > > incident to get this fixed? Or is it something I am doing
> wrong?
> > > > > Thanks!
> > > > > > >
> > > > > > > -Corey
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>