none
Json script falls over when run in sql job

    질문

  • Hi,

    i have written a code to import JSON data into temp table and it works fine when i run the script in query window but as soon as i schedule the script it fall over with following "JSON text is not properly formatted. Unexpected character '"' is found at position 508. [SQLSTATE 42000] (Error 13609).  The step failed."

    Any idea why it through error in SQL JOb. i am running sql 2016

    	DECLARE @json NVARCHAR(MAX)
    	SELECT @json = convert(varchar(max),strxml) FROM #temp
    
    
    	INSERT INTO #Customer
    	SELECT *  
    	FROM OPENJSON(@json)  
    	  WITH (email nvarchar(200) 'strict $.email',sStatus varchar(200) 'strict $.status')  

    2018년 5월 18일 금요일 오전 8:52

답변

  • Thanks, i know table column is XML but actual data was in JSON format, i have fixed it with introducing 
    SET TEXTSIZE -1

    • 답변으로 표시됨 MalikJaved 2018년 5월 18일 금요일 오후 2:41
    2018년 5월 18일 금요일 오후 2:41

모든 응답

  • Well, the important part is obviously missing.. where and how do you populate strxml?

    btw, I would not expect this code to work ever. Why should there be a JSON in a variable named XML??

    2018년 5월 18일 금요일 오전 9:05
  • Where are you fetching the JSON data from in the case of running from the job?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 9:10
  • strxml is a column name within #temp table and after pulling data from the file i am storing in that column and that column datatype is XML. i am not sure why you are saying this code shouldn't work, it is working but only in ssms query window but fails with error in sql job.
    2018년 5월 18일 금요일 오전 9:11
  • JSON variable takes values from #temp table column strxml. you can see from the code, i am selecting value into JSON variable from temp table.
    • 편집됨 MalikJaved 2018년 5월 18일 금요일 오전 9:29 modified
    2018년 5월 18일 금요일 오전 9:25
  • JSON variable takes values from #temp table column strxml. you can see from the code, i am selecting value into JSON variable from temp table.
    sorry still unclear on how values are getting populated to #temp. The error looks like value getting into the table is not a valid JSON. use of strict clause will make sure it works only if the relevant elements are present inside the JSON structure

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 10:01
  • 1) D'oh?

    strxml is a column name within #temp table and after pulling data from the file i am storing in that column and that column datatype is XML.

    Why do you think OPENJSON() can work on XML data???

    2) As you said "file" 

    You're aware that the SQL Server Agent runs possible in a different Windows account?

    The normal setup of this account is a network account, but has not access to non standard shares. But you need also to check the permissions of the file and the path to it.

    When you run this piece of code in SSMS, then the file access is normally executed with your users Windows permissions.

    Depending on what Windows account SQL Server Agent runs you either adjust the NTFS permissions or you change the Windows account of SQL Server Agent to a Windows account, which has the necessary NTFS permissions.

    Configure Windows Service Accounts and Permissions

    2018년 5월 18일 금요일 오전 10:39
  • that column datatype is XML

    Then its obvious that its XML data. In that case you should be using XML functions like nodes() and value to shred the data from it

    refer the below link on how you can do it

    https://visakhm.blogspot.ae/2012/10/shred-data-as-well-as-metadata-from-xml.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 1:29
  • Thanks, i know table column is XML but actual data was in JSON format, i have fixed it with introducing 
    SET TEXTSIZE -1

    • 답변으로 표시됨 MalikJaved 2018년 5월 18일 금요일 오후 2:41
    2018년 5월 18일 금요일 오후 2:41
  • Thanks, i know table column is XML but actual data was in JSON format, i have fixed it with introducing 
    SET TEXTSIZE -1

    Sorry I dont think XML datatype can parse and store JSON data so I dont understand how it is working for me

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 4:14