October 31, 2013 at 6:27 pm
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.'
October 31, 2013 at 7:25 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2013 at 9:54 pm
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.'
November 1, 2013 at 7:01 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2013 at 2:30 pm
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.'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy