How to Convert Cursor Procedure Into Set-based Query T-SQL

Cursors have been taught as a no-no in SQL programming, mostly because it is resource-intensive. Of course, there will be some exceptional cases where the use of cursors is justified. In my case, I used cursors because it feels familiar. If you have learnt some procedural ‘ancient’ programming languages, you would understand what I meant. [...]

Read Full Post »

One-liner : Find out the node/ server name

In SQL Server 2000: select * from ::fn_virtualservernodes() In SQL Server 2005: select * from sys.dm_os_cluster_nodes Or this backward compatibility function: select * from fn_virtualservernodes() Reference: BOL

Read Full Post »

One-liner: sp_change_users_login

To automatically map users to the SQL logins on the server: exec sp_change_users_login ‘Auto_fix’ To find orphaned users ( users with no logins): Use exec sp_change_users_login @Action = ‘Report’ Reference: BOL

Read Full Post »

How to Rename Your Database – SQL Server 2005

In SQL Server 2000, you can easily rename your database running sp_renamedb. The syntax from BOL as follows: sp_renamedb [ @dbname = ] ‘old_name’ , [ @newname = ] ‘new_name’ In SQL Server 2005, however, this has changed. You will need to use ALTER DATABASE MODIFY NAME instead. ALTER DATABASE database_name MODIFY NAME = new_database_name [...]

Read Full Post »