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
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? |