locked
Need Help with DB job hanging RRS feed

  • Question

  • Hello,

    We are working in SQL SERVER(2014) on a Procedure, where in we are invoking webservice and getting JSON data as response, this procedure is called in DB job, but the job is not proceeding further, 
    when we debugged it the JSON data is getting truncated, this procedure works fine when ran manually.
    Note: we have used NVARCHAR(max) for the variable to store the JSON response
    We need help on this.

    Thanks & Regards,
    Naga

    Friday, July 10, 2020 11:32 AM

All replies

  • To be able to help you, we need to know more. A start would be to post the code of the stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 10, 2020 11:57 AM
  • Hi,

    I work on a Virtual Desktop with a banking client, so I can't copy paste the code here.

    Thanks,

    Naga

    Sunday, July 12, 2020 3:37 PM

  • I work on a Virtual Desktop with a banking client, so I can't copy paste the code here.

     

    Provide an obfuscated version of the relevant code. We won't be able to help without seeing some code.

    The fact that the proc works when executed manually but not as a job is a clue. Not sure how you debugged via the SQL Agent job and observed truncated data. Details on how exactly you did that may help too.  


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, July 12, 2020 3:57 PM
  • Hi Naga,

    Please provide more details so that we could help you further.

    If the DB job is hanging,you could make use of sp_stop_job procedure to stop jobs in Sql Server. You can create another job, in the job step you need to mention this sp and schedule it to run exactly after 30 minutes of the original job.

    If the DB job has stopped, you could try to create another job with same settings and see whether it could work.

    You could find more details about how to troubleshoot the job issue from below and check whether it is helpful to you.

    Troubleshooting SQL Server Jobs

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 13, 2020 6:24 AM
  • Hi Melissa,

    Thanks for your response.

    We have created 2 jobs, the second job also does not proceed further and after debugging we ended up with the same json data truncating/cropping  issue.

    Thanks & Regards,

    Naga

    Monday, July 13, 2020 7:22 AM
  • Hi Naga,

    Thanks for your update.

    Please have a try with lastet version of SSMS  to avoid any known issue.

    You could also try to export data to test file using SSIS and check whether any truncation appears.

    Please find some related links which may be helpful from below:

    FOR JSON PATH results in SSMS truncated to 2033 characters

    SQL Server json truncated (even when using NVARCHAR(max) )

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, July 13, 2020 7:50 AM
  • Thanks Melissa for your quick response. 

    I will check if i can generate the same to a file and will get back to you.

    Best Regards,

    Naga


    Monday, July 13, 2020 8:26 AM
  • We have resolved the issue, we have used SET TEXTSIZE 2147483647

    Thanks for your support.

    Naga

    • Proposed as answer by AV111 Thursday, July 16, 2020 3:22 PM
    Thursday, July 16, 2020 1:38 PM
  • Hi Naga,

    Thanks for your update.

    You could mark the replies as answers if they helped or mark your own answer. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 17, 2020 1:31 AM