A client recently saw a strange error after installing a new BizTalk 2010 application. The app produced the following error when using the WCF SQL adapter to connect to a remote SQL database:
The adapter failed to transmit message going to send port "RequestMessageData" with URL "mssql://VSQL05.PROD.acme.com//Messages?". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed. ---> System.Runtime.InteropServices.COMException: The MSDTC transaction manager was unable to push the transaction to the destination transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers. (Exception from HRESULT: 0x8004D02A)
At first I thought the error might have to do with the fact that the SQL address is virtual. But after some flopping about like a fish on deck, we found a guy at the client site who knew MSDTC really well. He suggested that we choose the "Enable XA Transactions" option in the DTC properties dialog box. I didn't know it at the time, but the production DB was in SQL 2000, running on an older version of Windows. The QA DB had been SQL 2005.
That fix didn't work by itself (there was another issue), and then I remembered that I once ran into something like this when installing BizTalk 2004 for another client. In that case, I had to select "No Authentication Required" in the MSDTC properties on all boxes. But since I knew that no request was going to be coming from SQL 2000, I selected "Incoming Caller Authentication Required". Problem solved!
Wednesday, August 10, 2011
Tuesday, August 02, 2011
BizTalk Map - Copy Node containing HTML
I recently had to map data from an InfoPath 2007 form to a canonical schema. The input from InfoPath usually contained HTML, although on occasion it contained encoded HTML (depending on where the original data was pulled from). The output needed to be in a CDATA section so that it could be treated as plain old text.
After consulting several people at work and looking at lots of blog posts, I was able to pull off a solution. I'm sure there are ways to improve the quality of the code, but it does work. One thing I don't like about this solution is that it adds a namespace attribute to the top level HTML nodes. Fortunately every browser I tested with ignores the namespace.
Here's the source schema:
And here's the contents of the Inline XSLT functoid that I used:
<xsl:element name="ns0:FreeformData1" >
<xsl:text disable-output-escaping="yes"><![CDATA[</xsl:text>
<xsl:choose>
<xsl:when test="count(/*[local-name()='myFields']/*[local-name()='FreeData1']/child::*/child::node()) > 0">
<xsl:for-each select = "/*[local-name()='myFields']/*[local-name()='FreeData1']/child::node()">
<xsl:copy-of select="self::*" />
</xsl:for-each>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string(/*[local-name()='myFields']/*[local-name()='FreeData1'])" disable-output-escaping="yes"/>
</xsl:otherwise>
</xsl:choose>
<xsl:text disable-output-escaping="yes">]]></xsl:text>
</xsl:element>
If the input XML looks like this:
<ns0:myFields xmlns:ns0="http://sampleforblog.namespace.com">
<ns0:field1>Test data</ns0:field1>
<ns0:FreeData1><html><FONT color="#ff0000">Hello</FONT> world this <STRONG>is a test</STRONG></html></ns0:FreeData1>
</ns0:myFields>
Then the output XML looks like this:
<ns0:yourFields xmlns:ns0="http://sample2forblog">
<ns0:field>Test data</ns0:field>
<ns0:FreeformData1><![CDATA[<html xmlns:ns0="http://sampleforblog.namespace.com"><FONT color="#ff0000">Hello</FONT> world this <STRONG>is a test</STRONG></html>]]></ns0:FreeformData1>
</ns0:yourFields>
I'm not showing the case where the HTML is encoded, but I did test that as well. Encoded HTML is handled by the "otherwise" case in the XSLT above.
After consulting several people at work and looking at lots of blog posts, I was able to pull off a solution. I'm sure there are ways to improve the quality of the code, but it does work. One thing I don't like about this solution is that it adds a namespace attribute to the top level HTML nodes. Fortunately every browser I tested with ignores the namespace.
Here's the source schema:
And here's the contents of the Inline XSLT functoid that I used:
<xsl:element name="ns0:FreeformData1" >
<xsl:text disable-output-escaping="yes"><![CDATA[</xsl:text>
<xsl:choose>
<xsl:when test="count(/*[local-name()='myFields']/*[local-name()='FreeData1']/child::*/child::node()) > 0">
<xsl:for-each select = "/*[local-name()='myFields']/*[local-name()='FreeData1']/child::node()">
<xsl:copy-of select="self::*" />
</xsl:for-each>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string(/*[local-name()='myFields']/*[local-name()='FreeData1'])" disable-output-escaping="yes"/>
</xsl:otherwise>
</xsl:choose>
<xsl:text disable-output-escaping="yes">]]></xsl:text>
</xsl:element>
If the input XML looks like this:
<ns0:myFields xmlns:ns0="http://sampleforblog.namespace.com">
<ns0:field1>Test data</ns0:field1>
<ns0:FreeData1><html><FONT color="#ff0000">Hello</FONT> world this <STRONG>is a test</STRONG></html></ns0:FreeData1>
</ns0:myFields>
Then the output XML looks like this:
<ns0:yourFields xmlns:ns0="http://sample2forblog">
<ns0:field>Test data</ns0:field>
<ns0:FreeformData1><![CDATA[<html xmlns:ns0="http://sampleforblog.namespace.com"><FONT color="#ff0000">Hello</FONT> world this <STRONG>is a test</STRONG></html>]]></ns0:FreeformData1>
</ns0:yourFields>
I'm not showing the case where the HTML is encoded, but I did test that as well. Encoded HTML is handled by the "otherwise" case in the XSLT above.
Friday, July 29, 2011
SharePoint - Counting Social Tags
I found some code on the web for counting social tags, but unfortunately it failed when the page was a "welcome" page. The code below handles that special case. I used a Web Service Reference to the SocialDataService web service, although I probably could have created a (WCF) Service Reference instead.
The method IsWelcomePage() is detailed in my previous post. The method TrimPageFromUrl() is fairly simple, it just changes a URL such as http://spsite/pages/default.aspx to http://spsite/.
The method IsWelcomePage() is detailed in my previous post. The method TrimPageFromUrl() is fairly simple, it just changes a URL such as http://spsite/pages/default.aspx to http://spsite/.
private void CountTagsByTermNameAndUrl()
{
using (SocDataSvc.SocialDataService socialDataService = new SocDataSvc.SocialDataService())
{
socialDataService.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
socialDataService.Url = GetUrlWithUpdatedDomain(socialDataService.Url, m_siteUrl);
SocDataSvc.SocialTermDetail termDetail;
try
{
termDetail = socialDataService.GetTagTermsOnUrl(m_siteUrl, null)
.Where(detail => detail.Term.Name == m_termName).SingleOrDefault();
if (termDetail == null && IsWelcomePage(m_siteUrl))
{ // this could be the default page, remove the last part of the Url and try again
m_siteUrl = TrimPageFromUrl(m_siteUrl);
termDetail = socialDataService.GetTagTermsOnUrl(m_siteUrl, null)
.Where(detail => detail.Term.Name == m_termName).SingleOrDefault();
}
}
catch (Exception ex)
{
// handle exception
}
// make sure we got at least one result
m_tagCount = (termDetail != null && termDetail.Term.Name == m_termName) ? termDetail.Count : 0;
}
}
Getting the Welcome Page for a SharePoint Publishing Site
I wish I had found this sooner, it could have saved me a lot of time:
private static bool IsWelcomePage(string url)
{
bool rslt = false;
using (SPSite site = new SPSite(url))
{
using (SPWeb web = site.OpenWeb())
{
if (PublishingWeb.IsPublishingWeb(web))
{
PublishingWeb pweb = PublishingWeb.GetPublishingWeb(web);
string defaulturl = pweb.DefaultPage.Url;
if (defaulturl == new Uri(url).GetComponents(UriComponents.Path, UriFormat.SafeUnescaped))
{
rslt = true;
}
}
}
}
return rslt;
}
Properties for SharePoint 2010 Visual Web Parts
I seem to spend a lot of time looking up where to set various properties in Visual Web Part projects, so I'm recording what I have learned recently. Here's how to set the Category that the web part will appear in when adding it to a page:
<Property Name="Group" Value="Acme" />
This property is in the Elements.xml file for the web part. In this case, the Category will be Acme.
Here's a picture of some things that affect the appearance of this feature in SharePoint under Manage Features. Title and description should be fairly obvious. The interesting thing about this feature is that it has a dependency on another feature called Social Ribbon. I found the GUID by looking in the TEMPLATE\FEATURES folder in the 14 hive. In my case, I opened the Feature.xml file in the SocialRibbonControl folder and copied the Feature Id value.
I also wanted show an image in the feature gallery next to the feature. First, I right clicked my project name in VS Solution Explorer and selected Add / SharePoint "Images" mapped folder. I created a sub-folder directly in the Images folder and copied my image there. Finally, I edited the Feature1.Template.xml file and added the following attribute to the Feature element: ImageUrl="Neudesic\Neudesic.jpg".
Finally, here are some properties that I added to the .webpart file: to affect the display of the web part in the web part gallery and to set a couple of properties that take effect when the web part displays on a page:
<property name="Title" type="string">Acme Web Part</property>
<property name="Description" type="string">Displays stuff, along with doing other stuff"</property>
<property name="ChromeType" type="chrometype">None</property>
<property name="Width" type="unit">200</property>
<Property Name="Group" Value="Acme" />
This property is in the Elements.xml file for the web part. In this case, the Category will be Acme.
Here's a picture of some things that affect the appearance of this feature in SharePoint under Manage Features. Title and description should be fairly obvious. The interesting thing about this feature is that it has a dependency on another feature called Social Ribbon. I found the GUID by looking in the TEMPLATE\FEATURES folder in the 14 hive. In my case, I opened the Feature.xml file in the SocialRibbonControl folder and copied the Feature Id value.
I also wanted show an image in the feature gallery next to the feature. First, I right clicked my project name in VS Solution Explorer and selected Add / SharePoint "Images" mapped folder. I created a sub-folder directly in the Images folder and copied my image there. Finally, I edited the Feature1.Template.xml file and added the following attribute to the Feature element: ImageUrl="Neudesic\Neudesic.jpg".
Finally, here are some properties that I added to the .webpart file: to affect the display of the web part in the web part gallery and to set a couple of properties that take effect when the web part displays on a page:
<property name="Title" type="string">Acme Web Part</property>
<property name="Description" type="string">Displays stuff, along with doing other stuff"</property>
<property name="ChromeType" type="chrometype">None</property>
<property name="Width" type="unit">200</property>
Tuesday, July 26, 2011
Error Handling Code for Web Part
I keep needing to write the same error handling code for Web Parts, HTML pages, etc. The only difference is in how I display the error. This is something I wrote for a client who told me that he wanted the web part to look ugly if an exception is thrown. Well, this does just that! The label with ID lblError is invisible until DisplayException gets called.
private void DisplayException(Exception ex)
{
using (StringWriter stringWriter = new StringWriter())
{
using (HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter))
{
// show exception message
HtmlWriteLine("Exception", ex.Message, htmlTextWriter);
HtmlWriteLine("Method", ex.TargetSite, htmlTextWriter);
HtmlWriteLineReplaceNewLines("Stack Trace", ex.StackTrace, htmlTextWriter);
Exception exInner = ex.InnerException;
int innerExceptionNum = 1;
while (exInner != null)
{
htmlTextWriter.WriteBreak();
htmlTextWriter.Write("-----");
htmlTextWriter.WriteBreak();
HtmlWriteLine(String.Format("Inner Exception {0}", innerExceptionNum), exInner.Message, htmlTextWriter);
HtmlWriteLine("Method", exInner.TargetSite, htmlTextWriter);
HtmlWriteLineReplaceNewLines("Stack Trace", exInner.StackTrace, htmlTextWriter);
exInner = exInner.InnerException;
innerExceptionNum++;
htmlTextWriter.WriteBreak();
}
lblError.Text = stringWriter.ToString();
lblError.Visible = true;
}
}
}
private void HtmlWriteLineReplaceNewLines(string label, string text, HtmlTextWriter htmlTextWriter)
{
htmlTextWriter.Write(String.Format("{0}=", label));
string[] textArray = text.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
foreach (string s in textArray)
{
htmlTextWriter.Write(s);
htmlTextWriter.WriteBreak();
}
}
private static void HtmlWriteLine(string label, Object value, HtmlTextWriter htmlTextWriter)
{
htmlTextWriter.Write(String.Format("{0}=\"{1}\"", label, value));
htmlTextWriter.WriteBreak();
}
Thursday, June 09, 2011
Schema referenced by Map...has been deleted
While trying to troubleshoot an automated installer for the BizTalk ACME.Notifications application, my client was seeing this error:
Schema referenced by Map 'ACME.Notifications.Insertion.BizTalk.Transforms.Notification_to_InsertionRequest' has been deleted. The local, cached version of the BizTalk Server group configuration is out of date. You must refresh the BizTalk Server group configuration before making further changes.
We saw it in the log of the automated installation, and then when we tried to use the BizTalk Administration Console to view the application, we also saw the error. The error also prevented us from removing DLLs from the ACME.Notifications application.
The map named in the error is the only schema where I actually changed the name of one of the schemas in the map. I think that the map reference got broken when we installed the new schema DLL which no longer had the old map name, and at that point BizTalk choked – in a way that was unrecoverable.
We tried running the auto installer again, but that did not resolve the issue. We also tried installing this hotfix: http://support.microsoft.com/kb/2516201, which references the same error message, but that did not resolve the issue, even after re-deploying.
In order to fix this, we had to go into the database BizTalkMgmtDb and delete the relevant map from the table bt_MapSpec. Since this table does not list the names of the maps, I found the relevant schema by looking that the field “outspec_docspec_name”, which had the old name of the schema. Then I used the BizTalk Management Console to delete all of the DLLs, followed by redeploying, and everything started working again.
In order to prevent this from being a possible issue in the future, we rewrote the installer so that it deletes the DLLs from BizTalk before trying to add in the latest versions.
Wednesday, May 11, 2011
Beware of .CS Files
I just had the joy of debugging a thorny issue in BizTalk 2010. I spent quite a while wondering why a property schema I had changed was not showing up in the Admin Console with the changes. I even went to the trouble to delete everything from the GAC using gacutil (the BizTalk Assembly Viewer isn't supported on my 64 bit operating system). Still the schema was unchanged.
It finally occurred to me to look at the .CS file corresponding to the problem XSD file. The .CS file had not changed at all, and the attribute on it was read-only. Deleting it from the file system just caused me to get other errors when I compiled.
Then I looked in TFS, and I realized that a co-worker had added the .CS file there. When I deleted the .CS file from TFS, the issue was resolved. Now the schema changes show up in the Admin Console.
It finally occurred to me to look at the .CS file corresponding to the problem XSD file. The .CS file had not changed at all, and the attribute on it was read-only. Deleting it from the file system just caused me to get other errors when I compiled.
Then I looked in TFS, and I realized that a co-worker had added the .CS file there. When I deleted the .CS file from TFS, the issue was resolved. Now the schema changes show up in the Admin Console.
Friday, April 22, 2011
BizTalk 2010 SQL Adapter
I'm finally working with BizTalk 2010, after hearing about it for quite a while. I love the new mapper.
I was using the new WCF-SQL Adapter. I like the new way of doing things, it seems much easier than to use than the old one. In order to invoke it, I right clicked the project and then choose Add / Add Generated Items. Then I clicked Consumer Adapter Service and clicked Add.
On the new dialog box, under Select a binding I chose sqlBinding, and then clicked Configure. On the popup, I chose Windows security. On the URI Properties tab, I entered values for Server and InitialCatalog, and then clicked OK. Back to the Consume Adapter Service dialog, where I clicked Connect, which refreshed some metadata below. Using the tree under Select a Category, I navigated to Strongly-Typed Procedures. I then clicked on my stored proc under Available categories and operations and clicked Add. I entered a filename prefix for the new schemas, and clicked OK.
Two new schemas were created, along with a binding file for the WCF port. The schema file that I use for creating messages, mapping, etc. ends with ...TypedProcedure.dbo.xsd. There's another schema that appears to create a schema that has the types for records to be returned from the proc. One is for stored procs returning one record, and another for stored procs returning multiple records. By default, the ...TypedProcedure.dbo.xsd uses the type for single records, but it looks as though it would be pretty easy to set it up for multiple records.
So I created my messages and imported the binding file WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml using BizTalk Admin Console. When I first tried sending a message through the port, I was surprised to see the following message:
The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://localhost//StevesTest?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="GetByAccountNo" Action="TypedProcedure/dbo/GetByAccountNo" />
</BtsActionMapping>" was not understood.
Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".
I don't remember where I found the answer to this, but it turns out that in the send port, in the SOAP action header, you have to specify the Operation Name as the same value that the Operation is inside of the Orchestration send port. By default in the Orchestration, it is Operation_1. By default when the binding for the port was created, it was GetByAccountNo, the name of the stored proc. When I made the name of the Operation on the port inside the orchestration the same as the Operation Name in the binding, all was well.
This is actually the first time I can remember that the name of the Operation inside of an Orchestration in BizTalk had significance. Until now I have always left the Operation name as the default.
I was using the new WCF-SQL Adapter. I like the new way of doing things, it seems much easier than to use than the old one. In order to invoke it, I right clicked the project and then choose Add / Add Generated Items. Then I clicked Consumer Adapter Service and clicked Add.
On the new dialog box, under Select a binding I chose sqlBinding, and then clicked Configure. On the popup, I chose Windows security. On the URI Properties tab, I entered values for Server and InitialCatalog, and then clicked OK. Back to the Consume Adapter Service dialog, where I clicked Connect, which refreshed some metadata below. Using the tree under Select a Category, I navigated to Strongly-Typed Procedures. I then clicked on my stored proc under Available categories and operations and clicked Add. I entered a filename prefix for the new schemas, and clicked OK.
Two new schemas were created, along with a binding file for the WCF port. The schema file that I use for creating messages, mapping, etc. ends with ...TypedProcedure.dbo.xsd. There's another schema that appears to create a schema that has the types for records to be returned from the proc. One is for stored procs returning one record, and another for stored procs returning multiple records. By default, the ...TypedProcedure.dbo.xsd uses the type for single records, but it looks as though it would be pretty easy to set it up for multiple records.
So I created my messages and imported the binding file WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml using BizTalk Admin Console. When I first tried sending a message through the port, I was surprised to see the following message:
The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://localhost//StevesTest?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="GetByAccountNo" Action="TypedProcedure/dbo/GetByAccountNo" />
</BtsActionMapping>" was not understood.
Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".
I don't remember where I found the answer to this, but it turns out that in the send port, in the SOAP action header, you have to specify the Operation Name as the same value that the Operation is inside of the Orchestration send port. By default in the Orchestration, it is Operation_1. By default when the binding for the port was created, it was GetByAccountNo, the name of the stored proc. When I made the name of the Operation on the port inside the orchestration the same as the Operation Name in the binding, all was well.
This is actually the first time I can remember that the name of the Operation inside of an Orchestration in BizTalk had significance. Until now I have always left the Operation name as the default.
Friday, February 04, 2011
SSR Report Fun
I was working on an SSRS Report recently that needed to select a bunch of, shall we say, Widgets, and the filter criterion was to select one or more Widget Managers. Each widget manager ID is a GUID. Fairly easy to do in SSRS. Here's the basic query:
And here's the query to select the Widget Managers:
Here's what the parameter definition looks like:
This works okay, but for my purposes there were 2 issues. First of all, my client wanted all items to be selected by default. That's an easy problem to handle, all I need to do is to set the Default values for the WidgetManagers report parameter to the same dataset I use for Available values.
Unfortunately, that query ran really slowly. When I looked at the query using SQL Profiler, it looked like this:
I only showed 3 of the GUIDS, but the query sent to SQL had all 1000+ of them inline in the query. No wonder it was slow.
I'll skip to the final solution. I changed the query for the Widget Managers to look like this:
Then I changed the query for the Widgets themselves to look like this:
And finally, I changed the report parameter to look like this:
The basic idea is that when the report first comes up, "All Widget Managers" is chosen, and all other Widget Managers are unchecked. When the Widget query runs, @WidgetManagerCount will be set to 0, and only the top part of the outer case statement will be evaluated, and all Widgets will display on the report.
If any other Widget Managers are checked, @WidgetManagerCount will then be greater than zero, which will cause the inner case statement to be evaluated for each Widget. If a Widget Manager has been checked, all related Widgets will be displayed on the report.
This version of the report ran much, much faster than the original. What I'm showing above is a stripped down version of the report, but the real one had 4 filter criteria, and a couple of those filter criteria had thousands of choices.
SELECT * FROM Widget W
WHERE WM.WidgetManagerId IN (@WidgetManagers)
And here's the query to select the Widget Managers:
SELECT FirstName + ' ' + LastName AS WidgetManagerName, WidgetManagerId
FROM WidgetManager
ORDER BY WidgetManagerName
Here's what the parameter definition looks like:
This works okay, but for my purposes there were 2 issues. First of all, my client wanted all items to be selected by default. That's an easy problem to handle, all I need to do is to set the Default values for the WidgetManagers report parameter to the same dataset I use for Available values.
Unfortunately, that query ran really slowly. When I looked at the query using SQL Profiler, it looked like this:
SELECT * FROM Widget W
INNER JOIN WidgetManager WM ON
W.WidgetManagerId = WM.WidgetManagerId
WHERE WM.WidgetManagerId IN
(N'0fa07056-8e50-43a0-b72d-000a68d17be1',
N'8eff8f59-ca6a-4eed-a434-016a8831c7ec',
N'2a3a885e-8fb2-4e8b-b55a-02a7225a1143',
...)
I only showed 3 of the GUIDS, but the query sent to SQL had all 1000+ of them inline in the query. No wonder it was slow.
I'll skip to the final solution. I changed the query for the Widget Managers to look like this:
SELECT 'All Widget Managers' AS WidgetManagerName,
'00000000-0000-0000-0000-000000000000' AS WidgetManagerId, 1 AS OrderBy
UNION
SELECT FirstName + ' ' + LastName AS WidgetManagerName,
WidgetManagerId, 2 AS OrderBy
FROM WidgetManager
ORDER BY OrderBy, WidgetManagerName
Then I changed the query for the Widgets themselves to look like this:
DECLARE @WidgetManagerCount AS INT
SET @WidgetManagerCount = (SELECT COUNT(*) FROM WidgetManager
WHERE WidgetManagerId IN (@WidgetManagers))
SELECT * FROM Widget W
WHERE
(1 =
CASE
WHEN @WidgetManagerCount = 0 THEN 1
ELSE
CASE
WHEN W.WidgetManagerId IN (@WidgetManagers) THEN 1
ELSE 0
END
END)
And finally, I changed the report parameter to look like this:
The basic idea is that when the report first comes up, "All Widget Managers" is chosen, and all other Widget Managers are unchecked. When the Widget query runs, @WidgetManagerCount will be set to 0, and only the top part of the outer case statement will be evaluated, and all Widgets will display on the report.
If any other Widget Managers are checked, @WidgetManagerCount will then be greater than zero, which will cause the inner case statement to be evaluated for each Widget. If a Widget Manager has been checked, all related Widgets will be displayed on the report.
This version of the report ran much, much faster than the original. What I'm showing above is a stripped down version of the report, but the real one had 4 filter criteria, and a couple of those filter criteria had thousands of choices.
Subscribe to:
Posts (Atom)