Text* Snippets: transact code

Fix orphaned users in MSSQL

Thanks to CodeProject for this code

Run this in the Query Analyzer (make sure the right database is selected first).

sp_change_users_login 'auto_fix', 'UserName'

to users sql mssql microsoft data transact orphan orphaned user restore restoration restoring by chrelad on Dec 11, 2007

Reset identity seed for a table

This SQL query will reset an identity seed to the specified value.

Thanks to this site for the answer:

http://www.thescripts.com/forum/thread657059.html

I know this works for MSSQL 2000 at least :)

DBCC CHECKIDENT('mytable', RESEED, 0) ;

to sql reset microsoft column transact reseed seed identity ident field ms dbcc checkident by chrelad on Nov 16, 2007

Selecting different parts of a DATETIME with MSSQL

Thanks to this website for the information:

http://www.databasejournal.com/features/mssql/article.php/1442021

-- The syntax for pulling a certain part of a DATETIME is:
--
-- CONVERT(date_type[(length)],expression[,style])
--
-- The available styles are as follows:
--
-- NULL  Jun 24 2001 9:48PM
-- 1     06/24/01
-- 101   06/24/2001
-- 2     01.06.24
-- 104   24.06.2001
-- 108   21:48:00
-- 112   20010624
-- 121   2001-06-24 21:48:00.000

-- Some example usage:
SELECT CONVERT(DATETIME,GETDATE(),112) as date
-- Will output in YYYYMMDD format
SELECT CONVERT(DATETIME,client.birthday,101) as birthday
-- Will output in MM/DD/YYYY format

to time date sql timestamp mssql language query transact structured datetime parts part stamp only sub by chrelad on Nov 14, 2007

Related Tags

Never been to TextSnippets before?

Fix orphaned users in MSSQL

Reset identity seed for a table

Selecting different parts of a DATETIME with MSSQL