I am looking to use the EXE/SCRIPT ADVANCED SENSOR to run a number of SPs but the SPs have parameters/variables. In the PRTG sensors parameter settings box how would I state my parameter/variable that the SP needs to run?
Just like you would call it from the command line :)
Could you give an example? At the moment I use the following in the parameter box. What do I add to the end to set my parameter for the sp?
-d= database name -sp= SP name -s= server -u= username -p= password -sa
Then your parameter field just has to look a little something like this:
-d=database -sp=SPname -s=%host -u=username -p=password -sa
Let me know if that works :)
Yes that is currently what I have but I would like to know what I can add to the string to tell my sensor to call a parameter/variable that has been set in my sp?
I am making generic sp's and my plan is to have one or more parameters in the sp which will let me change the type of information I want as an output.
Pardon, what do you mean by SP?
Stored Procedure. I want to use the EXE/SCRIPT ADVANCED SENSOR to call an SP in our database but the SP has a parameter in it that needs to be stated when calling it.
Sorry for any confusion.
Ah - and you want that parameter to be dynamic? You could add the necessary parameters in the sensor comments, little example:
-d=database -sp='CALL %comments' -s=%host -u=username -p=password -sa
The %comments placeholder will then be replaced with the value of the sensor comment - it can be entered by heading to the tab with the small chat bubble on the sensor page.
Okay thank you I see. So in the small chat bubble I state the sp name and the parameter that needs to be set?
That's great thank you.
You're very welcome :)
I have now put in place my SP's but am having problems with this. I have put -d=database -sp='CALL %comments' -s=%host -u=username -p=password -sa in the setting and setup the comments tab with
EXEC SP Name and then my parameters @parameter1, @parameter2
But I get an error saying Could not find stored procedure 'CALL'. Any advise on what I'm doing wrong?
Have you tried double quotes (like this: ") around the SP parameter yet?
Stephan is right, if a parameter contains a white space, the OS will think that these are actually two parameters.
Keeping them together you need to use -sp="CALL %comments%"
Yes I got the error Could not find stored procedure 'CALL %comments'.
Are the comments entered within the sensor or the device? Strange that it's not getting replaced...
Ah okay thanks. Well I have tried it like so -sp="CALL%comments%" and still get an error (Could not find stored procedure 'CALL%comments%'.)
Any chance this might be due to what I have in the comments tab?
If so what should I include in the comments tab? Currently I have it layed out as follows:
EXEC sp name @parameter1, @parameter2
The correct syntax would be this:
Note the whitespace between CALL and the placeholder and that there's only one %, preceeding the placeholders. The comments have to be like this:
EXEC sp name @parameter1, @parameter2
Thanks I have tried both exactly as you have suggest above but still no luck.
I'm not quite sure what you mean by test button. My steps to get to this problem are:
-Add a EXE/Script Advanced sensor to my chosen Device (the device doesn't have any real config it is set to 127.0.0.1) -set the EXE/Script field to SQLspXML.exe -Input -d=database -sp="CALL %comments" -s=%host -u=username -p=password -sa in the Parameters field -click save -open the comments tab and input EXEC sp name @parameter1, @parameter2
are you able to offer any further advice on this? I'm currently running the most up to date version of PRTG.
Sorry, my bad - seems like %comments isn't parsed in the parameters...You might want to use a wrapper script that reads out the comment via API:
...and then executes it accordingly.
okay how would I set this up? Do you have some steps to do this?
Something like this: http://pastebin.com/G9t05hS3 It will read the current comment and execute your SQLspXML.exe with the given parameters, and output the result of it (it should, at least) :) The passhash can be obtained from the account settings within PRTG.
alright thank you. Do you have an example of the body for the wrapper script?
What body do you mean?
Don't worry have got this working now by using the following info:
Using the optional –cp= parameter, it is possible to pass a comma separated list of parameters to your stored procedure. Parameters and their value need to be separated by a | e.g. -cp="@Firstname|Jane,@LastName|Doe,@Age|26"
Alright, glad that you got it working now :)