Jon's Blog

.NET Development & More

Reporting Services: Unspecified Error

First off, I want to start by saying I don't know a whole lot about Reporting Services. Smile

We recently had an issue where a staff member told us that they hadn't been receiving a certain report. When I logged onto Reporting Services and tested the connection to the data source I was getting "Unspecified Error". Not too helpful. I then tried to find the last successful run. I could not find this in the Reporting Services interface. However, there is a view named ExecutionLog2 that had what I wanted.

SELECT TOP 100 elog.status, elog.TimeStart, elog.*
FROM [dbo].[ExecutionLog2] elog
WHERE elog.reportPath = 'MyReportPath'
AND elog.UserName = 'MyReportUser'
ORDER BY elog.timestart DESC

We actually ended up restarting the Reporting Services service and the "Unspecified Error" went away. I just thought I'd post this in case it helps anyone else.

Using SQL Session State with Multiple Environments

I really like storing Session State out-of-process in a SQL Server database. However, I use custom code that switches the connection string based on the current environment my code is running in (test, stage, production, etc.). It was a little tricky to get this setup properly. But I am pretty happy with the following solution.

  • First, you have to setup the Session State database on the SQL machines/environments you are interested in storing your out-of-process session state in. You need to execute a command similar to the following on the command line to build out the database structure:
    aspnet_regsql -S localhost -E -ssadd -sstype p
    See this link for further information:
  • Add the following line in your web.config under the <system.web> node. Make note of the partitionResolverType. We will discuss that further in the next step.
    <sessionState mode="SQLServer" partitionResolverType="MyApplication.Utility.SessionConnStringResolver" timeout="60" cookieless="false"/>
  • The partitionResolverType above can reference a cusom class that can be used to choose which connection string you want to use at run-time. For example you can create the following class for dynamically switching your connection string:
namespace MyApplication.Utility
    public class SessionConnStringResolver : System.Web.IPartitionResolver
        public void Initialize() { }

        // Return the correct ASP.NET Session DB for the current running environment
        public String ResolvePartition(Object key)
            // Custom code to get connection string based on current environment
            return MyConfigClass.GetConfigSettings.SessionDBConnString;


If you are using LINQ to SQL you should also set Serialization Mode to Unidirectional on the Object Relational Designer as discussed in this link. This allows you to serialize/deserialize your LINQ classes when saving to the database.

For a better explanation of the Partition Resolver feature of SQL Server Session State see the following links. Above is just the steps I followed to set everything up.

How to Use FreeTextTable

The FreeTextTable function in SQL Server is very useful when you need to search a column for words, phrases, etc. FreeTextTable will return a table that you can join on.  It also returns the Rank of the matches so you can order the most relevant matches at the top.

In the example below I am searching the Keywords column of the MyTable table using the @Keywords passed into the stored procedure.  I am then ordering them by Rank so the highest matches show up first.

CREATE PROCEDURE [dbo].[FreeTextSample]
    @Keywords [varchar](1000) = ''

    SELECT myTable.MyPrimaryKeyID,
  FROM MyTable myTable(nolock) INNER JOIN FreeTextTable(dbo.MyTable, Keywords, @Keywords) AS searchTable ON myTable.MyPrimaryKeyID = searchTable.[KEY] ORDER By searchTable.[RANK] DESC END GO


One thing to note is that LINQ does not contain an equivalent keyword for FreeTextTable.  So what I did was to create a new method on my DataContext that returns the results of the stored procedure.  See my post here for further details on how to do this.

Source Safe: The character encodings on these files are different. Only files with the same character encoding can be merged or compared.

We saw the above message in SourceSafe after moving to SQL Server 2008 and then saving the scripts for our tables back to SourceSafe.  It appears that SQL 2008 Management Studio uses a different text encoding than prior versions of Management Studio/Enterprise Manager.

To save your script using a specific text encoding.  You can "Save as.." and then there is an arrow to the right of the Save button.  Click this and select "Save with Encoding...".  Then you can select the specific encoding you need.  In my case I needed to change the encoding from "Western Eurpean (Windows) - Codepage 1252" to "Unicode - Codepage 1200".



SQL: Converting DateTime to MM/DD/YYYY Format

I tend to look this up quite a bit, so here it is for quick reference.  This converts a datetime column into a MM/DD/YYYY format.

SET DateColumn2 = CONVERT(varchar(10), DateColumn1, 101)

LINQ Advanced Search

When creating an advanced search you often need to dynamically create the Where statments in your SQL.  Here is an easy way to do this in LINQ.  In the example below orderID, lastName, firstName, shipped are all values retrieved from controls on the page.

var query = from orders in dataContext.MyOrdersTable
select orders;

if (orderID.HasValue)
query = query.Where(order => order.OrderID == orderID);
if (!string.IsNullOrEmpty(lastName))
query = query.Where(order => order.LastName == lastName);
if (!string.IsNullOrEmpty(firstName))
query = query.Where(order => order.FirstName == firstName);
if (shipped.HasValue)
query = query.Where(order => order.Shipped == shipped);

myGridView.DataSouce = query;

SQL Server Trigger for Row Archive

This trigger will write to an archive table when a row is updated or deleted.  Very useful when you want to know who did what and when.

USE [DatabaseName]


CREATE TRIGGER [dbo].[trigTableName_Archive] 
   ON  [dbo].[TableName]
    FROM deleted


LINQ, Visual Studio, and Stored Procedure Mapping

So the other day while attempting to drag a stored procedure onto the LINQ Designer in Visual Studio I received the following error:


The stored procedure was rather complex including a call to FREETEXT and for some reason the LINQ Designer would not accept it, so I manually mapped the stored procedure for the DataContext.  For future reference, here is some sample code:

public partial class MyDataContext
[Function(Name = "dbo.MyStoredProc")]
public ISingleResult<MyClass> MyProcName(
[Parameter(Name = "Parameter1")] int? parameter1,
[Parameter(Name = "Parameter2")] ProductCategory? parameter2,
[Parameter(Name = "Parameter3")] bool? parameter3)
IExecuteResult result = this.ExecuteMethodCall(this,
parameter1, parameter2, parameter3);

return ((ISingleResult<MyClass>)(result.ReturnValue));


Then you can call the stored procedure in code like so:

var results = myDC.MyProcName(parameter1, parameter2, parameter3);