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 [...]
Archive for September, 2009
How to Convert Cursor Procedure Into Set-based Query T-SQL
Posted in SQL Server, tagged tips, tsql on 24 September 2009 | 2 Comments »
One-liner : Find out the node/ server name
Posted in SQL Server, tagged one-liner, tsql on 21 September 2009 | Leave a Comment »
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
One-liner: sp_change_users_login
Posted in SQL Server, tagged one-liner, tsql on 18 September 2009 | Leave a Comment »
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
How to Rename Your Database – SQL Server 2005
Posted in SQL Server, tagged modify, tips on 14 September 2009 | Leave a Comment »
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
Although the database [...]
