SQL version

I have a little list – of MS SQL servers and what to get some general data on them.  There are various collections of scripts to run and stored procedures/DMVs to get that data, but won’t you know – not all versions of SQL have the same tables and stored procedures.  And we have 2005, 2008, 2008R2 and 2012.

Fairly quickly in Powershell, i can open a connection to a server and pull back data from the scripts, but also quering the version of SQL.  I have written a function that i can put into the scripts to check the version, and if it is, for example, 2005, i can either change the sql to run, or run something that will not fail but will give me a response that i can work around.

The function is called with the version of SQL to test for (this is basically a placeholder) and a bit of response to look for.  For example, the SQL version number.  If i get that back from the server in response to a query to the master table, i know that i have a ‘bad SQL” instance i will have to modfiy the code.

The code look like

function checkversion {
$sql = "SELECT @@SERVERNAME AS [Server Name], `
" @@VERSION AS [SQL Server and OS Version Info];"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($sql,$cn)
$rdr = $cmd.ExecuteReader()
    ($rdr[1] -like "*$test*")
} # close while}# end function

and  you can assign the return value which is true or false to a variable like

$goodversion = checkversion 2005 9.00

and use the value of $goodversion to set your connection command string to an appropriate value.

This entry was posted in Uncategorized. Bookmark the permalink.