ASP.NET 2.0 SQL permission failed

I have a web application that heavily relies on SQL Server for data retrieval. It worked magic up until I actually had posted it on the website. I got the following error:

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

The solution was rather easy - i had to set trust level to Full in the web.config file.
This, along with the allowed anonymous access resolves the problem.

SQL 2005 Business Days Difference Function

Easy way to calculate business days difference in SQL 2005. The script below can be wrapped in a function or used with stored procedure by itself:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Day varchar(30)

SET @StartDay = '01/01/2001'
SET @EndDate = getdate()

DECLARE @BusDaysDifference int

SET @Day = DATENAME(dw, @EndDay)

IF (@Day = 'Saturday')
SET @EndDay = @EndDay - 1
ELSE IF (@Day = 'Sunday')
SET @EndDay = @EndDay - 2

SET @BusDaysDifference = CASE

WHEN DATENAME(dw, @StartDate) IN ('Saturday') THEN DATEDIFF(day, @StartDate, @EndDate) - (2*(DATEDIFF(wk, @StartDate, @EndDate)) + 2)

WHEN DATENAME(dw, @StartDate) IN ('Sunday') THEN DATEDIFF(day, @StartDate, @EndDate) - (2*(DATEDIFF(wk, @StartDate, @EndDate)) + 1)

WHEN DATENAME(dw, @StartDate) IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') THEN DATEDIFF(day, @StartDate, @EndDate) - 2*(DATEDIFF(wk, @StartDay, @EndDate))

END

PRINT (@BusDaysDifference)

The assumption IS week days are: Monday, Tuesday, Wednesday, Thursday and Friday. Unfortunately this script doesn't consider holidays, but this can be easily done using the holidays table.

SQL Integer Padding

I've found a very easy way for integer padding in SQL 2005.

cast(replace(str(INT_N, 3),' ','0')as char(3)),
where INT_N is an integer number, and 3 - the size of the string required, e.g

DECLARE INT_N int
SET INT_N = 33

SELECT cast(replace(str(INT_N, 8), ' ', '0') as char(8)) as PADDED_INT

Will result in:


PADDED_INT

00000033.

Permissions for BizTalk 2006

BizTalk interface was failing on writing to the system event log - System.Diagnostics.EventLog.WriteEntry("BizTalk orchestration", Message, System.Diagnostics.EventLogEntryType.Error);

To solve - I set full permissions for the BtsInstanceHost account (or account which used for BizTalkServerApplication logon) on HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog registry key.

Sometimes FILE adapter fails to pool file from the directory; As well - giving full permissions on the folder to BtsInstanceHost solves the problem.