Detecting applications causing SQL Server locks

On one of our testing environments, login attempts to a legacy web application that uses MS SQL Server were timing out and failing. I suspected that the reason might be that another process is locking one of the table needed in the login process.
I ran a query similar to this:

SELECT request_mode,
 request_type,
 request_status,
 request_session_id,
 resource_type,
 resource_associated_entity_id,
 CASE resource_associated_entity_id 
  WHEN 0 THEN ''
  ELSE OBJECT_NAME(resource_associated_entity_id)
 END AS Name,
 host_name,
 host_process_id,
 client_interface_name,
 program_name,
 login_name
FROM sys.dm_tran_locks
JOIN sys.dm_exec_sessions
 ON sys.dm_tran_locks.request_session_id = sys.dm_exec_sessions.session_id
WHERE resource_database_id = DB_ID('AdventureWorks2014')

Which produces a result similar to:

It shows that an application is granted exclusive lock on the table EmailAddress, and another query is waiting for a shared lock to read from the table. But who is holding this lock? In my case, by checking the client_interface_name and program_name columns from the result we could identify that a long running VBScript import job was locking the table. I created a simple application that simulates a similar condition which you can check on Github. You can run the application and run the query to see the results.

It's a good practice to include "Application Name" property in your connection strings (as in the provided application source code) to make diagnosing this kind of errors easier.

Returning full path of records in tables with recursive relationships

A common requirement when dealing with tables with recursive relationship where a record points to another (parent) record in the same table, is to get the full path of the record name. like the case with full folder paths in a file system hierarchy.
For example: (data from http://www.ida.liu.se/~iislab/projects/secont/main/)

CategoryId	Name	ParentCategoryId
1	Asset	NULL
2	Countermeasure	NULL
3	Cryptography	2
4	Encryption	3
5	SignatureAlgorithm	4
6	CryptographicHashFunction	5
7	DSA	6
8	MD5	6
9	EncryptionAlgorithm	4
10	BlockCipher	9
11	AES	10
12	DES	10

For the above data, we need to get this:

CategoryId	Name
1	Asset
2	Countermeasure
3	Countermeasure > Cryptography
4	Countermeasure > Cryptography > Encryption
5	Countermeasure > Cryptography > Encryption > SignatureAlgorithm
9	Countermeasure > Cryptography > Encryption > EncryptionAlgorithm
10	Countermeasure > Cryptography > Encryption > EncryptionAlgorithm > BlockCipher
11	Countermeasure > Cryptography > Encryption > EncryptionAlgorithm > BlockCipher > AES
12	Countermeasure > Cryptography > Encryption > EncryptionAlgorithm > BlockCipher > DES
6	Countermeasure > Cryptography > Encryption > SignatureAlgorithm > CryptographicHashFunction
7	Countermeasure > Cryptography > Encryption > SignatureAlgorithm > CryptographicHashFunction > DSA
8	Countermeasure > Cryptography > Encryption > SignatureAlgorithm > CryptographicHashFunction > MD5

This can be achieved using Recursive Common Table Expressions:

WITH CategoryCTE
AS
(
SELECT C.CategoryId, CONVERT(NVARCHAR(500), C.Name) AS Name FROM dbo.Category C
WHERE ParentCategoryId IS NULL
UNION ALL
SELECT C.CategoryId, CONVERT(NVARCHAR(500), CTE.name + N' > ' + C.Name) AS Name FROM dbo.Category C
JOIN CategoryCTE CTE ON C.ParentCategoryId = CTE.CategoryId
)
SELECT * FROM CategoryCTE 

It works like this:

• The CTE selects from the base table data, the level which has no parents
• The result is union-ed with the recursive part, which joins the base table with the last value of the CTE up to the current level of recursion
• The name field of a record is a concatenation between its parent name and its own name
• Select the result of the CTE

SSMS and Deleting all records from a self referencing table

Clearing all data from a self referencing table using SSMS can be tricky. Selecting all records in SSMS results grid and pressing DEL just won't work in many times.

Take the common example of Employee - Manager relationship:

If you try using SSMS results grid to delete all records, you may get this error:

No rows were deleted.

A problem occurred attempting to delete row 1.
Error Source: .Net SqlClient Data Provider.
Error Message: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Employee_Employee". The conflict occurred in database "test", table "dbo.Employee", column 'ManagerId'.

The statement has been terminated.

Using the Query window and running:
DELETE employee
Will work however.

The difference is that SSMS actually tries to delete row by row, which will violate the constraint. But when using a query to delete all records. SQL Serve is smart enough to clear all data.

Tip: turn auto close Off for you SQL Server databases

On a development machine, I had a high CPU usage for the SQL server service. To understand the reason, I looked at the SQL Server logs (From SQL Server Management Studio - SQL Server Logs).

As you may notice from the screenshot above, the activity of starting the reporting service database was recurring again and again.

I checked the database options, and the noticed that the auto close property was set true. This explains why SQL Server tries to start the database at every connection.

If the Auto close property is set to on:

the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete.

As quoted from msdn.

Unfortunately, the property was set to true when the database was created during the SQL server installation.

Turning Auto close to false (off), and maybe restarting the service, returned the CPU usage of the SQL Server service to a normal level.

Using PowerShell and SMO to change database columns collations

Changing a SQL serve database columns collations manually can be a tedious task. I have a database that I want to change the collation of all its non system columns to "Arabic_CS_AS".
Here is a PowerShell script that uses SQL Server Management Objects (SMO) to do this task:
(note that I load the assemblies with version 10.0.0 which is the version of SQL server 2008 I have installed on my system)


[System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

$con = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

$con.ConnectionString="Data Source=.\SQLExpress;Integrated Security=SSPI;"

$con.Connect()

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $con
$db = $srv.Databases["test"]

foreach ($table in $db.Tables)
{
if($table.IsSystemObject)
{
continue
}

foreach($column in $table.Columns)
{
if(-not ([string]::IsNullOrEmpty($column.Collation)))
{
$column.Collation = "Arabic_CS_AS"
$column.Alter()
}
}
}

$con.Disconnect()


Storing Arabic (or Unicode) data in MySQL using Connector/Net 5.2

Storing Unicode data in varchar columns in MySQL can be tricky. I spent some time to use MySQL Connector/Net 5.2 to store Arabic data.
Once you know it, it's simple:

• The varchar column Charset should be utf8
• The varchar column Collate property should be utf8_general_ci. I tried utf8_bin and worked too. This is a screen shot from MySQL Administrator table editor:
  

• In the .net application, the connection string must include charset=utf8. For example:

"server=localhost;uid=root;password=123;database=test;port=3306;charset=utf8"

Scripting SQL Server database objects using SMO

Scripting a database objects from development environment is a necessary step before deployment to production or test environments and to add the script to source control. Doing this manually is error prone and time consuming.
Also If you practice continuous integration, it's a must to automate the script generation process.

I used Database Publishing Wizard which is hosted on codeplex. It's good but the command line options lack the ability to determine which objects to script. That caused a problem because it scripted users as well. Which I did not want it to do.

Although it's hosted on codeplex, I could not download the source code to make the necessary modifications.

So, I had to go the hard way. And I did it myself. Using SQL Server Management Objects (SMO), I built a small command line utility to script: Tables, Indexes, UDFs, Veiws, SPs, Defaults, Checks, Foreign keys.
There are limitations ofcourse, But at least it's under control as long as the code is available.

The application depends on the core class ScriptingEngine, which in turn depends on Microsoft.SqlServer.Management.Smo.Scripter class.

Points of interest:

• Order of scripting is important for the script to be used to regenerate objects.
• Determining object dependencies is an important trick. Using DiscoverDependencies method, it's possible to order objects bu
  


public void ScriptViews()
{
List<urn> urns=new List<urn>();

foreach (View view in _database.Views)
{
if (view.IsSystemObject)
continue;
urns.Add(view.Urn);

}

DependencyTree tree = _scripter.DiscoverDependencies(urns.ToArray(), true);
DependencyCollection dc = _scripter.WalkDependencies(tree);

RemoveUrnType(dc,"Table");
RemoveUrnType(dc, "UserDefinedFunction");

_scripter.ScriptWithList(dc);

}


• Note the RemoveUrnType method, it's used to filter unwanted object types from the DependencyCollection:


private void RemoveUrnType(DependencyCollection dc,string type)
{
for (int counter = 0; counter < dc.Count; counter++)
{
if (dc[counter].Urn.Type==type)
{
dc.RemoveAt(counter);
counter--;
}
}
}


• Usage:

SqlScripter.exe -server:server\instance -database:dbname -login:login -password:pwd -filename:pathtoscriptfile

You can download the source from skydrive:

How to check query syntax programmatically

A nice feature in MS SQL Server Management studio or the old good query analyzer is the ability to check query syntax before executing it by pressing (Parse) or (Ctrl+F5).

You also can provide this functionality in your application in case your application creates queries on the fly or has a query designer.

This can be done using the PARSEONLY option. Queries that run while PARSEONLY option is ON are parsed but not executed, For example:

SET PARSEONLY ON
Go
Select * from dbo.Books
Go
SET PARSEONLY OFF

The above Select will not be executed, it will only be parsed, the result will be:

Command(s) completed successfully.

Without returning any data.

It's simple, open the database connection, execute "SET PARSEONLY ON", then execute your query, then "SET PARSEONLY OFF"

Another place when NULL gets tricky

Dealing with NULLs in SQL causes confusion in many cases. Two good articles cover a lot about NULLs in SQL server: 4 Simple Rules for Handling SQL NULLs and Gotcha! SQL Aggregate Functions and NULL.

I this the latest should have covered another situation, when aggregate functions return NULL in case of not satisfying the where condition.
Let's take this Books table as an example:

ID Title  Price
--------------------------------
1 C#  10
2 Database 15
3 VB6  30
3 VB.net  35

When you want to get the sum of all books that start with 'vb', you simply use SUM function like this:

Select sum(Price) from Books where title like 'vb%'

And the result would be 65

One may expect that if he asks for the sum of c++ books prices, the result will be zero (as we have none). for example, this query returns zero:

Select count(Price) from Books where title ='c++'

But with sum:

Select sum(Price) from Books where title ='c++'

The result is NULL. Why? Because, using the same logic described in the above articles, SQL will consider this as an absence of data (It couldn't determine the total price since it did not find any prices) if that makes sense.

This can cause issues when trying to read the value returned by this query in the application which expects a float value. A simple remedy for this would be using COALESCE or ISNULL: (I prefer to use COALESCE since it is standard.)

Select COALESCE(sum(Price),0) from Books where title ='c++'
Select ISNULL(sum(Price),0) from Books where title ='c++'

The result in this case is zero as desired.

Restart your machine after installing Fulltext service for MS SQL Server Express 2005

I had MS SQL server express 2005 installed without Fulltext index support. When I needed it, I installed it, created a catalog, an index, and started change tracking, then I tried to make simple CONTAINS query, nothing returned.
I worked with FTS many times and for years (mainly with SQL Server 2000). It was hard to retry many times to get any result with not good.
I tried many times to drop the index, start full population, incremental population. Nothing worked.
I looked into the event viewer, I found this error:

Errors were encountered during full-text index population for table or indexed view '[dbname].[dbo].[TableName]', database ''[dbname].[' (table or indexed view ID '1550628567', database ID '7'). Please see full-text crawl logs for details.

I looked in the fulltext log ( located at : installationpath/MSSQL.1\MSSQL\LOG\SQLFTxxxxxxxxxx.LOG.x) , this info existed in error logs:

Error '0x80040e09' occurred during full-text index population for table or indexed view '[dbname].[dbo].[TableName]' (table or indexed view ID '1550628567', database ID '7'), full-text key value 0x00000001. Attempt will be made to reindex it.
The component 'sqlfth90.dll' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.

After searching for such error, I found that after installing fulltext service on MS SQL Express 2005, the machine must be restarted, and that this is mentioned in the ReadMe!!

If only we read the manual !!!!

Another reason for “Cannot Generate SSPI Context” error

Today, I had trouble with SharePoint server. Each time I open the main page I get the error page (500-internal server error). This error occurred in an environment that worked fine for months. But suffered an accidental electricity cut off.

When I looked in the event log, I realized that SharePoint cannot connect to SQL Server configuration database (which existed on another server) the error details contained the famous error:

“Cannot Generate SSPI Context”

I tried to ping the server, flush DNS, connected to SQL Server using SQL authentication, restarted some services and machines, just to get the same error.

Searching Google led me to a good article: Troubleshooting Cannot Generate SSPI Context Errors , which is really good, but I still did not find the solution.

Finally, after consulting an infrastructure engineer, he suggested to check that the net logon service is running on the domain controller. I checked in and I found that it was paused. After running it, everything went well.

So, the next time you get “Cannot Generate SSPI Context” when connecting to SQL Server, add this check to the list of checks you do, and good luck.

Are MS SQL Server stored procedures precompiled ?

A common interview question and a common answer:
Q: Why should you use Stored procedures not ad-hoc queries?
A (happily, he's sure that he knows how to answer this question): They perform better because they are precompiled at creation time, also ...(Some, other reasons that I don't really care about now)...

Is that true?

In fact, this is a common misconception that many users of SQL Server 7.0, 2000, and 2005 suffer, may be because this was the case in SQL Server 6.5. Also because inaccurate and not up to date articles, books ,and learning materials.

I quote this from msdn:

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure.

This is not the case in for SQL Server 7.0 and higher, again from msdn:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement.

Knowing this, it's common that arguments take place from time to another comparing using Stored procedures vs. ad hoc queries.

Personally, I prefer to use stored procedures because I find this more modular.

My advice is, whether you use Stored procedures or ad hoc queries, always use best coding practices when performing database operations to avoid SQL injection attacks. Be them parameterized queries, prepared statements, or whatever depending on the programming language and framework you use.

Free Microsoft E-Learning: What's New in Microsoft SQL Server 2008

Microsoft e-learning site offers free online training for SQL Server 2008
I Hope this helps getting ready for the new release...

Query to get database relations in SQL Server 2005

It is a common need to get a list of foreign key relations in a database
This simple query does this:

Select a.Column_name as [FK Column],a.Constraint_Name as [FK],c.column_name [PK Column] ,Unique_Constraint_Name As [PK]
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b on a.Constraint_Schema=b.Constraint_Schema and a.Constraint_Name=b.Constraint_Name
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on c.Constraint_Schema=b.Unique_Constraint_Schema and c.Constraint_Name=b.Unique_Constraint_Name

How granting execute permission on stored procedures made easier in SQL Server 2005

In SQL Server 2000, a common problem was faced by developers who design their data access to depend on stored procedures, SQL server had db_datareader and db_datareader roles. But no such db_executer role.
This caused trouble as the user used to execute stored procedure had to be granted execute permission on every stored procedure in the database. This can be automated using scripts that grant execute permissions to the required user. But imagine how much time you'll spend debugging your application after creating a new procedure just to discover that you forgot to run the script.
Another easy and dirty way was to add the user used by the application to the db_owner group and make all procedures owned by the dbo. No need to talk about why this is bad.

In SQL Server 2005, the concept of permissions, securables and schemas ,were introduced.
now you can have it all done using one statement. Assuming you have a user test and a schema named HR, test can have execute permissions on all stored procedures belong to HR schema:

GRANT EXECUTE on SCHEMA::HR to test

Now if you (as dbo) create a procedure like:
CREATE proc HR.GetAccData
as
Select * from dbo.acc
GO

The used test will have execute permission on the HR.GetAccData procedure.

MS SQL Server Views: Order By

If you create a view in SQL Server 2005 management studio and add an order by clause, run the query, the order by seems to take effect.
But when you reference the view in a simple query (with no order by) you find that the returned results are not as expected, records are not ordered..
check the definition of the created view :

sp_helptext view_name

you find that the management studio added a TOP clause to the view.
reviewing SQL Server Books Online:

SQL Server 2000:

The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.

SQL Server 2005:

The SELECT clauses in a view definition cannot include the following:
.
.
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

This makes extreme sense to me, as views represent logical tables, so just like tables, the order in which you get data should not be assumed, unless explicitly requested using an order by in the query.

ctrl+R to refresh SQL Server enterprise manager query results

A small tip that many people don't know..
You can run or refresh SQL Server 2000 enterprise manager query results without clicking the (run) toolbar button using the (ctrl+R) shortcut.

Returning random records.. MS SQL Server vs. MySQL

Sometimes it's useful to return a number of random records from a database. For example a "tip of the day" functionality or displaying random products in an e-commerce site.
In MS SQL Server, this can be done using a technique that depends on the newid() function that returns a GUID . This technique is well described in this SQLTeam article.
I tried to use the same technique in MySQL using a query like:

select name from country order by uuid() limit 10;

Thinking that the uuid() function will do the same job as newid() in MS SQL Server.
for my astonishment, this could not do the job at all, the same 10 records are always returned.
After changing the query to :

select name,uuid() from country limit 10;

the results where:

+------------+--------------------------------------+
| name       | uuid()                               |
+------------+--------------------------------------+
| Anguilla   | 85f55cb0-2620-102a-9f3b-3e89f326e405 |
| Argentina  | 85f55cf4-2620-102a-9f3b-3e89f326e405 |
| Australia  | 85f55d0f-2620-102a-9f3b-3e89f326e405 |
| Austria    | 85f55d2a-2620-102a-9f3b-3e89f326e405 |
| Belgium    | 85f55d64-2620-102a-9f3b-3e89f326e405 |
| Brazil     | 85f55d73-2620-102a-9f3b-3e89f326e405 |
| Canada     | 85f55d80-2620-102a-9f3b-3e89f326e405 |
| Chile      | 85f55d8d-2620-102a-9f3b-3e89f326e405 |
| China      | 85f55d9b-2620-102a-9f3b-3e89f326e405 |
| Costa Rica | 85f55da8-2620-102a-9f3b-3e89f326e405 |
+------------+--------------------------------------+

Looking at the results above, the uuid() function seems to return sequential unique identifier. which is not the intended behavior by any means (in my case of course).

So..I tried another technique that would fail in SQL Server(As I may explain why later)

select name from country order by rand() limit 10;

and this worked great!! Each time I run this query, different 10 country names are returned.

Why does using rand() to get random records in SQL Server fail? And how to overcome this?

Ok, maybe I'll talk about this later in another post. I want to sleep.

Editing SQL Server 2000's DTS packages in SQL Server 2005

In MS SQL Server 2005, DTS was replaced with SSIS.
Fortunately, Microsoft appreciates the cost and effort spent by companies and DBAs to create DTS packages. So SSIS has (Execute DTS 2000 Package) task.
This is great, But what if you want to make a small edit to the DTS package after migrating to the new version of SQL Server?
You have 2 options:

1. Import the DTS package to SSIS, it's relatively easy. And although I had minor problems when trying this, the process was successful.
   
2. If you don't want to migrate to SSIS, Microsoft provided a good download in its SQL Server 2005 feature pack called Microsoft SQL Server 2000 DTS Designer Components. which can be used to edit and save DTS packages using the same interface used in SQL Server 2000 and save it. A really good one from Microsoft!!.
   

Dynamic declaration of cursors in MS SQL Server

Sometimes it's required to declare and use cursors to apply some logic on the results of queries that return the same columns, but using different criteria.
For example, you format a query based on user's selection in a search screen. Then you need to apply some logic that requires cursors on the result. This logic can be paging logic (as it was the case the first time I use this technique) or whatever logic defined by your application.

This dynamically generated code can look like:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10

Now, the question is, how to declare a cursor for such a dynamic query and use this cursor in static code within a stored procedure for example to apply the required logic.

This technique cames to work great:

Create Proc CursorLogic(@SQL varchar (1000))
as
declare @id int
declare @name varchar(1000)
declare @joindate datetime

Set @sql='declare cur cursor for '+ @sql
exec (@SQL)
open cur

fetch next from cur into @id,@name,@joindate
while @@FETCH_STATUS=0
Begin

print @name
-- Cursor logic goes here

fetch next from cur into @id,@name,@joindate
End
close cur
deallocate cur
Go

This creates a stored procedure that takes the @sql variable which is a SQL query.
It inserts the declare cursor statement then uses this cursor inside the stored procedure logic, just as if it was declared regularly within the stored procedure code.

This stored procedure can be called as:

declare @sql varchar(1000)
Set @sql='Select ID,Name,JoinDate Fom Employee Where ID>10
Exec CursorLogic @SQL

Set @sql='Select ID,Name,JoinDate Fom Employee Where Name like 'M%'
Exec CursorLogic @SQL