SQLGeordie's Blog

Helping the SQL Server community……where i can!

How to output from invoke-sqlcmd to Powershell variable — February 3, 2012

How to output from invoke-sqlcmd to Powershell variable

Sorry for another Powershell post but I’ve been doing a lot of it recently and coming up with (what i think are) a few nifty tricks.

One of the issues I encountered recently was with Kerberos delegation whilst trying to automate Log Shipping. What I was trying to do was use an OPENROWSET query to run against the Primary and Secondary servers in order to obtain the Primary_id and Secondary_id in order to pass to the script to be ran on the monitor server. However, seeing as the environment was not setup for Kerberos I encountered the “double-hop” issue.

Enabling Kerberos delegation for the service account would be too high a risk without thorough testing so wasn’t an option in this instance so I decided to look into using invoke-sqlcmd against each of the servers to get the IDs required and pass it to the monitor script.

So how did I go about doing this you ask, well its actually really simple. After a bit of googling I came across this blog by Allen White which gave me a starting block.

Firstly, you have to amend your TSQL script to SELECT the parameter you want to output and use within the rest of the script, something like this:

TSQL snippet to be ran against the Primary Server:

--Cut down version of the script for readability
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
		@database = N'$(Database)' 
		...
		,@primary_id = @LS_PrimaryId OUTPUT --This is what we want
		,@overwrite = 1 
		,@ignoreremotemonitor = 1 

--Need to output this in order for powershell to take it and use it in the monitor script
SELECT @LS_PrimaryId as LS_PrimaryId 

Do the same for the script to run on the secondary server but obviously for the secondary_id 🙂

So, now you’ve setup the TSQL side of things, you need to then call these from Powershell and assign the output parameter to a Powershell variable like so:


$script = "LogShip_Primary.sql"
$PrimaryID = Invoke-Sqlcmd -InputFile $ScriptLocation$script -Variable Database=$DatabaseName, etc etc etc -ServerInstance $PrimaryServer 

$script = "LogShip_Secondary.sql" 
$SecondaryID = Invoke-Sqlcmd -InputFile $ScriptLocation$script -Variable Database=$DatabaseName, etc etc etc -ServerInstance $SecondaryServer

So, relatively simple. Basically your setting the output to a Powershell variable. keeping things tidy, re-assign it to another variable and something to note is that the output is actually a DataTable object. Make sure you use the name of the alias you used in your last TSQL statement.


$PID = $PrimaryID.LS_PrimaryId
$SID = $SecondaryID.LS_SecondaryId 

Once this is done then you can use this in your script to run against the monitor server


$script = "LogShip_Monitor.sql" 
Invoke-Sqlcmd -InputFile $ScriptLocation$script -Variable Database=$DatabaseName, etc etc etc, PrimaryID=$PID, SecondaryID=$SID -ServerInstance $MonitorServer

And there you have it, nice n simple! All you then have to do is wrap it in a foreach loop for the databases you want to setup and a nice and simple automated logshipping build script.

Obviously I’ve omitted a lot of the setup / checking of scripts etc from this post as I don’t want to be doing all the work for you!

Enjoy 🙂

Advertisements
So then, what’s the definition of an object……..? — January 25, 2012

So then, what’s the definition of an object……..?

Not blogged for a while due to client and project commitments but something which has surprised me when speaking with colleagues both past and present is that when I mention the built in function OBJECT_DEFINITION, the majority of DBA’s haven’t heard of it, never mind used it. So i felt it necessary to dust off the blog typing fingers and see if i can enlighten 🙂

So, I though it be a good idea to enlighten a few people to how it can be used by giving real world examples.

Firstly, a short definition (no pun intended) from BOL (http://msdn.microsoft.com/en-us/library/ms176090.aspx) as to what exactly this function does:

Returns the Transact-SQL source text of the definition of a specified object.

Its as simple as that!

Pass in the Object_ID which it expects to be in the current database context and it spits out the text. I’ll show you a couple of examples of how it works in comparison to how I’ve seen the same thing done but by using sp_helptext as well as some of the other system tables.

I’ll not beat around the bush and get straight into a few examples and old skool alternatives as there’s not really much more i can say about the function itself:

Example 1 – OBJECT_DEFINITION

SELECT OBJECT_DEFINITION(OBJECT_ID('usp_StoredProcedureName'))


Example 2 – sp_helptext

EXEC sp_helptext 'usp_StoredProcedureName'


Example 3 – Using system tables to search (this is a common way I’ve seen this done)

SELECT  o.[name]
	  , o.type_desc
	  , sc.[text]
FROM  sys.objects o
INNER JOIN syscomments sc ON o.[object_id] = sc.id
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
	  AND o.[name]  = 'usp_StoredProcedureName'


Example 4 – OBJECT_DEFINITION for multiple objects

SELECT [object_ID], [Name], OBJECT_DEFINITION([object_ID]) AS ProcText
FROM sys.procedures


Example 5 – OBJECT_DEFINITION for multiple with filtering

SELECT [object_ID], [Name],  OBJECT_DEFINITION([object_ID]) AS ProcText
FROM sys.procedures
WHERE OBJECT_DEFINITION([object_ID]) LIKE '%CATCH%'


Example 6 – OBJECT_DEFINITION to Script out Procedures

SET NOCOUNT ON;

DECLARE @strSQL NVARCHAR(MAX)

SET @strSQL = ''
SELECT @strSQL += OBJECT_DEFINITION([object_ID])+CHAR(10)+'GO'+CHAR(10)
FROM sys.procedures

SELECT @strSQL


Now this can be used for all programmability objects within SQL Server, not just procedures so the same works for Views, functions, triggers etc

Again from BOL here is a full list:

C = Check constraint

D = Default (constraint or stand-alone)

P = SQL stored procedure

FN = SQL scalar function

R = Rule

RF = Replication filter procedure

TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)

IF = SQL inline table-valued function

TF = SQL table-valued function

V = View

So there you have it, short n snappy blog today and I really hope that it helps give people a new insight into how to get object text.

Powershell and Dynamic SSIS (or any for that matter) Config files — December 5, 2011

Powershell and Dynamic SSIS (or any for that matter) Config files

As I imagine that the majority of people who are reading this will have some level of SSIS knowledge, I’ll not go into explanations about package configurations in SSIS and its various methods but rather jump straight to it.

The majority of environments I’ve worked in where SSIS packages are utilised, tend to sway down the XML Config package configuration method. As many of you are aware, in multi-tier SQL Environments (ie. Integration, QA, UAT etc etc) this can be a pain when deploying the packages to the relevant environments because you have to at some stage reconfigure the XML configuration file to have the correct parameters to pass into the package for each environment. This can become an even worse scenario when you have tens if not hundreds of SSIS packages (and corresponding dtsConfig’s) and are upgrading your infrastructure with new servers (and/or instance names) as well as drive configurations.

If you don’t have the time to be re-working the SSIS packages to use a SQL table (depending on your process this could take a while to get through development, testing etc) to hold the configuration parameters which makes it easy to script, deploy and update then here’s a simple trick using Powershell (i’m still at the very basic Powershell level so bare with me!!) you can use for your existing dtsConfig files. The sample i’ll be using is for a dtsConfig used in a Restore package.

Unfortunately you’re still going to have to do some initial amending of one config file here :(.

Firstly, lets amend the relevant parameter values to have a standard name for each, as an (snipit) example:

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[master – destination].Properties[ServerName]” ValueType=”String”>
<ConfiguredValue>*SQLInstance*
</Configuration>
<Configuration ConfiguredType=”Property” Path=”\Package.Variables[User::DataFilesDir].Properties[Value]” ValueType=”String”>
<ConfiguredValue>*DataFilesDir*
</Configuration>

I’ve used a * to prefix and suffix the parameter so that you don’t amend anything that may have a similar name.

By doing this, you can run a bit of Powershell to update the relevant element parameter values for each instance by using the code below, NOTE i’ve excluded the setting of the parameters etc as I use this as a module and don’t want to waste space:

#Create Copy for dtsConfigs by piping the date from the static file (with *'d parameters to the new file to be used
(Get-Content $RestoreDatabasesDynamicConfig) |
Set-Content $RestoreDatabasesDynamicConfig_New

#Amend the dtsConfig's and create new files to use
$restoreArray = ("SQLInstance", "DataFilesDir", "LogFilesDir", "SSISRestoreLocationDBs")

#Loop through the array to replace each parameter listed
foreach($Arr in $restoreArray){
	$Replace = "\*"+$Arr+"\*" #This sets the parameter with the * prefix and suffix
	$WithThis = "$"+$Arr #What parameter name passed in to replace the text with, ie. $SQLInstance, $DataFilesDir

	switch ($Arr)
	{
		# Use $ExecutionContext.InvokeCommand.ExpandString($WithThis) to evaluate the string (ie $SQLInstance)
		"DataFilesDir" {$WithThis = $SQLInstallLocation+$ExecutionContext.InvokeCommand.ExpandString($WithThis)}
		"LogFilesDir" {$WithThis = $SQLInstallLocation+$ExecutionContext.InvokeCommand.ExpandString($WithThis)}

		#I've left the above in as an example of how to set the files to a folder location passed in as a Parameter ($SQLInstallLocation)
		default {$WithThis = $ExecutionContext.InvokeCommand.ExpandString($WithThis)}
	}

	#Now create the new dtsConfig file with updated Parameter information
   (Get-Content $RestoreDatabasesDynamicConfig_New) |
	Foreach-Object {$_ -replace "$Replace", $WithThis} |
	Set-Content $RestoreDatabasesDynamicConfig_New

}

In short, this script is taking the amended dynamic file with all the parameters with their *’s prefixed and creating a new dtsConfig file. It then builds an array of parameters to work through and do the replacement, the values of these are(or indeed can be…) passed through to the function/module. I’ve put a switch in there to check for particular items in the array as in my example i wanted to append a folder location to the value passed in. you don’t necessarily have to do this but left it in to show it can be done.

Another example of using this is for a silent SQL Server 2008 install. Once you have a ConfigurationFile.ini then you can follow the same process to put a standard tag in the file and use powershell to find and replace it with a parameter value – works an absolute treat when installing many instances

I’m sure there’ll be someone far more clever than me that can somehow the file for the element and replace any value without standardising the parameter values but I’m no expert with Powershell and learning everyday and hope some others out there can get some use out of this…..and yes, I also realise that you can do a “Find and Replace in Files” with Notepad++ but this technique is great for automating!

Powershell to Script out SQL Server Objects using a CMS list — September 19, 2011

Powershell to Script out SQL Server Objects using a CMS list

I’ve recently decided I’ve got to get with the times and started dabbling with Powershell. Up until now i’ve not really seen any use (as a DBA) for it from a SQL Server perspective as I’ve been able to achieve pretty much everything in the past using TSQL, VBScript and/or SMO etc but the client stated they wanted Powershell to script out all the objects from all instances in their large estate.

I’ll not bore you too much with what its doing but in a nutshell it’ll loop a list of servers from a Central Management Server (CMS) but this can be amended to loop a textfile or indeed a text list. From this, the script will create a folder for the server, loop all databases (incl system but this can be turned off) creating a folder for each and generate a .sql file for procedures, views and functions each within their own folders within the database. You can add/remove the ability to script other objects such as tables, indexes etc but it wasn’t a requirement at the time so I’ve not included it.

I’m not going to pretend that this script is top notch but for a beginner I’m quite proud of it :).
There are a number of similar scripts on the web but none of them did quite what I was after, hopefully It’ll be of some use to some.

Will I be using Powershell again? Definitely! I know I could have knocked this up quicker using other technologies but where’s the fun in that 🙂 ????

And here it is, there are 2 scripts: One which is a function to get the objects called (funnily enough) getSQLObjects (fn_getSQLObjects.ps1) and the other is a script to get the server list and call the function (Run_getSQLObjects.ps1). These could have been combined and a parameter used to dictate whether to use text file, CMS or other method of listing the SQL Servers but I decided to keep them separate.

One other thing to note is that I used SQLPS to run this but the script could be tweaked to use a relevant connection to the CMS to get the serverlist.

fn_getSQLObjects.ps1:


##########################################################
# PowerShell Script of DB Objects
#
# Uses PS and SMO to script the drop and create a type of
# SQL Server object (views, tables, etc.) to individual 
# folders and files
#
# Chris Taylor		07/09/2011
##########################################################

Function getSQLObjects ($SQLInstance, $DB, $ObjType, $OutputFile)
{
	#List of schemas to exclude
		$ExcludeSchemas = @("sys","Information_Schema") 
	  

	#Create smo objects##########################################
		[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
	   
	#SQL Instance &amp; DB object
		$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance
		$dbs=$server.Databases
	   
	#Set ScriptingOption object option
		$ScriptingOption = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')        
		
		$ScriptingOption.SchemaQualify = 1
		$ScriptingOption.AllowSystemObjects = 0
		
	#Script Drop Objects###########################################
		$ScriptingOption.ScriptDrops = 1         
		$ScriptingOption.IncludeIfNotExists = 1
				
	#Create Directory for Database and ObjType
		$chkFolder = $OutputFile + $DB + "\"
		if (!(Test-Path -path $chkFolder))
		{
			New-Item ($chkFolder) -type directory
		}	

#write-host $chkFolder	
	#Create directory for object type within database folder
		$chkFolder += $ObjType + "\"
		if (!(Test-Path -path $chkFolder))
		{
			#write-host $chkFolder
			New-Item ($chkFolder) -type directory
		}	
	
	#Generate script for all objects##################################
		foreach ($objects in $dbs[$DB].$ObjType)
		{
			If ($ExcludeSchemas -notcontains $objects.Schema )  
			{				
				$filename = $chkFolder + $($objects.Schema) + "." + $($objects.name) + ".sql"
				$objects.Script($ScriptingOption) + "`r GO `r " | out-File $filename 
			}
		}

	#Script Create Objects#########################################
    #Append this to the file generated above with the Drop - possibly 
    #a better way to do it but works for me
		$ScriptingOption.ScriptDrops = 0         
		$ScriptingOption.IncludeIfNotExists = 0
		
		foreach ($objects in $dbs[$DB].$ObjType)
		{
			If ($ExcludeSchemas -notcontains $objects.Schema )
			{
				$filename = $chkFolder + $($objects.Schema) + "." + $($objects.name) + ".sql"
				$objects.Script($ScriptingOption) + "`r GO `r " | out-File $filename -Append
			}
		}
}		

Run_getSQLObjects.ps1

##########################################################
# PowerShell Script of DB Objects
#
# Loop CMS passing in database list from a particular 
# server
#
# Chris Taylor		07/09/2011
##########################################################


#Loop the CMS Server to list of Servers
foreach ($sqlserver in invoke-sqlcmd -query "SELECT svr.name
											 FROM   msdb.dbo.sysmanagement_shared_registered_servers_internal svr
											 INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal grp
											             ON svr.server_group_id = grp.server_group_id")
{
	$ExcludeServers = @("ALL","Servername")
	If ($ExcludeServers -notcontains $sqlserver.name )  
	{	
		write-host $sqlserver.name
		$SQLInstance = $sqlserver.name 
		$OutputFile = "C:\temp\"
	
		#Create Folder for Server, note that this will create a sub folder for any sql instances on that server
		#For example, servername\instance will have a folder for servername with a subfolder for instance
		$OutputFile += $SQLInstance + "\"
		if (!(Test-Path -path $OutputFile))
		{
			New-Item ($OutputFile) -type directory
		}	
		
		#Create smo objects##########################################
			[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
		   
			#SQL Instance &amp; DB object
			$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance
			$dbs=$server.Databases
			
		#Set what you want to script out
		$Objects = @("StoredProcedures","Views","UserDefinedFunctions")
		
		#Loop DBs
			foreach ($db in $server.Databases)
			{
				#write-host $db.name

				#Loop the object types
				foreach ($objType in $Objects)
				{
					#write-host $objType + ' ' + $db.name + ' ' + $objType.name + ' ' + $OutputFile
					getSQLObjects $SQLInstance $db.name $objType $OutputFile	
				}	
			}
	}#Don't do anything if exceptions, could use a BREAK in the loop instead
}

Database Mail – Collection of SQL Server Scripts — March 21, 2011

Database Mail – Collection of SQL Server Scripts

As part of my blogging I’ll be periodically providing readers with a collection of my scripts which I use on a daily basis (collected over the years) and hopefully others can gain from these and utilise them going forward.

Today i’m providing a list of Database Mail scripts I use when troubleshooting issues. These are predominantly SQL Server 2005 but I believe will work on SQL Server 2008 but as I don’t have a 2008 instance available to test I can’t guarantee this!

Firstly, a good place to start is to check the status of your Database Mail:

EXEC msdb.dbo.sysmail_help_status_sp

it may be possible that it isn’t sending mail because it hasn’t been started 🙂

Following on from this, if you discover that Database Mail has in fact stopped then you can start it again with:

EXEC msdb.dbo.sysmail_start_sp

NOTE: It can also be stopped by simply running

EXEC msdb.dbo.sysmail_stop_sp

If Database Mail is running then it may be that Service Broker has not been enabled:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

Another technique I use when troubleshooting Mail issues (if its running and the settings are correct) is to check if anything is actually stuck in the SQL Server Mail system, it can be apparent that mail is sat queued but unable to send. A quick and easy way to determine this is to run:

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

If the value is 0 then you can determine that there isn’t actually anything in the queue. If the value is anything other than 0 then you can investigate this further by running one or all of the below:

Test to see if your mail has actually been sent

SELECT * FROM msdb.dbo.sysmail_sentitems where sent_date &gt; dateadd(d,-1,getdate());

Check the mail eventlog for further errors

SELECT * FROM msdb.dbo.sysmail_event_log;

Check for any mail item which is sitting at the unsent status

SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'unsent';

A favourite of mine, determine the reasons why a mail item has actually failed

SELECT items.subject,
    items.last_mod_date
    ,l.description ,*
FROM msdb.dbo.sysmail_faileditems as items
INNER JOIN msdb.dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > getdate()-1

The script above tends to be the ideal script to run in order to find out why an email isn’t being sent, i tend to find 90% of my issues by running that script!

Something else worth checking is whether the user actually has permissions to be sending emails via SQL Server:

Check members of the DatabaseMailUserRole

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'

These are just a handful of queries that can be ran when troubleshooting Database Mail issues, by no means is it a complete list but from my experience i rarely have to go elsewhere. There has been times when i’ve had to venture into amending retry attempts, querying sys.dm_broker_queue_monitors and sys.service_queues but this has been very rare circumstances so I’ve not included the queries for these

If anyone has anything they feel worthwhile adding to this then feel free to email me or comment and I’ll add them if relevant.