It turned out Not to be so straight forward, executing a SQL Server stored-procedure from Entity Framework to insert data into Postgres (yes, I know, I said that in the meeting).
(A stored-procedure with an ordinary INSERT executed perfectly from within SSMS, using a linked server with an ODBC driver.)
After a fruitless day tweeking RPC and Distributed Transactions, my pragmatic (dreary) solution was to manually create a Postgres table, then a Postgres function to do the insert, then a stored-procedure to pass parameters to that function.
Here’s the code …
-- in postgres
-- DROP TABLE public.errorlogs;
CREATE TABLE public."ErrorLogs"
(
"id" serial primary key,
"edesc" char(500),
"etype" int,
"appid" int
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.ErrorLogs
OWNER to postgres;
Then to create the insert function …
--in postgres
CREATE OR REPLACE FUNCTION pg_insert_new_error(edesc varchar(500), etype int, appid int) RETURNS VOID AS
$$
BEGIN
INSERT INTO "ErrorLogs" (edesc, etype, appid) VALUES (edesc, etype, appid);
END
$$
LANGUAGE 'plpgsql';
And finally the stored-proc …
-- in sql server
alter procedure [PostgreSQL].[SPU_InsertNewErrorLog]
@description varchar(500),
@messagetype int,
@appid int
as
BEGIN
DECLARE @cmd VARCHAR(500) = 'SELECT a.* FROM OPENQUERY(pgserver,''select pg_insert_new_error(''''' + @description + ''''', '
+ CONVERT(VARCHAR(5), @messagetype) + ', ' + CONVERT(VARCHAR(5), @appid) + ')'') a'
EXEC (@cmd)
END
GO
(Notice, I had to encapsulate the whole query and its parameters to work with OPENQUERY).
Database Consultant 