How to run a SQL on SQL Server DB with database name as parameter?

Hi,

I want to run a custom SQL (to monitor some SQL server mirroring stats) on SQL Server Database [master].

I'll have list of database names and want to pass one list item at a time to the SQL as a parameter.

This value will go in one of the where clause as condition (WHERE column_name = 'PARAMETER FROM THE LIST').

How can I do this? I don't know if I can do this using "SQL Server User Experience monitor".

So, I think I'll have to use "Windows PowerShell monitor" or "Windows Script monitor". As the node is Windows 2012 server I can't use "Linux/Unix Script Monitor".

Please suggest me a way that I can try out.

Find more posts tagged with

sam components

sql server dba

Accepted answers

jeilers

After getting the query to work in powershell I created a Template with the Windows Powershell Monitor. I was still getting statistic NaN. So I turned on the debug log and

2018-05-08 10:02:24,024 [STP Pool:608 Thread #0] [C15997] ERROR SolarWinds.APM.Probes.PowerShellProbe - SolarWinds.APM.Probes.Script.ScriptNoStatisticExeption: Scripting Error: Script does not contain the expected parameters or is improperly formatted. 'Statistic' missing.

at SolarWinds.APM.Probes.Script.ScriptResult.ValidateStatistics(List`1 definitions)

at SolarWinds.APM.Probes.Script.ScriptResult.get_Columns()

at SolarWinds.APM.Probes.ProbeResults.DynamicMonitorResult.SetResult(DynamicEvidenceProbeSettingsBase settings, IScriptResult scriptResult)

at SolarWinds.APM.Probes.PowerShellProbe.ProbeInternal(ProbeInformation probeInfo, DynamicMonitorResult result, Func`1 powershellScriptHelperFactory)

2018-05-08 10:02:24,024 [STP Pool:608 Thread #0] [C15997] DEBUG SolarWinds.APM.Probes.PowerShellProbe -

Begin PowerShell Execute Result: ================================

Target: 192.168.69.39

Script: ====================================================

[string] $Server= "DB-Hostname"

[string] $Database = "DatabaseName"

[string] $UserSqlQuery= $("select count(*) FROM AlertHistoryView ahv WHERE EventTypeWord = 'Triggered' AND DATEDIFF(DAY,TimeStamp, GETDATE()) < 30")

# executes a query and populates the $datatable with the data

function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {

$Datatable = New-Object System.Data.DataTable

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Command.CommandText = $SQLQuery

$Reader = $Command.ExecuteReader()

$Datatable.Load($Reader)

$Connection.Close()

return $Datatable

}

# declaration not necessary, but good practice

$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery

Write-Host ("Statistic:{0}" -f $resultsDataTable.Column1)

Write-Host "Message: Transaction Delay is " $Datatable

exit 0

Output: ====================================================

Statistic:

Message: Transaction Delay is

Errors: ====================================================

Exception calling "Open" with "0" argument(s): "Login failed for user 'ComputerNameWhereTemplateAssigned'."

At line:21 char:5

+ $Connection.Open()

+ ~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : SqlException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The connection's current state is closed."

At line:29 char:5

+ $Reader = $Command.ExecuteReader()

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : InvalidOperationException

Exception calling "Load" with "1" argument(s): "Value cannot be null.

Parameter name: dataReader"

At line:31 char:5

+ $Datatable.Load($Reader)

+ ~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : ArgumentNullException

------------------------------------------------------

It looks like my connection string is the issue and $Connection.Open() is failing. I have tried messing with the connection string a bit but cannot seem to get it to work. It always goes to that computer name and I'm guessing their is an issue with the trusted_connection=true part. Hopefully we can figure this out because it would be nice to be able to do easily.

All comments

jeilers

It sounds like you are on the right track with what you are wanting to do. Windows Script monitor is what has been used in the past where I am now to do exactly what you are wanting to do using vbscript. Still working just fine too.

It's hard to give much advice without more information but starting with the Windows Script Monitor or Windows PowerShell Monitor should be able to do what you are needing with some work put into them.

300

Thank you for your response jeilers.

I am trying to use "Windows Power Shell monitor" and when I try to test the component, I am getting "NaN" as output all the time. Regardless of what SQL I use.

As I am not a all familiar with Power shell, I might be making some basic(stupid) mistake, but I am unable to find it.

This is a simple form of what I am trying:

[string] $Server= ".\HOSTNAME_OF_DB"

[string] $Database = "master"

[string] $UserSqlQuery= $("select count(*) from [master].[sys].[database_mirroring]")

# declaration not necessary, but good practice

$resultsDataTable = New-Object System.Data.DataTable

$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery

# executes a query and populates the $datatable with the data

function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {

$Datatable = New-Object System.Data.DataTable

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Command.CommandText = $SQLQuery

$Reader = $Command.ExecuteReader()

$Datatable.Load($Reader)

$Connection.Close()

return $Datatable

}

Write-Host "Statistic: $Datatable"

Write-Host "Message: Transaction Delay is " $Datatable

exit 0

This same SQL gives me a numeric value when I run it on same host from a DB editor (SQL Server Management Studio)

I have tried using $resultsDataTable instead of $Datatable and also tried using $result = function ExecuteSqlQuery and then use $result but still I get NaN.

This is the result I get:

Output Result: Message.Statistic: Transaction Delay is

Statistic.Statistic: NaN

Would you be able to help me find what I am missing here?

jeilers

I can try, but my powershell isn't my strongest suit. Nothing is glaringly wrong that I can tell. I would consider getting the script to work outside of Solarwinds. I think that there is probably an issue with getting the query to run and your statistic value is not actually becoming a number which is why you are seeing NaN.

Was able to get the statistic to work in powershell with this:

Write-Host ("Statistic:{0}" -f $resultsDataTable.Column1)

The unfortunate thing is I was still getting the NaN in the statistic when trying my own powershell monitor in solarwinds.

jeilers

After getting the query to work in powershell I created a Template with the Windows Powershell Monitor. I was still getting statistic NaN. So I turned on the debug log and

at SolarWinds.APM.Probes.Script.ScriptResult.ValidateStatistics(List`1 definitions)

at SolarWinds.APM.Probes.Script.ScriptResult.get_Columns()

at SolarWinds.APM.Probes.ProbeResults.DynamicMonitorResult.SetResult(DynamicEvidenceProbeSettingsBase settings, IScriptResult scriptResult)

at SolarWinds.APM.Probes.PowerShellProbe.ProbeInternal(ProbeInformation probeInfo, DynamicMonitorResult result, Func`1 powershellScriptHelperFactory)

2018-05-08 10:02:24,024 [STP Pool:608 Thread #0] [C15997] DEBUG SolarWinds.APM.Probes.PowerShellProbe -

Begin PowerShell Execute Result: ================================

Target: 192.168.69.39

Script: ====================================================

[string] $Server= "DB-Hostname"

[string] $Database = "DatabaseName"

[string] $UserSqlQuery= $("select count(*) FROM AlertHistoryView ahv WHERE EventTypeWord = 'Triggered' AND DATEDIFF(DAY,TimeStamp, GETDATE()) < 30")

# executes a query and populates the $datatable with the data

function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {

$Datatable = New-Object System.Data.DataTable

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Command.CommandText = $SQLQuery

$Reader = $Command.ExecuteReader()

$Datatable.Load($Reader)

$Connection.Close()

return $Datatable

}

# declaration not necessary, but good practice

$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery

Write-Host ("Statistic:{0}" -f $resultsDataTable.Column1)

Write-Host "Message: Transaction Delay is " $Datatable

exit 0

Output: ====================================================

Statistic:

Message: Transaction Delay is

Errors: ====================================================

Exception calling "Open" with "0" argument(s): "Login failed for user 'ComputerNameWhereTemplateAssigned'."

At line:21 char:5

+ $Connection.Open()

+ ~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : SqlException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The connection's current state is closed."

At line:29 char:5

+ $Reader = $Command.ExecuteReader()

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : InvalidOperationException

Exception calling "Load" with "1" argument(s): "Value cannot be null.

Parameter name: dataReader"

At line:31 char:5

+ $Datatable.Load($Reader)

+ ~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : ArgumentNullException

jeilers

The issue was me using the wrong credentials. After getting that fixed the above script worked for assigning the statistic.

Write-Host ("Statistic:{0}" -f $resultsDataTable.Column1)

300

I found how to turn on the debug log here: Turn on application debugging for SAM - SolarWinds Worldwide, LLC. Help and Support

The monitor is still failing for me (showing NaN) and when I changed the statistic line to "Write-Host ("Statistic: " + $resultsDataTable.Rows.Count)" it shows 0.

So I think there is something wrong with the way server is configured or the credentials.

I'll try to get the debug log enabled and see how it goes from there. If I finally get it to work, I'll post here :-)

Thank you again jeilers.

jeilers

No problem looking forward to hearing what you find out. Thanks for posting the question helped me learn some things as well!

Turning on the debug log and reading the output I ended up seeing these two things before figuring out which credentials I needed:

Exception calling "Open" with "0" argument(s): "Login failed for user 'ComputerNameWhereTemplateAssigned'."

and

Exception calling "Open" with "0" argument(s): "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

When assigning the application monitor you can choose which credentials to use:

I was using old credentials and was able to get the right ones by assigning custom credentials to figure out which of the stored ones was needed for it.

Feel free to like or mark posts as helpful that are worthy of it. Got my eye on that thwack backpack lol

300

Finally it worked for me:

Write-Host ("Statistic:{0}" -f $resultsDataTable.Column1) is what I needed and now is working.

Thank you again :-)