10 Replies Latest reply on May 8, 2018 5:18 PM by 300

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

    300

      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.

        • Re: How to run a SQL on SQL Server DB with database name as parameter?
          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.

            • Re: How to run a SQL on SQL Server DB with database name as parameter?
              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:

              Message.Statistic: Transaction Delay is

               

                                      Statistic.Statistic: NaN

               

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

                • Re: How to run a SQL on SQL Server DB with database name as parameter?
                  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.

                  • Re: How to run a SQL on SQL Server DB with database name as parameter?
                    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.

                • Re: How to run a SQL on SQL Server DB with database name as parameter?
                  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.

                    • Re: How to run a SQL on SQL Server DB with database name as parameter?
                      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

                    • Re: How to run a SQL on SQL Server DB with database name as parameter?
                      300

                      Finally it worked for me:

                       

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

                      Thank you again :-)