0

This is the syntax I am trying to use, but I get an error of

Incorrect syntax near #

I have also tried it with an actual table and not a temp table and still not succesful. Here is my syntax, what should I do in order to be able to update a table on my SQL Server 2008 installation with data from a linked connection to postgresql?

Create Table #Test (field1 varchar(100), field2 varchar(100))

DECLARE @salesname nvarchar(500) = N'''Sam Wise''';
DECLARE @d1 nvarchar(100) = N'05/01/2016';
DECLARE @d2 nvarchar(100) = N'05/31/2016';
DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(LinkedServer, 
''UPDATE #Test
SET field1 = a.salesname SalesName
,field2 = b.salesTotal TotalSales
from sales a
inner join empinfo b
on a.empid = b.empid
WHERE a.salesname IN ('+ REPLACE(@salesname, '''', '''''') + N')
AND iv.jbinvoicedate BETWEEN ('+ CHAR(39) + CHAR(39)
+ CAST(@d1 AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@d2 AS NVARCHAR(500)) + CHAR(39)
+ CHAR(39) + N')''
);
';

EXEC sp_executesql @sql;
2

So OPENQUERY() is basically saying "Open this query on the linked server" - It's the preferred method for doing a select when able for me. Instead of saying something like

SELECT col1, col2
FROM LinkedServerName.DatabaseName.Schema.Table
WHERE SomeValue = Something Local Here

it does the filtering and joining at the linked server. This avoids you having to bring data back to just filter it out, etc with the four part query aproach.

The problem, though, is that update is trying to run on the destination. It's basically a pass-through query, so you are trying to pass that update through.

So you'll need to do the update locally, but can join to the open query to join to the data on the other side. For more you can see the syntax guide here with some notes.

  • So there is not a way to update my local mssql table with data from my postgresql database with dynamic parameters as my example shows? I can get an Insert into my local mssql table to work issue free, but an update does not work. – SmallFries BigGuys Nov 23 '16 at 19:41
  • You can still do the local update. Just treat the open query as one table. So do the update outside of the Open Query, joining to the open query liked a derived table. Out of pocket right now but will try and get back later and give an example. But you want to do the update outside of the openquery since you are updating locally, and you are simply passing the select portion of the update through to the linked server. – Mike Walsh Nov 23 '16 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.