Sql Server Case sensitive query

MS Sql Server is case insensitive by default. You can change this behavior for the entire database which you do not usually want if you need this only for one particular query. You can use te COLLATE clause to do this only for this particular query.

Select Foo 
from Bar
where Foo = 'Bar' COLLATE SQL_Latin1_General_CP1_CS_AS

This query and especially the part Foo ='Bar' is now case sensitive.

ASN1 out of memory

If you ever get the Exception "ASN1 out of memory" on a Windows 7 oder Windows Server 2008 R2, this is due to a bug in Windows. You can find the Solution and te Hotfiy at http://support.microsoft.com/kb/2480994. On my maschine it even started when i was trying to decrypt a 3 MB encrypted content. The Hotfix is not integrated in any Update or Service Pack.

Signing Wix Bootstrapper

If you use the Burn Bootstrapper from wix to install multiple MSI packages e.g. for installing .net or sql Server Local Storage you might run into an error when you codesign your installer. The Error log might look similar to this

Error 0x80070001: Failed to extract all files from container, erf: 1:2:0
Error 0x80070001: Failed to wait for operation complete.
Error 0x80070001: Failed to open container.
Error 0x80070001: Failed to open container: WixAttachedContainer.
Failed to extract payloads from container: WixAttachedContainer to working path: C:\Users\....\AppData\Local\Temp\{b00b300e-dcbd-4db6-9424-fff3f5869c94}\9294FEEED62481A625CDDD3C0742A729F6A9BC9A, error: 0x80070001.
Error 0x80070001: Failed while caching, aborting execution.

The problem is caused by the burn engine not beeing signed.To fix this you just need to perform the following steps after building the bootstrapper package.

  1. detach the engine from YourInstaller:
    insignia -ib "YourInstaller.exe" -o engine.exe
  2. sign engine.exe with your certificate
  3. re-attach the signed engine.exe to the bundle:
    insignia -ab engine.exe "YourInstaller.exe" -o "YourInstaller.exe"
  4. sign "YourInstaller.exe" with your certificate


You will find the insignia.exe in the Wix Toolkit's bin folder.

For more information refer to the Wix Documentation


Json Data Sql Script

We store some JSON Data in our Database to bee more flexibel with changes in the Datamodel. Everything works fine till the moment you need to get the value of a property. Then your struggeling with Sql Statements charindex and substring.

This is the reason why i created a short script which allows you to get JsonProperties out of your Json string,


