Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SQL SERVER (TECHNET) > Using MSMQ from CLR Stored Procedure
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 16th February 15:38
david s platt
External User
 
Posts: 1
Default Using MSMQ from CLR Stored Procedure


clients telling them that the data has change as of that time, so that the
next time they display the data they will know that they need to refresh it.

Unfortunately, when I try to deploy the assembly containing the stored
procedure to SQL Server, I get an error message saying that "system.messaging
was not found in the SQL catalog." How can I get it there so that I can use
it from a stored procedure, or maybe a trigger? THanx.

Dave
  Reply With Quote


  sponsored links


2 22nd February 07:26
remus rusanu
External User
 
Posts: 1
Default Using MSMQ from CLR Stored Procedure


David,

Sounds too me that what you try to achieve is already taken care through
Query Notifications and SqlDependency
(http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx).
If a Query Notification does not meet the criteria, why not send a Service
Broker message to a client instead of MSMQ. This gives you a number of
benefits:
- transactional consistency between the procedure and the message (MSMQ
would require you to do a distributed transaction between SQL and MSMQ).
- simplified backup/restore of your messages along with your data
- clustering failover of your messages for disaster recovery, along with
your data
- database mirroring availability of yor messages, along with the data

And to answer your question, you need to add the assembly to SQL Server
since your assembly depends on it:
CREATE ASSEMBLY System.Messaging FROM '.\System.Messaging.dll"

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
  Reply With Quote
3 29th February 00:46
david s platt
External User
 
Posts: 1
Default Using MSMQ from CLR Stored Procedure


Remus, thank you for your response. Mine is a very simple, small-scale
application in which using QueryNotifications and SqlDependency would be vast
overkill, on a scale similar to using atomic bombs to crack walnuts. Such
features as order preservation, DTC coordination, and database mirroring are
not useful to it. I just want a one-line post to a queue, no more. It's
frustratingly difficult to get. Complex, powerful things are possible in SQL
Svr 2005, but simple things are not simple.

When I try to Create Assembly for MSMQ, it first made me create an
asymmetric key and a logon for it. Having succesfully done that, Create
Assembly from <path> system.messaging.dll produces a number of warnings
saying that several assemblies (messaging and its dependencies, apparently)
"have't been fully tested in SQL 2005". It then fails with an error saying
"CREATE ASSEMBLY for assembly 'System.Messaging' failed because assembly
'System.Windows.Forms' is not authorized for PERMISSION_SET = UNSAFE. The
assembly is authorized when either of the following is true: the database
owner (DBO) has UNSAFE ASSEMBLY permission and the database has the
TRUSTWORTHY database property on; or the assembly is signed with a
certificate or an asymmetric key that has a corresponding login with UNSAFE
ASSEMBLY permission." When I try to generate a key for this DLL, it fails
with the error message "An error occurred during the generation of the
asymmetric key." Nothing more. So Create Assembly isn't working for me either.

Does anyone know for sure if using MSMQ from stored procedure or trigger is
or isn't possible, and if so, how?

Thanx,

DAve
  Reply With Quote
4 6th March 11:53
david s platt
External User
 
Posts: 1
Default Using MSMQ from CLR Stored Procedure


OK, got it working now. FIddled with this and that, slashed and burned all
the security in sight, got it running.

The switch from shipping everything enabled and requiring admins to lock
down what they should, to shipping everything shut down and requiring admins
to turn on the pieces that they need, definitely needed to happen. The next
evolution along those lines would be to make it easier for admins to turn on
only the pieces they require instead of slashing and burning everything in
sight because it's easier.

Dave
  Reply With Quote
5 9th March 13:47
roger wolter[msft]
External User
 
Posts: 1
Default Using MSMQ from CLR Stored Procedure


What you're trying to do is to get a SQL Trigger to execute an external
process. That by design is a complex operation because it's a major
security issue. Using Query notifications or Service Broker to get outside
the SQL Server process without calling out from inside SQL Server is a lot
easier to implement because it doesn't require unsafe code to run within SQL
Server. It's not clear to me why you think notifying you of a change to a
table - the exact scenario that query notifications was designed for - it
overkill and something that can be done more easily by you duplicating the


--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
"David S Platt" <DavidSPlatt@discussions.microsoft.com> wrote in message news:42C68F20-40E0-46DE-9DB7-712C9112B791@microsoft.com...
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666