## Broken Windows

June 12, 2017

Dear Windows, why do you place a broken shortcut into your search results?

Title obviously inspired by Broken Windows. Brought to you by “broken Windows”.

But who are those “We” who never seem to be able to accomplish anything?

## Viewing ELMAH Logs of multiple applications

January 26, 2017

I recently added ELMAH and ELMAH.Mvc to a couple of web applications, and configured them to log into the same MSSQL database.

The table ELMAH_Error distinguishes the source of error in the columns Host (storing the host name of the web application) and Application (storing the IIS Configuration Path of the web application, for explanations see e.g. here).

By default, the /elmah handler only displays the error messages of the current application.

However, I wanted to see the errors of all ELMAH-enabled applications.

After some research into the C# code and the MSSQL database, the solution is surprisingly simply:

• Create a new database, e.g. “elmah-read”
• Create synonyms to the original table and the logging SP (we won’t really need this SP)
CREATE SYNONYM ELMAH_Error FOR Elmah.dbo.ELMAH_Error
GO
CREATE SYNONYM ELMAH_LogError FOR Elmah.dbo.ELMAH_LogError
GO
• Copy the stored procedure [dbo].[ELMAH_GetErrorXml] and remove the @Application from the WHERE clause
WHERE [ErrorId] = @ErrorId
--AND [Application] = @Application
• Copy the stored procedure [dbo].[ELMAH_GetErrorsXml], removing the @Application from the WHERE clauses, and extending the selected [host] column
 SELECT
errorId = [ErrorId],
application = [Application],
host = [Host] + ' ' +
RIGHT( [Application], CHARINDEX( '/', REVERSE( [Application] ) + '/' ) - 1 ),
• Create an empty ASP.Net Web Application in VS, add Elmah Core Library and Elmah.Mvc from NuGet, and configure the connection string as in the original web applications
<elmah>
<security allowRemoteAccess="true"/>
</elmah>
• Don’t forget to properly configure the Authentication feature of the log viewer application in IIS
• Run

## Feature Request

July 28, 2014

You know that your product is missing a critical feature, if a quick search (case in point: “Firefox search bookmark folder name”) brings up forum entries dating back at least 5 years:

“firefox search bookmark folder name”

## Surprises Changing the Length of a Character Field in SSMS

December 14, 2013

I needed to convert a couple of CHAR and NCHAR columns to their VARCHAR/NVARCHAR equivalent, and noticed a peculiar behavior of the SSMS (2008 R2) table designer:

For example, when the original column was defined as CHAR(10), and I added the characters VAR at the beginning of the Data Type field and tabbed to the next column, the editor would not keep the resulting VARCHAR(10), but change the data type to VARCHAR(50), as is the default length for VARCHAR and NVARCHAR columns in the designer.

I found that this behavior was discussed in this SO question as occurring in SSMS 2005, confirmed in 2008, and, by personal experience, still exists in 2008R2 and 2012.

It seems that MS either does not care, or that it does not want to fix weird bugs due to “compatibility issues”.

## VS – Side-Effects of Upgrading a Project’s .Net Framework Version

September 16, 2013

The other day I was playing around with two of my applications, checktsql and SMOscript, as I was considering to include the functionality of checktsql into SMOscript.

Now, up to the current versions, both applications have been developed using Visual Studio 2008 targeting .Net 2.0. (I prefer to keep requirements to a minimum). But now I thought it was time to migrated them to VS2010/3.5 (client framework), and came across a couple of unresolved mysteries.

### Missing Exceptions in SqlCommand.Execute*()

The first mystery is that the behavior of the Execute* methods changed such that ExecuteNonQuery() and ExecuteReader() do not reliably raise exceptions any more if an error occurs in T-SQL.

Take the example

CREATE PROCEDURE TestTemp AS
CREATE TABLE #T (id int)
SELECT * FROM #T
GO

which checktsql tries to verify (with the SET FMTONLY ON option) like this

BEGIN TRAN
SET FMTONLY ON; EXECUTE [dbo].TestTemp
ROLLBACK

(Note that the transaction is actually created by SqlConnection.BeginTransaction(), but essentially it’s the same mechanism)

SSMS returns an error:

Msg 208, Level 16, State 0, Procedure TestTemp, Line 6
Invalid object name '#T'.

What I noticed after migrating the code to VS2010/.Net3.5 is that some (most?) stored procedures cause the error to be raised as an exception in .Net, and some do not. However, the behavior is consistent for each stored procedure.

