Thread: Linked server query problem

Linked server query problem

From
"Anderson, Steven"
Date:

I have SQL Server 2005 that is linked to Postgresql  8.3.1-1

 

The following query does not work

SELECT id FROM ALERT.novastar.[public].[point]

 

Error:

OLE DB provider "MSDASQL" for linked server "ALERT" returned message "ERROR: syntax error at or near ""Col1004"";

Error while executing the query".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query

"SELECT "Tbl1002"."id" "Col1004" FROM "novastar"."public"."point" "Tbl1002""

against OLE DB provider "MSDASQL" for linked server "ALERTIII".

 

This query does work from MS SQL:

EXEC ('SELECT id FROM point') at ALERT

 

Any ideas as what the problem is

 

Thank you

 

Steven Anderson

City of Dallas

Streets Department

Flood Control

2255 Irving Blvd.

Dallas, TX 75207-6201

(214)671-0424

fax - (214)670-6526

web http://fc.dallascityhall.com

wireless web http://fc.dallascityhall.com/wap

 

Re: Linked server query problem

From
Richard Huxton
Date:
Anderson, Steven wrote:
> I have SQL Server 2005 that is linked to Postgresql  8.3.1-1

Upgrade to the latest version in 8.3 at your soonest convenience.

> The following query does not work
>
> SELECT id FROM ALERT.novastar.[public].[point]
>
> Error:

> "SELECT "Tbl1002"."id" "Col1004" FROM "novastar"."public"."point"
> "Tbl1002""

What's this supposed to do? Is "novastar" a database name? That's not
going to work - you'll need to connect to "novastar" and select from
"public.point".

Are you using the MS-SQL "Linked Server" feature? I've never tried it
with non-MS targets.

--
  Richard Huxton
  Archonet Ltd

Re: Linked server query problem

From
"Hiroshi Saito"
Date:
Hi.

Probably, it need the new feature of Ver 8.4.
See,
http://archives.postgresql.org/pgsql-committers/2008-02/msg00172.php

Regards,
Hiroshi Saito

----- Original Message -----

I have SQL Server 2005 that is linked to Postgresql  8.3.1-1


The following query does not work

SELECT id FROM ALERT.novastar.[public].[point]

Error:

OLE DB provider "MSDASQL" for linked server "ALERT" returned message
"ERROR: syntax error at or near ""Col1004"";

Error while executing the query".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query

"SELECT "Tbl1002"."id" "Col1004" FROM "novastar"."public"."point"
"Tbl1002""

against OLE DB provider "MSDASQL" for linked server "ALERTIII".


This query does work from MS SQL:

EXEC ('SELECT id FROM point') at ALERT



Any ideas as what the problem is



Thank you



Steven Anderson

City of Dallas

Streets Department

Flood Control

2255 Irving Blvd.

Dallas, TX 75207-6201

(214)671-0424

fax - (214)670-6526

web http://fc.dallascityhall.com

wireless web http://fc.dallascityhall.com/wap