Linked Server Error

From: "Jeff Crumbley" <jeffc(at)iilogistics(dot)com>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: Linked Server Error
Date: 2008-04-16 21:09:30
Message-ID: 58748158EFB67344819B48564FCAD4A808119BB6@exchange2.iilogistics.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

To whom it may concern:

I am running SQL Server on Windows 2003 R2 (32-bit) connecting to
Postgres on SCO Unix. I have installed the ODBC driver
(psqlodbc_08_03_0100) and can query the tables in Postgres using
OPENQUERY from SQL Server. Everything works great where this is
concerned.

The issue I am having involves calling a Postgres Function from SQL
Server. The user ID that I am using to connect to Postgres is a
SuperUser and the function I created in Postgres is accepted without a
error (in pgAdmin III).

The Function:

-------------------------------------------------------

CREATE OR REPLACE FUNCTION mssql_test()

RETURNS integer AS

$BODY$DECLARE

retval bigint;

BEGIN

retval:=(SELECT count(*) FROM dminvoice);

return retval;

END;$BODY$

LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION mssql_test() OWNER TO testuser;

GRANT EXECUTE ON FUNCTION mssql_test() TO public;

GRANT EXECUTE ON FUNCTION mssql_test() TO testuser;

The way I am calling the function is as follows:

Function Call:

-------------------------------------------------------

EXEC [PostgreSQL].dta.testuser.mssql_test (ODBC
Connection.Database.User.Function Name)

The response I get from Postgres is:

Error:

-------------------------------------------------------

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

Error while executing the query".

Msg 7212, Level 17, State 1, Line 1

Could not execute procedure 'mssql_test' on remote server 'PostgreSQL'.

Any help you can provide on this would be greatly appreciated.

Thanks in advance,

Jeff

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Hiroshi Saito 2008-04-16 21:58:04 Re: Linked Server Error
Previous Message Michael Knigge 2008-04-16 11:38:57 How to force an Option?