2

I'm struggling with my SQL Job Agent interpreting a quotation mark " as an apostrophe '.

I have a T-SQL query which works in SQL Server Management Studio but not when I insert the exact same statement into a SQL Server Agent Job as T-SQL Script Step.

To understand the following T-SQL statement, it might be helpful if I point out what should be done. In the first place I created an MDX Query to the Data Warehouse Cube. This query fetches the product use of each customer which should be imported to our CRM.

This MDX query runs then in an OPENQUERY Statement where the results are "manipulated" and converted to the correct data types.

The result of this is then inserted into a table in a linked server.

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')

The query runs fine in SQL Server Management Studio and brings the expected result.

Measures in the MDX are addressed with Quotation Marks outside of the OPENQUERY, for an example:

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]
')

But when I copy this SQL Statement into a SQL Server Agent Job it will interpret CONVERT(float, "[Measures].[average_price]") as CONVERT(float, '[Measures].[average_price]'). It is obvious that it cannot convert the text '[Measures].[average_price]' into a float, but why does the SQL Server Agent try to do so? How can I prevent it from interpreting the Quotation Marks as Apostrophes?

please ask if you don't understand any part of it.

If you need more information about the whole query here it is:

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 ' )
0
2

You need to turn quoted identifiers on in the T-SQL job step.

SET QUOTED_IDENTIFIER ON;

By default, SQL Server Agent treats quotation marks as if QUOTED_IDENTIFIER is OFF, which means items in quotes are treated as literals. Turning QUOTED_IDENTIFIER ON will allow those items to be treated as column names.

See this MSDN blog entry about the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.