May 20, 2011 at 10:36 pm
I have a job that scripts out database objects such as tables, indices and schemas using the scripter object:
$scrp.Options.FileName = "$homedir\Tables\$tblname.sql"
$scrp.Script($tbl)
When I run it as a Powershell job through the job agent, it does not write the scripts to the files but using the script method works fine.
$tbl.Script() + "`r GO `r " | out-File "$homedir\Tables\$tblname.sql" -Append
I would like to use the script object because it does exactly what I want but I need to run it through the job agent. Is there any way to make it work? Thanks for any ideas -
Judy
May 23, 2011 at 8:05 am
I'm adding to my own post here by saying that I think this may be a connectivity issue in disguise. It's not that it doesn't product output - it's that it has not actually connected to the remote servers. I accidentally reproduced the problem by commenting out some lines of my script for testing. The lines related to the connectivity portion of the script. So now I will have to see if that can somehow be happening when the job is run through the SQL Agent. I'm leaving the question open since this is just a conjecture at this point.
May 23, 2011 at 12:16 pm
How are you connecting to the remote server? Windows Authentication? If yes, make sure the Windows login running the SQL Agent job (Windows service account or proxy account) can connect to the remote server and has permission to do the things you want to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 12:17 pm
A try/catch structure around the code you think may be having an issue would be a wise move to capture and output the error message.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 1:20 pm
I will do that. I thought I was covered because I thought what I had asked it to do was not to continue the script if it couldn't connect but it is processing the local server instead. I'm now checking for a null instance name but not sure yet if I've covered all my bases.
I have to rerun the job in quasi-production mode now so I won't be able to test this out for a day or so. It's scripting most aspects of 61 servers and takes over a day to run. A side effect of the null instance was wiping out the saved scripts for all the servers and I have to get them back before I proceed.
May 24, 2011 at 8:55 pm
Well, I am back to where I started. When I change these lines and only these lines -
$scrp.Options.FileName = "$homedir\Tables\$tblname.sql"
$scrp.Script($tbl)
to this -
$tbl.Script() + "`r GO `r " | out-File "$homedir\Tables\$tblname.sql" -Append
my job runs through the sql agent and produces output. It runs perfectly either way through Powershell ISE. I don't think it is permissions because when run through the agent, files are created but the scrp.Script($tbl) does not put anything in the file. I know it connects to the server because it finds the databases and creates directories for them Any ideas would be appreciated.
May 24, 2011 at 9:59 pm
Did you add the try/catch? I am only making out bits and pieces so far...if you post your entire script I am happy to take a look at it to see if I can help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 10:04 pm
Tomorrow I will prepare a mini-script I can send you. I'll pare it down to the essentials. Thanks for helping -
Judy
May 25, 2011 at 10:02 am
Here is the script. It is still pretty complex. It was based on a script I found here:
http://www.simple-talk.com/sql/learn-sql-server/change-management-and-source-control/
My beginning adaptation of that script DOES work correctly so I know I have managed to break it in this iteration.
I am running my script through SQL Agent with this command:
powershell.exe -NoLogo -Command ' F:\powershell\scripts\test\DBscriptingtest4.ps1'
I don't really know how to use the try/catch loop - I'm still a Powershell learner. I suppose I would like to trap what happens with this statement - {$scrp.Script($sp)} - since it is the thing that doesn't work although I suspect a connectivity issue is causing the problem. The script can be made to work correctly - i.e. - produce scripts - by following the comments to initiate Plan B. Here is the script
***********************************************
#DBscriptingtest4.ps1
# Set Variables
$ServerList = get-content "F:\Powershell\Scripts\Production\serversshort.txt"
$ScriptPath = "F:\Powershell\Scripts\Production"
$ResultsDir ="F:\Powershell\Results"
$Exclusions = @("LiteSpeedLocal","tempdb","pubs","Northwind") #databases to exclude Example: @("LiteSpeedLocal" ,"model")
$DateSuffix = "{0:MMddHHMMss}" -f (Get-Date)
# Remove the old log file
Remove-Item $Resultsdir\log.txt | out-null
New-Item $Resultsdir\log.txt -type file | out-null
# The restart list can be used to restart an incomplete run from the place it left off.
# It is updated after every server completes.
if ($list -ne "Restartlist.txt")
{Copy-Item "$ScriptPath\$list" "$ScriptPath\Restartlist.txt" -force}
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
# $v checks server name to check for the version number so the right assemblies are loaded.
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}
# Instantiate the Scripter object and set the base properties
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.ScriptDrops = $False
$scrp.Options.WithDependencies = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.AppendToFile = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True
$scrp.Options.permissions = $True
$scrp.Options.IncludeDatabaseRoleMemberships = $TRUE
# Main Processing Loop $instance = ''
foreach ($Instance in $ServerList) { # Main Processing Loop
if (!$Instance)
{"Instance is null. Serverlist may be unavailable,empty or contain
blank lines. Job can be restarted from the output file if it has
processed some servers." |out-file -append $resultsdir\log.txt;exit} }
"***** Starting " + $instance + ' ' + (get-date) + " *****"|out-file -append "$Resultsdir\log.txt"
#check that SQL Server is available
"start availability loop"
$SqlCatalog = “master”
# $SqlConnection.Close()
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $instance; Database = $SqlCatalog; Integrated Security = True”
trap {" Cannot connect to $instance" |out-file -append $resultsdir\log.txt} $sqlConnection.Open()
if($SqlConnection.state -eq “open”) { # Begin server Loop
"start server loop"
$InstDir = $Instance.replace('\','_')
# wipe out database directories. Must do this so old deleted databases or other objects won't linger on
if ($Instdir) {
get-childitem "$ResultsDir\$InstDir" | where{$_.PSIsContainer} |remove-item -recurse -ErrorAction "SilentlyContinue" #-whatif
if (!(Test-Path "$ResultsDir\$InstDir"))
{ New-Item "$ResultsDir\$InstDir\" -Type directory}
# Connect to the specified instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$s.name }
foreach ($db in $s.databases) { # Begin Database Loop
"Start database loop"
if ($exclusions -contains $db.name)
{"$db.name appears in the exclusions list"}
Else { # Process Databases
"Process Database Loop"
$dbname = $db.name
"`t$db"
$homedir = "$ResultsDir\$InstDir\$dbname"
if (!(Test-Path -path "$homedir\"))
{
New-Item "$homedir" -type directory | out-null
}
New-Item $Homedir\exceptions.txt -type file | out-null
# Script the StoredProcedures in the database
$db.StoredProcedures | foreach-object { # Start Stored Procedures Loop
Trap {
$err = $_.Exception
while ( $err.InnerException )
{
$err = $err.InnerException
'Database: ' + $db.name + ' Error: ' + $err.Message + ' ' + (get-date) |out-file -append "$homedir\exceptions.txt"
};
continue
}
$sp = $_
if (!(Test-Path -path "$homedir\StoredProcedures\"))
{
New-Item "$homedir\StoredProcedures\" -type directory | out-null
}
if($_.IsSystemObject -eq $False) { # Start System Object Loop
$spname = $sp.Name
$scrp.Options.FileName = "$homedir\StoredProcedures\$spname.sql"
if ($sp.isencrypted)
{"# $sp is encrypted. It cannot be reverse engineered."|out-file -append "$homedir\StoredProcedures\$spname.sql"}
Else
{$scrp.Script($sp)}
# For Plan B, comment out "$spname = $sp.Name" and "$scrp.Script($sp)}"
# Then uncomment out the line below
#{$sp.Script() + "`r GO `r " | out-File "$homedir\StoredProcedures\$spname.sql" -Append}
} # End System Object Loop
} # End Stored Procedures Loop
"End database loop"
}# End Database Loop
"End Process Database loop"
} # End Process Database Loop
" End server Loop"
} # End Server Loop
# After every server completes, remove its name from the restart list.
# The restart list can be used to restart an incomplete run from the place it left off.
$instance + " End Server Loop"
$RemainingServers =Get-Content "$ScriptPath\Restartlist.txt" | where { $_ -notlike $instance}
set-content "$ScriptPath\Restartlist.txt" $RemainingServers
" End Main Processing Loop"
#} # End Main Processing Loop
# Remove the restart file if we got to the end and didn't need it
Remove-Item "$ScriptPath\Restartlist.txt" | out-null
May 25, 2011 at 4:46 pm
I found a permissions issue and thought the problem was resolved but I don't think it is. Still checking ...
May 26, 2011 at 12:33 am
Judy Scheinuk (5/25/2011)
I don't really know how to use the try/catch loop
try/catch is not a form of a loop...it's an exception handling structure. I prefer it over using traps because I think it's a little more precise and readable.
Here is a really good explanation of the two: http://blogs.msdn.com/b/powershell/archive/2009/06/17/traps-vs-try-catch.aspx
And here is a really good example of how to use each one: http://powershell.com/cs/blogs/tips/archive/2009/09/14/trap-and-try-catch.aspx
I'm still a Powershell learner.
Me too 🙂
I suppose I would like to trap what happens with this statement - {$scrp.Script($sp)}
Good call. I did that with this code:
try
{
$scrp.Script($sp)
}
catch
{
echo "$_"
}
and I got this:
Exception calling "Script" with "1" argument(s): "To accomplish this action, set property Server."
Your original script worked in ISE but failed from the command line just like you said. I thought I was going crazy for a minute but I eventually was led me to the conclusion that something different was happening when invoking the script into a new/clean shell as opposed to in ISE after a script was run repeatedly in different forms. The re-used shell within ISE may have contained residue from a previous script execution that propped up the script to make it look like it was OK even though it would not run in a clean shell...never really nailed that down but it's not important now. It appeared that the SMO Scripter object was never initially in the context of any particular Server...took me a while to figure that out.
So, I re-arranged the loop and am now instantiating an SMO Server and SMO Scripter object within the instance-loop and it now works for me from the command line. Here is the product of that effort...sorry I gutted some areas until I found the problem but it can easily be built back up with the console messages and RestartList stuff you had.[/quote]
# Set Variables
$ScriptPath = "C:\@\serversshort"
$ServerList = get-content "$ScriptPath\serversshort.txt"
$ResultsDir ="$ScriptPath\Results"
$Exclusions = @("LiteSpeedLocal","tempdb","pubs","Northwind") #databases to exclude Example: @("LiteSpeedLocal" ,"model")
$DateSuffix = "{0:MMddHHMMss}" -f (Get-Date)
# Remove the old log file
New-Item -Path "$Resultsdir\log.txt" -type file -Force | out-null
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
# $v checks server name to check for the version number so the right assemblies are loaded.
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9')
{
"Loading stuff..."
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}
# Main Processing Loop $instance = ''
foreach ($Instance in $ServerList)
{
if (!$Instance)
{
"Instance is null. Serverlist may be unavailable,empty or contain blank lines. Job can be restarted from the output file if it has processed some servers." |out-file -append $resultsdir\log.txt
exit
}
"***** Starting " + $instance + ' ' + (get-date) + " *****" | out-file -append "$Resultsdir\log.txt"
$SqlCatalog = “master”
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $instance; Database = $SqlCatalog; Integrated Security = True”
try
{
$sqlConnection.Open()
}
catch
{
" Cannot connect to $instance" |out-file -append $resultsdir\log.txt
}
if($SqlConnection.state -eq “open”)
{
# Instantiate a new Server object to pass into the Scripter object
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($instance)
# Instantiate the Scripter object and set the base properties
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
$scrp.Options.ScriptDrops = $False
$scrp.Options.WithDependencies = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.AppendToFile = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True
$scrp.Options.permissions = $True
$scrp.Options.IncludeDatabaseRoleMemberships = $TRUE
# Begin server Loop
#"start server loop"
$InstDir = $Instance.replace('\','_').replace('.','_')
# wipe out database directories. Must do this so old deleted databases or other objects won't linger on
if ($Instdir)
{
if (Test-Path "$ResultsDir\$InstDir")
{
Get-ChildItem "$ResultsDir\$InstDir" | where{$_.PSIsContainer} |remove-item -recurse -ErrorAction "SilentlyContinue" #-whatif
}
else
{
New-Item "$ResultsDir\$InstDir\" -Type directory
}
# Connect to the specified instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$s.name
}
foreach ($db in $s.databases)
{
# Begin Database Loop
#"Start database loop"
if ($Exclusions -contains $db.Name)
{
"$db.name appears in the exclusions list"
}
Else
{
# Process Databases
#"Process Database Loop"
$dbname = $db.name
"`t$dbname"
$homedir = "$ResultsDir\$InstDir\$dbname"
"`t$homedir"
if (!(Test-Path -path "$homedir\"))
{
New-Item "$homedir" -type directory | out-null
}
New-Item $Homedir\exceptions.txt -type file | out-null
# Script the StoredProcedures in the database
foreach ($sp in $db.StoredProcedures)
{
# Start Stored Procedures Loop
# _suggestion_ convert this to a try/catch
Trap
{
$err = $_.Exception
'Database: ' + $dbname + ' Error: ' + $err.Message + ' ' + (get-date) |out-file -append "$homedir\exceptions.txt"
while ( $err.InnerException )
{
'Database: ' + $dbname + ' Error: ' + $err.Message + ' ' + (get-date) |out-file -append "$homedir\exceptions.txt"
$err = $err.InnerException
}
continue
}
if (!(Test-Path -path "$homedir\StoredProcedures\"))
{
New-Item "$homedir\StoredProcedures\" -type directory | out-null
}
if($sp.IsSystemObject -eq $False)
{
# Start System Object Loop
$spname = $sp.Name
$spfilename = "$homedir\StoredProcedures\$spname.sql"
if ($sp.IsEncrypted)
{
"# $sp is encrypted. It cannot be reverse engineered."|out-file -append $spfilename
}
Else
{
"`t`t$spfilename"
try
{
$scrp.Options.FileName = $spfilename
$scrp.Script($sp)
}
catch
{
echo "$_"
#throw $_
}
}
}
}
}
}
}
}
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 10:37 am
I can't wait to try this. I have to wait until the job which is running now gets to a good stopping place which may be a while. This makes total sense to me as does the simple try/catch example. It had crossed my mind that I might be benefiting from some leftover defaults from ISE and had tested the script by starting a new ISE session. Then I thought the login wasn't really getting to something required by SMO. I agree with you - at this point, I don't care what the problem is and will be happy to fix it by changing where I pick up the scripter object.
Thank you so much for persevering on this problem. I hated to throw away all my work because I couldn't get it to run as a scheduled job!
May 26, 2011 at 11:00 am
Judy Scheinuk (5/26/2011)
I can't wait to try this. I have to wait until the job which is running now gets to a good stopping place which may be a while. This makes total sense to me as does the simple try/catch example. It had crossed my mind that I might be benefiting from some leftover defaults from ISE and had tested the script by starting a new ISE session. Then I thought the login wasn't really getting to something required by SMO. I agree with you - at this point, I don't care what the problem is and will be happy to fix it by changing where I pick up the scripter object.Thank you so much for persevering on this problem. I hated to throw away all my work because I couldn't get it to run as a scheduled job!
My pleasure...I learned a ton from working on it too! I hope it helps moves your project forward.
As an aside there is a dedicated PowerShell forum on SSC which is a great place to pickup tips and see what other people are implementing: http://qa.sqlservercentral.com/Forums/Forum1351-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 5:27 pm
I have made the changes you suggested and it works like a charm. I will change my traps to try/catch and clean up my formatting per your good example. It still had the training wheels on it so I could keep track of where it was.
For the benefit of people who have forgotten why I cared about this - it was because with the script object, you can simply specify "TRUE" to options you want to script out such as indexes and triggers. You have access to properties like "isSystemObject" so you don't have to write a bunch of SQL to exclude them from your collection.
I will close this issue if I can figure out how to do so. Thanks again!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply