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
  • SQLNonQuery2.xml

    Same problem here .

    , I have included a copy of the XML file we are using for this if you can try it.

    We are not trying to add a timestamp to every ticket, only for certain ones. We have tried to accomplish what we need using a dedupe procedure, but have had no luck.

    We have been using an SD procedure to check to see if a ticket matched on condition XYZ (actual condition obscured for privacy reasons). If this matched, then the SQLNonQuery would run to add a timestamp into the sdDesc field as per the query "Insert alert datetime into XYZ tickets" from the attached XML.

    We are using a convert to add the timestamp into the sdDesc field as the creationDateTime field and the sdDesc field had different field types and we do not need this timestamp to be 100% accurate - the time from the system that ticket runs through the SD procedure will be sufficient.

    We have used many variations of this query, adding the database name of ksubscribers, adding and removing square brackets etc, but while everything we try works in SMSS, nothing works through the SD procedure.

    Similar to Kevin, we are also using halt on fail, writing notes to the ticket and proving that the ticket is entering and exiting the procedure by writing entry and exit notes as the first and last lines of the procedure.

  • I'll give you both in case you wanted to test the whole setup.

    Here's the XML file (C:\Kaseya\xml\SDProcSQL\0\SQLNonQuery\waitingPlusThree.xml):

    <?xml version="1.0" encoding="utf-8"?>
    <queryList>
    <queryDef label="waitingPlusThree" sql="exec ksubscribers.kasadmin.pbs_waitingPlusThree '[$TicketId$]'" />
    </queryList>


    And here's the stored procedure it references:

    ALTER procedure [kasadmin].[pbs_waitingPlusThree]
    @ref varchar(255)
    as
    begin

    declare @d datetime
    set @d = case
    when DATEPART(dw,GETDATE()) in (4,5,6) then dateadd(day,5,getdate())
    when DATEPART(dw,GETDATE()) = 7 then dateadd(day,4,getdate())
    else dateadd(day,3,getdate())
    end

    ;WITH XMLNAMESPACES(DEFAULT N'http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd')
    update SDI
    set customFields.modify('replace value of (/CustomFields/Field[@fieldName="followupdate"]/text())[1]
    with sql:variable("@d")')
    from kasadmin.SDIncident SDI
    where SDI.ref like @ref

    end

  • I also just tried changing it from a NonQuery to a Query, by adding a "select 1" at the end of the stored procedure and updaing the XML and SD procedure (storing the result in a junk variable).  It still looks like it works (the note appears in the ticket verifying the SqlQuery didn't die) but the custom attribute never gets changed.

  • You have a semicolon at the end of your second query.  Delete it.  I've seen that break things sometimes when running queries through SQL APIs.

  • Hi Kevin,

    We have tried it with and without the semicolon, and every other combination we can think of, to no avail.

    Thanks for the suggestion though.

  • My next thought was permissions.  Although I didn't have to set any different permissions on the other stored procedures that work fine, I checked SQL Profiler to see what account was running this stored procedure.  It says the account is KElevated5F6C2F, which isn't an account on the system so I can't grant it permissions to the SP.  But I see that account running lots of direct SQL in the Profiler trace, so it must be fine.  I'm running out of guesses here.

  • Yep, I'm getting nowhere either. Will probably ditch the idea altogether now.

  • I just submitted a support ticket.  I need this to work.

  • Best of luck, let me know how you get on!

  • As a workaround I changed my script to a SQLQuery that returns the datetime I need, stores it in a SD variable, and then use the assignCustomAttribute step to get the date into my 'followUpDate' custom attribute.  So my system is working, but I'm still pursuing the Kaseya support ticket because we need to be able to modify the database directly from SQLNonQueries and the like...

  • Hi Kevin, have you made any progress with support on this one?