Category Archives: SQL server 2005

ORDER BY and CASE Expression

/* We can use CASE expression in ORDER BY clause. This is useful if a stored procedure has parameters from client side to specify ordering column and direction, e.g.: */ USE AdventureWorks GO DECLARE @SortOrder nvarchar(5), @OrderBy sysname; SELECT @SortOrder=N‘ASC’, … Continue reading

Posted in SQL server 2005 | Leave a comment

Pivot more than one columns

In a recent T-SQL forum post, an interest question was how to pivot two columns in a table.The raw data is like:[Time],[Type],[Value],[ID]N’21:00′,N’A’,60.24,1N’21:00′,N’B’,20.00,2N’22:00′,N’C’,21.33,3N’22:00′,N’D’,80.00,4 And it was required to pivot [Type] and [ID] as following: Time    A     IDA    B    IDB    C    IDC  … Continue reading

Posted in SQL server 2005 | Leave a comment

Is the length of empty string always 0?

DECLARE @x nvarchar(max), @b varbinary(max), @v sql_variant; SELECT @x=”,@b=cast(” as varbinary(max)),@v=cast(” as sql_variant) — For those variable length data type, the length of empty string always returns 0 SELECT DATALENGTH(@x),DATALENGTH(@b),DATALENGTH(@v); GO — for untyped xml: declare @x xml; — You … Continue reading

Posted in SQL server 2005 | Leave a comment

Avoid dollar sign in table name

Dollar sign ($) is allowed to be used in temparory table name in SQL Server. E.g. run the following script in SSMS: CREATE TABLE #AA$(c1 int)INSERT #AA$(c1) VALUES (1)SELECT * FROM #AA$DROP TABLE #AA$GO The script works fine. However, if … Continue reading

Posted in SQL server 2005 | 3 Comments

A severe error occurred on the current command

Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.   A scary message, isn’t it? Create the following stored procedure and run it in SSMS:   … Continue reading

Posted in SQL server 2005 | Leave a comment

SQL Server proxy account and local policy

If a user is a member of Sysadmin server role of SQL Server 2005, the user runs xp_cmd_shell in the security context of SQL Server service running account. If he is not a member of sysadmin role, the server proxy … Continue reading

Posted in SQL server 2005 | 4 Comments

Replication Agents and Maintnance Jobs

There are bunch of agents and maintnance jobs to perform replication in SQL Server 2005. The following is their running location and sumary: SnapShot Agent: Runs on distributor; used by All type replications.There is one agent for each publication; It … Continue reading

Posted in SQL server 2005 | Leave a comment