Fortunately, I noticed the different behavior only when using the SET FMTONLY ON option, but still, there is a difference depending on which VS or framework version is used.

Teh internets did not really help me – I found a couple of discussions, but no documentation of the change, or how to get the original semantics back:

### SMO dependency on System.Core

When I tried to debug (and understand) above issue, and reverted the application back to .Net 2.0, but still build with VS2010, I suddenly got the error message during build:

The primary reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the framework assembly “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089” which could not be resolved in the currently targeted framework. “.NETFramework,Version=v2.0”. To resolve this problem, either remove the reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” or retarget your application to a framework version which contains “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

There was nothing I could do to get VS2010 to the original behavior (i.e.: compile successfully) targeting .Net 2.0, even though the same assemblies were referenced both in the VS2008 and the VS2010 project.

This thread on MS connect shows the “amusing” dependency zigzag that SMO assemblies implement, switch dependencies even between minor releases:

Posted by Chris Dennis on 4/20/2011 at 10:19 AM
Installing SQL Server 2008R2 Cumulative Update 7 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1777.0 which again depends on System.Core v3.5.
Posted by Chris Dennis on 3/25/2011 at 12:50 PM
Installing SQL Server 2008R2 Cumulative Update 6 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1765.0 which does not depend on System.Core v3.5. Only version 10.50.1750.9 has a dependency on System.Core v3.5.
Posted by Microsoft on 3/25/2011 at 11:08 AM
Your assertion is correct the Microsoft.SqlServer.Management.SqlParser took a dependency on System.Core v3.5. Any projects which reference this assembly directly or indirectly need to be targeted to .NET Framework V3.5.
This is a nightmare!

## Dealing with NHibernate System.IndexOutOfRangeException

September 4, 2013

Whenever I call SQL Server stored procedures from an NHibernate (3.2) application, I generate proxy methods that look like this

IList<T> MyProcedure<T>(ISession session
, [some parameters]
)
{
IQuery sp = session.CreateSQLQuery(@"BEGIN
EXECUTE MyProcedure
:SomeParameter
END")
sp.SetParameter<[datatype]>("SomeParameter", SomeParameter);
...;
sp.SetResultTransformer(Transformers.RootEntity);
//sp.SetTimeout(timeout);
return sp.List<T>();
}

and manually create the data class MyProcedureResult and the corresponding mapping class MyProcedure_Result.

The SP is invoked like this:

var result = Procedures.MyProcedure<MyProcedureResult>(session, [parameters]);

This usually works, but recently I experienced an exception when calling a specific SP:

[Data]

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: [Data]

Source Error:

Stack Trace:

[IndexOutOfRangeException: [Data]]
System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +5043194
NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:236
NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:195
NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\AbstractType.cs:131

[ BEGIN
EXECUTE MyProcedure
@p0
,@p1
END ]
[SQL: BEGIN
EXECUTE MyProcedure
@p0
,@p1
END]]
NHibernate.Impl.StatelessSessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:376
NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:353
NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:363
NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:160

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.547

As you can see, NH expects the column name [Data], which does not exist in the result set, causing GetOrdinal() to throw an exception.

What’s going on here?

Well, the stored procedure returns a result set containing the column name Data, but NH tries to find the column [Data].

After stepping through the source code of NH 3.2, I claim to partially understand how this happens.

When the entities and mappings are initialized, NH calls connection.GetSchema(DbMetaDataCollectionNames.ReservedWords) to check whether column names are reserved words. If a column name is found to be a reserved word, the name is flagged to be quoted and/or aliased when used in SQL.

If, however, the column name occurs in the result set of an SP, aliasing cannot be applied, and quoting is not required.

Quoting using backticks (name) as proposed on SO did not help either.

After many experiments, my solution was to simply rename the columns in the SP (DataX), and add a column name mapping

map.Column("DataX");

to the class map.

## Visual Studio 2010: Publish hangs

May 23, 2013

I noticed something weird the last couple of days, something I had never experienced before:

Select an ASP.Net MVC application in Solution Explorer, right-click and select Publish…

The Publish dialog opens, I select publish to File System and enter the target path.

The Build process starts and completes, but the last line that is displayed in the Output Window is

Connecting to C:\path\to\publish...`

Then Visual Studio hangs at 100% CPU usage (or, 25% on a quad-core machine).

You can hit Ctrl-Break, and the break is indicated, but nothing else happens, and VS is busy burning CPU cycles.

Fortunately I found this entry on Connect, and the first Workaround immediately solved my problem:

Delete the .suo file!