0

This query is failing in SQL Server over the PGNP provider:

SELECT      1 AS x
FROM        pg_catalog.pg_attribute a
LEFT OUTER JOIN pg_catalog.pg_index i
        ON (i.indrelid = a.attrelid 
            AND a.attnum = i.indkey[a.attnum-1])

This is the error message returned:

The OLE DB provider "PGNP" for linked server "XXX" indicates that either the object has no columns or the current user does not have permissions on that object.

If I run this query directly on the PostgreSQL server with pgAdmin, it returns results. Furthermore, if I remove the clause "a.attnum = i.indkey[a.attnum-1]", the query works over the PGNP provider. (I need this clause in my original query to identify which of a table's columns is the primary key.)

What is the issue, and how do I resolve it?

For the record, this is the complete query that I am executing, from which the query above was excerpted:

SELECT      c.relname as table_name, a.attname as column_name
        ,   t.typname as type_name
        ,   i.indisunique AS is_unique_key
        ,   i.indisprimary AS is_primary_key 
        ,   CASE t.typname
            WHEN 'bpchar' THEN 'char('+CAST(atttypmod - 4 AS VARCHAR)+')'
            WHEN 'numeric' THEN 'numeric('+CAST((atttypmod - 4)/65536 AS VARCHAR)
                            +','+CAST((atttypmod - 4)%65536 AS VARCHAR)
                            +')'
            WHEN 'text' THEN 'varchar(max)'
            WHEN 'varchar' THEN 'varchar('+CASE atttypmod WHEN -1 THEN 'max' ELSE CAST(atttypmod-4 AS VARCHAR) END+')'
            ELSE t.typname
            END as type_name_1          
        ,   CAST(CASE atttypmod WHEN -1 THEN NULL ELSE atttypmod - 4 END AS INT4) AS type_precision_scale
FROM        pg_catalog.pg_attribute a
    JOIN    pg_catalog.pg_class c on ( a.attrelid = c.oid)
    JOIN    pg_catalog.pg_namespace n on (c.relnamespace = n.oid)
    JOIN    pg_catalog.pg_type t on (a.atttypid = t.oid)
LEFT OUTER JOIN pg_catalog.pg_index i
        ON (c.oid = i.indrelid 
            AND i.indrelid = a.attrelid
            AND a.attnum = i.indkey[a.attnum-1])
WHERE       c.relkind = 'r' and a.attnum > 0
     AND    n.nspname = 'public'
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Jay Bienvenu
  • 3,069
  • 5
  • 33
  • 44
  • In your linked server setup what user name and password did you use? – Kuberchaun Jul 19 '13 at 19:32
  • Are you absolutely certain that you're connecting to the *same database* on the *same server* with the same username and password when using PgAdmin-III and the real app? – Craig Ringer Jul 20 '13 at 05:13
  • Very old post, but MSSQL Linked Servers use 4-part naming: Linked server.Database.SchemaOwner.Table. Like this example: FROM [HEROKU_SFDC].[MyDatabase].[dbo].[account] – smoore4 Nov 27 '18 at 18:03

0 Answers0