-- 启用 Ole Automation Procedures仅当当前为关闭状态IF EXISTS (SELECT *FROM sys.configurationsWHERE name Ole Automation ProceduresAND value_in_use 0)BEGINPRINT Ole Automation Procedures 当前已禁用正在启用...;-- 先确保能查看高级选项IF (SELECT value FROM sys.configurations WHERE name show advanced options) 0BEGINEXEC sp_configure show advanced options, 1;RECONFIGURE;PRINT 已启用 show advanced options。;END-- 启用 Ole Automation ProceduresEXEC sp_configure Ole Automation Procedures, 1;RECONFIGURE;PRINT ✅ Ole Automation Procedures 已成功启用。;ENDELSE IF EXISTS (SELECT *FROM sys.configurationsWHERE name Ole Automation ProceduresAND value_in_use 1)BEGINPRINT ✅ Ole Automation Procedures 已启用无需操作。;ENDELSEBEGINPRINT ⚠️ 未找到 Ole Automation Procedures 配置项可能版本不支持。;END-- -- 兼容性校验更正-- DECLARE CurrentCompatLevel INT;SELECT CurrentCompatLevelcompatibility_levelFROM sys.databasesWHERE name DB_NAME();if CurrentCompatLevel130BEGINALTER DATABASE [JW_Pro] SET COMPATIBILITY_LEVEL 130;end-- ------------------------------ Table structure for ApiRequests 接口配置表-- ----------------------------IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id OBJECT_ID(N[dbo].[ApiRequests]) AND type IN (U))DROP TABLE [dbo].[ApiRequests]GOCREATE TABLE [dbo].[ApiRequests] ([Id] int IDENTITY(1,1) NOT NULL,[ApiUrl] nvarchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL,[HttpMethod] nvarchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,[Remark] nvarchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL,[strKey] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,[Status] bit NULL,[strSql] varchar(4000) COLLATE Chinese_PRC_CI_AS NULL)GOALTER TABLE [dbo].[ApiRequests] SET (LOCK_ESCALATION TABLE)GOEXEC sp_addextendedpropertyMS_Description, N传入的json,SCHEMA, Ndbo,TABLE, NApiRequests,COLUMN, NstrSqlGO-- ------------------------------ Auto increment value for ApiRequests-- ----------------------------DBCC CHECKIDENT ([dbo].[ApiRequests], RESEED, 1062)GO-- ------------------------------ Primary Key structure for table ApiRequests-- ----------------------------ALTER TABLE [dbo].[ApiRequests] ADD CONSTRAINT [PK__ApiReque__3214EC0774153588] PRIMARY KEY CLUSTERED ([Id])WITH (PAD_INDEX OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS ON)ON [PRIMARY]GO-- ------------------------------ Table structure for HTTPRequestLog 日志表-- ----------------------------IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id OBJECT_ID(N[dbo].[HTTPRequestLog]) AND type IN (U))DROP TABLE [dbo].[HTTPRequestLog]GOCREATE TABLE [dbo].[HTTPRequestLog] ([LogID] int IDENTITY(1,1) NOT NULL,[LogTimestamp] datetime2(3) DEFAULT getdate() NULL,[URL] nvarchar(500) COLLATE Chinese_PRC_CI_AS NULL,[Method] nvarchar(10) COLLATE Chinese_PRC_CI_AS NULL,[Data] nvarchar(max) COLLATE Chinese_PRC_CI_AS NULL,[HeadersJson] nvarchar(max) COLLATE Chinese_PRC_CI_AS NULL,[QueryParamsJson] nvarchar(max) COLLATE Chinese_PRC_CI_AS NULL,[ResponseText] nvarchar(max) COLLATE Chinese_PRC_CI_AS NULL,[HttpStatus] int NULL,[ErrorMessage] nvarchar(max) COLLATE Chinese_PRC_CI_AS NULL,[DurationMilliseconds] int NULL)GOALTER TABLE [dbo].[HTTPRequestLog] SET (LOCK_ESCALATION TABLE)GO-- ------------------------------ Auto increment value for HTTPRequestLog-- ----------------------------DBCC CHECKIDENT ([dbo].[HTTPRequestLog], RESEED, 111355)GO-- ------------------------------ Primary Key structure for table HTTPRequestLog-- ----------------------------ALTER TABLE [dbo].[HTTPRequestLog] ADD CONSTRAINT [PK__HTTPRequ__5E5499A8FA351390] PRIMARY KEY CLUSTERED ([LogID])WITH (PAD_INDEX OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS ON)ON [PRIMARY]GO-- -- 创建/更新链接服务器 LOOPBACK (如果不存在则删除后重建)-- -- 1. 彻底清掉旧名防止半残IF EXISTS (SELECT 1 FROM sys.servers WHERE name NLOOPBACK)EXEC sp_dropserver server NLOOPBACK, droplogins droplogins;-- 2. 建新的EXEC sp_addlinkedserverserver NLOOPBACK,srvproduct N,provider NSQLOLEDB,datasrc SERVERNAME; -- 指向本实例EXEC sp_serveroption LOOPBACK, Nrpc out, Ntrue;EXEC sp_serveroption LOOPBACK, Nremote proc transaction promotion, Nfalse; -- 关键防止分布式事务-- -- 1. 如果HTTP_NetworkRequestCore存储过程已存在则先删除-- IF EXISTS (SELECT * FROM sys.objects WHERE object_id OBJECT_ID(Ndbo.HTTP_NetworkRequestCore) AND type IN (NP, NPC))BEGINDROP PROCEDURE dbo.HTTP_NetworkRequestCore;PRINT 已删除旧版存储过程: dbo.HTTP_NetworkRequestCore;ENDGOCREATE PROCEDURE dbo.HTTP_NetworkRequestCoreURL NVARCHAR(500),Method NVARCHAR(10) POST,DATA NVARCHAR(MAX) NULL,HeadersJson NVARCHAR(MAX) NULL,QueryParamsJson NVARCHAR(MAX) NULL,ResponseText VARCHAR(MAX) OUTPUT,HttpStatus INT OUTPUT,ErrorMessage NVARCHAR(MAX) OUTPUTASBEGINSET NOCOUNT ON;DECLAREobject INT,returnStatus INT,fullUrl NVARCHAR(1000),key NVARCHAR(256),value NVARCHAR(256),errMsg NVARCHAR(4000),errorSource NVARCHAR(4000),responseBodyTemp VARCHAR(8000), -- 临时变量用于减少大小风险responseLength INT, -- 用于检查长度chunkSize INT 2000, -- 分块读取大小offset INT 0,chunk NVARCHAR(1000),tempResponse VARCHAR(MAX) N, -- 用于拼接最终结果StartTime DATETIME2(3) GETDATE(), -- 记录开始时间Duration INT; -- 计算耗时-- 初始化输出参数SET ResponseText NULL;SET HttpStatus 0;SET ErrorMessage NULL;-- 标准化 HTTP 方法转大写SET Method UPPER(LTRIM(RTRIM(Method)));IF Method NOT IN (GET, POST, PUT, DELETE, PATCH, HEAD)BEGINSET ErrorMessage 不支持的 HTTP 方法: ISNULL(Method, NULL);RETURN;END-- 构建带查询参数的完整 URLSET fullUrl URL;IF QueryParamsJson IS NOT NULL AND LEN(QueryParamsJson) 0BEGINDECLARE queryPart NVARCHAR(MAX);SELECT queryPart STUFF((SELECT CAST([key] AS NVARCHAR(256)) CAST([value] AS NVARCHAR(256))FROM OPENJSON(QueryParamsJson)WITH ([key] NVARCHAR(256) $.key, [value] NVARCHAR(256) $.value)FOR XML PATH(), TYPE).value(., NVARCHAR(MAX)), 1, 1, );IF queryPart IS NOT NULL AND LEN(queryPart) 0BEGINSET fullUrl fullUrl CASE WHEN CHARINDEX(?, fullUrl) 0 THEN ELSE ? END queryPart;ENDEND-- 创建 WinHttpRequest 对象EXEC returnStatus sp_OACreate Msxml2.ServerXMLHTTP.3.0, object OUT;IF returnStatus 0BEGINEXEC sp_OAGetErrorInfo object, errorSource OUT, errMsg OUT;SET ErrorMessage 创建 WinHttpRequest 对象失败: ISNULL(errorSource, ) | ISNULL(errMsg, );RETURN;END-- 忽略 SSL 证书错误 (Option 4 SslErrorIgnoreFlags)EXEC sp_OASetProperty object, Option, NULL, 4, 13056;-- 设置超时毫秒: Resolve, Connect, Send, ReceiveEXEC sp_OAMethod object, SetTimeouts, NULL, 30000, 30000, 30000, 30000;-- 打开连接EXEC returnStatus sp_OAMethod object, Open, NULL, Method, fullUrl, false;IF returnStatus 0BEGINEXEC sp_OAGetErrorInfo object, errorSource OUT, errMsg OUT;SET ErrorMessage 打开连接失败 ( Method ): ISNULL(errorSource, ) | ISNULL(errMsg, );EXEC sp_OADestroy object;RETURN;END-- 设置请求头IF HeadersJson IS NOT NULL AND LEN(HeadersJson) 0BEGINDECLARE header_cursor CURSOR FORSELECT [key], [value]FROM OPENJSON(HeadersJson)WITH ([key] NVARCHAR(256) $.key, [value] NVARCHAR(256) $.value);OPEN header_cursor;FETCH NEXT FROM header_cursor INTO key, value;WHILE FETCH_STATUS 0BEGINEXEC returnStatus sp_OAMethod object, SetRequestHeader, NULL, key, value;IF returnStatus 0BEGINEXEC sp_OAGetErrorInfo object, errorSource OUT, errMsg OUT;SET ErrorMessage 设置 Header [ key ] 失败: ISNULL(errorSource, ) | ISNULL(errMsg, );CLOSE header_cursor;DEALLOCATE header_cursor;EXEC sp_OADestroy object;RETURN;ENDFETCH NEXT FROM header_cursor INTO key, value;ENDCLOSE header_cursor;DEALLOCATE header_cursor;ENDEXEC returnStatus sp_OAMethod object, Send, NULL, DATA;IF returnStatus 0BEGINEXEC sp_OAGetErrorInfo object, errorSource OUT, errMsg OUT;SET ErrorMessage 发送请求失败 ( Method ): ISNULL(errorSource, ) | ISNULL(errMsg, );EXEC sp_OADestroy object;RETURN;ENDEXEC returnStatus sp_OAGetProperty object, Status, HttpStatus OUT;IF returnStatus 0BEGINEXEC sp_OAGetErrorInfo object, errorSource OUT, errMsg OUT;SET ErrorMessage 获取状态码失败: ISNULL(errorSource, ) | ISNULL(errMsg, );EXEC sp_OADestroy object;RETURN;ENDSET responseBodyTemp N;EXEC returnStatus sp_OAGetProperty object, responseText, responseBodyTemp OUT;IF returnStatus 0BEGINEXEC returnStatus sp_OAGetProperty object, responseText, responseLength OUT;IF returnStatus 0 AND responseLength IS NOT NULLBEGINSET ErrorMessage 读取 ResponseBody 失败状态码 CAST(HttpStatus AS NVARCHAR(10)) : 获取长度成功但获取内容失败。长度: CAST(responseLength AS NVARCHAR(10));ENDELSEBEGINEXEC sp_OAGetErrorInfo object, errorSource OUT, errMsg OUT;SET ErrorMessage 读取 ResponseBody 失败状态码 CAST(HttpStatus AS NVARCHAR(10)) : 获取长度失败。错误: ISNULL(errorSource, ) | ISNULL(errMsg, );ENDSET ResponseText N;EXEC sp_OADestroy object;RETURN;ENDSET ResponseText responseBodyTemp;EXEC sp_OADestroy object;PRINT 接口返回内容预览 LEFT(ISNULL(ResponseText, NULL), 100);SET Duration DATEDIFF(MILLISECOND, StartTime, GETDATE());DECLARE sql NVARCHAR(MAX);SET sql NINSERT INTO LOOPBACK.[日志数据库].dbo.HTTPRequestLog (URL,Method,Data,HeadersJson,QueryParamsJson,ResponseText,HttpStatus,ErrorMessage,DurationMilliseconds)VALUES (URL,Method,DATA,HeadersJson,QueryParamsJson,ResponseText,HttpStatus,ErrorMessage,Duration);;EXEC LOOPBACK.master.dbo.sp_executesqlsql,NURL NVARCHAR(500),Method NVARCHAR(10),DATA NVARCHAR(MAX),HeadersJson NVARCHAR(MAX),QueryParamsJson NVARCHAR(MAX),ResponseText NVARCHAR(MAX),HttpStatus INT,ErrorMessage NVARCHAR(MAX),Duration INT,URL URL,Method Method,DATA DATA,HeadersJson HeadersJson,QueryParamsJson QueryParamsJson,ResponseText ResponseText,HttpStatus HttpStatus,ErrorMessage ErrorMessage,Duration Duration;END;--案例-- DECLARE ResponseText NVARCHAR(MAX) ,-- HttpStatus INT ,-- ErrorMessage NVARCHAR(MAX),-- PostHeaders NVARCHAR(MAX)-- SET PostHeaders ( SELECT Content-Type AS key, application/json AS value FOR JSON PATH)-- select * from ApiRequests where-- EXEC HTTP_NetworkRequestCore URL https://spqm-api.aurobayh.com/api/v1/token/getTokenInfo,-- DATA {vendorCode: 230056, secretKey: 5d86d21ccdfc3248810f3edd727ce78a},-- HeadersJson PostHeaders,-- ResponseText ResponseText OUTPUT,-- HttpStatus HttpStatus OUTPUT,-- ErrorMessage ErrorMessage OUTPUT------ -- 1. 如果HTTP_BusinessRequest存储过程已存在则先删除-- IF EXISTS (SELECT * FROM sys.objects WHERE object_id OBJECT_ID(Ndbo.HTTP_BusinessRequest) AND type IN (NP, NPC))BEGINDROP PROCEDURE dbo.HTTP_BusinessRequest;PRINT 已删除旧版存储过程: dbo.HTTP_BusinessRequest;ENDGOCREATE PROCEDURE dbo.HTTP_BusinessRequestFID INT,strKey VARCHAR(MAX),sStrRtn NVARCHAR(MAX) OUTPUT,sStr NVARCHAR(MAX) OUTPUTASBEGINSET NOCOUNT ON;DECLAREresp NVARCHAR(MAX),status INT,err NVARCHAR(MAX),APIURL NVARCHAR(MAX),HttpMethod NVARCHAR(10),PostHeaders NVARCHAR(MAX),BODYJSON NVARCHAR(MAX),strSql VARCHAR(max),Remark varchar(max),uploadStationStatus INT;SET sStrRtn 999999;-- 查询订单上传接口配置SELECTAPIURL ApiUrl,HttpMethod HttpMethod,uploadStationStatus Status,strSqlstrSql,RemarkRemarkFROM ApiRequestsWHERE strKey strKey;-- 仅当接口启用时才推送IF APIURL IS NOT NULL AND uploadStationStatus 1 AND strSql is not nullBEGIN-- 构造请求头含 vendorCodeSET PostHeaders (SELECT * FROM (VALUES(Content-Type, application/json)) t([key], [value])FOR JSON PATH);DECLARE strSqlN NVARCHAR(MAX) SELECT Result (CAST(strSql AS NVARCHAR(MAX)));-- 构造数据请求体EXEC sp_executesqlstrSqlN,NFID INT, Result NVARCHAR(MAX) OUTPUT, -- 在这里声明 ResultFID FID, -- 输入参数Result BODYJSON OUTPUT;IF BODYJSON IS NULLBEGINSET sStr BodyJson为空!;RETURN;END-- 执行推送请求DECLAREsiteResp VARCHAR(MAX),siteStatus INT,siteErr NVARCHAR(MAX);EXEC dbo.HTTP_NetworkRequestCoreURL APIURL,Method HttpMethod,DATA BODYJSON,HeadersJson PostHeaders,ResponseText siteResp OUTPUT,HttpStatus siteStatus OUTPUT,ErrorMessage siteErr OUTPUT;-- 检查推送结果IF siteStatus 200 AND JSON_VALUE(siteResp, $.success) trueBEGINSET sStrRtn 000000;SET sStr Remark数据推送成功;RETURN;ENDELSEBEGINDECLARE Errors NVARCHAR(MAX);-- 使用 OPENJSON 解析 Errors 数组SELECT Errors STRING_AGG(value, ; )FROM OPENJSON(siteResp, $.Errors)WITH (value NVARCHAR(MAX) $);SET sStr Remark推送失败: ISNULL(siteErr, Errors);RETURN;ENDENDELSEBEGINif uploadStationStatus0BEGINSET sStrRtn 000000;SET sStr RemarkstrKey接口未启用;ENDELSEBEGINSET sStr RemarkstrKey接口未启用或未配置数据源;endRETURN;ENDEND