Copying from SQLSMS grid, pasting in Excel, the word "NULL" is pasted!

I actually had to handhold them in packaging SSMS 2008R2 - took about 9 months to finally get it - and of course they've packaged none of the SP's since then.

We have a very backwards IT department. When I first started using Access 2007 they still had SP 0 packaged - despite having SP 2 for the rest of Office. It took months to get that resolved too, and even that they messed up.

Trying to get them to approve and package something nonstandard that very few people use would take years at best and most likely just waste time. I'd be better off risking someone in IT's job by convincing them to grant me temp admin rights and install it myself...

Incidentally Teradata SQL Assistant does allow you to replace null values with whatever you want to replace them with in its grid - it defaults to a ? when first installed.

Found it on MS Connect - unfortunately it's been closed as won't fix (although allegedly will be kept in mind...)

http://connect.microsoft.com/SQLServer/feedback/details/751948/grid-results-option-to-not-copy-export-the-word-null

The workarounds are SSMS Boost, Find/Replace or use ISNULL; all 3 of which I think we've covered here.

The only other 'fix' I can think of is to create views on the tables with the ISNULL's in, at least you'd only need to do it once then.

Although from the sound of it you might have trouble getting anyone in IT to do that if you don't have the rights yourself.

+1 for Luis' post. The query results grid in SSMS was never intended as an end-user reporting tool; there are separate purpose-built tools for that. I use the SSMS grid all the time during development to check the behaviour of queries.

Here is the bottom line and overarching issue (which a previous post touched on): NULL has a distinct meaning and use in the database-world. In the reporting-world, NULL has no meaning nor use.

It is sad that people who design and architect products in the database industry don't take the time to understand what the reporting-world needs, even though that is one of the most fundamental and primary purposes for their database product (to provide human-consumable data). And it is inappropriate, intrusive, and arrogant that they force their database-world concepts into and onto the reporting-world.

When you do a SELECT and the characters NULL are returned in the results set where there is nothing, erroneous content has been placed in the results set. In other words, since letters have been placed where the source didn't have them, the process has altered the results set.

It is cute that they give a light-yellow background as a visual cue that the NULL is not a real value or not the real contents. But visual cues don't impact the data shown and doesn't remove the four letter word NULL when the data is used. There are many discussions about how to treat NULL to avoid erroneous results in a database-world context, but when it arrives in the data values in a reporting-world context, as NULL, it is erroneous/incorrect/invalid results content ... period.

There are simple, meaningful methods that could, and one or more should, be used to appropriately handle and bridge the difference between the database-world and the reporting-world when something is NULL:

• Display nothing (no characters/numbers/symbols of any kind) and make the background a latticed or tightly hashed or "grayed-out" to better imply that there's nothing there.

• Display the word NULL italicized, but in a separate display layer that is not captured into the Clipboard by a Copy event.

• Even though a DISPLAY of the data may somehow indicate NULL, NEVER add those characters into ANY other form of output (file, query results, etc.).

• Create a new industry standard where some new symbol or icon is displayed in database products to mean NULL, but that is either omitted when output to anything but the screen or is a symbol/icon/unprintable-character that is ignored or not importable by other products.

• Give users a database setting and/or a display option and/or a query option/flag to turn off the display of the word NULL for output, whether to the screen or to a table.

bcp omits NULL, but also omits text-qualifiers, even if forced into the results using QUOTENAME() or '"' + [fieldname] + '"'. CORRECTION: bcp DOES keep the text-qualifiers!!

sqlcmd keeps the text-qualifiers, but returns the word NULL in the results set, as does a SELECT to the screen in SSMS.

Excel import (Data, External, SQL Server) omits NULL and sees and handles content within the text-qualifiers correctly (Yay!). But Excel is the process-initiator and end users must have a connection and read permissions into the database and table/view.

But for SQL Server process-initiated output (such as .csv, tab-delimited .txt), that still leaves a void (or shall we say, a NULL!?). CORRECTION: See bcp above.

The funny thing is you complain that the string NULL is returned. This is not actually true. What you are describing is how various front end and other types of third party applications handle NULL. The issue is that you want all these other applications do deal with it a certain way. The bottom line is that in the reporting world you have to decide how to deal with NULL.

I think having a setting in SSMS is a good idea, but again, that is the application and NOT the DBMS. The DBMS does and should always return NULL when there is no value. The issue is not there is a problem with the DBMS, the issue for you is how SSMS handles NULL.

Returning the four characters "NULL" is NOT the same as returning something that indicates that the content is NULL or that there is nothing to return. A DBMS should NEVER subsitute characters for content that is actually something different (in this case, nothing) ... EVER. It is fine that it needs to indicate it, but subsituting character content is flat out wrong. 🙂

The DBMS is NOT returning characters. It is how SSMS interprets the NULL in its display (the yellow background with NULL). I agree 10000% that is never acceptable to replace a value with something. But when you have a grid of values how would you prefer that SSMS indicate a NULL? The grid is 100% text based just like any other application that has a grid. Would you prefer a NULL be left as an empty string? That goes directly against your comment about replacing a value. You wouldn't be able to tell which rows are NULL and which are empty.

That's a great example. Some weeks ago, I had a problem because Toad for Oracle shows nulls and empty strings exactly the same way and being used to see a difference between those two on SSMS, I thought that the value was indeed an empty string. I spent half an hour wondering why my filter didn't work until I changed the code from " = '' " to " IS NULL ".

When I did some reports in VFP6, it showed "(empty)" instead of NULL. The point of this is to reinforce Sean's point. You shouldn't blame the DBMS, you could blame the reporting tool.

The amazingly annoying thing to me is they didn't fix this in 2012 (I see it as a bug, not a feature or acceptable way of doing things).

What "fix" would you like? As I have said previously putting anything other than the word NULL in the grid cell is changing the data. The only truly viable option I have heard of is to allow a setting for text replacement of NULL. The default would be "NULL" but the user could change that. Maybe even have only 2 options NULL and empty string.

ALT + E + F + P does wonders. 😛