Re: Linked Server Error

Hi.

In it, "AS" key word was an indispensable reason and a problem. However, it becomes possible because it was equipped from version8.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
>