xml handy items

I do a lot of processing of XML documents from various sources.  Just a couple of items I want to have around even through they are in my library of tricks. Removes declaration from XML document so that it can be loaded into XDocument. 

private Regex M_RemoveDecl = new Regex(@"<\?xml.*?\?>");

This is to remove namespaces in documents.  Makes the query of the document easier.
... Taken from this discussion


public static string RemoveAllNamespaces(string xmlDocument) { XElement xmlDocumentWithoutNs = RemoveAllNamespaces(XElement.Parse(xmlDocument)); return xmlDocumentWithoutNs.ToString(); } private static XElement RemoveAllNamespaces(XElement xmlDocument) { if (!xmlDocument.HasElements) { XElement xElement = new XElement(xmlDocument.Name.LocalName); xElement.Value = xmlDocument.Value; foreach (XAttribute attribute in xmlDocument.Attributes()) xElement.Add(attribute); return xElement; } return new XElement(xmlDocument.Name.LocalName, xmlDocument.Elements().Select(el => RemoveAllNamespaces(el))); }

Updating ReportViewer to VS2010

OK, today I blew the dust off of a project that had ReportView 9.0.  Since I had coded that website many moons have pasted and we have upgraded to VS2010.

I tried to just stay on RV9 however, one of the reports was erroring and I had javascript issues with the handler page that it inserts.

So, upgrade I did and not without google's help!

1. Install on your machine ReportViewer 10
2. change the references in your project to point to version 10 of Microsoft.ReportViewer.Common and Microsoft.ReportViewer.WebForms
3. If you registered your control, change this tag in your .aspx or control 
1. <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0...

 If you have .rdlc files that you need to upgrade, some work and some don't

I had a report that was cause compile errors (The processing of Parent for the tablix ‘table1’ cannot be performed) with the grouping.  I had to take out the "parent" tag and then all was well.  Basically old versions glossed over this mishap, but not version 10.  For the long explanation of why you need to do this, go here.

            OrderNumGrp
">
              <GroupExpressions>
                <GroupExpression>=Fields!ClientOrderNum.Value</GroupExpression>
              </GroupExpressions>
              =Fields!ClosedDate.Value
           

Finally, when you are deploying to a server with IIS7.  Make sure that you add the following to your web.config

webServer
>
...
      ReportViewerWebControlHandler
" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
...
   
  webServer>

Finally, as it always happends, when you get it onto the production server something else goes wrong.  I was getting this error

Compiler Error Message: CS0433: The type 'Microsoft.Reporting.WebForms.ReportDataSource' exists in both 'c:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WebForms\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.WebForms.dll' and 'c:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WebForms\9.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.WebForms.dll'

If you add the following to your web.config it tells .net to always direct to the new version


                                                publicKeyToken="b03f5f7f11d50a3a" />
                                              newVersion="10.0.0.0"/>
      

Happy Reporting

Getting the results from a stored procedure into a single variable

I have used both the methods below to put result sets into a single variable.  I will then use that variable as the message body to email the results to myself (or others).

Create Procedure DoSomethingEmailResult

....

I run a bunch of queried looking for data integrity issues, each result set I write into the following table.

    declare @Problems table(
                        SiteId varchar(15) not null,
                        GenId int not null,
                        Alert varchar(max) not null,
                        ProblemDate datetime null default getdate(),
                        AlertLevel varchar(20) null
                )

...

At the end, I want to email the results to myself because I am too lazy to go look for them.

This is how I get a multi-row table worth of results into a single variable.  This becomes the body of my message.

    select @result = coalesce(@result +
        SiteId + char(9) +
        cast(GenId as varchar) + char(9) +
        alert + char(9) +
        AlertLevel + '<br/><br/>','')
        from @problems   


Another query that I have used, but seems a bit more problematic

    set @result = stuff(
    (
    select
        SiteId + char(9) +
        cast(GenId as varchar) + char(9) +
        [Proc] + char(9) +
        alert + char(9) +
        cast(ProblemDate as varchar) +  char(9) +
        AlertLevel + '<br/><br/>'
    from @ErrorTable
    for xml Path(''), Type
    ).value('.', 'varchar(max)'), 0, 1, '');