Get first day of current week or last week with TSQL

To get the first day of the current week use:

select dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate())

To get the first day of previous week and last day of previous week use:

select dateadd(day, -8, dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate()))
select dateadd(day, -1, dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate()))

You can also, use a variable for the current date and time, like:

declare @currentDateTime as datetime
set @currentDateTime = '2011-01-15 07:47:31.887' 

select dateadd(day, -8, dateadd(dd, (datepart(dw, @currentDateTime) * -1) + 2, @currentDateTime))
-- Result: 2011-01-02 07:47:31.887

select dateadd(day, -1, dateadd(dd, (datepart(dw, @currentDateTime) * -1) + 2, @currentDateTime))
-- Result: 2011-01-09 07:47:31.887

Leadtools engine initialization error, or there is no appropriate license

If you get the error:

Leadtools.Forms.Ocr.OcrException : Engine initialization error, or there is no appropriate license.
at Leadtools.Forms.Ocr.Plus.OcrEngine.StartupEngine(String startupParameters)
at Leadtools.Forms.Ocr.Plus.OcrEngine.Startup(RasterCodecs rasterCodecs, String workDirectory, String startupParameters)

make sure the ocr engine is started with the correct path to the installation of the Leadtools.

 // Start OCR-engine var ocrEngine = OcrEngineManager.CreateEngine(OcrEngineType.Plus, false);
            if (!ocrEngine.IsStarted)
                ocrEngine.Startup(null, null, @“C:\InstallationFolderLeadTools”);


The LeadTools OCR Engine should be installed in  C:\InstallationFolderLeadTools.


Adding LeadTools installation folder to the PATH variable

The error can some times be caused by the folder C:\InstallationFolderLeadTools not being in the PATH variable.

Add the [C:\InstallationFolderLeadTools] to the PATH variable.

Control Panel > System > Advanced System Settings






This error can also be caused by an IIS or IIS express application pool not running in 32-bit application mode.



Change IIS Application Pool setting [Enable 32-bit Applications] to [True]



IIS Express

Add enable32BitAppOnWin64="true" in [C:\Users\User1\Documents\IISExpress\config\applicationhost.config].


            <add name="Clr4IntegratedAppPool" managedRuntimeVersion="v4.0" managedPipelineMode="Integrated" CLRConfigFile="%IIS_USER_HOME%\config\aspnet.config" autoStart="true" enable32BitAppOnWin64="true">

                <processModel loadUserProfile="true" />


            <add name="Clr4ClassicAppPool" managedRuntimeVersion="v4.0" managedPipelineMode="Classic" CLRConfigFile="%IIS_USER_HOME%\config\aspnet.config" autoStart="true" enable32BitAppOnWin64="true"/>

            <add name="Clr2IntegratedAppPool" managedRuntimeVersion="v2.0" managedPipelineMode="Integrated" CLRConfigFile="%IIS_USER_HOME%\config\aspnet.config" autoStart="true" enable32BitAppOnWin64="true"/>

            <add name="Clr2ClassicAppPool" managedRuntimeVersion="v2.0" managedPipelineMode="Classic" CLRConfigFile="%IIS_USER_HOME%\config\aspnet.config" autoStart="true" enable32BitAppOnWin64="true"/>

            <add name="UnmanagedClassicAppPool" managedRuntimeVersion="" managedPipelineMode="Classic" autoStart="true" enable32BitAppOnWin64="true"/>

            <applicationPoolDefaults managedRuntimeLoader="v4.0">

                <processModel />



You should use System.Security.SecurityElement.Escape in C# to escape special characters in XML and not System.Web.HttpUtility.HtmlEncode

You should use the function System.Security.SecurityElement.Escape and not the function System.Web.HttpUtility.HtmlEncode to escape special characters in XML if you don’t want the characters like éûÉ to be converted to &#233;&#251;&#201; which is permit able but not necessary en can result in long xml fields.

var escapedXml = System.Security.SecurityElement.Escape(@"&<>'""’éûÉغ");
// Result=&amp;&lt;&gt;&apos;&quot;’éûÉغ

escapedXml = System.Web.HttpUtility.HtmlEncode(@"&<>'""’éûÉغ");
// Result=&amp;&lt;&gt;'&quot;’&#233;&#251;&#201;غ

To revert the escape process, use:

SecurityElement securityElement = System.Security.SecurityElement.FromString("<test>H&amp;M</test>");
string unescapedText = securityElement.Text;
Console.WriteLine(unescapedText); // Result: H&M


// Data is the un-escaped text that should be inserted in a XML tag.
string data = "H&amp;M";

// A xml tag with the name "test" is used, just for creating the SecurityElement.
var securityElement = new SecurityElement("test", data);

// Generate the un-escaped text.
string unescapedText = securityElement.Text;

// Result: H&M

Or use the InnerText property of a XmlNode:

// Data is the un-escaped text that should be inserted in a XML tag.
string data = "H&amp;M";
XmlDocument document = new XmlDocument();
document.LoadXml(string.Format("<test>{0}</test>", data));
XmlNode node = document.DocumentElement.FirstChild;

// Generate the un-escaped text.
string unescapedText = node.InnerText;

// Result: H&M

Check if a table exists in a SQL Server 2008 database

To check if a table exists in a SQL Server 2008 database, use the following T-SQL code:
Use the system view sys.objects and not the old sql server 2000 system table sysobjects

not exists (select 1 from sys.objects o where =’TableName’ and o.type =’U’)
    create table
dbo.TableName (
Id int identity(1,1) not nullprimary key,
Barcode varchar(35) not null


Insert image with TSQL and a hex string

You can insert an image from t-sql script, if you use the hex string notation:
The next line will insert an image representing a "check mark" into the database. In a table [Test] with a column [Image].




Some times, Microsoft SQL Server Management Studio will add a "zero" at the beginning, like 0x08950….
In this case add a "zero" at the end of the hex value, like:
insert [Test] values (0x8950……0)

Then magically the data will be correctly inserted into the database, see also:

Remove ExpandedColunCount and ExpandedRowCount when using Microsoft Exel (*.xml) file as template

When you use a Microsoft Excel "*.xml" file as template for generating Microsoft Excel reports, remove the ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1"

from the <Table> tag under the <Worksheet> tag. If you don’t remove these counters a error will occur, because the row and or column count is higher then 1:

Problem During Load
Problems came up in the following areas during load:
Worksheet Setting
This file cannot be opened because of errors. Erros are listed in C:\Users……\Content.MSO\7BF935F6.log


<Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">


An other error that can occur is:

XML ERROR in Table
REASON:    Bad Value
FILE:    C:\Temp\MyTest.xml
GROUP:    Table
TAG:    Row
ATTRIB:    Index
VALUE:    128


This was caused by the line:

<ss:Row ss:Index="128" ss:AutoFitHeight="0" ss:Height="12.9375">

Cause and solution

The index was not correct, by removing the ss:Index="128" ss:AutoFitHeight="0" ss:Height="12.9375" from the Row tag, the file could be opened correctly again.