SQL 2008 Active Node

The following can be used to determine the active node in a SQL cluster

SELECT ServerProperty('ComputerNamePhysicalNetBIOS')

Transfering SQL 2008 Logins Between Servers

The following script can be used when transfering logins between SQL Server instances whilst preserving the password. This has been tested on SQL 2008 only. 1. create the sp_help_revlogin stored procedure using the following.

USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO

2. Now run the stored procedure sp_help_revlogin to generate a script for each login. 3. Run the required login creation commands on the destination instance.

BlogThis!

List all Objects per Filegroup

The following query can be used to list all obejcts per filegroup including the allocated data size.

SELECT ds.name AS DataSpaceName ,au.type_desc AS AllocationDesc ,au.total_pages / 128 AS TotalSizeMB ,au.used_pages / 128 AS UsedSizeMB ,au.data_pages / 128 AS DataSizeMB ,sch.name AS schemaName ,obj.type_desc AS ObjectType ,obj.name AS ObjectName ,idx.type_desc AS IndexType ,idx.name AS IndexName FROM sys.data_spaces AS ds INNER JOIN sys.allocation_units AS au ON ds.data_space_id = au.data_space_id INNER JOIN sys.partitions AS PA ON (au.type IN (1, 3) AND au.container_id = PA.hobt_id) OR (au.type = 2 AND au.container_id = PA.partition_id) INNER JOIN sys.objects AS obj ON PA.object_id = obj.object_id INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id LEFT JOIN sys.indexes AS idx ON PA.object_id = IDX.object_id AND PA.index_id = idx.index_id WHERE obj.type_desc NOT IN('SYSTEM_TABLE', 'INTERNAL_TABLE') ORDER BY ds.name ,sch.name ,obj.name ,idx.name

SSIS - Overiding Package Variable Configuration Values on the Command Line

To overide SSIS pacakge variable values set in configuration files with those specified on the command line the following can be added to the command line.

/Set \Package.Variables[User::start_date].Properties[Value];2012-09-09

Powershell - Get a Specific Line from a Text File

To retrieve the specific line of a text file in powershell use the following command:

Get-content -Path myfile.txt | Select-Object -Index 60614

To retrieve the first 10 rows use:

Get-content -Path myfile.txt | Select-Object -First 10

To retrieve the last10 rows use:

Get-content -Path myfile.txt | Select-Object -Last 10

Transaction Log Restore

The following statement can be used to restore a transcation log back up and keep the database on standby as read only.

RESTORE LOG [corporate_log] FROM DISK = N'\\10.0.1.83\dba\logship\dbname\filename.trn' WITH FILE = 1 ,STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_dbname.BAK' ,NOUNLOAD ,STATS = 10

MySQL Script all Stored Procedures

The following will generate a script to recreate all stored procedures and functions for all MySQL databases in an instance.

mysqldump -u username -p -h servername --routines --no-create-info --no-data --no-create-db --skip-opt --all-databases > outputfile.sql

To recreate the routines on another server run the following:

mysql -u username -p -h servername < outputfile.sql

To view all stored procedures and functions use the following commands:

SHOW PROCEDURE STATUS SHOW FUNCTION STATUS

Adding a Linked Server

DECLARE @Server sysname SET @Server = 'LINKED_SERVERNAME' EXEC sp_addlinkedserver @Server , @srvproduct = N'' , @provider = N'SQLOLEDB' , @datasrc = N'10.0.1.70' EXEC sp_serveroption @Server, N'data access', N'true' EXEC sp_serveroption @Server, N'rpc out', N'true' EXEC sp_serveroption @Server, N'rpc', N'true' EXEC sp_serveroption @Server, N'use remote collation', N'true' EXEC sp_serveroption @Server, N'collation name', N'null' EXEC sp_serveroption @Server, N'connect timeout', 0 EXEC sp_serveroption @Server, N'query timeout', 0 EXEC sp_addlinkedsrvlogin @Server, 'false', null, 'username', 'password'

SQL Server Mirroring Disconnected - Debug

If mirroring is showing as suspended it should only be necessary to run the resume command. If it is showing as disconnected the following may help to track down and fix the problem.

Check all things mirroring from the following tables:

SELECT * FROM sys .dm_db_mirroring_auto_page_repair SELECT * FROM sys .dm_db_mirroring_past_actions SELECT * FROM sys .dm_db_mirroring_connections SELECT * FROM sys .database_mirroring SELECT * FROM sys .database_mirroring_endpoints

Check the error log

Check that the route between servers is accessible.

Audit Database Mirroring Login Database Mirroring State Chang Database Mirroring Connection Broker: Connection

Try resuming mirror by running the following on the principal for each database:

ALTER DATABASE dbName SET PARTNER RESUME

Try restarting the endpoints on both servers.

ALTER ENDPOINT endpointName state = STOPPED ALTER ENDPOINT endpointName state = STARTED

SQL Server Server-Side Paging

Server side paging of SQL 2008 result sets can be achieved using the following code:

DECLARE @start_row INT ,@end_row INT SET @start_row = 1 SET @end_row = 10 SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY a.updated_date ) AS row_num ,* FROM dbo.address a WHERE a.updated_date > '2012-02-13' ) AS r WHERE row_num >= @start_row AND row_num < @end_row ORDER BY row_num

Mysql Start/Stop

svcadm –v enable/disable mysql-csk /etc/init.d/mysql start/stop