distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

User, date Message
Written by GoranP.se
9 months ago
Category: General
3 posts since Fri, 29 Nov 13
Hello.

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.

Rgs
Göran
Written by jfalchMoney, Euro
9 months ago
388 posts since Sat, 17 Oct 09
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; ?
Written by GoranP.se
9 months ago
3 posts since Fri, 29 Nov 13
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 ??

Written by khanunMoney, Euro
7 months ago
5 posts since Tue, 03 Dec 13
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 :-)
Written by jfalchMoney, Euro
7 months ago
388 posts since Sat, 17 Oct 09
does not offend heidisql, but the mssql server. heidisql just passes your query text to the server, and shows the results coming back.
Written by khanunMoney, Euro
7 months ago
5 posts since Tue, 03 Dec 13
..and why does the MS SQL Management Studio not fail, then, if that is the case?
Written by jfalchMoney, Euro
7 months ago
388 posts since Sat, 17 Oct 09
it filters out the ';' by itself before sending to server ?
Written by khanunMoney, Euro
7 months ago
5 posts since Tue, 03 Dec 13
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.
Written by GoranP.se
7 months ago
3 posts since Fri, 29 Nov 13
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.