OPENJSON() returns 0 rows from SQLCMD, but returns many rows in SSMS

Viewed 35

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?

0 Answers
Related