IF OBJECT_ID (N'dbo.GetJsonPropertyValue', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetJsonPropertyValue;
CREATE FUNCTION dbo.GetJsonPropertyValue (@Content varchar(MAX),@PropertyName varchar(100), @IsPropertyValueString bit)
RETURNS varchar(255)
     DECLARE @JsonFormatLength int;
     DECLARE @PropertyNameLength int;
     SET @PropertyNameLength= len(@propertyName)
     set @JsonFormatLength = case when @IsPropertyValueString = 1 then 2 else 1 end
     set @PropertyName = '"'+@PropertyName+'"'
     set @PropertyNameLength = LEN(@PropertyName)

return SUBSTRING(@Content, charindex(@PropertyName,@Content) + @PropertyNameLength + @JsonFormatLength,
case when charindex(',',@Content,charindex(@PropertyName,@Content) + @PropertyNameLength + @JsonFormatLength) > 0
then charindex(',',@Content,charindex(@PropertyName,@Content) + @PropertyNameLength + @JsonFormatLength)-
(charindex(@PropertyName,@Content) + @PropertyNameLength + @JsonFormatLength +1)
else charindex('}',@Content,charindex(@PropertyName,@Content) + @PropertyNameLength)-
(charindex(@PropertyName,@Content) + @PropertyNameLength + @JsonFormatLength + case when @IsPropertyValueString = 1 then 1 else 0 end)
IF OBJECT_ID (N'dbo.GetJsonPropertyValue', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetJsonPropertyValue;


SELECT dbo.GetJsonPropertyValue(JsonContent,'Foo',1) from bar

In the script I create a function which can be used within the select statement. Everythin is pretty simple. You just need to pass the Content where to search, the propertyname and wether it is a string type Value e.g. strings and Datetimes or not.
Hope this helps when you need to get a property value real quick from the Database.

Ajax call Window.Open triggers Popupblocker

There are scenarios when you do not want to display the link in the html, so you're trying to make an ajax call to get the url and try to open it in a new window. This triggers the Popupblocker of the browser though. To prevent that, just open the window, and Redirect in the Asp.Net Controller to the new Url. This will not trigger the Popupblocker and gives the user a better feeling.

public ActionResult OpenExternalUrl(){
return Redirect("http://google.com");

Hope this helps.


X509Certificate The System cannot find the file specified

When you try to load a certificate within an Asp.Net Application you might get an error which states:


Type:System.Security.Cryptography.CryptographicException: The system cannot find the file specified.

   at System.Security.Cryptography.CryptographicException.ThrowCryptographicException(Int32 hr)
   at System.Security.Cryptography.X509Certificates.X509Utils._LoadCertFromFile(String fileName, IntPtr password, UInt32 dwFlags, Boolean persistKeySet, SafeCertContextHandle& pCertCtx)
   at System.Security.Cryptography.X509Certificates.X509Certificate.LoadCertificateFromFile(String fileName, Object password, X509KeyStorageFlags keyStorageFlags)

To fix this you must change your settings of the Application Pool. Set "Load User Profile" to true, otherwise the crypto subsytem will not work.



AppDomain.CurrentDomain.BaseDirectory vs. Directory.GetCurrentDirectory()

We had a problem with our MEF implementation in the web. We have an GenericFactory class wich enables easy access to MEF features and which handles all the catalog compositions. To enable use in Windows and web applications we added the following lines:

if (Directory.Exists(@".\bin"))
      catalog.Catalogs.Add(new DirectoryCatalog(@".\bin"));

Suprisingly the code didn't work, because the directory bin never existed. After a little debugging I found out that there is a difference between AppDomain.CurrentDomain.BaseDirectory which is used by MEF and Directory.GetCurrentDirectory() used by Directory.Exists.

Directory.GetCurrentDirectory() returns the working directory

AppDomain.CurrentDomain.BaseDirectory returns roughly the path of the executable.

For example:

If you have an application in C:\bar\baz.exe

After entering the following commands:

cd C:\foo

AppDomain.CurrentDomain.BaseDirectory will return C:\bar
Directory.GetCurrentDirectory() will return C:\foo

Hope this helpes to clarify.

Process.Start and Virus Scanner

We had a problem on our servers with building zip files. We're using an external tool, which is started via Process.Start. On that particular server the Website randomly hung after trying to build the archive. The archive was written sucessfully but the Process never exited. First of all I thaught, that the Process.WaitForExit was causing the problem and never returning even though were specifiying a timeout. I found the solution in a StackOverflow Post . When UseShellExecute is set to true, which is the default value, shellextensions like Virus Scanner can block the calling Process. So you have to set UseShellExecute to false. Then you have to remember, that the WorkingDir Property has a different meaning an you must specify the full path to the executable you want to start. See msdn for more information on this. Took me a long time to figure this out. Hope this helps you out there, if you have a similar problem.

SQL Server schema export

If you want to get the databaseschema of a Microsoft SQL Server easily exportet in Excel just use following query


This will give you the follwing example result:

You can now copy the result into an Excel sheet and use it for documentation etc.

NHibernate Rollback Timeout

In one of our projects we imported lots of data into the SQL Server with NHibernate in a transaction. When we called Rollback on our testruns we got an Timeout Exception after about 20 Seconds. I've started to search for the timeouts in NHibernate and found the command_Timeout property of NHibernate which had no effect. After about  2 hours of searching I found the answer in this Post.

After setting the Connection Timeout in the Connection String everything was fine. Never thought that this Timeout would effect the Transaction. In msdn the Timeout in the ConnectionString is described like this "The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. " see msdn




<<  December 2017  >>

View posts in large calendar