Tag Archives: SQL

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

Full text search time out problem

This is a MS SQL 2005 server. The full text search seems time out after some idle time. Interestingly, this time out only happens to ASP.NET connections. Query Analyzer never timed out no matter what login name used.  This MS … Continue reading

Posted in SQL | Tagged | Leave a comment

Crosstab Query (Rows to Columns)

First,  columns to rows are very simple with UNION: SELECT col1 as [new column]  FROM table1 UNION SELECT col2 FROM table1 UNION SELECT col3 FROM table1 This is very helpful when I need to run aggregate function on multiple columns: … Continue reading

Posted in SQL | Tagged | Leave a comment