SSRS: Disappearing MDX Queries

The Reporting Services bug which removes the MDX you've previously typed into the query designer is not a new one. It has been well documented however for the purposes of clarity, I'll review it here too.

You have a SQL 2005 report which uses an SSAS data source. Rather than using the MDX designer, you write your own MDX because you need to do more than the weak-arse SSRS MDX designer can do. All works well you save, deploy, close, whatever. Some time later you come back to it to make a change. You open your Visual Studio project and when you click on the dataset the query is no longer there - you are presented with the blank MDX designer pane as if your query never existed.

It's worth noting that, your query isn't actually gone at this point. Just don't hit SAVE!
If you close the report, right-click it in the Solution and select View Code. Do a search for a tag. You should see your MDX still there.

The common answer directs you to the knowledge base and a particular hotfix which was included in SP1. However, as an SP3 user, I'm currently experiencing the same problem and installing the hotfix solved nothing.

I believe this knowledge base item is no longer relevant because when I look at my report's code-behind by selecting View Code, there is no tag which that hotfix presumes exists.

Googling the issue has also found suggestions like updating your version of SQL Express to the latest service pack. Well, I don't have SQL Express installed at all so it ain't that.

I have had the problem before but I couldn't find a reasonable solution then, as now, either. What did I do to fix it before, you ask? The answer is unfortunately quite painful. I reinstalled BIDS.

Loathe to go through that pain again, I started thinking about what changed since it stopped working this time. Well, loads obviously. But the most obvious change? Installing Visual Studio 2010 Professional.

So here I am installing Visual Studio 2010 SP1 Beta. Yep, beta. That's how desperado I am.

Did it work? I'll tell you as soon as it finishes installing :)

UPDATE: Can't remember what the outcome of this was because at some point I uninstalled VS2010 for some unrelated reason. Having just reinstalled it on Friday, I find that the problem is reoccuring. SP1 Beta is installed however obviously there's no fix there either. Have logged Connect Feedback.

SQL CLR .Net Function - Split

Having needed a split function many times in the past, I've always utilised patindex and done it the hard way…. And when it comes to nvarchar values, forget about it! So today I took a few minutes to write a SQL CLR function to do it.

The function takes a string and a delimiter and returns a table of string values.

Here are the instructions for those who may want to do the same thing…

1. Create a Visual Studio project of the type Database Projects \ SQL Server\ SQL-CLR \ C# SQL Server Project – I called it udfclrSplitString but you know, call it whatever you like!
   
2. Add the following code:
   

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   using System.Collections;

   public class CSRSplitString
   {
   [SqlFunction(FillRowMethodName = "FillRow")]
   

   public static IEnumerable udfclrSplitString(SqlString InputString,SqlString Delimiter)
   
   {
   
   return InputString.Value.Split(Delimiter.Value.ToCharArray(0, 1));
   }
   
   public static void FillRow(object row, out string InputString)
   {
   InputString = (string)row;
   InputString = InputString.Trim();
   }
   }
   

3. Build that sucker
   
4. Drop the dll onto the SQL server somewhere relevant
   
5. If CLR is not already enabled on the server, execute the TSQL code:
   

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO
   

6. In the desired database, execute the following TSQL code:
   

   CREATE ASSEMBLY CSRSplitString
   FROM 'D:\<YourDLLName>.dll'
   WITH PERMISSION_SET = SAFE
   

7. In the desired database, execute the following TSQL code:
   

   CREATE FUNCTION udfclrSplitString
   (@String nvarchar(4000),@Delimiter nvarchar(1))
   RETURNS
   TABLE (ArrayItem nvarchar(4000))
   AS EXTERNAL NAME CSRSplitString.CSRSplitString.udfclrSplitString;
   
   Where external name is MyDotNETAssembly.MyClassname.Method

8. To test it, try:
   

   select *
   from udfclrSplitString('Kristen,Patrick,Julian,Werner,Koe',',')
   
   You should get 5 rows back …

9. If you want to test it using a table column as the input try something like:
   
   Select a.name as DbNameOriginal,s.*
   from msdb.sys.databases a cross
   apply udfclrSplitString(a.name,'_') s
   where a.name like '%_%'
   
   You will need to have the function in the msdb database for this example to work
   
   
10. To make this function automatically available in all new databases created on that server, execute steps 6 + 7 in the model system database.