0

I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server.

I have created a linked server from my SQL Server instance to my SSAS instance. I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models.

My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name...".

E.g.

UPDATE temp
SET temp.[Current Contract Count] = cte.[Contract Count]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey]

The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone].

I may well be missing a simple trick here?

How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?

1

If a column name has brackets, you need to escape them. You can see how if you use function QUOTENAME().

SELECT QUOTENAME('[awful column name]') 
-- Result: [[awful column name]]]

Note the extra closing brackets. You should try adding an extra pair of enclosing brackets together with an additional closing one for your column names that include brackets.

Assuming that your problem is tabular_cte's columns:

UPDATE temp
SET temp.[Current Contract Count] = cte.[[Contract Count]]]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[[Copy of Service Zone Code]]] = temp.[ServiceZoneAlternateKey]
  • Thanks, I had already tried QUOTENAME(), but I hadn't tried including the brackets. Sadly neither worked. As it happens, I had also tried escaping the column names, which produced error "An expression of non-boolean type specified in a context where a condition is expected, near ';'". However, in your example you suggested to escape the column names with an additional ]. I did this, but got a conversion error (from ntext to varchar). After using CAST on the joining column, it all worked! Thanks. – DimUser Jul 2 '18 at 15:12

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.