Context: I'm having difficulty modifying a stored procedure in SQL Server 2016. The stored procedure performs parsing of json data within a file. For some reason I'm able to execute the stored procedure and it executes successfully but when I try to modify the stored procedure I get the following message:
Question: Does anyone have any troubleshooting tips? Below is the content of the stored procedure. SQL Server 2016 supports the various functions used including the OPENJSON function.
USE mattermark_sandbox
GO
CREATE PROCEDURE get_company_data
AS
IF OBJECT_ID('tempdb..##jsondump') IS NOT NULL DROP TABLE ##jsondump
IF OBJECT_ID('tempdb..##jsonparsed') IS NOT NULL DROP TABLE ##jsonparsed
IF OBJECT_ID('tempdb..##json_loop') IS NOT NULL DROP TABLE ##json_loop
CREATE TABLE ##jsondump (
[my_json] [nvarchar](max) NULL
)
-- Create a table to house the parsed content
CREATE TABLE ##jsonparsed (
[id] [int] NULL,
[url] [varchar](255) NULL,
[company_name] [varchar](255) NULL,
[domain] [varchar](255) NULL
)
-- Clear ##jsondump
TRUNCATE TABLE ##jsondump;
-- Clear ##jsonparsed ( only if you don't want to keep what's already there )
TRUNCATE TABLE ##jsonparsed;
-- Import ( single column ) JSON
--IMPORTANT: Need to be sure the company_data.json file actually exists on the remote server in that directory
BULK INSERT ##jsondump
FROM 'C:\mattermark_etl_project\company_data.json' -- ( <-- my file, point to your own )
WITH (
ROWTERMINATOR = '\n'
);
-- Select JSON into ##jsonparsed
SELECT my_json
INTO ##json_loop
FROM ##jsondump;
--SELECT * FROM ##jsondump;
INSERT INTO ##jsonparsed (
id, [url], company_name, domain
)
SELECT DISTINCT
jsn.id, jsn.[url], jsn.company_name, jsn.domain
FROM ##json_loop
OUTER APPLY (
SELECT * FROM OPENJSON(##json_loop.my_json, '$.companies' )
WITH (
id INT '$.id',
[url] VARCHAR(255) '$.url',
company_name VARCHAR(255) '$.company_name',
domain VARCHAR(255) '$.domain'
)
) AS jsn
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) =
' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 = @exe_path4 +
' BCP.EXE "SELECT ''Company_ID'', ''MatterMark_URL'', ''Company_Name'', ''Domain'' UNION ALL SELECT DISTINCT cast(id as varchar( 12 )) as id, url, company_name, domain FROM ##jsonparsed" queryout ' +
' "C:\mattermark_etl_project\company_data.txt" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4,no_output;
SELECT DISTINCT * FROM ##jsonparsed
ORDER BY id ASC;
DROP TABLE ##jsondump
DROP TABLE ##jsonparsed
DROP TABLE ##json_loop
/*
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
*/
exec xp_cmdshell 'C:\mattermark_etl_project\powershell "C:\mattermark_etl_project\open_file.ps1"',no_output
