header1   header
header
header Register : : Login header
header
connector   connector
menuleft menuright
submenu   submenu
left
How to output a log file to a remote server via Out-File -filepath
Last Post 05 Jan 2010 06:00 PM by BikeBoy. 3 Replies.
Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
BikeBoyUser is Offline
New Member
New Member
Posts:25
Avatar

--
04 Jan 2010 06:51 AM
    I am using Invoke-Sqlcmd from SQLPS to execute a stored proc, and want to output a log to a path on the remote server that I get earlier in my script.

    So first I get the path by querying a table, and capture it in a $Path variable:
    $Path=Invoke-Sqlcmd -Query "select value from MyTable where name = 'Path';" -Database MyDB -ServerInstance "$Servername"


    I then try to execute my stored proc, and put the output to the path I got earlier:
    Invoke-Sqlcmd -Query "EXEC MyStoredProc;" -QueryTimeout 65534 -Database MyDB -ServerInstance "$Servername" | Out-File -filepath "\\" + $Servername + $Path + "LogOutput.txt"

    But I get an error:
    Out-File : Cannot validate argument "+" because it does not belong to the set "unicode, utf7, utf8, utf32, ascii, bigendianunicode, default, oem".

    So it doesn't look like I can do that, but I need to. Can someone help?
    SureshUser is Offline
    New Member
    New Member
    Posts:31
    Avatar

    --
    05 Jan 2010 10:08 AM
    Simple!

    Invoke-Sqlcmd -Query "EXEC MyStoredProc;" -QueryTimeout 65534 -Database MyDB -ServerInstance "$Servername" | Out-File -filepath $("\\" + $Servername + $Path + "LogOutput.txt")

    isn't it?
    SureshUser is Offline
    New Member
    New Member
    Posts:31
    Avatar

    --
    05 Jan 2010 10:09 AM
    You're missing "\" also here. Please take care of them.
    BikeBoyUser is Offline
    New Member
    New Member
    Posts:25
    Avatar

    --
    05 Jan 2010 06:00 PM
    Nope, I tried that. I build my path based on cancatination of instance name and previous variable $Path1 that I get initially in the same script, like so:

    [string]$MyPath="\\" + $Servername + "\" + $Path1.value.Replace(":\","$\") + "DBBackup.txt"

    But nothing gets logged. If I execute this stored procedure from a job I get a nice output file that looks like
    Processed 440 pages for database 'MyDB'...etc

    I load sql assembly like and create an object this
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $SQLserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Servername

    # Create Databases object
    $dbs = $SQLserver.Databases

    #Here I execute my stored proc

    #then I want to iterate through dbs and log backup status for each database:

    foreach ($db in $dbs) { Write-Debug "Backing up $db.Name on $Servername "
     if ($?) {"Successfully backed up " + $db.Name + " to " + $Path.value | Out-File -filepath $logFile -encoding oem}
    else {"Failed to back up " + $db.Name + " to " + $serverBackup.File | Out-File -filepath $logFile -encoding oem} }

    But the only this that gets logged is
    Successfully backed up tempdb to C:\MyPathHere
    You are not authorized to post a reply.


    Active Forums 4.3
    right
    footer   footer
    footer Sponsored by Quest Software • SAPIEN Technologies • Compellent • Microsoft Windows Server 2008 R2 footer
    footer   footer