Dynamic query with openrowset doesnt execute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic query with openrowset doesnt execute

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-06-19 : 22:55:24

In the query below, when I use the print statement the full query prints out as expected and I can pick it up and execute no problems. But if instead of printing it, I run it with EXEC, I get an error which
says incorrect syntax by taking some portion of the query and saying that it's an incorrect identifier, as if the executor just sees a partial query and not the full thing. As you can see, I am using varchar(max), which ought to fit the entire query string. Anyone have any ideas here? Thanks!

DECLARE @SQL VARCHAR(MAX)
SET @SQL =
'INSERT INTO #RESULT
SELECT CONVERT(VARCHAR,"[Member0].[MEMBER_CAPTION]") AS Zeroth,
CONVERT(VARCHAR,"[Member1].[MEMBER_CAPTION]") AS First,
CONVERT(VARCHAR,"[Member2].[MEMBER_CAPTION]") AS Second,
CONVERT(VARCHAR,"[Member3].[MEMBER_CAPTION]") AS Third,
CONVERT(VARCHAR,"[Member4].[MEMBER_CAPTION]") AS Fourth,
CONVERT(VARCHAR,"[Member5].[MEMBER_CAPTION]") AS Fifth,
CONVERT(VARCHAR,"[Member6].[MEMBER_CAPTION]") AS Sixth,
CONVERT(VARCHAR,"[Member7].[MEMBER_CAPTION]") AS Seventh,
CONVERT(MONEY,"[Measures].[MyMeasure]") AS Eighth
FROM OPENROWSET(''MSOLAP'',''DataSource=MyServer;Initial Catalog=Sales'' ,''
WITH MEMBER [Measures].[MyMeasure]
AS (SUM (StrToMember("[Trans Date].[Year - Quarter - Month - Date].[Month].&["+ Format(Now(),"yyyyMM") + "]").lag(12)
:StrToMember("[Trans Date].[Year - Quarter - Month - Date].[Month].&["+ Format(Now(),"yyyyMM") + "]").lag(1)
,[Measures].[Revenue]))
SELECT NON EMPTY([Measures].[MyMeasure]) on 0,
NON EMPTY({[Commission Category Current].[EP Business Line].[Business Line].members *
[Sales].[Product].members *
[Territory].[Territories].[Territory].members *
[Purchasing Site].[Customers].[Customer].members *
[Purchasing Site].[Cust ID].Children *
[Site].[Customers].[Customer].members *
[Site].[Cust ID].Children} *
[Territory].[Countries].[Territory RollUp].&[''' + @RollUp + ''']
) on 1 FROM SALES
)'''

DECLARE @SQL1 VARCHAR(MAX)= Replace(Replace(@SQL, '[''', '['), ''']', ']')

print @sql1

EXEC @SQL1

--PhB

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-20 : 08:15:53

What's in the variable @RollUp?

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-06-20 : 10:50:30

I fixed by breaking up the query in 4 substrings, then at the end just do

EXEC (@STR1 + @STR2 + @STR3 + @STR4).

I'm not sure how a varchar(max) doesn't accept a dynamic string that is clearly not greater than 1000 characters long. Unless the editor is adding hidden chars.

In any event, fixed.

--PhB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources