How Query Analyzer treats Nulls

[MrJRW]

This one has me baffled....

When I use Enterprise Manager to show (query) records from a table, Nulls are displayed as <null> and the fields are undefined. O.K. so far so good

But when I use SQL Query Analyzer to show (query) the same records from the same table, Nulls are now displayed as NULL

If I save the SQL Query Analyzer results as a delineated text file, the Null fields are now defined with the literal 'NULL' (!)

Is this a known bug, er, feature of SQL Server ?

How do I keep my Nulls as Nulls when using Query Analyzer to select and export records ?

For the record, I'm using SQL Server 2000 build 8.00.818 (SP3),
SQL Query Analyzer version 8.00.194,

All running on Windows 2000 Server, build 5.00.2195 (Service Pack 4)

 

[Bygbobbo]

try exporting this...

select
null as temp1,
null as temp2,
isnull(null,'') as temp3,
isnull(null,'') as temp4

since its a text stream that is being written, instead of streaming a 'null' text it will be written as blank only when it is <null>

If thats not what you are looking for please let me know what kind of file you are trying to create (i.e. CSV, TXT, RPT, ect...)


--Bygs

 

[MrJRW]

In Query Analyzer, I'm just doing a Select * from a table

I'm creating a text file (*.txt) that tab delineated. I have some fields (Address, etc) that include commas as part of the literal.

I'm then importing that text file into Access.

My question is why doesn't Analyzer just show fields that are null as <null> and when saving as a text file, the field(s) remain undefined.

 

[hneal98]

Why don't you just import the table you need directly from SQL to Access? Or if you don't need the whole table, you can just use a pass through query to get what you do need? If you are needing data from several tables, create a temp table (not the #tmp), and then just import that into Access?

 

[MrJRW]

Well, actually, that's what I'm doing now. I wrote a DTS package to do the transfer for this situation.

However, the bigger question is why QUERY ANALYZER is defining fields with literals ('NULL') that should stay undefined ?

 

[hneal98]

Do you mean that Query Analyzer is putting NULLS in fields where they should not be?

 

[MrJRW]

Yes !!
Please re-read my original presentation of the facts.

QA is &quot;entering&quot; the literal 'NULL' on grid results as well as for exports !

 

[hneal98]

To my knowledge, it would only do that if the field is NULL. Can you post your query so we can see what is happening?

 

[MrJRW]

My query is just a select * from the table in question

Try using QA to query a table with <nulls>. Then export the results to a text file. For a CSV file, I'm getting ...,'NULL',... in stead of just ...,,...

 

[hneal98]

I got it. Ok. Are there very many fields? if not, you could do something like this: isnull(FieldName,'') in your select statement. Actually, if you are not lazy like me, you could do that for all fields regardless of how many there are. What that would do is replace the null with a blank. By the way, on this post it looks like there is a space between the two single quotes, but there is nothing between them.

Hope this helps.

 

[MrJRW]

...space between the two single quotes (?) I think you mean two single commas ?

I'm curious. Could you try and duplicate my results ?

I wonder if I've discovered a new bug, er, feature of QA

 

[hneal98]

I don't know what all of your settings are for QA. But did this solution work for you?

 

[hneal98]

<<...space between the two single quotes (?) I think you mean two single commas ? >>

No, in my post, I put a '' in the example. I wanted you to know that I did not put a space between them. That would have looked like this ' '.

 

[MrJRW]

Right now I'm doing something else. I'll try tomorrow.

 

[Bygbobbo]

Like I mentioned early when you use the utility to write to a file in Query Analyzer, it uses a text stream. So it is taking everything that is literal and writing it to the .txt.

There is a way to turn off the NULLs in the query analyzer settings tab as a setting but I dont recommend it.

DTS is most likely the best or instead of select *
write out all the field names with the isnull(<fieldname>,'') as I wrote before.
'' = 2 single ticks

The example I wrote tests both scenarios when writing to a file in query analyzer.

Bygs