Handling OLE DB Square Brackets: Quick Tip

Jim Knicely authored this blog. Many Microsoft products, including SSAS, can connect to Vertica using the Vertica OLE DB driver for Windows which is installed as part of the Client Drivers and Tools for Windows. Unfortunately tools like MS SSAS can include square brackets to qualify identifiers in the queries it generates to run in Vertica. Example: SELECT DISTINCT [SSAS_DIM_TIME].[CAL_DATE_YEAR] AS [SSAS_DIM_TIMECAL_DATE_YEAR0_0] FROM [DIM_SCHEMA].[SSAS_DIM_TIME] AS [SSAS_DIM_TIME]; Vertica does not play nice with these brackets. To overcome this incompatibility, you can set the OLE DB driver parameter ConvertSquareBracketIdentifiers to “True”. This parameter controls whether square-bracket query identifiers are converted to a double quote identifier for compatibility when making queries to a Vertica database. After changing the parameter to “True”, the above query is converted to the following when executed in Vertica: SELECT DISTINCT "SSAS_DIM_TIME"."CAL_DATE_YEAR" AS "SSAS_DIM_TIMECAL_DATE_YEAR0_0" FROM "DIM_SCHEMA"."SSAS_DIM_TIME" AS "SSAS_DIM_TIME"; Have Fun!