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
