动态MS SQL查询

Fox 发表于 Dev

狐狸

我的动态ms sql查询有问题。有人能帮我吗。这是我的代码。问题在OPENQUERY内部，靠近“ 1033”

DECLARE @sql nvarchar(max);
    DECLARE @server nvarchar(255) = (SELECT [Value] FROM [WarehouseMgmt].[SyncConfig] WHERE [Key] = 'ReportServerLinkedServer')
    DECLARE @database nvarchar(255) = (SELECT [Value] FROM [WarehouseMgmt].[SyncConfig] WHERE [Key] = 'ReportServerDatabase')




 SET @sql = 'MERGE [WarehouseMgmt].[DimReportServerReports] AS DRSR
    USING (SELECT ItemId,Name FROM OPENQUERY('+@server+',''SELECT ItemId,Name FROM '+@database+'.[dbo].[Catalog] WHERE Type=2 AND Name NOT LIKE ''1033%'' AND Path NOT LIKE ''/Reports/%Subs'')   
     ) AS CATALOG
    ON (DRSR.[SourceOrigId] = [Catalog].[ItemId])
    WHEN NOT MATCHED BY TARGET  THEN 
        INSERT 
        (
            [SourceOrigId],
            [ReportName],
            SyncExecId
        )
        VALUES 
        (
            [Catalog].[ItemId],
            ISNULL([Catalog].[Name],''<UNKNOWN>''),
            @SyncExecId
        )
    OUTPUT  
            [Catalog].[ItemId],
            [Catalog].[Name]
    INTO #NewReportServerReports;'

    EXEC sp_executesql @sql,N'@SyncExecId int',@SyncExecId

错误代码为：

消息50000，级别11，状态1，过程WriteJobLog，行101写入作业日志时出错：行＃90：[错误]＃2：'1033'附近的语法不正确。

普雷迪普

增加了一些quotes。试试这个。

 SET @sql = 'MERGE [WarehouseMgmt].[DimReportServerReports] AS DRSR
    USING (SELECT ItemId,Name FROM OPENQUERY('+@server+',''SELECT ItemId,Name FROM '+@database+'.[dbo].[Catalog] WHERE 
    Type=2 AND Name NOT LIKE ''''1033%'''' AND Path NOT LIKE ''''/Reports/%Subs'''''')   
     ) AS CATALOG
    ON (DRSR.[SourceOrigId] = [Catalog].[ItemId])
    WHEN NOT MATCHED BY TARGET  THEN 
        INSERT 
        (
            [SourceOrigId],
            [ReportName],
            SyncExecId
        )
        VALUES 
        (
            [Catalog].[ItemId],
            ISNULL([Catalog].[Name],''<UNKNOWN>''),
            @SyncExecId
        )
    OUTPUT  
            [Catalog].[ItemId],
            [Catalog].[Name]
    INTO #NewReportServerReports;'
    --print @sql
    EXEC sp_executesql @sql,N'@SyncExecId int',@SyncExecId