May 1, 2010 at 10:10 pm
I'm writing a PowerShell 2.0 script that calls SQL Server 2008 sqlcmd.exe, and I am looking for a way to capture output from sqlcmd.exe to a log file. I've tried conventional redirection (">", "2>&1", etc.), "| Output-File", Start-Transcript, etc. Ideally, I would like to emulate the way you can redirect the output from osql.exe in a cmd.exe script to a text file with ">". Since we are building new hardware for our SQL 2008 environments, I would like to avoid using older cmd.exe and osql.exe technology. At this point, I don't see value in re-writing our SQL 2005 T-SQL (.sql) scripts so that they can run under SQLPS.
So far, Start-Transcript has come the closest to our needs, but it does not capture T-SQL print statements or SQL Backup progress messages that display on the PowerShell console when sqlcmd.exe executes a T-SQL script. I've tried the "-r1" switch, but it gets ignored in the following example from my script:
sqlcmd.exe -S $DatabaseConnection_STR -E -w600 -b -d dba -r1 -i $TempInputFile_NME
Any ideas?
May 1, 2010 at 10:32 pm
Why not just use the output parameter for sqlcmd itself?
sqlcmd.exe -S $DatabaseConnection_STR -E -w600 -b -d dba -r1 -i $TempInputFile_NME -o $OutputFile_Name
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 1, 2010 at 10:39 pm
May 1, 2010 at 10:59 pm
I dont Know if I understand , But If you want to capture the output from sqlcmd to txt file, you can use the -o from sqlcmd..but in powershell you can try :
$a = "sqlcmd.exe -s MyServer -d master -Q ""SELECT TOP 10 name from spt_values"""
Invoke-expression $a | Out-File c:\temp\testsqlcmd.txt
or using -o from sqlcmd
$Server = "Myserver"
$Database = "MAster"
$Query = """SELECT TOP 10 name from spt_values"""
$OutputFile = "C:\temp\testesqlcmd.txt"
$a = "sqlcmd.exe -s $Server -d $database -Q $query -o $OutputFile "
Invoke-expression $a
But I firmly believe you can use Invoke-Sqlcmd
$Query = "SELECT TOP 10 name from spt_values"
invoke-sqlcmd -ServerInstance $Server -Database $Database -Query $Query | Out-File c:\temp\testesqlcmd.txt
I dont know, I think I do not understant your problem
May 2, 2010 at 5:43 am
Jeffrey Williams-493691 (5/1/2010)
Why not just use the output parameter for sqlcmd itself?sqlcmd.exe -S $DatabaseConnection_STR -E -w600 -b -d dba -r1 -i $TempInputFile_NME -o $OutputFile_Name
In addition to performing database backups, the script does some "housekeeping" tasks related to the backups. If possible, I would like to have a single log to reference if anything fails and I need to debug what happened.
I had already thought about using -o and "cat"ing the output in my script, but that messes up the output if you run the script manually, which I would be doing if I needed to debug it at some point in the future.
May 2, 2010 at 6:07 am
Laerte Poltronieri Junior-367636 (5/1/2010)
Hi,I really do not understand your question, can you explain better ?
Here is the long version.
- All of our SQL Server backups go to disk and subsequently get picked up to tape by NetBackup
- Our tape backup team has a long (multi-year) history of backing up open files and calling the backups "good," despite a multitude of re-education attempts by the DBAs to help them understand that a NetBackup backup of "in use" .mdf, .ndf, and .ldf files will be corrupt when anyone tries to restore that backup; in like fashion, a NetBackup of an "in use" SQL Server database backup files (.bak, .trn) will likely be corrupt as well
- In an attempt to stop the NetBackup insanity, we are implementing a directory rotation scheme that writes the "live" SQL backups to "DirA"; when finished, the script renames DirA to DirA.01, etc.; even if NetBackup backs up DirA while it is open, DirA should be renamed to DirA.01 (and be "closed") by the time NetBackup comes along the next night; so, at that point, we should have a good copy of what used to be in DirA on tape; each evening, DirA.01 gets renamed to DirA.02 and so on--eventually, we should have several nights of "closed" backups on tape, in case we need them
A stripped down version of my script does something like this:
mkdir DirA
sqlcmd.exe -S ... -E -Q "backup database [dba] to disk = N'D:\DirA\dba_2010-05-02_002436.bak'" ...
rmdir DirA.03 -recurse
mv DirA.02 DirA.01
mv DirA.01 DirA.02
mv DirA DirA.01
mkdir DirA
This script will eventually be scheduled with Task Scheduler, and I would like to be able to capture all of the activity (PowerShell and sqlcmd.exe) in a single log file so that I can use the log file as a road map to debug whatever went wrong in a prior run.
May 2, 2010 at 1:08 pm
I did not think much on how to join the output of sqlcmd with powershell, but there is a possible solution made in powershell for everything (not test with the script you send its ok, just rewrite in powershell)
$Server = $env:computername
$Database = "master"
$LogFile = "c:\temp\testsqlcmd.txt"
$error.Clear()
try
{
New-Item -ItemType directory -Path c:\dirA -Force -ErrorAction Stop
"New Path c:\DirA created with success" | Out-File $LogFile -Append
$Query = "BACKUP DATABASE [master] TO DISK = N'C:\temp\master.bak' WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
invoke-sqlcmd -ServerInstance $Server -Database $Database -Query $query -ErrorAction Stop
"Database Master backuped with success " | Out-File $LogFile -Append
Remove-Item -Path c:\dirA -ErrorAction Stop
"Path c:\dirA removed with success" | Out-File $LogFile -Append
Move-Item -Path c:\dirA.02 -Destination c:\dirA.01 -ErrorAction Stop
"c:\dirA.02 moved to c:\dirA.01 with success " | Out-File $LogFile -Append
Move-Item -Path c:\dirA.01 -Destination c:\dirA.02 -ErrorAction Stop
"c:\dirA.01 moved to c:\dirA.02 with success " | Out-File $LogFile -Append
Move-Item -Path c:\dirA -Destination c:\dirA.01 -ErrorAction Stop
"c:\dirA moved to c:\dirA.01 with success " | Out-File $LogFile -Append
New-Item -ItemType directory -Path c:\dirA -Force -ErrorAction Stop
"New Path c:\DirA created with success" | Out-File $LogFile -Append
} catch {
$Error | Out-File $LogFile -Append
}
The -erroraction in each cmdlet to stop send script execution to catch block and log the error.
Hope can help 🙂
May 2, 2010 at 1:27 pm
Laerte Poltronieri Junior-367636 (5/2/2010)
I did not think much on how to join the output of sqlcmd with powershell, but there is a possible solution made in powershell for everything (not test with the script you send its ok, just rewrite in powershell)...
Hmmm.... This looks very interesting. I plan to check it out later today. Thanks for the code!
May 2, 2010 at 1:57 pm
Laerte Poltronieri Junior-367636 (5/2/2010)
I did not think much on how to join the output of sqlcmd with powershell, but there is a possible solution made in powershell for everything (not test with the script you send its ok, just rewrite in powershell)...
I think this is going to work. Thanks for your help! Here is the syntax I am using for a quick and dirty test:
start-transcript temp.txt
write-host "test"
$Server = "server_name\instance_name"
$Database = "db_name"
$LogFile = "c:\temp\testsqlcmd.txt"
$error.Clear()
$Query = "select * from junk"
invoke-sqlcmd -ServerInstance $Server -Database $Database -Query $query -ErrorAction Stop
stop-transcript
In this example, temp.txt contains the output from "write-host" and the output from invoke=sqlcmd:
cat temp.txt
**********************
Windows PowerShell Transcript Start
Start time: 20100502155439
Username : MOUNTAIN\DBAJPS1
Machine : MOUNTAIN (Microsoft Windows NT 6.0.6002 Service Pack 2)
**********************
Transcript started, output file is temp.txt
test
cola
----
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
**********************
Windows PowerShell Transcript End
End time: 20100502155439
**********************
May 2, 2010 at 2:10 pm
February 8, 2011 at 8:06 pm
I resolved a similar issue while building an infrastructure script to process T-SQL batch files. I encountered many issues trying to programmatically capture the error message from sqlcmd.exe. Attempted to use the invoke-sqlcmd cmdlet in SQLPS because the error code is easy to capture programmatically. I abandoned invoke-sqlcmd, however, due to the limitations with capturing results and messages from the T-SQL queries.
My Requirements
=============
Requirement #1 - Trap the error returned from the sqlcmd call in Powershell.
Requirement #2 - Write any error messages to a designated log file.
Requirement #3 - Save all results and processing (PRINT) messages returned from each T-SQL command executed through sqlcmd.
My Solution
=============
This implementation wraps the sqlcmd call in the INVOKE-COMMAND cmdlet. The following Powershell command invokes the sqlcmd.exe utility and executes two T-SQL statements. The 1st succeeds and the 2nd fails during the same session. The whole operation fails but only after query #1 returns data - and I want to capture this data!
Requirement #1 is addressed by setting the -r0 and -b sqlcmd parameters. They redirect error messages to the standard output stream and set the ERRORLEVEL variable, respectively. Parameter -r0 causes Powershell to save the error message in the $Error[0].Exception object. Parameter -b sets the $LASTEXITCODE environment variable.
Requirement #2 is addressed using the 2> redirection operator from Windows. The redirection operator is located inside of the -scriptblock parameter as part of the sqlcmd. This effectively pushes the error stream to the ScriptError.txt file.
Requirement #3 is addressed by piping all standard output the ScriptOutput.txt using the out-file cmdlet. The -e sqlcmd parameter echoes the T-SQL statement to the standard output stream
======================================
Powershell Command
======================================
INVOKE-COMMAND -scriptblock {sqlcmd -S .\SQL2008R2 -E -Q 'SELECT [name] FROM master.sys.databases;SELECT TOP 10 * FROM sys.columnses;' -r0 -b -e 2> "C:\ScriptError.txt"} | out-file "C:\ScriptOutput.txt"
======================================
Powershell Results
======================================
$LASTEXITCODE = 1
$ERROR[0].Exception.Message = Invalid object name 'sys.columnses'.
===================
ScriptError.txt
===================
SQLCMD.EXE : Msg 208, Level 16, State 1, Server CONNEMARA\SQL2008R2, Line 1
At line:1 char:36
+ INVOKE-COMMAND -scriptblock {sqlcmd <<<< -S .\SQL2008R2 -E -Q 'SELECT [name] FROM master.sys.databases;SELECT TOP 10 * FROM sys.columnses;' -r0 -b -e 2> "C:\Users\Maste
rChief\Documents\vbb5spc.err"} | out-file "C:\Users\MasterChief\Documents\vbb5spc.txt"
+ CategoryInfo : NotSpecified: (Msg 208, Level ...L2008R2, Line 1:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Invalid object name 'sys.columnses'.
===================
ScriptOutput.txt
===================
SELECT [name] FROM master.sys.databases;SELECT TOP 10 * FROM sys.columnses;
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
ReportServer$SQL2008R2
ReportServer$SQL2008R2TempDB
SchemaTest
(7 rows affected)
I hope this helps!
June 25, 2012 at 12:57 pm
Steve, that is fantastic! Thank you very much. I wasted hours looking for ways to run a folder full of SQL scripts with GO commands in them via PowerShell. Invoke-sqlcmd makes detecting errors a serious pain, and the POSH community written version of Invoke-sqlcmd2 does not parse the GO keyword as a batch terminator, and returns "Incorrect syntax near 'GO'" errors.
I was looking at writing code to read each SQL script line by line, and execute the buffer of commands each time it reached a GO, then skip to the next line when I stumbled across this post, and your fabulous answer.
I hope I've embedded enough keywords in this post to make it easy for others attempting the same kind of thing to find!
I ended up omitting the nifty 2> error output redirect (and the -r0 parameter), since it created a file even when there were no errors. Instead, I just piped the $Error[0].Exception.Message to Out-File to write the SQL error to an error file, and I placed that inside an if($LastExitCode -eq 1) {}, so I only see error files when there are errors.
Works a charm!
--David
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply