Generate sqlcmd statements

I have a change implemented by several (10+) T-SQL script files in sizes from about 200 KB to 739 MB. Executing a 739 MB T-SQL script in Microsoft SQL Server Management Studio (SSMS) will fail, and typing several sqlcmd statements manually will be tedious and faulty.
By placing alle the T-SQL script files in one folder and using a single PowerShell statement all the sqlcmd statements can be created nice and quick:
ls *.sql | %{"`n@ECHO $($_.BaseName)...`nsqlcmd.exe -E -S %DbInstanceName% -i `"$($_.Name)`" -o `"$($_.BaseName).log`" -r1 -b`n@ECHO :: ErrorLevel = %ERRORLEVEL%`n@ECHO."}
The generated sqlcmd statements I can then copy into a Windows Shell script file (.cmd) where the variable DbInstanceName is defined by
@SET DbInstanceName="sandbox.sqladmin.lan\SSDB01"
It is on purpose that the Windows Shell script continues on error - in this specific case.

The logfile defined by the parameter -o in each sqlcmd statement holds output as results (e.g. SELECT), events (e.g. PRINT) and errors (e.g. RAISERROR).
This is verified by using a T-SQL test script
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello from PRINT';
GO
RAISERROR( N':: Hello from RAISERROR', 18, 0 );
and calling this from a sqlcmd statement generated by the PowerShell statement above.

SQL Server Agent schedule owner

When a database mirroring session is created using Microsoft SQL Server Management Studio (SSMS) the job "Database Mirroring Monitor Job" is created automatic. But if you create the database mirroring session by script the job is not created. This is described in more details tin the MSDN Library article "Monitoring Database Mirroring".

A database instance had a mirroring session running against one host, and the mirroring host was recycled. Then the database mirroring sessions were recreated against another host. This time by script.
No Problem I thought.
The job "Database Mirroring Monitor Job" was scripted out in SSMS and then created on the mirroring database instance. It then turns out that the jobs was not created exact as on the principal database instance.

The owner of the SQL Agent schedule "Database Mirroring Monitor Schedule" was NULL on the mirroring installation where is was "sa" on the principal installation.
This does not show in SSMS windows or by calling the procedure msdb.dbo.sp_help_schedule. But the table msdb.dbo.sysschedules holds the SID of the schedule owner.
SELECT [msdb].[dbo].[sysschedules].[name],[msdb].[dbo].[sysschedules].[owner_sid]
FROM msdb.dbo.[sysschedules]
WHERE [msdb].[dbo].[sysschedules].[name]=N'Database Mirroring Monitor Schedule';

I could not find a way to alter the owner of a given schedule in SSMS. The procedure msdb.dbo.sp_update_schedule does the trick.

EXECUTE [msdb].[dbo].[sp_update_schedule]
@name = N'Database Mirroring Monitor Schedule',
@owner_login_name = N'sa';

You could also do it quick and dirty.

UPDATE [msdb].[dbo].[sysschedules]
SET [owner_sid]=0x01
WHERE [sysschedules].[name]=N'Database Mirroring Monitor Schedule';

Not nice!

Now the schedules are identical and I can disregard the finding.

SQL Server consolidation

This Technical White Paper was published by Microsoft March 2009:
SQL Server Consolidation at Microsoft
The document is rather interesting as it in a structured way describes what we as concerned and dedicated DBAs thinkabout when we have a spare moment...
I like the Servers Ratings based on CPU usage (p 7), as it is a simple and yet effective way of rating the existing SQL Server installations. The existing monitoring tools in the infrastructure such as Microsoft System Center Operations Manager (SCOM) or Idera SQL Diagnostics Manager (SQLdm) will have the historic data needed to rate the existing SQL Server installations.
The three consolidation approaches (p 7) are also usefull. I only miss an approach like "Network Consolidation", to handle a needed segregation of duties and environments and still manage the entire enterprise SQL Server platform.
In general the half hour spend reading the paper was worth it.

Copy SQL login

In my daily surroundings we have a lot of mirrored SQL2005 databases, and to be ready to fail over we do a daily compare of the instances in each mirroring setup.
Among other things we compare the logins, and is a SQL login missing on the mirror instance, it must be created. It could be nice to have a single statement to do this...
The obstacle has been that the password hash is type varbinary(max), but using my favorite internet search engine inspired a solution.

The basic statement is
DECLARE @login_name sysname;
SET @login_name = N'Siegfried';
DECLARE @pw_hash varbinary(max);
SELECT @pw_hash = [password_hash] FROM master.sys.sql_logins WHERE [name]=@login_name;
SELECT 'CREATE LOGIN ['+@login_name+'] WITH PASSWORD=0x'+ CAST('' as xml).value('xs:hexBinary(sql:variable("@pw_hash"))','varchar(max)') + ' HASHED;' AS [DCL];
The value of the variable @login_name must be entered manual, but this is the basic statement...

Discussion
The statement could be implemented in a single function (UDF), procedure (USP) or a complete procedure.
This depends on the given database infrastructure. Among other things the remoting possibilities must be take in consideration.

Reference
The inspiration is the blog entry at "SQL Server Engine Tips":
http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
Also the documentation of [sys].[sql_logins] at MSDN Library:
http://msdn.microsoft.com/en-us/library/ms174355(SQL.90).aspx