External Stored procedure blocks when called from trigger

262 Views Asked by At

I have a stored procedure calling an external function:

ALTER PROCEDURE [dbo].[spFoo_CallApp]
    @Url [nvarchar](max),
    @Payload [int],
    @Type [nvarchar](max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Foo_HttpCaller].[StoredProcedures].[CallWebService]

Calling directly from SSMS works perfectly. The stored procedure finishes in almost no time.

However, when calling it from a trigger, it takes some time and throws an error message.

DECLARE @Id int

SELECT @Id = Id 
FROM FooBelege AS T1
WHERE EXISTS (SELECT 1 FROM inserted AS T2 WHERE T1.Id = T2.Id)

DECLARE @return_value int

EXEC [dbo].[spFoo_CallApp]
        @Url = N'https://...../trigger/',
        @Payload = 219095,
        @Type = N'VK'

Error:

Msg 6522, Level 16, State 1, Procedure dbo.spFoo_CallApp, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "spFoo_CallApp":
System.Net.WebException: The remote server returned an error: (500) Internal Server Error.
System.Net.WebException:
at System.Net.WebClient.UploadDataInternal(Uri address, String method, Byte[] data, WebRequest& request)
at System.Net.WebClient.UploadString(Uri address, String method, String data)
at StoredProcedures.CallWebService(SqlString url, SqlInt32 payload, SqlString Type)

What is even more funny, it depends on the payload. Using the 219095 works only when calling the stored procedure directly but not from inside of the trigger. Using another Id works even from the trigger.

Any help would be appreciated.

Thanks

0

There are 0 best solutions below