Procedure or function has too many arguments specified

Topics: User Forum
Feb 26, 2009 at 9:11 PM
Edited Feb 26, 2009 at 9:11 PM
Hello - I'm trying to create a SQL Monitor that calls a stored procedure which has only one parameter:

CREATE PROCEDURE p_MyProcedure (@intMyGroupID int)
AS
SET NOCOUNT ON
SELECT COUNT(1) AS MyCount FROM MyTable WHERE GroupID = @intMyGroupID


My XML settings are as follows:

<SQLMonitor>
    <ConnectionString>Provider=SQLOLEDB.1; Data Source=MyServer; Initial Catalog=MyDatabase; Integrated Security=SSPI;</ConnectionString>
    <SP>
        <Name>p_MyProcedure</Name>
        <Parameters>
            <Parameter Name="intMyGroupID">1</Parameter>
        </Parameters>
    </SP>
</SQLMonitor>


I save the monitor, then when I click on the "Test Monitor" button in the "Test" tab, I get the following message:

Status: Fail
Status Message: Procedure or function p_MyProcedure has too many arguments specified.


What am I doing wrong?  Thanks!
Feb 26, 2009 at 9:44 PM
Sorry.  I didn't realize that the proc had to adhere to a specific structure (have StatusCode and StatusMessage output params, etc).
Developer
Mar 1, 2009 at 11:24 AM
Edited Mar 1, 2009 at 11:55 AM

Hi Solaco,

You can do it the official way, or you can try it my way using a PowerShell Monitor rather than a SQL Monitor. I've found it's much nicer not having to customize my SQL SPs and I love having the ability to provide a nicely formatted message for $status.statusText so I can get some useful information in the email notifications.

# ---------------  Script Starts Here ---------------------
# The invoke-SQLCommand is from here:
# http://www.leeholmes.com/blog/InteractingWithSQLDatabasesInPowerShellInvokeSqlCommand.aspx

# $PolyMon is set so the script can behave differently when it's not run in PolyMon
$PolyMon = $true
# 'Default MSH Host' is the name of the PolyMon host
if ($Host.name -ne "Default MSH Host"){$PolyMon = $false}
# if not in PolyMon create an object to simulate the $status object in PolyMon
if (!$PolyMon){
  $status = "" | Select-Object StatusID, StatusText
  $counters = @{}
}

# run a SQL stored procedure to get some data to analyze
# The data could be anything, in this case, the SP returns the following data
# Name                        CurrentValue DesiredValue State
# ----                        ------------ ------------ -----
# AcceptRequests                         1            1 Success
# BounceDaemonCount                      4            2 Success
# MailerDaemonCount                      4            4 Success
# ProcessEmailAll                        1            1 Success
# ProcessingMailerDaemonCount            4            4 Success
# ThrottledMailerDaemonCount             1            1 Success
# UnlimitedMailerDaemonCount             3            3 Success

$results = invoke-sqlcommand -sqlCommand "spCheckEmailConfig_PowerShell" -dataSource 'YOURSERVER' -database 'YOURDATABASE'
$errors   = @($results | ?{$_.State -eq "Error"}).count
$warnings = @($results | ?{$_.State -eq "Warning"}).count

# Turn the results into a nicely formatted table that will appear in the email notifications
$errorMsg = @("There are [$errors] errors and [$warnings] warnings.","--------------------------",($results |
  format-table -auto | out-string).trim()) | out-string
 
# Add the perf counters
$Counters.Add("Email Configuration Errors",$errors)
$Counters.Add("Email Configuration Warnings",$warnings)

# set the properties of the $status object
if ($errors -gt 0) {
  $status.StatusID = 3   # 3=Fail
  $status.StatusText = $errorMsg
}
elseif ($warnings -gt 0) {
  $status.StatusID = 2   # 2=Warning
  $status.StatusText = $errorMsg
}
else {
  $status.StatusID = 1   # 1=OK
  $status.StatusText = $errorMsg
}

# If not in PolyMon return the $status and $counters objects
if (!$PolyMon){
  $status | fl
  $counters | ft -auto
}
# ---------------  Script Ends Here ---------------------