CPU count from T-SQL

I would like to know the number of processors (cores) on a given computer. No matter what the CPU affinity is or how many database instances is running on the computer.

The answer is given by the DMV "sys.dm_os_schedulers"
SELECT MAX([cpu_id]) + 1 AS [cpu_count]
FROM [master].[sys].[dm_os_schedulers];

I could use WMI and the Win32_Processor class (MSDN Library), but I don't always have the necessary rights to read the value remote.

Log failure in a SQL Agent PowerShell job step

I wanted to have a template for a SQL Server Agent PowerShell job step. A key feature of the template is to make the job step fail controlled with a customized and informative error message.
The job step is defined in a script file (.ps1) called by the SQL Server PowerShell job step. If an error occurs in the script file, this is caught, described and handled. The script exits and the job step presents the error in the job step history and fail. By failure I also want that the value of “run_status” in the table msdb.dbo.sysjobhistory is set to “0” (zero).
The output from the script and the job step is streamed to a log file defined in the job step. The log file should hold all output and messages of the job step execution. That is both customized output and standard output from components.

The name of the logfile is defined with other job step parameters in a script configuration file for the job step.
$LogName = 'JobStepFailure'
$LogFileFolder = 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Log'
In the script file the log file is implemented and initialized
$ScriptStartTime = [System.DateTime]::Now
$LogFileName = "$LogFileFolder\$LogName.{0:yyyyddMMTHHmmss}.log" -f $ScriptStartTime

After the script and the logfile is initialized, I move on to reset the error array. Just in case...
$Error.Clear()
and the script execution can start for real
"{0:s}  Begin." -f $([System.DateTime]::Now) > $LogFileName

If a error occurs in the script execution, I usually handle the basics like this
try {$rowsSource = $DataAdpt.Fill($DataSet) }
catch {
  "{0:s}  Exeption: $_" -f $([System.DateTime]::Now) >> $LogFileName
  throw "Error in script. Check logfile '$LogFileName' for details."
}
finally { $CnnSource.Close() }

At the end of the script
"{0:s}  End." -f $([System.DateTime]::Now) >> $LogFileName
I check one more time for errors and exit the script
if ($Error) {
  throw "One or more error in script execution.`nCheck logfile '$LogFileName' for details."
}
else {
  "Script executed with success.`nCheck logfile '$LogFileName' for details." | Write-Output
}

One major argument to create the log file from the script file instead of using the parameter @output_file_name to the procedure msdb.dbo.sp_add_jobstep is that if the PowerShell script throws an exception, the content of the logfile is only the exception.
I do believe that this template can be used as a general solution.

PowerShell script configuration file

There are two major reasons for looking into how to handle configuration files for PowerShell scripts.

1. Makes a reuse of the script easy. Just make another configuration file, and you have a reuse.
2. Makes test and deployment possible. With a configuration file your script can be executed in all environments with the right values.

I like to keep things simple, and by dot-sourcing (not Duck Sauce - Sorry...) a PowerShell script file I have a simple solution. For convenience I name the file "*.config.ps1".
. ".\myScript.taskOne.config.ps1"
When I use the structure for a SQL Agent job with several steps, like import data from various sources, I like the naming "{task}.{job step}.config.ps1".

Each value in the configuration file is defined as a normal PowerShell variable. If you want to tighten your solution, you can define context or type.
[string]$script:SourceServer = 'SANDY.sqladmin.lan'

The configuration file can be tested in the script file on each run by checking the parameter and their value.

One thing I almost always define i the configuration file is the logfile name for the current script execution. Usually with a timestamp in the name.

The solution I have used in production, and it works fine. The simplicity makes it easy to use and maintain.

I tried some XML formatted configuration files, like in Visual Studio projects, but I found them too complicated  to use in a script as I had to parse the XML file to get the values.
Also some old-style configuration files, also called initialization files, use to define sections with [] and values assigned in the sections. Again I found them too complicated to use. Usually the initialization file is accessed with regular expressions, which I normally find to be a rather powerful tool, but when I can do a thing more simple, I do that - Occam's razor...

SQL Server Agent PowerShell job step

Some time ago I wrote about this in another blog entry, and I do it again while there are some major changes in what I want to use.
First of all I want the SQL Server Agent jobstep to be defined as a PowerShell job step, not a CmdExec job step.
Also I have some other issues I want to handle.

1. The job step is defined in a script file, not a script in the job step command field.
2. The script file takes input by named parameters.
3. There are one or more spaces in the script file path, e.g. "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS".

When I looked around on the internet, two different solutions were used.
One using the PowerShell call operator ampersand (&).
One using the Invoke-Expression CmdLet. The last should be avoided while it has some risks. These risks are described in the book "Windows PowerShell in Action, Second Edition" (ISBN-13 978-1-935182-13-9) by Bruce Payette in section 21.6.4. This book is most recommend.

