March 19, 2008 at 9:46 am
Hi guys,
I for one find it rather confusing when I have to concatenate string like in the query below:
SELECT * INTO ##tmpTbl FROM OPENQUERY(TEST, 'SELECT
[Test 1] AS Test1, [Test 2] AS Test2,''[Sum of'' + @month +'']'' AS MonthlyAmount FROM [TESTfor sheet1$]')
@month is a sp parameter being passed in.
When I run this query the resultset actually selects [Sum of @month] for the MonthlyAmount column. Instead I want to end up with something like this as soon as I can figure out how to correctly concatenate the string:
SELECT * INTO ##tmpTbl FROM OPENQUERY(TEST, 'SELECT
[Test 1] AS Test1, [Test 2] AS Test2,[Sum of JAN] AS MonthlyAmount FROM [TESTfor sheet1$]')
For some reason I get very confused when I have to deal with string concatenation like in the above scenario. Is there any logically way that could help me not getting lost when dealing with this or is it just me? 😀
March 19, 2008 at 2:07 pm
Like this:
SELECT * INTO ##tmpTbl FROM OPENQUERY(TEST, 'SELECT
[Test 1] AS Test1, [Test 2] AS Test2,[Sum of' + @month +'] AS MonthlyAmount FROM [TESTfor sheet1$]')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 12:47 am
No sorry that is still incorrect.
Incorrect syntax near '+'.
That is what I initially did too.
Can anybody else perhaps help me out on this one?
Thanks.
R
March 20, 2008 at 5:47 am
Try this so you can verify the SQL....
declare @SQL varchar(500)
set @SQL = 'SELECT [Test 1] AS Test1, [Test 2] AS Test2, [Sum of ' + @month + '] AS MonthlyAmount FROM [TESTfor sheet1$]'
select @SQL --so you can see what the SQL string actually is
--comment out the final select until you resolve the syntax issue else you can't see the variable above
--SELECT * INTO ##tmpTbl FROM OPENQUERY(TEST, @SQL)
Work with the syntax until the output of @SQL is what you need it to be. The syntax showed by the other poster should work...you can fine tune it by displaying it as above. This should help you build these strings in the future because you can see what you are getting, rather than just guessing at it.
Also, if OPENQUERY behaves like EXEC does, I find that I have to build the query string in a variable, and then use the variable in the function (like I showed above). When I try to build the string inside the function, it gets angry.
Hope this helps!
If it was easy, everybody would be doing it!;)
March 20, 2008 at 6:55 am
Hi,
Thanks for getting back to me.
I did exactly as you recommended, and the sql does infact return exactly what it should.
However, the final step in which I pass the param to OPENQUERY I get the following error refering to the piece of sql below:
SELECT * INTO ##tmpTbl FROM OPENQUERY(TEST, @SQL)
Incorrect syntax near '@SQL.
March 20, 2008 at 7:15 am
Have you tried to run the SQL (select blah, blah, blah, etc) directly on the linked server?
If it was easy, everybody would be doing it!;)
March 20, 2008 at 7:23 am
Yes the query run just fine when I hard-coded the value of @month in my original OPENQUERY query.
What am I doing wrong here? :hehe:
March 20, 2008 at 7:28 am
Don't hardcode it, take the output of 'SELECT @SQL' and run it on the linked server to check to see if the SQL syntax is correct (because that is the SQL that the OPENQUERY is going to try to run).
If it was easy, everybody would be doing it!;)
March 20, 2008 at 7:36 am
Ok, I did that and yes the query run perfect with the output of SELECT @SQL.
March 20, 2008 at 7:41 am
OK, I just read on BOL...
'OPENQUERY does not accept variables for its arguments.' (sorry, I missed that earlier)
So, now that you know that the SQL is good, put your string back into OPENQUERY and see what happens.
If it was easy, everybody would be doing it!;)
March 20, 2008 at 7:47 am
Ahh great 🙁
I just replace the query string back into the OPENQUERY and still it give me:
Incorrect syntax near '+'.
March 20, 2008 at 8:01 am
ryno (3/20/2008)
Ahh great 🙁I just replace the query string back into the OPENQUERY and still it give me:
Incorrect syntax near '+'.
whatever is being passed to openquery must be a static string or you will get an error. So - you aren't wrapping enough into your dynamic SQL. You have to put the OPENQUERY statement in there, too.
(air-code - i probably whiffed the number of ' needed in there)
declare @SQL varchar(2000)
set @SQL = 'SELECT * INTO ##tmpTbl FROM OPENQUERY(TEST,''SELECT [Test 1] AS Test1, [Test 2] AS
Test2, [Sum of ' + @month + '] AS MonthlyAmount FROM [TESTfor sheet1$]'')'
Exec @SQL
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 8:01 am
Ok, seems OPENQUERY has an issue with concatentation.
Here is a solution...see this link...it explains your problem exactly.
You have to actually build the entire SQL, including OPENQUERY, the insert, etc in a string, and then execute the entire thing in EXEC.
Good luck! 🙂
If it was easy, everybody would be doing it!;)
March 20, 2008 at 8:04 am
Yep, what Matt said. I posted a few seconds after he did, so I didn't see his post.
This is what the link I sent earlier was explaining as well.
This was an interesting exercise!
If it was easy, everybody would be doing it!;)
March 20, 2008 at 8:13 am
😀 Oh YAY!!!
Thanks guys, this has been buggin me soooo much.
If I think back now I can recall that someone also showed me this technique before, a bit to late now I suppose.
Once again thanks for all the replies guys.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply