How to output file to remote server via Out-File -filepath

[MOMBoy]

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".

[MOMBoy]

Now I get a new error. I build my path based on cancatination of instance
name and previous variable $Path1 that I get initially in the same script,
like so:

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

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

I wonder if I need to load sql assemblies like this:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

and then connect to sql instance like this:
[Microsoft.SqlServer.SMO.Server]$SQLserver = New-Object
([Microsoft.SqlServer.SMO.Server])
$SQLserver.Connect($Servername)

off course I get an error here too : [quote] Unable to find type
[Microsoft.SqlServer.SMO.Server]: make sure that the assembly containing this
type is loaded. [/quote]
I thought the above code was valid :(

[Bob Landau]

MOMBoy,

I'm not smart enough to know if this is truely a bug or inconvenince. If you
feel this to be a bug go up to the connect site and create a bug against this.

There is an easy workaround simply wrap the expression within parenthesis

('\\' + $ServerName + '\' + $Path + '\' + 'LogOutput.txt')

Another method would be to assign the above expression to a string

$path = '\\' + $ServerName + '\' + $Path + '\' + 'LogOutput.txt'

The difference is without paren's the parser is working in "command mode"
which is expected when calling a cmdlet, etc. Command mode everything but
numbers are treated as a string type. So what is being passed to Out-File
below are 6 parameters.

Perhaps the parser should be smarter in this case since what is being
invoked is a cmdlet and must follow Powershell's rules for passing
parameters/arguments. In general though this would be hard to implement since
native commands have no defined method of parsing parameters.

The parens force the parser into expression mode for the duration of that
string of characters. In expression mode strings concatenation works as
expected so what will be passed in is 1 parameter.

You can see this easily using this simple function

## Echo-Args.ps1
"The number of parameters passed in is $($Args.Count)"
$i = 0
foreach ($arg in $Args) { echo "The $i parameter is $arg"; $i++ }
## End of script

[MOMBoy]

Bob,
My problem is that the output to the log file. Log file gets created, but
the output I expect from the stored proc is not there. The databases get
backed up, but the logging isn't working.

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 wonder if I need to load sql assemblies like this:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

and then connect to sql instance like this:
[Microsoft.SqlServer.SMO.Server]$SQLserver = New-Object
([Microsoft.SqlServer.SMO.Server])
$SQLserver.Connect($Servername)

I wish I could attach the script and show where the problem is.

[Bob Landau]

I wish I could help more but I don't have time to download SQL Server and all
it would take to get to where you currently are.

Having said this if I understand you the file will get created locally
correct? If you've not tried this you need to.

Now try this to see if you can write to the remote share manually

echo 'hello world' | Out-File -FilePath $MyPath

If this can't work then either $MyPath is malformed or you have a
permission's issue.

If this does work you would be better served asking on a SQL DL

Lastly according to the helpfile

http://msdn.microsoft.com/en-us/library/cc281962.aspx

you should not to need to explicilty load SMO its done for you.