-1

As the title says, I have a set of SQL queries using OPENJSON() that work beautifully in SQL Server Management Studio, but when ran from the Command Prompt using SQLCMD, the queries return 0 rows.

Example:

INSERT INTO [common].[dbo].[distrcd] (schyr, distrcd, beginschyr, endschyr, cooprcdt, coopdistname, adminname, adminphone, adminemail, fax, address1, address2, city, [state], zip)
    SELECT * FROM OPENJSON(@JsonDefinition)
    WITH (schyr VARCHAR(9) '$.schoolYear',
    distrcd VARCHAR(5) '$.distributionCode',
    beginschyr VARCHAR(9) '$.beginSchoolYear',
    endschyr VARCHAR(9) '$.endSchoolYear',
    cooprcdt VARCHAR(11) '$.coopRCDT',
    coopdistname VARCHAR(33) '$.coopName',
    adminname VARCHAR(30) '$.adminName',
    adminphone VARCHAR(10) '$.adminPhone',
    adminemail VARCHAR(100) '$.email',
    fax VARCHAR(10) '$.fax',
    address1 VARCHAR(50) '$.address1',
    address2 VARCHAR(50) '$.address2',
    city VARCHAR(33) '$.city',
    [state] VARCHAR(2) '$.state',
    zip VARCHAR(5) '$.zipCode')
    WHERE ISJSON(@JsonDefinition) > 0;

When executed from SSMS, this returns 181 rows!

However, when I use the following in Command Prompt...

sqlcmd -S server -U username -P password -i \\server\share\ApiTest.sql

...it returns "(0 rows affected)".

I have verified that the variable @JsonDefinition is being populated properly both in SSMS & SQLCMD. It appears to be the OPENJSON() procedure that is "failing" - but without technically failing.

Any thoughts as to why this would be occurring?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
mortodestructo
  • 1
  • 1
  • Provide a sample for json value – Meyssam Toluie Sep 22 '22 at 18:16
  • `code` [{"schoolYear":"2022-2023","distributionCode":"ZZZZZ","beginSchoolYear":"1969-1970","endSchoolYear":"9998-9999","coopRCDT":"99999999999","coopName":"Coop Association","adminName":"Joe Schmoe","adminPhone":"9999999999","email":"test@test.org","fax":"9999999999 ","address1":"245 W Main St Ste 4","address2":"","city":"Wrigleyville","state":"IL","zipCode":"62999"}] `code` – mortodestructo Sep 22 '22 at 19:32
  • "I have verified that the variable @JsonDefinition is being populated properly" how? – Charlieface Sep 22 '22 at 19:42
  • 2
    The query is an insert statement so won't return any rows unless you have a trigger that returns a resultset. If you are referring to the rows affected message have you validated that there are actually no rows inserted (and that it is not just a misleading message?) – Martin Smith Sep 22 '22 at 20:53

1 Answers1

0

The solution was posted here:

https://dba.stackexchange.com/questions/196966/how-can-i-increase-the-length-of-the-string-returned-by-object-definition-in-sql

Based on @Charlieface 's suggestion, I decided to check my string length. It was 2048 - much shorter in SQLCMD than SSMS. Adding "-y0" to the Command Prompt command solved the issue entirely.

Ex:

sqlcmd -S server -U username -P password -i \\server2\share\NightlyProcess.sql -y0

After adding "y0", the string length limit is bypassed. Thank you for the clues, everyone.

mortodestructo
  • 1
  • 1