Get "SQL Error (137)" when running a query ...

GoranP.se posted 4 years ago in General

I'm connected to a MS SQLExpress 2012.
When I try to run a Query that contains a scalar variable, I get this message SQL Error (137).
Example query
declare @OrderNo varchar(20);
Set @OrderNo = '7124476';
select * from tblPaket 
where OrderNr = @orderNo;

If I try to run it in for example SQL Magnament Studio so it OK.
Even if I run in my report, (Fast-Report), so it working.

Why could I not run this type of questions in HeidiSQL?
Please tell me what I'm doing wrong.
Otherwise I like this superb tool.

jfalch posted 4 years ago
137 is "must declare variable"; possibly variable names are case sensitive ? @orderNo in WHERE not same as @OrderNo in declare ? what happens if you change this to where OrderNr = @OrderNo; ?
GoranP.se posted 4 years ago
Thank's for the Idea.
I tested that once time more, but it was not the solution.

Anyhow, I have "google'd" around and find something interesting, at link http://www.sql-server-helper.com/error-messages/msg-137.aspx. There they says, the "lifetime of the variable is to next GO.

In my case, in the script above, I used ; as limit.
I changed the script, take the , away, and EUREKA, it work even in HeidiSQL, see my example below.

declare @OrderNo varchar(20)
Set @OrderNo = '7124476'
select * from tblPaket 
where OrderNr = @OrderNo;

The my final question, what's the difference in the execution in MS SQL Magnament Studio and HeidiSQL?
Is it possible to have them more equal in this case ??

khanun posted 4 years ago
I applied the "fix" of removing the offending ";" from my declare statement - and it failed again. Inspecting my code revealed that every line I ended with ";" offended HeidiSQL, so removing the lot made my day :-)
jfalch posted 4 years ago
does not offend heidisql, but the mssql server. heidisql just passes your query text to the server, and shows the results coming back.
khanun posted 4 years ago
..and why does the MS SQL Management Studio not fail, then, if that is the case?
jfalch posted 4 years ago
it filters out the ';' by itself before sending to server ?
khanun posted 4 years ago
Yeah, that is one possibility, of course, but if that is the case, I need to know what happens on "GO" statements. My clear wish for Heidi SQL is for it to be as close to a drop-in replacement as possible, saving me the need to run two consoles. Heidi is great for me both for its speed, the tabs you get when running multiple queries, and because it is the best thing happening to MariaDb since they forked from MySQL.
GoranP.se posted 4 years ago
I have done som more test's. I looked at http://technet.microsoft.com/en-us/library/ms188332.aspx[/url] and found something interesting.

If I write my code from top of this thread like this:
execute ('declare @OrderNo varchar(20); Set @OrderNo = ''7124476''; select * from tblPaket where OrderNr = @OrderNo;')

then it works even in HeidiSQL. As You see, then we are able to use the ; in the sqript.

I'm interested why it act's like this ...

Please login to leave a reply, or register at first.