Monthly Archives: April 2015 - Page 8

When should we use @@identity or scope_identity() or ident_current()

Posted by on April 7, 2015 No comments

SELECT IDENTITY() returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and within the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like IDENTITY returns the last identity value created in the current session, but it will also limit it to your current scope as well.

SELECT IDENT_CURRENT(‘tablename’) returns the last IDENTITY value produced in a table.

Database Refresh – Fix orphan accounts that are NOT in sync on different servers

Posted by on April 7, 2015 No comments

DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR FOR
SELECT 'EXEC sp_change_users_login UPDATE_ONE, ' + name + ', ' + name
FROM sysusers
WHERE issqluser = 1 AND name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
print @SQL
EXEC (@SQL)
FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

Migrating Databases from SQL Server 2005, 2008, 2008 R2 to 2012

Posted by on April 7, 2015 No comments

Here are the pre-migration and post-migration steps:

Premigration Steps:

/* 1) run DBCC consistency checker */
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
/* 1a) Normally we KILL as user connections for an outage but since the application servers were shutdown we didnt need to run SQL script to kill user connections */

/* 2) check for all the logins having access to FEWS database so that there are no permission issues after migration */
/* 3) Backup Database and make sure to click on "verify backup when finished" in options tab */
/* 4) After the backup finishes, copy to destination, restore from the destination */
/* 5) Modified the auto-growth increments of the database for all the secondary files to avoid the DB growing in baby steps and avoid performance issues */

Postmigration Steps:

-- check for orphaned users
exec sp_change_users_login 'report'

-- Some logins are orphaned, so we need to migrate the login from source server
select 'create login ' + quotename(sp.name) + ' with password = ' + CONVERT(nvarchar(max), sl.password_hash, 1) + ' HASHED'
from sys.server_principals sp
inner join
sys.sql_logins sl
on sp.principal_id = sl.principal_id

-- attach the Database user with the server login
exec sp_change_users_login 'UPDATE_ONE', 'sqllogin01', 'sqllogin01'

-- verify that the SQL login sqllogin01logins to the server without access issue.

-- check the port - since NOT listening to dynamic ports it is 1433
select local_tcp_port from sys.dm_exec_connections
where session_id = @@SPID

-- run DBCC on the destination for final confirmation
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS