SQL Server无效版本:15(微软. SQL服务器.Smo)

prdp8dxp  于 2022-11-21  发布在  SQL Server
关注(0)|答案(4)|浏览(829)

**上下文:**在SQL Server 2016中修改存储过程时遇到困难。该存储过程对文件中的json数据执行分析。由于某种原因,我可以执行该存储过程,并且该存储过程执行成功,但当我尝试修改该存储过程时,收到以下消息:

**问题:**是否有人有任何疑难解答提示?以下是存储过程的内容。SQL Server 2016支持使用的各种函数,包括OPENJSON函数。

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
deyfvvtc

deyfvvtc2#

您可以使用查询来检视存储程序定义,而不需要安装新的SSMS。在执行此查询之前,您可以选择性地使用SSMS功能表项目Query -> Results To -> Results to Text,将结果格式化在一个文字字段中。

exec sp_helptext [get_company_data]

(其中get_company_data是存储过程名称。)
另请注意,存储过程的“Modify”选项只打开一个常规查询选项卡,该选项卡预先填充了ALTER PROCEDURE的定义,您可以通过在常规查询选项卡中运行ALTER PROCEDURE来更新它。

mzmfm0qo

mzmfm0qo3#

此问题有一个解决方法:右键单击数据库名称并选择Tasks〉Generate Scripts,而不是选择Modify:选择特定对象,检查对象。

s5a0g9ez

s5a0g9ez4#

如果您使用的是Oracle服务器,请使用OPENQUERY。选取语法,如下所示:

select * from OPENQUERY(LINK_SERVER_NAME,'select * from DUAL')

相关问题