SEO

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 from version8.4 of server.
See,
http://winpg.jp/~saito/psqlODBC/SQLS...ostgreSQL2.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
>


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc