How to change the value of a default on a column with TSQL

If you want to change the value of default on a column, you must use the ALTER TABLE syntax (DROP DEFAULT and CREATE DEFAULT should be avoided, will be removed in future releases):

-- Create a default on column 'cust_name' in table 'MyCustomer' with value 'new customer'
IF OBJECT_ID('dbo.MyCustomers') IS NOT NULL
  DROP TABLE dbo.MyCustomers
CREATE TABLE dbo.MyCustomers(
  cust_id int NOT NULL,
  cust_name varchar(30) NOT NULL DEFAULT ('new customer'))

-- Remove the default from column 'MyColumn1' in table 'MyTable'
declare @defaultName varchar(100), @cmd varchar(1000)
set @defaultName =
 select name
 from sys.objects so JOIN sys.sysconstraints sc on so.object_id = sc.constid
 where object_name(so.parent_object_id) = 'MyTable'
 and so.type = 'D'
 and sc.colid = (select column_id from sys.columns where name = 'MyColumn1' and Object_ID = Object_ID(N'MyTable'))
set @cmd = 'alter table MyTable drop constraint ' + @defaultName

-- Add a default on column 'cust_name' in table 'MyCustomer' with value 'new text customer'
alter table MyCustomers add constraint DF_MyCustomers_cust_name default 'new text customer' for cust_name;

Configuring Internet Explorer to Remember Your SharePoint Login Credentials


Go to Control Panel
Go to Internet Options
Click the “Security” tab
Click “Local Intranet”
Click “Sites”
Click “Advanced”
Add your SharePoint site into the “Local Intranet” zone.
Click OK to confirm on all Internet Options screens.
Open up Internet Explorer and go to your SharePoint site.  When prompted for username/password make sure you enter your credentials and check “Remember My Password”.
Your username/password should now be passed to the SharePoint site automatically.

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.