Why Query Analyzer not able to detect missing column in table for Sql2k

Question

Why Query Analyzer not able to detect missing column in table for Sql2k

Vladimir Sim

Ten Centuries

Points: 1310
More actions
March 4, 2007 at 10:07 am

#175501

Hi,
Recently, we have some table structure changes that having some columns
drop or change.
However, QA of SQL2k sometimes will detect the missing columns and
flag the errors when we press F5. In some cases, there is not error
flagged by QA.
We need QA to help us identify any script for any error.
What is wrong, why and any fixes for this issue.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's a difference between the Parse Query Function and the Execute Query;

Parse Query (the check mark button) validates the query for syntax violations...it does not actually check if the objects exist in sysobjects or anything; that is left to the Run Query:

test it yourself:

Parse this statement, then run it:

select bob,tom from faketable

Parsing returns Query batch completed, and running it raises an error Invalid object name 'faketable'.

If you are saying that you really ran the query(F5), then I would guessthe issue could be you changed one database, and it worked, but re-ran the query on another db that was not yet changed)

In rare situations, the server might be servicepack 4, but the client tools you are using are not, and some unexpected behavior might occur.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 2

Hi,

Thank for the advise.

1. When I edit the SP, it will default to 'Alter stored procedure xxx', so when I

press F5, it should parse and apply the new code. But it also didn't flag any errors

of the missing columns in some tables.

Before that, my QA is working fine. It can flag any missing columns for me.

2. We are using SQL2k SP4, but how do I know that client tool are not using it ?

Anyway to check ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes... if you select @@version, you would most likely see this:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

May 3 2005 23:18:38

Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

if you check any of the .exe's or .dll's in the Program Files\Microsoft SQL Server\80\Tools\Binn folder,

they should all be labeled version 2000.80.2039.0 if they were SP4, if they are a lesser number, you know you might want to upgrade the client tools.

do you see the wierd behaviour when connecting to just one server, or all servers?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

Running the code to create a stored procedure is not the same as running a script... the stored procedure will allow for "deferred naming" until runtime. This is so you can create procs before you create the tables or columns they will reference (who does THAT?). The procs will, of course, fail when run if the tables or column names are not present.

I've never understood this type of problem that people have... I write scripts, make sure they work by unit testing, THEN convert it to a proc and unit test again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 5

thanks Jeff I should have thought of that...he never said "procedure" so i assumed he was talking about ad hoc queries.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!