Linked Server to PostgreSQL Problem

[flo...@yahoo.com]

hi

I try to Linked server in SQL server to postgreSQL 6.5. I got the error
message when I make a query.

The error message : invalid schema or catalog specified for provider
'MSDASQL'.

1. I already set up ODBC data Source in Control panal.
2. On the sql server, i create the link server with the following field
MS OLE DB provider for ODBC driver , link server name and ODBC
datasourse name.
3. I checked the box "Be made without using a security context" under
Security Tab.
4. Nothing under "local server login to remote server login mappings"
5. In the server option, only checked on "DataAccess" and "User remote
collection"

Afterthat, I can see all the tables name in my postgreSQL database

I guess, If I can view all the table, it means that the setting is
corrected. isn't it?

Is it something else that I need to do to make it works?
When I setup the linked server, Do I have to provide info for location ,
catalog , provider string ???

I try to query with 4 part - servername.database.owner.table
but I got this error too.
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema.

Thanks for help. I am appreciated that.

yours,
Florence Lai

[Rand Boyd [MS]]

Florence,

If you look at the Linked Server properites in Enterprise Manager id the
Catalog option greyed out? If it is then the ODBC driver does not support
the 4 part naming convention. Even if it is not greyed out it is still
possible that the 4 part naming convention is not supported by the driver.

You can run the following from Query Analyzer to psooibly get further
information:
dbcc traceon(-1, 7300) go select statement against linked server using 4
part naming convention.

You probalbly can use the OPENQUERY function to query the postgreSQL 6.5
database.

Rand Boyd
Microsoft SQL Server Support

You can view any Microsoft Knowledge Base aricle by entering mskb <article
number> in the URL line in Internet Explorer.

Please reply only to the newsgroups.
When posting, please state the version of SQL Server being used and the
error number/exact error message text received, if any.

This posting is provided AS IS, with no warranties, and confers no rights.

[flo...@yahoo.com]

hi
    I try to execute the statement dbcc traceon(-1, 7300) . Afterthat,
 when I make a query in 4 part number, I got the same error message

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error:  Invalid schema or catalog specified for the provider.].

The following is my setting my connect and ODBC
1. I already set up ODBC data Source (System DSN) in Control panal.
2. On the sql server, i create the link server with the following field in the properties
 provider = MS OLE DB provider for ODBC driver ,
 link server name  = MylinkServerName
 ODBC datasourse name = MyODBCName

3. I checked the box "Be made without using a security context" under Security Tab.
4. Nothing under "local server login to remote server login mappings"
5. In the server option, only checked on "DataAccess" and "User remote collection"

I didn't put any schema and catalog , provider string  in the sever properties, etc,
When I run    sp_tables_ex  <MylinkServerName> , I can see my linked server's table information , The following is the result

 Table_cat  | table_schem | table_name | table_type| remake
                  |                     |xxxxxx         |TABLE    |

When I create a query. I use

select * from MylinkServerName...tableName

So. Do I have to install any things to make it works? or upgrade any component or dll to the server.

If you need detail info, I can provide to you. Thanks

Thanks for help. I am appreciated that.

Yours
Florence

[flo...@yahoo.com]

hi!!
I solve the problem. I can view the data. Thanks.
But I have other problem on type convertion not support
In postgresql database, I have a field "action" and datatype "TEXT" with
length "VAR" in Table A.
so, When i make a query
select * from openquery(MyLinkServerName, 'Select action from TableA')

Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[MSDASQL].action' from the OLE
DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned
0x80040e1d].

How do i write the SQL statement to view the datatype?
Or What do I need to do to make it works?

Thanks for help! I am appreciated that!!

Florence

[Rand Boyd [MS]]

Do other queries against this linked server work? Do you only have a
problem when you include this 'action' column?

Rand Boyd
Microsoft SQL Server Support

You can view any Microsoft Knowledge Base article by entering mskb <article

[Clay Mayers]

rboyd...@microsoft.com (Rand Boyd [MS]) wrote in message
<snip>

> You probalbly can use the OPENQUERY function to query the postgreSQL 6.5
> database.
>
> Rand Boyd
> Microsoft SQL Server Support
>

</snip>

Pardon for jumping in on this thread, but I'm currently trying to
solve the same problem. I can say that OPENQUERY does work for
PostgreSQL 7.1.3 while the select * from server...table returns the
7313 error. I captured some debug output from the PostgreSQL ODBC
driver and as far as I can tell it's happy so I think it's the
MS-OLEDB driver or MS-SQL server that's unhappy.

