Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server - How to escape reserved-word column names in update? Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 6:27 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:14 PM
Points: 870, Visits: 581
I have discovered that my previous error was caused by a different problem:

The following query fails when the destination column is a reserved word:

use<TestDB>;
go
--setup example data
create table testDestination(intVal int, [values] varchar(15));
insert into testDestination values (1, 'toBeOverwritten');
create table testSource(intVal int, stringVal varchar(15))
insert into testSource values (1, 'thisIsInQuotes');
go
--setup linked server.
EXEC sp_addlinkedserver
@server='TESTLOCAL',
@srvproduct='',
@provider='SQLNCLI11',
@datasrc='(local)'
go
--test linked server (this query works. Please update the database name for your system.)
select intval, [values] from TESTLOCAL.[<TestDB>].dbo.testDestination;

--this fails. (Please update the database name below for your system)
update td
set [values]=ts.stringVal
from TESTLOCAL.[<TestDB>].dbo.testDestination td
inner join dbo.testSource ts on ts.intVal = td.intVal

I have also tried aliasing the column using openquer as in the following snippet, but I get the same problem (I tried quotes as well).

--this also fails
update td
set vals=ts.stringVal
from openQuery(TESTLOCAL, 'select intVal, [values] as vals from [<TestDB>].dbo.testDestination') td
inner join dbo.testSource ts on ts.intVal = td.intVal

The local server is sql express 2012 (11.0.3128) and I've tried linking to a 2012 standard edition server (also 11.0.3128) and an sql2008 r2 standard edition server (10.50.2550)

I also tried using the native client 11 provider, and the microsoft ole db provider for sql server.

Thanks for your help!




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #1510467
Posted Thursday, October 31, 2013 7:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
This would be one of those reasons why it is recommended to not use reserved words as table or column names.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1510473
Posted Thursday, October 31, 2013 9:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:14 PM
Points: 870, Visits: 581
yes... Unfortunately I don't own the database in question.

Hopefully someone knows how to properly escape the names, or some other workaround.




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #1510491
Posted Friday, November 01, 2013 7:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
A workaround would be to update the values from local rather than across the linked server.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1510574
Posted Monday, November 04, 2013 2:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:14 PM
Points: 870, Visits: 581
In the end I could not fuigure out how to escape the reserved words (this has to be a bug in the driver??) but putting a view on the remote system was deemed acceptable, so I created an updatable view with no reserved words and am running the update against that.



Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #1511303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse