Problems with writeCustomizedMsg.

[ammanviii]

I wrote a PS script that queries a database on the server that has webCommander on it and I found that to display the results of your script after running it on webcommander is to use writeStdout or writeCustomizedMsg and neither display the value. It just outputs 0. What should I do to display the right output?

`$numRow = $sqlOutput.Count;

writeCustomizedMsg ("The table contains: "+$numRow +" rows")`

[9whirls]

writeCustomizedMsg "The table contains $($sqloutput.count) rows"

[ammanviii]

Thank you for responding. I tried what you implemented and it shows this when I run in PS ISE
PS SQLSERVER:\SQL\localhost\DEFAULT\Databases\UCS_DB> C:\WebCommander\powershell\Query.ps1
The table contains 20 rows

but it displays this when I run on webCommander's UI:
[2018-07-18 20:24:05] The table contains 0 rows

[9whirls]

Would you please share the entire script?

[ammanviii]

$table is any table inside the database that I was switching out and filter it using a WHERE clause

`Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\UCS_DB

$UserSqlQuery = @"
SELECT *
FROM $table
WHERE UCS = 'Location'
"@

$sqlOutput = Invoke-Sqlcmd -Query $UserSqlQuery -ServerInstance "localhost" -SuppressProviderContextWarning

$psfile = $PSScriptRoot + ".\objects.ps1"
.$psfile

$numRow = $sqlOutput.Count;

writeCustomizedMsg "The table contains $($sqloutput.count) rows"`

[9whirls]

Please output $sqloutput at the end of the script and run it from Powershell console directly. Then post the whole script output here.

[ammanviii]

It's actually very long so I posted a snippet of it, it's just repetitive days.
`The table contains 5875 rows

RowID : 84355
DisplayHint : DateTime
DateTime : Monday, January 01, 2018 12:17:52 AM
Date : 1/1/2018 12:00:00 AM
Day : 1
DayOfWeek : Monday
DayOfYear : 1
Hour :
Kind : Local
Millisecond : 15
Minute : 17
Month : 1
Second : 52
Ticks : 636503626720157500
TimeOfDay : 00:17:52.0157500
Year : 2018

RowID : 84356
DisplayHint : DateTime
DateTime : Monday, January 01, 2018 12:47:50 AM
Date : 1/1/2018 12:00:00 AM
Day : 1
DayOfWeek : Monday
DayOfYear : 1
Hour :
Kind : Local
Millisecond : 781
Minute : 47
Month : 1
Second : 50
Ticks : 636503644707813750
TimeOfDay : 00:47:50.7813750
Year : 2018

RowID : 84357
DisplayHint : DateTime
DateTime : Monday, January 01, 2018 1:18:27 AM
Date : 1/1/2018 12:00:00 AM
Day : 1
DayOfWeek : Monday
DayOfYear : 1
Hour : 1
Kind : Local
Millisecond : 328
Minute : 18
Month : 1
Second : 27
Ticks : 636503663073282500
TimeOfDay : 01:18:27.3282500
Year : 2018
`

[9whirls]

Please run this script
https://gist.github.com/9whirls/fc0e51a8f468470d024ef146cc66de13

When webcommander returns, right click on the page and select "view page source".
Please post the content here.

[ammanviii]

This is what I got when I viewed the page source @9whirls
`

	Set-Location : Cannot find drive. A drive with the name 'SQLSERVER' does not
	exist.
	At C:\WebCommander\powershell\USCQueryDVPod1Trial.ps1:1 char:1
	+ Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\UCS_DB
	+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	+ CategoryInfo : ObjectNotFound: (SQLSERVER:String) [Set-Location
	], DriveNotFoundException
	+ FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetL
	ocationCommand
	Invoke-Sqlcmd : Invalid object name 'IOM'.
	At C:\WebCommander\powershell\USCQueryDVPod1Trial.ps1:9 char:14
	+ ... sqlOutput = Invoke-Sqlcmd -Query $UserSqlQuery -ServerInstance "local ...
	+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
	hellSqlExecutionException
	+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
	ll.GetScriptCommand
	0
	Cannot index into a null array.
	At C:\WebCommander\powershell\USCQueryDVPod1Trial.ps1:15 char:1
	+ write-host $sqlOutput[0]
	+ ~~~~~~~~~~~~~~~~~~~~~~~~
	+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
	+ FullyQualifiedErrorId : NullArray
	[2018-07-19 18:19:26] The table contains 0 rows
	4488
	5.8 seconds
	ammanuel.getenet
	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36
	2018-07-19 18:19:26

`

[9whirls]

The first command failed. There is no sqlserver provider available at all.

[ammanviii]

Hey @9whirls. The first command was failing and I was able to solve it by using this command at the top of the PS script.
Import-Module SQLPS
Thank you very much for you help.

I had two more questions I wanted to ask, can I do in this thread or do I have to create another issue?

[9whirls]

Let’s keep the issue focused on a specific topic

[ammanviii]

Ok got it! I'll post my other questions on a new one