Kaseya Community

Help with SQL Non-Query needed.

This question is not answered

I was wondering if anyone would know why this command

UPDATE kasadmin.SDIncident SET sdSolutionTypeFK=NULL WHERE (kasadmin.SDIncident.ref = 'M000655')

works fine if run in the SQL management studio but if I set a Service Desk step 'Execute SQL Non-Query' if does not work?  I don't get a fail, the ticket just doesn't update.

 

Thanks,

Grant

 

All Replies
  • You've probably already got it working by now Grant, but just in case anyone else is having problems with this, I'd suggest trying something like:

    UPDATE [ksubscribers].[kasadmin].[SDIncident] SET sdSolutionTypeFK=NULL WHERE ref = 'M000655'

    I haven't tested this in your case, but I've found the hard way that it likes the square brackets (along with DB and schema name) when referring to tables.

  • Hi Simon,

    Thanks for the idea, I had actually given up on this and was going to live with the resolution not resetting itself when a customer re-opened a ticket.  I have tried your suggested solution and I get the same result, if I run it in the management studio it works, run it from a script it does not.

    What I actually have is this:

    UPDATE [ksubscribers].[kasadmin].[SDIncident] SET  sdSolutionTypeFK=NULL WHERE ref = '[$TicketId$]'  

    which does correctly send the ticket ID, as the next line sends me a message with the same statement which gives me say

    UPDATE [ksubscribers].[kasadmin].[SDIncident] SET sdSolutionTypeFK=NULL WHERE ref = 'M003589'

  • Did you ever get this to work? I have and almost identical query to update sdDesc in the same table. Works every time in SSMS specifying the ticket number but when running through a KSD procedure using the [$TicketId$] variable, the value is not updated.

  • Can you guys post your XML file? We used to have dozens of SQL queries in Service Desk, and often they would work in SSMS but not in SD. It invariably turned out to be something in the XML, usually with variable references that were not quite right.

    Our current SD implementation has eliminated over 600 lines of procedure code from our original (in house) design and all but a few SQL queries, and late spring 2018, we should be down to about 6 lines in procedures and zero SQL calls, doing nearly everything on the back-end. Between procedure code and SQL, it was worth the effort to move to an alternate process and reduce/eliminate these challenges.

    Glenn

  • Hi Glenn,

    Our requirement is, for certain tickets, to add the date of ticket creation into the description field of the ticket. The SQL we are currently using for this is:-

    "UPDATE [ksubscribers].[kasadmin].[SDIncident] SET [ksubscribers].[kasadmin].[SDIncident].[sdDesc] = (convert(varchar(23),GETDATE(),121)) + ' ' + [ksubscribers].[kasadmin].[SDIncident].[sdDesc] WHERE ref = '[$TicketId$]'"

    This works exactly as expected in SSMS, but is not working through the service desk for us.

  • why not just use assign ticket property and make [$Description$] = [$Description$] ([$ClosedDateTime$])

    Ie just rewrite the description but append the created date to it

  • Hi Paul,

    Thanks for the reply. We did try that initially using "[$Description$] = [$Description$] ([$CreateDateTime$])" but we were getting failures in SMSS as the field types were not the same and the contents of the datetime field could not be added to the varchar field, hence trying to use the convert.

    We do not need this timestamp to be 100% exact, so running this SQL query as a subprocedure called from the ticket entry stage and adding the current timestamp from the system would suffice.



    Typo
    [edited by: JB1975 at 7:05 AM (GMT -8) on Mar 5, 2018]
  • Hi Paul,

    I misread your earlier reply. I had assumed you were suggesting a change to the SQL and missed the assign ticket property suggestion.

    I have tried using assign ticket property but "description" was not a ticket property that can be assigned using that step.

  • You can do it via a Dedupping Procedure

    The Assign Ticket Property step has "Description" as an option

  • Hi Paul,

    Thanks for that. I have tried but these alerts are coming in by email and there is no record of them in the dbo.ticRequest table for the dedupe procedure to pick up on.

  • Check your SD Incoming Email and Alarm Settings

    Under the Reader check that you have selected a Dedupping procedure.

    If not create one and then you can add the required step to that procedure which will be processed when incoming emails are turned into SD tickets

  • Hi Paul,

    Yes we already have a de-depe and mapping procedure set up there, both of which are working well, but what we are seeing is that alerts raised by the VSA are in the dbo.ticRequest table, but email alerts do not seem to be present there at all.

    If I send an email into the service desk address a ticket is created, but there is no record of this email or ticket in the ticket request table, just in kasadmin.SDIncident.

  • OK .. but I though the issue was you wanting to  to add the date of ticket creation into the description field

    Then just using the Dedupping procedures ( either in a email reader or Alert ) , and Adding the CreateDate to the existing Description does not need you to make any SQL queries to Any tables

  • I am having a very similar problem.  I've used SQLQuery xml files to run both inline SQL and stored procedures with success.  Now I want to make a SQLNonQuery because I'm doing an UPDATE.  I made the xml file and the stored procedure.  The stored procedure works from SMSS.  I verified it's being called by the Kaseya script by watching the calls in SQL Profiler (the correct $variables are getting passed properly).  I'm stumped.  Profiler says it runs, it doesn't "fail" because I have it set to Halt on Fail and the script lines after the NonQuery call run (I have it writing a note to the ticket to prove that the NonQuery returned success).

    With all that said, Kaseya runs the NonQuery stored procedure but the results don't actually hit the database.  I've even trimmed down the stored procedure to do nothing more than tack on a 'Z' to the sdSummary so I could quickly see if it worked.  No dice.

  • Can you post copies of your XML files .. not  copies of the query ... the actual XML file so we can test on our systems