Thread: Linked Server Error
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
Hi. In it, "AS" key word was an indispensable reason and a problem. However, it becomes possible because it was equipped fromversion8.4 of server. See, http://winpg.jp/~saito/psqlODBC/SQLServer_linkserver_PostgreSQL2.png Regards, Hiroshi Saito "Jeff Crumbley" >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 >