CODEWITHSUNDEEP
sql-serverclrsqlclrservice-broker
Philip K. Adetiloye
Philip K. Adetiloye

Reputation: 3270

CLR stored procedure activated by a broker queue to post to a webservice

I want to call a CLR inside a service broker queue activated store procedure but doing this causes the queue to be disabled - POISON_MESSAGE_HANDLING (STATUS = ON).

Basically, the CLR just calls a webservice to post the data it recieves.

Is there a permission or something I need to do first ?

Upvotes: 0

Views: 1026

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294307

The activated proc '[dbo].[ProcessOrders]' running on queue 'App01.dbo.InstTargetQueue' output the following: 'Could not find stored procedure 'dbo.ProcessOrders

This error indicates that the queue is configured with a stored procedure that was dropped from the database, perhaps. Can you double check that the procedure exists? Perhaps you have a typo, eg. an extra space, in the queue configuration? Run select * from sys.service_queues and double-check that the activated procedure name is exactly what you expect. Run select * from sys.procedures and make sure the procedure exists and is named as you expect.

To answer your question about CLR: first and foremost I would urge you to move the webservice calls outside of the database. Monitor the queue with an external process service, and post the web service call from your process, not from inside the database.

If you insist on calling webservices from SQLCLR, then this is what you need to know:

  • CLR procedures require EXTERNAL_ACCESS on the assembly to be allowed to call web services
  • Activated procedures run in an 'execute as' sandboxed environment, see Why does feature … not work under activation?
  • The sandboxed environment prevents EXTERNAL_ACCESS

So you need to exit the sandboxed environment of the activated procedure, and this can be done either by marking the database as TRUSTWORTHY, or by signing the procedure, see Signing an activated procedure. But right now your problem seem to be a simple name mismatch.

If you keep your web services inside SQLCLR (and I strongly discourage that) then please read about Thread.BeginThreadAffinity() and ServicePointManager.DefaultConnectionLimit (the second applies to an external process as well, not only SQLCLR).

Upvotes: 1

Related Questions