Thread: [Squirrel-sql-users] Query runs only when all of its SQL is selected
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Alex M. <ale...@wo...> - 2013-11-21 20:10:28
|
I have a query that runs against MS SQL 2012 only when all of its SQL is selected. If I tried to run it with no selection, it would throw and error: Error: The multi-part identifier "pid.PI_LINE_NUMBER" could not be bound. SQLState: S0001 ErrorCode: 4104 What am I doing wrong? Thanks Alex |
From: Alex M. <ale...@wo...> - 2013-11-21 22:25:53
|
I did slightly more testing with the same query. The error goes away if I put the caret within the first few declaration and variable assignment lines of the query: DECLARE @Program VARCHAR(10) ,@PartNumber VARCHAR(10) ,@CompNumber VARCHAR(10) SELECT @Program = 'P415' ,@PartNumber = 'abc' ,@CompNumber = 'xyz' But as soon as I move the caret onto the query's 1st line containing SELECT or further down, the error returns. Hoping that helps Thank you Alex From: Alex Malmyguine [mailto:ale...@wo...] Sent: Thursday, November 21, 2013 3:10 PM To: squ...@li... Subject: [Squirrel-sql-users] Query runs only when all of its SQL is selected I have a query that runs against MS SQL 2012 only when all of its SQL is selected. If I tried to run it with no selection, it would throw and error: Error: The multi-part identifier "pid.PI_LINE_NUMBER" could not be bound. SQLState: S0001 ErrorCode: 4104 What am I doing wrong? Thanks Alex |
From: John H. <jh...@im...> - 2013-11-21 23:15:32
|
On Thu, 21 Nov 2013, Alex Malmyguine wrote: > I have a query that runs against MS SQL 2012 only when all of its SQL is selected. > If I tried to run it with no selection, it would throw and error: > > Error: The multi-part identifier "pid.PI_LINE_NUMBER" could not be bound. > SQLState: S0001 > ErrorCode: 4104 > > What am I doing wrong? That usually happens when there are blank lines embedded in the query. -- John Hardin KA7OHZ http://www.impsec.org/~jhardin/ jh...@im... FALaholic #11174 pgpk -a jh...@im... key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 ----------------------------------------------------------------------- Vista is at best mildly annoying and at worst makes you want to rush to Redmond, Wash. and rip somebody's liver out. -- Forbes ----------------------------------------------------------------------- 539 days since the first successful private support mission to ISS (SpaceX) |
From: John H. <jh...@im...> - 2013-11-22 16:11:18
|
Please keep the conversation on-list unless confidential information is discussed, so that others may benefit. On Fri, 22 Nov 2013, Alex Malmyguine wrote: > Lots of comments, but no blank lines. > Blank lines in queries should be all right, should they not? The parser in Squirrel considers them delimiters of query blocks (possibly depending on the SQL platform you're using). They are syntactically fine for the SQL engine, but Squirrel will only read the text around the caret up to the first blank line before and after. If your SQL has embedded blank lines, you need to explicitly select it all to get Squirrel to pass all of it to the database. That's why I suggested this possibility. The error indicates a syntactically-incorrect SQL query, and you said if you highlight it all it works, so there's the possibility Squirrel was passing only part of the query to the database, and blank lines are a common cause for that. This is particularly vexing if you have something like this in your query editing screen: DELETE FROM customer WHERE customer_id = :cid; :) As for your later expansion on the situation: that's very odd. If Squirrel was splitting the SQL between the definitions and the query, I'd expect to see an error about an undefined variable, not an error about not being able to bind a multi-part identifier... I'd suggest using SQL Profiler to trace what Squirrel is actually sending to the engine based on where the caret is when you execute the query. That might give a clue about what's happening. > -----Original Message----- > From: John Hardin [mailto:jh...@im...] > Sent: Thursday, November 21, 2013 6:15 PM > To: Alex Malmyguine > Cc: squ...@li... > Subject: Re: [Squirrel-sql-users] Query runs only when all of its SQL is selected > > On Thu, 21 Nov 2013, Alex Malmyguine wrote: > >> I have a query that runs against MS SQL 2012 only when all of its SQL is selected. >> If I tried to run it with no selection, it would throw and error: >> >> Error: The multi-part identifier "pid.PI_LINE_NUMBER" could not be bound. >> SQLState: S0001 >> ErrorCode: 4104 >> >> What am I doing wrong? > > That usually happens when there are blank lines embedded in the query. -- John Hardin KA7OHZ http://www.impsec.org/~jhardin/ jh...@im... FALaholic #11174 pgpk -a jh...@im... key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 ----------------------------------------------------------------------- Think Microsoft cares about your needs at all? "A company wanted to hold off on upgrading Microsoft Office for a year in order to do other projects. So Microsoft gave a 'free' copy of the new Office to the CEO -- a copy that of course generated errors for anyone else in the firm reading his documents. The CEO got tired of getting the 'please re-send in XX format' so he ordered other projects put on hold and the Office upgrade to be top priority." -- Cringely, 4/8/2004 ----------------------------------------------------------------------- 540 days since the first successful private support mission to ISS (SpaceX) |
From: Alex M. <ale...@wo...> - 2013-11-22 16:21:28
|
For now I would not be able to profile that query on the server as I have no SA rights. If this re-occurs with any of queries I can run on my own instance, I'll remember to provide the trace. The issue for sure occurs when there are no blank lines at all. However, if I insert more 1 blank line between declaration/variable assignment and SELECT while keeping the caret on the 1st blank line, than the query works w/o selecting. 2+ blank lines is enough. Thank you Alex -----Original Message----- From: John Hardin [mailto:jh...@im...] Sent: Friday, November 22, 2013 11:11 AM To: Squirrel SQL users list Subject: Re: [Squirrel-sql-users] Query runs only when all of its SQL is selected Please keep the conversation on-list unless confidential information is discussed, so that others may benefit. On Fri, 22 Nov 2013, Alex Malmyguine wrote: > Lots of comments, but no blank lines. > Blank lines in queries should be all right, should they not? The parser in Squirrel considers them delimiters of query blocks (possibly depending on the SQL platform you're using). They are syntactically fine for the SQL engine, but Squirrel will only read the text around the caret up to the first blank line before and after. If your SQL has embedded blank lines, you need to explicitly select it all to get Squirrel to pass all of it to the database. That's why I suggested this possibility. The error indicates a syntactically-incorrect SQL query, and you said if you highlight it all it works, so there's the possibility Squirrel was passing only part of the query to the database, and blank lines are a common cause for that. This is particularly vexing if you have something like this in your query editing screen: DELETE FROM customer WHERE customer_id = :cid; :) As for your later expansion on the situation: that's very odd. If Squirrel was splitting the SQL between the definitions and the query, I'd expect to see an error about an undefined variable, not an error about not being able to bind a multi-part identifier... I'd suggest using SQL Profiler to trace what Squirrel is actually sending to the engine based on where the caret is when you execute the query. That might give a clue about what's happening. > -----Original Message----- > From: John Hardin [mailto:jh...@im...] > Sent: Thursday, November 21, 2013 6:15 PM > To: Alex Malmyguine > Cc: squ...@li... > Subject: Re: [Squirrel-sql-users] Query runs only when all of its SQL > is selected > > On Thu, 21 Nov 2013, Alex Malmyguine wrote: > >> I have a query that runs against MS SQL 2012 only when all of its SQL is selected. >> If I tried to run it with no selection, it would throw and error: >> >> Error: The multi-part identifier "pid.PI_LINE_NUMBER" could not be bound. >> SQLState: S0001 >> ErrorCode: 4104 >> >> What am I doing wrong? > > That usually happens when there are blank lines embedded in the query. -- John Hardin KA7OHZ http://www.impsec.org/~jhardin/ jh...@im... FALaholic #11174 pgpk -a jh...@im... key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 ----------------------------------------------------------------------- Think Microsoft cares about your needs at all? "A company wanted to hold off on upgrading Microsoft Office for a year in order to do other projects. So Microsoft gave a 'free' copy of the new Office to the CEO -- a copy that of course generated errors for anyone else in the firm reading his documents. The CEO got tired of getting the 'please re-send in XX format' so he ordered other projects put on hold and the Office upgrade to be top priority." -- Cringely, 4/8/2004 ----------------------------------------------------------------------- 540 days since the first successful private support mission to ISS (SpaceX) ------------------------------------------------------------------------------ Shape the Mobile Experience: Free Subscription Software experts and developers: Be at the forefront of tech innovation. Intel(R) Software Adrenaline delivers strategic insight and game-changing conversations that shape the rapidly evolving mobile landscape. Sign up now. http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk _______________________________________________ Squirrel-sql-users mailing list Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |