log4net not logging to SQL Server table

By design, log4net silently fails if there are any configuration problems preventing it logging to the desired output.  To troubleshoot configuration problems, enable log4net internal debugging as described in the log4net support site FAQs.

Exception thrown the first time an NUnit test that opens a SQL connection is run

When running an NUnit test that opens a SQL connection, the first time the test is run you may get an error similar to the following:

System.Data.SqlClient.SqlException : A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - The pipe is being closed.)

Running the test again then passes.

This can happen in this scenario: NUnit has run data access code, creating a connection, then SQL Server is restarted. If this is the case, the cause of the exception is as follows. If SQL Server is restarted after ADO.NET has built a connection using the same connection string as that used by the failing test, ADO.NET will attempt to reuse that connection from the connection pool without checking whether the connection is invalid. Because SQL Server has been restarted the connection is no longer valid and the exception is thrown.

To demonstrate this, restart SQL Server, then run the test. The test will fail. If the test is run again, it will pass. Next, close NUnit, then restart SQL Server and reopen NUnit (in either order) and rerun the test. The test will pass.

Lack of NHibernate HQL support for question mark character in column name

If you’re faced with a database that contains reserved characters in some of its column names, NHibernate allows you to escape a column name in the mapping file by wrapping the column name in backticks, for example:

<class name="Foo">
   <property name="Bar" column="`Bar?`" />
</class>

This convention is used by NHibernate to generate SQL with the column name wrapped appropriately (e.g. [] for SQL Server). This works as intended for ICriteria queries and for HQL queries returning tuples in the following form:

session.CreateQuery("select Bar from Foo").List();

For SQL Server this results in the following SQL:

select foo0_.[Bar?] as col_0_0_
from Foo foo0_

The problem occurs if you use the entity selection form of the HQL query in conjunction with the generic List method:

session.CreateQuery("select foo from Foo foo").List<Foo>();

In this scenario, when constructing the SQL statement NHibernate interprets the question mark as a position parameter placeholder and substitutes the question mark with the parameter, i.e. the query winds up containing this column

foo0_.[Bar@p0]

instead of

foo0_.[Bar?]

Unfortunately this is a fault in NHibernate 3.1:

http://stackoverflow.com/questions/5805617/escaping-a-question-mark-character-in-a-column-name-in-nhibernate

How to use a CTE to create a crosstab query

You can use a CTE with the ROW_NUMBER function to create a crosstab query. For example, say your data structure stores various types of event in a single table, but you need to report the various event dates in separate columns for each type of event. Here is how you might do this using a CTE joining on the output produced by ROW_NUMBER:

WITH eventTypeX AS
(
SELECT e.EventDate, e.JobID,
ROW_NUMBER() OVER(PARTITION BY e.JobID ORDER BY e.EventDate ASC) AS rn
FROM dbo.tblEvents
WHERE...
),
eventTypeY AS
(
SELECT e.EventDate, e.JobID,
ROW_NUMBER() OVER(PARTITION BY e.JobID ORDER BY e.EventDate ASC) AS rn
FROM dbo.tblEvents
WHERE...
)
SELECT j.JobID, j.JobDescription, e1x.EventDate AS FirstEventTypeX,
e2x.EventDate AS SecondEventType1, e1y.EventDate AS FirstEventTypeY,
e2y.EventDate AS SecondEventTypeY
FROM tblJobs j
LEFT OUTER JOIN eventTypeX e1x ON j.JobID = e1x.JobID AND e1x.rn = 1
LEFT OUTER JOIN eventTypeX e2x ON j.JobID = e2x.JobID AND e2x.rn = 2
LEFT OUTER JOIN eventTypeY e1y ON j.JobID = e1y.JobID AND e1y.rn = 1
LEFT OUTER JOIN eventTypeY e2y ON j.JobID = e2y.JobID AND e2y.rn = 2

How to use a CTE to select the first n rows of a referenced table for every row

The Common Table Expression (CTE) feature introduced in SQL Server 2005 provides some very useful querying extensions (albeit these are obviously not ANSI-92 compliant). A CTE takes the following form:

WITH cte AS
(
SELECT col1
FROM dbo.tbl1
WHERE...
)
SELECT col1
FROM dbo.cte
WHERE...
GO

One useful adjunct to the CTE is the ROW_NUMBER function in combination with the PARTITION BY clause. This allows you to write a query returning one or more rows of a subquery without resorting to complicated correlated subqueries and performance killing TOP n predicates. For example, say you want to pull for every row in a parent table the first row in a child table. You can write this query using a CTE as follows:

WITH firstChild AS
(
SELECT ParentID, ChildID,
ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY ChildID ASC) AS rn
FROM dbo.tblChildren c
)
SELECT p.ParentID, fc.ChildID
FROM dbo.tblParents p
INNER JOIN firstChild fc ON p.ParentID = fc.ParentID AND fc.rn = 1;
GO

The performance of this query is blindingly fast because ROW_NUMBER is being performed in memory and has no disk IO cost. You can also use this technique to generate high performance crosstab queries.

SQL Server error not found

Operating system errors are often returned by SQL Server with just a number and the description “(error not found)”. The most common operating system errors that can be returned by SQL Server are:

3	The system cannot find the path specified
5	Access denied

Searching computed column definitions

Computed columns are not ANSI-92 compliant (i.e. they are a proprietary extension) and are therefore not included in the INFORMATION_SCHEMA views. To search for a string in a computed column in SQL Server 2005 use the syscolumns and syscomments system tables. For example:

SELECT object_name(cl.id) AS [Object Name], name AS [Column Name],
text AS [Definition]
FROM syscolumns cl
INNER JOIN syscomments cm
ON cl.id = cm.id
AND cm.number = cl.colid
WHERE iscomputed = 1
AND text LIKE '%search_string%'


Error parsing T-SQL

If XACT_ABORT is on for the current connection, parsing a T-SQL query will generate the following error:

.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The SQL will still execute. To avoid this error being reported, simply set XACT_ABORT off.

Ending SQL Server connections

To end a connection to an SQL Server instance, use the KILL command passing the SPID associated with the connection.

To kill all users from a given database, insert the output of sp_who into a temporary table, then set up a cursor with the appropriate WHERE clause and step through issuing a KILL command for each row:

DECLARE @Users TABLE(
[spid] int,
[ecid] int,
[status] NVARCHAR(60),
[login] NVARCHAR(100),
[hostname] NVARCHAR(100),
[blk] int,
[dbname] SYSNAME NULL,
[cmd] NVARCHAR(60),
[request_id] INT
)
INSERT INTO @Users EXEC SP_WHO
DECLARE [LoginCursor] CURSOR READ_ONLY FOR
SELECT [spid], [login]
FROM @Users
WHERE [dbname] = 'DBName' AND [spid] <> @@SPID
DECLARE @spid INT,
@login NVARCHAR(100)
OPEN LoginCursor
FETCH NEXT FROM [LoginCursor] INTO @spid, @login
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2) EXEC ('KILL ' + @spid)
FETCH NEXT FROM [LoginCursor] INTO @spid, @login
END
CLOSE [LoginCursor]
DEALLOCATE [LoginCursor]

Users that have been disconnected should reestablish their connections. (SQL Server Management Studio clients should reconnect queries. ODBC clients should be restarted.)

Viewing current SQL Server connections

To return information on all current user connections to an SQL Server instance, run sp_who:

EXEC sp_who

How to delete a row that has child rows when Cascade Deletes is false on the foreign key relationship

I was recently asked to implement a function that deletes a row and all child rows in SQL Server, even though Cascade Deletes is false on the relationships defined in the data source, that could be called from an MS Access client application.

I eventually came up with a stored procedure that can be called to retrieve or delete records, including all child records, by using a derived table to build a tree of foreign key relationships.

Note that this procedure uses sp_executesql to dynamically execute SQL from concatenated strings, but does not perform any input validation against SQL injection attacks. It should not therefore be called directly but should be called from a utility procedure responsible for sanitizing input parameters.

The parameters passed with this stored procedure are:

@fromTbl	The table from which to select or delete rows (supplied without qualifying owner name).
@whereCriteria	The WHERE criteria for the query (fully specified with table name).
@cmdType	1 = SELECT or 2 = DELETE.
@retMsg	Output parameter. Returns numbers of rows in each table affected.
@retCode	Output parameter. 1 indicates record(s) seleted, 2 indicates record(s) deleted successfully, 100 indicates no matching rows, -100 indicates error.

CREATE PROC [dbo].[procGetChildRows](
@fromTbl nvarchar(50),
@whereCriteria nvarchar(100),
@cmdType smallint,
@retMsg varchar(500) OUTPUT,
@retCode smallint OUTPUT
)

AS
SET NOCOUNT ON
DECLARE @errCount smallint,
@fromTblOwner nvarchar(50),
@fromPK nvarchar(50),
@err int,
@foundChildTbls bit,
@init bit,
@relLevel smallint,
@parentTbls nvarchar(500),
@tblID int,
@tblIDCount int,
@parentTblOwner nvarchar(50),
@parentTbl nvarchar(50),
@tblOwner nvarchar(50),
@tblName nvarchar(50),
@fkName nvarchar(50),
@pkName nvarchar(50),
@cmdSubject nvarchar(50),
@cmd nvarchar(4000),
@prevParentTbl nvarchar(100),
@rowCount int,
@paramDef nvarchar(50)

SET @errCount = 0
IF CHARINDEX('.', @whereCriteria) = 0
SET @whereCriteria = @fromTbl + N'.' + @whereCriteria

LockTimeoutRetry:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Get owner and primary key column name of top level table.
SELECT @fromTblOwner = tc.CONSTRAINT_SCHEMA,
@fromPK = kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON rc.UNIQUE_CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.TABLE_NAME = @fromTbl
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

SELECT @cmd = N'SELECT @rowCount = COUNT(*) FROM ' + @fromTblOwner + N'.' + @fromTbl + N' WHERE ' + @whereCriteria,
@paramDef = N'@rowCount int OUTPUT'
EXEC sp_executesql @cmd, @paramDef, @rowCount OUTPUT
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

IF @rowCount = 0
BEGIN
SELECT @retMsg = 'No matching rows. 0 rows affected.',
@retCode = 100

COMMIT TRANSACTION

RETURN 0

END

SELECT @foundChildTbls = 1, @init = 1, @relLevel = 0
DECLARE @delTbl TABLE (
FTblID int identity,
FTblOwner nvarchar(50),
FTblName nvarchar(50),
FKColName nvarchar(50),
PTblOwner nvarchar(50),
PTblName nvarchar(50),
PKColName nvarchar(50),
RelLevel smallint,
RowCount int)

-- Look for child tables until no more child tables found.
WHILE @foundChildTbls = 1
BEGIN
-- Increment relationship level variable.
SELECT @relLevel = @relLevel + 1
IF @init = 1
-- Initialize @parentTbls variable.
SELECT @parentTbls = N';' + @fromTbl + N';'
ELSE
BEGIN
-- Create comma separated list of child tables.
SELECT @parentTbls = ''
SELECT @parentTbls = @parentTbls + d.FTblName + N';'
FROM (
SELECT FTblName FROM @delTbl WHERE RelLevel = @relLevel - 1
) AS d
SELECT @parentTbls = N';' + @parentTbls
END

/*
Insert table owner, table name, foreign key column name, parent table owner, parent table, primary key column name and relationship level.
*/
INSERT INTO @delTbl (FTblOwner, FTblName, FKColName, PTblOwner, PTblName, PKColName, RelLevel)
SELECT CAST(ft.CONSTRAINT_SCHEMA AS nvarchar(50)),
CAST(ft.TABLE_NAME AS nvarchar(50)),
CAST(fk.COLUMN_NAME AS nvarchar(50)),
CAST(tc.CONSTRAINT_SCHEMA AS nvarchar(50)),
CAST(tc.TABLE_NAME AS nvarchar(50)),
CAST(pk.COLUMN_NAME AS nvarchar(50)),
@relLevel
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ft ON rc.CONSTRAINT_NAME = ft.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk ON rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk ON rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
WHERE @parentTbls LIKE '%;' + tc.TABLE_NAME + ';%'
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND ft.CONSTRAINT_TYPE = 'FOREIGN KEY'
SELECT @err = @@ERROR, @rowCount = @@ROWCOUNT
IF @err <> 0 GOTO ErrorHandler

-- If insert returns 0 rowcount, break out of loop.
IF @rowCount = 0 SELECT @foundChildTbls = 0
SELECT @init = 0
END

SELECT @tblIDCount = MAX(FTableID) FROM @delTbl
-- Start at end of @delTbl.
SET @tblID = @tblIDCount
IF @cmdType = 1
BEGIN
WHILE @tblID > 0
BEGIN
-- For each entry in @delTbl construct SELECT COUNT(PK) command.
SELECT @parentTblOwner = PTblOwner, @parentTbl = PTblName, @tblOwner = FTblOwner, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName, @relLevel = RelLevel
FROM @delTbl
WHERE FTblID = @tblID
SELECT @cmdSubject = COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE kcu.TABLE_NAME = @tblName
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

SELECT @cmd = N'SELECT @rowCount = COUNT(' + @cmdSubject +
N') FROM ' + @tblOwner + N'.' + @tblName + N' INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' '
WHILE @relLevel > 1
-- Add joins to FROM clause.
BEGIN
SELECT @prevParentTbl = @parentTbl
SELECT @parentTblOwner = PTblOwner, @parentTbl = PTblName, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName
FROM @delTbl
WHERE FTblName = @prevParentTbl AND RelLevel = @relLevel - 1
SELECT @rowCount = @@ROWCOUNT
IF @rowCount > 0
SELECT @cmd = @cmd + N'INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' ',
@relLevel = @relLevel - 1
ELSE BREAK
END

-- Add WHERE clause.
SELECT @cmd = @cmd + N' WHERE ' + @whereCriteria,
@paramDef = N'@rowCount int OUTPUT'
EXEC sp_executesql @command, @paramDef, @rowCount OUTPUT
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

UPDATE @delTbl SET RowCount = @rowCount WHERE FTblID = @tblID
SET @tblID = @tblID - 1
END

SET @retMsg = ''
SELECT @retMsg = @retMsg + CAST(d.RowCount AS varchar(50)) +
' row(s) from ' + d.FTblName + ', '
FROM (SELECT TOP 100 PERCENT RowCount, FTblName
FROM @delTbl
WHERE RowCount > 0
ORDER BY FTblID
) AS d
IF LEN(@retMsg) > 0 SET @retMsg = LEFT(@retMsg, LEN(@retMsg) - 1)
SELECT TOP 1 @parentTblOwner = PTblOwner FROM @delTbl WHERE PTblName = @fromTbl
-- Add WHERE clause.
SELECT @cmd = N'SELECT @rowCount = COUNT(' + @fromPK + N') FROM ' + @parentTblOwner + N'.' + @fromTbl + N' WHERE ' + @whereCriteria,
@paramDef = N'@rowCount int OUTPUT'
EXEC sp_executesql @cmd, @paramDef, @rowCount OUTPUT
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

IF LEN(@retMsg) > 0
SET @retMsg = CAST(@rowCount AS varchar(50)) + ' row(s) from ' + @fromTbl + ', ' + @retMsg
ELSE
SET @retMsg = CAST(@rowCount AS varchar(50)) + ' row(s) from ' + @fromTbl
SET @retCode = 1
END

IF @cmdType = 2
BEGIN
WHILE @tblID > 0
BEGIN
SELECT @parentTblOwner = PTblOwner, @ParentTbl = PTblName, @tblOwner = FTblOwner, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName, @relLevel = RelLevel
FROM @delTbl
WHERE FTblID = @tblID
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

SELECT @cmd = N'DELETE ' + @tblName + N' FROM ' + @tblOwner + N'.' + @tblName + N' INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' '
WHILE @relLevel > 1
BEGIN
SELECT @prevParentTbl = @parentTbl
SELECT @parentTblOwner = PTblOwner, @parentTbl = PTblName, @tblName = FTblName, @fkName = FKColName, @pkName = PKColName
FROM @delTbl
WHERE FTblName = @prevParentTbl AND RelLevel = @relLevel - 1
SELECT @rowCount = @@ROWCOUNT
IF @rowCount > 0
SELECT @cmd = @cmd + N'INNER JOIN ' + @parentTblOwner + N'.' + @parentTbl + N' ON ' + @tblName + N'.' + @fkName + N' = ' + @parentTbl + N'.' + @pkName + N' ',
@relLevel = @relLevel - 1
ELSE BREAK
END

-- Add WHERE clause.
SELECT @cmd = @cmd + N' WHERE ' + @whereCriteria
EXEC sp_executesql @cmd
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

SET @tblID = @tblID - 1
END

SET @cmd = N'DELETE ' + @fromTblOwner + N'.' + @fromTbl +
N' WHERE ' + @whereCriteria
EXEC sp_executesql @cmd
SET @err = @@ERROR
IF @err <> 0 GOTO ErrorHandler

SET @retCode = 2
END

COMMIT TRANSACTION

RETURN 0

ErrorHandler:
ROLLBACK TRANSACTION
IF @err IN (1205, 1222)
BEGIN
IF @errCount = 5
BEGIN
RAISERROR('Unable to connect to database.', 16, 1)
RETURN -100
END

WAITFOR DELAY '00:00:00.25'
SET @errCount = @errCount + 1
GOTO LockTimeoutRetry
END

ELSE
-- Unknown error
BEGIN
SET @retCode = -100
RAISERROR(@err, 16, 1) WITH LOG
RETURN -100

END

The @fromTbl parameter should be supplied without a qualifying owner name. If the @whereCriteria parameter includes multiple criteria, it must specify the table name with the column name.

The stored procedure first uses the TABLE_CONSTRAINTS, KEY_COLUMN_USAGE and REFERENTIAL_CONSTRAINTS ANSI standard views to retrieve the owner and primary key column name of the table specified by the @fromTbl parameter. It then checks whether there are any rows in the table that satisfy the WHERE criteria supplied by the @whereCriteria parameter. If there are none, it exits with the appropriate values in @RetMsg and @RetCode parameters. If it finds rows, it proceeds to query the TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE views for child tables and stores information about the child tables in a table variable, including information about the hierarchy. It does this for all the table names it has stored in the table variable. Once it has finished constructing its table of child tables, it starts at the end of the table and works its way up the levels to construct a SELECT COUNT or DELETE command.

The procedure wraps its SQL DML commands in a transaction. If any error occurs, it will roll back the transaction.

Note that this procedure does not support the following foreign key relationships in the database schema:

• Recursive foreign key relationship on a table.
• Cyclical foreign key relationships on two or more tables.

How to update a SQL Server DateTime column to a value that is interpreted as a time by Microsoft Access

When upsizing an MS Access database to SQL Server 2000, one issue that may arise is if you are displaying times in the Access front end from a SQL DATETIME data type column. If this is the case, Access interprets datetimes with the date '1899-12-30' as a time without a date part. So to save a value to a SQL DATETIME column formatted for correct display as a time in Access you need to replace the date part as follows:

CREATE FUNCTION [dbo].[fGetTimePartForDBUpdate](
@fullDateTime datetime)
RETURNS DATETIME
/*
Return time formatted for display in a Microsoft Access table.
*/
AS
BEGIN

DECLARE @datePart DATETIME,
@timePart DATETIME

SET @datePart = CAST(CONVERT(CHAR(10), @fullDateTime, 101) AS DATETIME)

SET @timePart = @fullDateTime - @datePart

RETURN CAST('1899-12-30 ' AS DATETIME) + @timePart
END