There is one error from an SQLGetConnetOption for option 1209 where
the driver returns 205 (unknown connect option). I have yet to track
that down but the failure doesn't seem to stop the select from
proceeding. I thought maybe the problem was what the ODBC driver is
returning for the SQLTables call. From what I can gather when
executing:
select * from server...myTable
MS-SQL calls SQLTable to get info about the table and that's
returning:
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
--------------- ----------- ---------- ---------- -------
myTable TABLE

All the other columns are blank rather than NULL. From the ODBC spec,
it appears to me that blank is a valid response but so is NULL.
Should the PostgreSQL ODBC driver be returning NULL for TQ and TO
instead or something?

Any insight at all would be appreciated.

Clay.

[flor...@yahoo.com]

Clay,
When you using sp_tables_ex <server name> , you will display all the
table name and table type in postgresql database. The TQ and TO should be
NULL. If you want to set up TQ and TO, You can specify the schema and catalog
under security-<server>- Properties.

But I didn't set up anything. It works. I only type the ODBC Name under
properties. All the field are blanks.

Only way to do on to select data from postgresql, I am using OPENQUERY to
work on .
select * from openquery(<link server name>, 'Select <column 1, column2 >
from <table>')

Sometime, U will get the error problem on Msg 7313 . because some data type
cannot view the datatype such as "TEXT". So far, I can view the types with
varchar, boolean, date, int4. You can try to specify the column to test on
select statement.

Also, u can insert into the link server.
http://support.microsoft.com/support/kb/articles/Q270/1/19.ASP

Hope it can help you.

Florence

[Clay Mayers]

FWIW: I've managed to get partial functionality. My hypothesis below
was correct and I can select/update tables as long as they have no
indexes.

<snip>

> I thought maybe the problem was what the ODBC driver is
> returning for the SQLTables call. From what I can gather when
> executing:
> select * from server...myTable
> MS-SQL calls SQLTable to get info about the table and that's
> returning:
> TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
> --------------- ----------- ---------- ---------- -------
> myTable TABLE
>
> All the other columns are blank rather than NULL. From the ODBC spec,
> it appears to me that blank is a valid response but so is NULL.
> Should the PostgreSQL ODBC driver be returning NULL for TQ and TO
> instead or something?

</snip>

MS-SQL expects the Table_qualifier and table_owner columns to be null
not blank for odbc sources that doesn't support categories or schemas.
So far I've modified the PostgreSQL's ODBC driver's SQLTables and
SQLColumns fncs to return NULL. I'm sure the index problem is the
same sort of thing. I did have one other problem that MS-SQL for
"select myCol from server...myTable" passes a query that looks like:

SELECT TBL1001."myCol" Col1003 from "myTable" TBL1001

The problem here is the missing "AS" between the column and the column
alias, something PostgreSQL doesn't support so I had to modify the
parser. Right now it's just a hack and there are plenty of
shift/reduce conflicts to resolve.

Bottom line: you can only use OPENQUERY on the current release of
PostgreSQL 7.1.3 to access it as a linked server from MS-SQL 2000.
Using the remote server select syntax requires modifying both the ODBC
driver to return NULL for category and schema as well as modifying the
PostgreSQL SQL parser to accept a column alias w/o req "AS".

Clay.

[Clay Mayers]

Hi Florence,

Thanks for the reply. I'm very new to linked servers and can use as
much information as possible. I have had success with OPENQUERY, but
I really wanted to use the <server>...<table> syntax too so I'm
continuing to investigate. I wasn't aware of sp_tables_ex, I'll check
it out - I was hoping there was such a SP.

I've played a lot with the schema and catalog fields, both with the
property page and with sp_addlinkedserver with no success. I have
since got it working but only by making modifications to postgresql
itself and psqlodbc driver (open source is very nice!). It works for
tables with an index now too. However, I'd prefer to use the released
versions.

Now my problem is, I can't get MS-SQL to propagate a where clause or
an order by to the remote server (here OPENQUERY is better since you
have control over what's sent). Instead it selects the whole table
then filters. My hope is that the problem is simply that the table
isn't large enough. It's also really slow. It took 1 min to insert
1000 items in a loop.

Clay.

flor...@yahoo.com wrote in message news:<3BFA8837...@yahoo.com>...