Results to File Problem with NULL values

Question

Results to File Problem with NULL values

No Idea

Say Hey Kid

Points: 675
More actions
August 4, 2009 at 10:26 am

#207796

I am in SQL Server 2005 in the Management Studio. When I change the query to be Results to File I am having problems with NULL values. Instead of having nothing where the NULL value should be the actual word "NULL" shows up. When I try to import this file into another program this is causing errors because it is expecting decimal values instead of a word. When I look at the data inside of SS 2005, the NULL values appear to be just NULL so the output should put nothing in place of it.
I have checked under Tools - Options, but could not see anything that changes this. I've not had any luck with a google search either.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i guess it depends on the program you are using; what happens if you find and replace NULL with a space or empty string in your result file prior to importing it? will your program import empty-string to decimal gracefully?

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!

No Idea Say Hey Kid Points: 675 More actions · Answer 2

I've had issues with the import. When it expects a decimal and the word "NULL" is there, the import completely stops. I was hoping there was a way to change the SS 2005 to not put the word "NULL" in the output.

When I used UltraEdit to change the word "NULL", I was getting an error about extra blank spaces at the end of a row. UltraEdit did not show any extra spaces anywhere.

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Since all the data is ultimately converted to string when exporting you should be to do something like this.

isnull(cast([YourFieldHere] as varchar(max)), '')

That way all your null values will just be an empty string.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/