SQL2000 vs SQL2005 Query Results (File) for Null Values

Last Post 13 Aug 2008 01:14 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
jloder
New Member
New Member

--
04 Apr 2008 07:04 AM
In SQL2000 when results are sent to a file from the Query Analyzer, null data is nothing (followed by the delimiter). In SQL2005 null data is respresented by the literal "NULL".

Does anyone know why microsoft made this change?

Does anyone know if there is a configuration or other varible that will allow NULL values to be exported as it was in SQL2000?

Yes, I know I can force it to export a '' using the "isnull" function, but who wants to do that. The vendors to whom we have been exporting NULL data as actual NULL data for years do not appreciate having to change all of their software.
jloder
New Member
New Member

--
09 Apr 2008 08:21 AM
SQL Server 2000 would have exported a Null Value as ,, (assuming "," is the delimiter) which would be interpretted as Null when imported by another system; if it was exporting it as an "empty string, it would be ,'', which would be interpreted as an empty string by the importing program. SQL Server 2005 exports Null values as ,'NULL'.

I know there are many ways to export data including those mentioned by both responders to this post, but my inquiry concerns why MicroSoft changed the manner in which Query Analyzer exported the data to a file.

Thanks for your response
Irineas
New Member
New Member

--
08 Aug 2008 05:45 AM
quote:

Originally posted by: gunneyk
I don't think anyone here can answer that question definitively. You would have to ask the developer of the tool. My guess is that it was due to the difference in the underlying architecture and framework of the new .net based tools. SSMS in general was a rewrite and had to conform to a lot of the restrictions of Visual Studio and a lot of the codeing was done by non-database savy people. This is just one reason why the current version is less than optimal. And who ever wrote that portion most likely never used that functionality previously and never realized it was different. I looked again and don't see a configuration option to change the behavior. So it appears you will need to rethink your process.



I more than agree wiht this.
If you are able to, allways put down a value like a "zero" in stead of leaving stuff infinnity null.
And further, if you can code, then the statement in the tool should be "if null than nothing, nothing Or if null than 0 (zero)" will do the trick.
Just a matter of codeing and common sense.
SQLUSA
New Member
New Member

--
13 Aug 2008 01:14 AM
If you use the SSIS Import/Export Wizard to export data into a csv file, it will not put in NULLs.

Let us know if helpful.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/


Acceptable Use Policy
---