|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Using MSMQ from CLR Stored ProcedureI am trying to use MSMQ from within a stored procedure that I've written in
C#. When the data in a table changes, I want to post MSMQ messages to remote 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 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" -- Show quoteThis 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 "David S Platt" <DavidSPl***@discussions.microsoft.com> wrote in message news:FE66D47F-28B6-4555-88B7-92B7C7804440@microsoft.com... >I am trying to use MSMQ from within a stored procedure that I've written in > C#. When the data in a table changes, I want to post MSMQ messages to > remote > 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 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 Show quote "Remus Rusanu" wrote: > 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 > > > "David S Platt" <DavidSPl***@discussions.microsoft.com> wrote in message > news:FE66D47F-28B6-4555-88B7-92B7C7804440@microsoft.com... > >I am trying to use MSMQ from within a stored procedure that I've written in > > C#. When the data in a table changes, I want to post MSMQ messages to > > remote > > 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 > > > 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 code that the query notifications team wrote. -- Show quoteThis 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" <DavidSPl***@discussions.microsoft.com> wrote in message news:42C68F20-40E0-46DE-9DB7-712C9112B791@microsoft.com... > 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 > > > > > "Remus Rusanu" wrote: > >> 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 >> >> >> "David S Platt" <DavidSPl***@discussions.microsoft.com> wrote in message >> news:FE66D47F-28B6-4555-88B7-92B7C7804440@microsoft.com... >> >I am trying to use MSMQ from within a stored procedure that I've written >> >in >> > C#. When the data in a table changes, I want to post MSMQ messages to >> > remote >> > 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 >> >> >> 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 Show quote "Remus Rusanu" wrote: > 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 > > > "David S Platt" <DavidSPl***@discussions.microsoft.com> wrote in message > news:FE66D47F-28B6-4555-88B7-92B7C7804440@microsoft.com... > >I am trying to use MSMQ from within a stored procedure that I've written in > > C#. When the data in a table changes, I want to post MSMQ messages to > > remote > > 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 > > >  |
|||||||||||||||||||||||
Other interesting topics