SQL Snippets

From tekkies.co.uk
Jump to: navigation, search

Get login used on this connection

SELECT suser_sname()

Get the number of bytes in an Image column

SELECT DATALENGTH(Data) from tbl_IMG_Jpg

Raise an error

Both errors are detected

DECLARE @MyErrorCode int
SET @MyErrorCode = 55
RAISERROR ('This has happened', 11, @MyErrorCode)          --TSQL continues to run
RAISERROR ('That has happened', 20, @MyErrorCode) WITH LOG --TSQL is terminated after this statement

Both methods show a red error message in SSMS [1]. Both methods will cause an SQLAgent[2] job to consider the statement to have failed.

Efficient way to strip the time element from a DateTime:

SELECT CONVERT(DateTime, CONVERT(varchar(10), GetDate(), 120), 120)

Reconnect Users

If you backup a database and restore it onto another server, often the logins can become unlinked due to internal ID's of the logins being different on the different servers. This procedure attempts to re link any logins it can find.

Run this query on the newly restored database (you may see some errors when it runs):

declare curUser cursor for
select name from sysusers where hasdbaccess=1 and uid <> 1
declare @name varchar(50)
open curUser
fetch next from curUser into @name
while @@FETCH_STATUS = 0
begin
  print 'Fixing: ' + @name
  exec sp_change_users_login 'Update_One', @name, @name
  fetch next from curUser into @name
end
close curUser
deallocate curUser