Sql-Server
防止 SQL 作業代理將引號解釋為撇號
我正在為我的 SQL Job Agent 將引號解釋
"為 撇號而苦苦掙扎'。我有一個 T-SQL 查詢，它在 SQL Server Management Studio 中工作，但當我將完全相同的語句插入到 SQL Server 代理作業中作為T-SQL Script Step時卻不行。
要理解下面的 T-SQL 語句，如果我指出應該做什麼可能會有所幫助。首先，我為數據倉庫多維數據集創建了一個 MDX 查詢。此查詢獲取應導入到我們的 CRM 的每個客戶的產品使用情況。
然後，此 MDX 查詢在
OPENQUERY語句中執行，結果被“操縱”並轉換為正確的數據類型。然後將其結果插入到連結伺服器的表中。
INSERT INTO [Linked OLE DB Server].[DB].[dbo].[Destination table] (Columns) SELECT Dataconversion and Dataconsolitation FROM OPENQUERY( [Linked SSAS Server], 'great mdx statement with [Measures].[Measures] on columns dimensions on rows FROM cube')該查詢在 SQL Server Management Studio 中執行良好，並帶來了預期的結果。
MDX 中的度量使用 之外的引號來處理
OPENQUERY，例如：SELECT CONVERT(float, "[Measures].[average_price]") AS AVG_Price FROM OPENQUERY( [Linked SSAS], 'SELECT { [Measures].[average_price] } ON COLUMNS, NON EMPTY { {[customers].[customerNR].CHILDREN} } ON ROWS FROM [DWH] ')但是當我將此 SQL 語句複製到 SQL Server 代理作業中時，它將解釋
CONVERT(float, "[Measures].[average_price]")為CONVERT(float, '[Measures].[average_price]'). 很明顯它不能轉換文本'$$ Measures $$.$$ average_price $$’ 變成一個浮點數，但為什麼 SQL Server 代理會嘗試這樣做呢？如何防止它將引號解釋為撇號？ 請詢問您是否不了解其中的任何部分。
如果您需要有關整個查詢的更多資訊，請訪問：
INSERT INTO [DATSA0014].[MACO_MSCRM_BUFFER].[dbo].[PROD_NUTZ_TOCRM] ( [IDKEY] ,[PROD_HAUPT_GRP] ,[HERSTELLER] ,[HERSTELLER_AJ] ,[HAENDLER] ,[Profil] ,[WAEHRUNG] ,[NIEDERLASSUNG] ,[KUNDE] ,[MENGE_VJ] ,[MENGE_AJ] ,[MENGE_PLAN] ,[YTD_MENGE_VJ] ,[YTD_MENGE_AJ] ,[YTD_MENGE_PLAN] ,[AVG_PREIS_VJ] ,[AVG_PREIS_AJ] ,[YTD_AVG_PREIS_VJ] ,[YTD_AVG_PREIS_AJ] ,[UMSATZ_VJ] ,[UMSATZ_AJ] ,[UMSATZ_PLAN] ,[YTD_UMSATZ_VJ] ,[YTD_UMSATZ_AJ] ,[YTD_UMSATZ_Plan] ) SELECT ( CONVERT(varchar(15),"[KNA1].[KUNNR].[KUNNR].[MEMBER_CAPTION]") + '_' + CONVERT(varchar(5), "[TVKO].[VKORG].[VKORG].[MEMBER_CAPTION]") + '_' + CONVERT(varchar(10),COALESCE( "[T179].[Parent].[Level 05].[MEMBER_CAPTION]", "[T179].[Parent].[Level 04].[MEMBER_CAPTION]", "[T179].[Parent].[Level 03].[MEMBER_CAPTION]", "[T179].[Parent].[Level 02].[MEMBER_CAPTION]" ))) AS [IDKEY] , CONVERT(varchar(100),COALESCE( "[T179].[Parent].[Level 05].[MEMBER_CAPTION]", "[T179].[Parent].[Level 04].[MEMBER_CAPTION]", "[T179].[Parent].[Level 03].[MEMBER_CAPTION]", "[T179].[Parent].[Level 02].[MEMBER_CAPTION]" )) AS [PROD_HAUPT_GRP] , '100' AS HERSTELLER, '100' AS HERSTELLER_AJ, '' AS HAENDLER, '' AS Profil, 'EUR' AS WAEHRUNG, CONVERT(varchar(50), "[TVKO].[VKORG].[VKORG].[MEMBER_CAPTION]") AS NIEDRLASSUNG, CONVERT(varchar(30),"[KNA1].[KUNNR].[KUNNR].[MEMBER_CAPTION]") AS KUNDE, TRY_CONVERT(float, "[Measures].[Garnituren_VJ]") AS MENGE_VJ, TRY_CONVERT(float, "[Measures].[Garnituren]") AS MENGE_AJ, TRY_CONVERT(float, "[Measures].[PlanGarnituren]") AS MENGE_PLAN, TRY_CONVERT(float, "[Measures].[Garnituren_VJ]") AS YTD_MENGE_VJ, TRY_CONVERT(float, "[Measures].[Garnituren]") AS YTD_MENGE_AJ, TRY_CONVERT(float, "[Measures].[PlanGarnituren]") AS YTD_MENGE_PLAN, TRY_CONVERT(float, "[Measures].[Durchschnittspreis_YTD_VJ_NN]") AS AVG_PREIS_VJ, TRY_CONVERT(float, "[Measures].[Durchschnittspreis_NN]") AS AVG_PREIS_AJ, TRY_CONVERT(float, "[Measures].[Durchschnittspreis_YTD_VJ_NN]") AS YTD_AVG_PREIS_VJ, TRY_CONVERT(float, "[Measures].[Durchschnittspreis_NN]") AS YTD_AVG_PREIS_AJ, TRY_CONVERT(float, "[Measures].[VV923_VJ]") AS UMSATZ_VJ, TRY_CONVERT(float, "[Measures].[VV923]") AS UMSATZ_AJ, TRY_CONVERT(float, "[Measures].[VV923001]") AS UMSATZ_PLAN, TRY_CONVERT(float, "[Measures].[VV923_VJ]") AS YTD_UMSATZ_VJ, TRY_CONVERT(float, "[Measures].[VV923]") AS YTD_UMSATZ_AJ, TRY_CONVERT(float, "[Measures].[VV923001]") AS YTD_UMSATZ_PLAN FROM OPENQUERY( [DATSA8151_OLAP], ' WITH MEMBER Durchschnittspreis_NN AS IIF([Measures].[Durchschnittspreis]=1/0 OR [Measures].[Durchschnittspreis]=-1/0,NULL,[Measures].[Durchschnittspreis]) MEMBER Durchschnittspreis_YTD_VJ_NN AS IIF([Measures].[Durchschnittspreis_YTD_VJ]=1/0 OR [Measures].[Durchschnittspreis_YTD_VJ]=-1/0,NULL,[Measures].[Durchschnittspreis_YTD_VJ]) SELECT { [Measures].[Garnituren_VJ] AS MENGE_VJ, [Measures].[Garnituren] AS MENGE_AJ, [Measures].[PlanGarnituren] AS MENGE_PLAN, [Durchschnittspreis_NN] AS AVG_PREIS_AJ, [Durchschnittspreis_YTD_VJ_NN] AS AVG_PREIS_VJ, [Measures].[VV923_VJ] AS UMSATZ_VJ, [Measures].[VV923] AS UMSATZ, [Measures].[VV923001] AS UMSATZ_PLAN } ON COLUMNS , NON EMPTY { { { [KNA1].[KUNNR].CHILDREN } * { [TVKO].[VKORG].&[NL10], [TVKO].[VKORG].&[PL10], [TVKO].[VKORG].&[GB10], [TVKO].[VKORG].&[FR10], [TVKO].[VKORG].&[IT10], [TVKO].[VKORG].&[AT10], [TVKO].[VKORG].&[SK10], [TVKO].[VKORG].&[CZ10], [TVKO].[VKORG].&[RO10], [TVKO].[VKORG].&[RU10], [TVKO].[VKORG].&[DE10], [TVKO].[VKORG].&[RU11], [TVKO].[VKORG].&[ES10] } * { [T179].[Parent].&[3], [T179].[Parent].&[414], [T179].[Parent].&[415], [T179].[Parent].&[441], [T179].[Parent].&[469], [T179].[Parent].&[472], [T179].[Parent].&[475], [T179].[Parent].&[483], [T179].[Parent].&[722], [T179].[Parent].&[835], [T179].[Parent].&[958], [T179].[Parent].&[960], [T179].[Parent].&[961], [T179].[Parent].&[974], [T179].[Parent].&[975], [T179].[Parent].&[984], [T179].[Parent].&[1010], [T179].[Parent].&[1025], [T179].[Parent].&[1033], [T179].[Parent].&[1071], [T179].[Parent].&[1122], [T179].[Parent].&[1178], [T179].[Parent].&[1182], [T179].[Parent].&[1194], [T179].[Parent].&[1204], [T179].[Parent].&[1208], [T179].[Parent].&[1270], [T179].[Parent].&[1281], [T179].[Parent].&[1284], [T179].[Parent].&[1290], [T179].[Parent].&[1297] } } } ON ROWS FROM [VertriebsDWH] WHERE ( [IC].[IC].&[0], [Zeit].[Year - Month - Date].[Year].&[2016], [Zeit].[Year Name].&[2016] ) CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL ' )
您需要在 T-SQL 作業步驟中打開帶引號的標識符。
SET QUOTED_IDENTIFIER ON;預設情況下，SQL Server 代理將引號視為
QUOTED_IDENTIFIERisOFF，這意味著引號中的項目被視為文字。轉動QUOTED_IDENTIFIERON將允許將這些項目視為列名。有關該問題，請參閱此MSDN 部落格條目。