Category Archives: SQL

Crosstab Query (3)

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ‘, ‘, ”) + Name FROM People SELECT @Names Or this post about FOR XML PATH http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string Advertisements

Posted in SQL | Leave a comment

SET NUMERIC_ROUNDABORT ON and others

A good link:¬†http://sqlservercode.blogspot.com/2006/11/use-sessionproperty-to-check-if.html Useful commands: DBCC USEROPTIONS SELECT¬†SESSIONPROPERTY(‘numeric_roundabort’) NUMERIC_ROUNDABORT seems is a server level (or user session level) setting. Because when I change it on one DB, all other DBs are changed too. If use Visual Studio Database project, make … Continue reading

Posted in SQL | Tagged | Leave a comment

SQL command always times out after 30 seconds

One of my query takes more than 30 seconds to run. Even I set the “Connection Timeout=120” in my connection string, it still throwing timed out exception. It turned out that the time out setting in the connection string is … Continue reading

Posted in .NET, SQL | Tagged | Leave a comment

Crosstab query (2)

Part one here. Another way to do vertical to horizontal query. Let’s have a table with sales_id, prod_id, sales_date, and sales_amount fields. This query will return 4 latest sales_id in 3 columns: SELECT prod_id, (SELECT TOP 1 sales_id FROM table … Continue reading

Posted in SQL | Tagged | Leave a comment

Reseed an Identity column in SQL 2008

In SQL 2000/2005, I used to be able to change the seed of an identity field in table designer. It will just prompt a message saying this table and a few other table (if having FK on) will be modified. … Continue reading

Posted in SQL | Tagged | Leave a comment

Scan stored procedures source script

Want to remove an obsolete table column but don’t want to break any views that may still reference it? SELECT v.NAME, c.[text] FROM syscomments c, sys.VIEWS v WHERE v.OBJECT_ID=c.id AND c.TEXT LIKE ‘%MY FIELD%’ The above is just for the … Continue reading

Posted in SQL | Tagged | Leave a comment

Email notification in MS SQL 2005/2008

A few undocumented steps, on top of the obvious ones, before being able to send email notifications Database Mail -> Configuration -> Manage profile security, make sure there is at least one public and default profile. SQL Server Agent -> … Continue reading

Posted in SQL | Leave a comment