Thanks Troy and Sherry! Daniel Ratliff
From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, March 28, 2014 11:22 AM To: [email protected] Subject: Re: [mssms] SQL query to get status messages? You want to do this: http://myitforum.com/cs2/blogs/jnelson/archive/2009/11/20/143120.aspx so you get your own custom schema, table, and view. Note that what’s available is “only” the status messages from cm07. If there are any status messages specific to cm12; those aren’t there. From: Troy Martin<mailto:[email protected]> Sent: Friday, March 28, 2014 10:03 AM To: [email protected]<mailto:[email protected]> Take a look/search on John Nelson's blog on myITforum... Sent from my iPhone On Mar 28, 2014, at 10:45 AM, "Daniel Ratliff" <[email protected]<mailto:[email protected]>> wrote: Has anyone ever done any SQL queries to gather status messages? Any ideas on how I can get this data? I can get just about any columns I need, but not the actual message itself! My query select top 1000 * from v_statusmessage MSG INNER JOIN v_statmsgattributes ATT ON MSG.RecordID = ATT.RecordID INNER JOIN v_StatMsgInsStrings INS ON MSG.RecordID = INS.RecordID INNER JOIN v_StatMsgWithInsStrings WINS ON MSG.RecordID = WINS.RecordID If I look at the canned report All messages for a specific message ID in SSRS, they are using an expression to populate the column, that’s pulling all kinds of strings in? SSRS expression =SrsResources.Localization.GetStatusMessage(Fields!MessageID.Value, Fields!Severity.Value, Fields!MsgDLLName.Value, User!Language, Fields!InsString1.Value, Fields!InsString2.Value, Fields!InsString3.Value, Fields!InsString4.Value, Fields!InsString5.Value, Fields!InsString6.Value, Fields!InsString7.Value, Fields!InsString8.Value, Fields!InsString9.Value, Fields!InsString10.Value) Canned SQL Query select top 1000 smsgs.RecordID, CASE smsgs.Severity WHEN -1073741824 THEN 'Error' WHEN 1073741824 THEN 'Informational' WHEN -2147483648 THEN 'Warning' ELSE 'Unknown' END As 'SeverityName', smsgs.MessageID, smsgs.Severity, modNames.MsgDLLName, smsgs.Component, smsgs.MachineName, smsgs.Time, smsgs.SiteCode, smwis.InsString1, smwis.InsString2, smwis.InsString3, smwis.InsString4, smwis.InsString5, smwis.InsString6, smwis.InsString7, smwis.InsString8, smwis.InsString9, smwis.InsString10 from fn_rbac_StatusMessage(@UserSIDs) smsgs join fn_rbac_StatMsgWithInsStrings(@UserSIDs) smwis on smsgs.RecordID = smwis.RecordID join fn_rbac_StatMsgModuleNames(@UserSIDs) modNames on smsgs.ModuleName = modNames.ModuleName where smsgs.MessageID = @msgId Order by smsgs.Time DESC Daniel Ratliff The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information. ________________________________ DISCLAIMER: This is a PRIVATE AND CONFIDENTIAL message for the ordinary user of this email address. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind 1E to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