Actually it turned out to be quite simple, when I had the quotation marks in place.
& "D:\Microsoft SQL Server\MSSQL10.SANDY\MSSQL\JOBS\SQLPS_test.ps1" -param_00 'Zero' -param_01 'One'
The trick is to put the full name of the script file in quotation marks, and the parameter names and values outside the quotation marks.

Whe you define the job step usin the stored procedure msdb.dbo.sp_add_jobstep, the value of the parameter @command has a lot of quotation marks.
@command=N'& "D:\Microsoft SQL Server\MSSQL10.SANDY\MSSQL\JOBS\SQLPS_test.ps1" -param_00 ''Zero'' -param_01 ''One'''
Please notice that some quotation marks are single and some are double.

The script file is like this.
param(
  [Parameter(Mandatory=$true)][string]$param_00,
  [Parameter(Mandatory=$true)][string]$param_01
)
"{0:s}  Hello." -f $([System.DateTime]::Now)
"{0:s}  Parameter value 00 = $param_00" -f $([System.DateTime]::Now)
"{0:s}  Parameter value 01 = $param_01" -f $([System.DateTime]::Now)
"{0:s}  Goodbye." -f $([System.DateTime]::Now)

and the output is like
2012-02-14T07:50:03  Hello.
2012-02-14T07:50:03  Parameter value 00 = Zero
2012-02-14T07:50:03  Parameter value 01 = One
2012-02-14T07:50:03  Goodbye.

Change database owner

I am about to join an internal course, and would like to prepare by installing the SQL Server Database Product Samples from CodePlex.
After the installation, the databases were owner by my login, and I would like to have the owner changed to "sa". I my case it is renamed, but that is another story.

Renaming a database in SQL Server Management Studio is done on the properties of the database in the page "Files".
When I ask for the change script by clicking the Script drop-down, I get a script that uses the stored procedure sp_changedbowner.
The article on this procedure in Microsoft documentation notes that this feature will be removed, and that I should use ALTER AUTHORIZATION instead.
Then I rewrote the statement to use the recommendation
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
Please recall that I have renamed "sa".

The samples generates several databases. Six in my case.
Instead of generating a script for each database, I made a script to generate these scripts
SELECT N'ALTER AUTHORIZATION ON DATABASE::[' + [databases].[name] + N'] TO [sql_sa];' AS [DCL]
FROM [master].[sys].[databases]
WHERE [databases].[owner_sid] != (
  SELECT [server_principals].[sid]
  FROM [master].[sys].[server_principals]
  WHERE [server_principals].[name] = N'sql_sa'
);

I could have generated the scripts for the first principal, as "sa" always is the first, but maybe another day I would like to use another principal.

The generated scripts are like this
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT] TO [sql_sa];

Dedicated Database Owner

By default the owner is the login that creates or restores the database. This is not a great situation to have a person as owner due to several security issues. Finding databases with the owner not being "sa" can be done with a script like below. To fence a database more in it can be assigned its own dedicated owner which is indicated in the column [expected_owner_name].

SELECT
	[databases].[name] AS [database_name],
	[server_principals].[name] AS [current_owner_name],
	[databases].[name] + N'_owner' AS [expected_owner_name]
FROM [master].[sys].[databases]
INNER JOIN [master].[sys].[server_principals] ON [databases].[owner_sid] = [server_principals].[sid]
WHERE [server_principals].[principal_id] > 1

The dedicated database owner is implemented as a SQL Login where the password is generated on creation. For that I use a script made by Pinal Dave and presented in this post. Creating the owner and the setting it can be done with a script like this:

-- Create owner
DECLARE @_login sysname = N'sqladmin_inventory_owner';  -- Copy from expected_owner_name
DECLARE @_database sysname = N'sqladmin_inventory';     -- Copy from database_name

DECLARE @_stmt_0 nvarchar(max) = N'CREATE LOGIN [' + @_login
	+ N'] WITH PASSWORD=''' + @_password
	+ N''', DEFAULT_DATABASE=[' + @_database
	+ N'],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;';
--RAISERROR( @_stmt_0, 0,0 ) WITH NOWAIT;  -- Debug
EXECUTE [master].[sys].[sp_executesql] @stmt = @_stmt_0;

DECLARE @_stmt_1 nvarchar(max) = N'ALTER LOGIN [' + @_login + N'] DISABLE;';
EXECUTE [master].[sys].[sp_executesql] @stmt = @_stmt_1;

-- Change database owner
DECLARE @_stmt_2 nvarchar(max) = N'ALTER AUTHORIZATION ON DATABASE::[' + @_database
	+ N'] TO [' + @_login + N'];';
EXECUTE [master].[sys].[sp_executesql] @stmt = @_stmt_2;

Using parameters with sp_executesql in a DCL statement fails with syntax errors. This is why I dynamically create the statement without using parameters. Not nice but working.

History

• 2025-05-23 : Section about Dedicated Database Owner added. Links changed from MSDN to current location.
• 2012-02-11 : Post created.