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

 

image

 

 

enable32BitAppOnWin64

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

 

IIS

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

image

 

IIS Express

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

<applicationPools>

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

                <processModel loadUserProfile="true" />

            </add>

            <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 />

            </applicationPoolDefaults>

        </applicationPools>

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(@"&<>'""’éûÉغ");
Console.WriteLine(escapedXml);
// Result=&amp;&lt;&gt;&apos;&quot;’éûÉغ

escapedXml = System.Web.HttpUtility.HtmlEncode(@"&<>'""’éûÉغ");
Console.WriteLine(escapedXml);
// 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

Or

// 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
Console.WriteLine(unescapedText);

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
Console.WriteLine(unescapedText);

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


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

     )
end
go

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].

insert
    [Test]
values(0x89504E470D0A1A0A0000000D49484452000000100000001008060000001FF3FF61000000017352474200AECE1CE90000000467414D410000B18
F0BFC6105000000206348524D00007A26000080840000FA00000080E8000075300000EA6000003A98000017709CBA513C000001B649444154384FA5926B
2F42711CC77924E7BDB8970873DB58AE4B11399CC8359A2754A7D23D86B91C0FCDFD3A5B68AEC36C1E2AC9780DE64D485FFFCEA68D329AB39D87DFCBEFF
3FFA60248F9D71735F8CF9FB458B9D9C035ADD4D83E43933268D9A8E774271A0C1E319072E52C7FFE5FEB37AFD771C3C7DD987F72C27367827A578952778
1F94F068AB55A4EEB5363F6D18199471BBAF65A51E62984C42A14C40CB4D74C65FF05DDFBBD917CB5968B569E0ED93015B2F2C9254E71B8703C4F103B417BC
514F79DD3AFECAD0E8CB779E4D384C0E2060E3B31F5308EC9070B989D16143BF2C305965C5ECC1B0C5E7656F59DB5BF4CF859CC86EC50EDCAC204D6886C5
93AD37F4063226881E79E45C7B602457651586CCE89897983DE5355BAC6D706FDCD10169EDDF0F84DD01EABA13BD5C0E537C2796F04BD2587C4267CCF37
7D15C74EA0F7E5144986EE4CC3DF391934C311D0C3E1D743B5D914851511B1D95F92E376406051B225297ABCED70060CB0DE8DA26D5D06022B223266251
4C7ED40BA584155CF9591CA0A28D71A4160458486CC1FC5098744DE972A71894160BDE5E933D37E1B5AC221115854EE58C6AFE2A4A6FC53930F93387C6A
1968F12D0000000049454E44AE426082)

 

Note

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: http://stackoverflow.com/questions/2657922/insert-hex-string-value-to-sql-server-image-field-is-appending-extra-0

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">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>

 

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.