Kaseya Community

ExecuteSqlQuery to variable in Ticket Mapping Procedure

This question is not answered

Is this possible?

The IF step is available and I can add my SQL query to the procedure and declare the output variable but then I don't seem to be able to call on the variable to use it in the procedure no matter how I format it. I have tried variablename, #variablename#, [=variablename=] and [$variablename$] all with no joy.

The query work exactly as expected in SMSS and produces a single result as a string of text.



Typo
[edited by: JB1975 at 5:50 AM (GMT -7) on Jun 22, 2017]
All Replies
  • If you are testing the variable you use the #variablename# format

    e.g.

    If #variablename# exits

    or

    If #variablename# Contains "ABC"

    But to use the actual variable you then use the [=variablename=] format

    Maybe add the If Exists test first just to be sure your SQL is actuall returning a value

    And make sure the SQL uses "Select Top 1 ......"  as if it returns more than one record things start to get messy

    Paul



    Typo
    [edited by: Paul Haaker at 5:42 PM (GMT -7) on Jun 22, 2017]
  • Hi Paul,

    Thanks for your reply. This query is using select top1, but I just can't get it to work in the ticket request mapping procedure or de-dupe procedures. If I use it in a stage entry procedure or subprocedure called by a stage entry procedure, everything works exactly as expected.

  • Could you post the actual line from the XML file that contains the query and I can test on my VSA

  • Hi Paul,

    Sorry for the delay, I've been away for a couple of weeks. I was pulling my hair out trying all sorts of things to get a result from this, and after two weeks out I can't remember whether the query in the XML file is one I changed when I was testing it but I will DM you with it shortly.

    Thanks for your assistance.

  • If you're looking for the syntax of using vars in the SQL command in the XML auth file, it's '[=VAR=]' - here's an excerpt from one of our files:

    sql="Select Count(ID) from [ksubscribers].[mb].[Working] where ID='[=ID=]'"

    Note that we wrap the var in single quotes as needed by SQL for a string value. The whole command is in double quotes. We've had challenges with proper quoting, so pay particular attention to that. We usually take what's between the double quotes and paste it into the SQL Admin command editor and verify it works as expected.

    Our old Ticket Request Mapping procedure was about 680 lines and used several executeSqlQuery commands. What I found, particularly with SD procedures, is that it doesn't particularly like when you reuse variables. I use "TV#" to reference multiple temp vars rather than simply using "TV" (TempVar) again and again.

    Here's the procedure code that calls the SQL query and checks the result:

    executeSqlQuery("SDNMG_GetCnt", "TV1", "Continue on Fail")

    If checkVariable("#TV1#") Equals "1"

     do things...

    Glenn