Copying from SQLSMS grid, pasting in Excel, the word "NULL" is pasted!
-
Easy to fix this - make it an setting that one can change in options. That way DBA purists can see their NULL text and people who have to actually produce reports that don't say "NULL" everywhere can tell it to go away permanently.
You could even easily cover both by just having the NULL value not be copied when you do a copy/paste...or have it represented by a visual cue of some sort that isn't copied along with the actual data.
Leaving it as is is lazy and a huge mistake on the part of Microsoft.
-
As I mentioned a while back - I deal with extremely large ad-hoc reports that often have LOTS of null values in them. When you're trying to do a copy/replace of several million NULLs in an Excel spreadsheet that's several dozen meg, it takes forever and often can and has crashed Excel. It's a regular recurring major issue for me.
And when you're creating a one-off report you really don't want to go to the effort of adding isnull() functions on every single one of the 50 or 100 columns you're dealing with.
Incidentally Teradata has exactly what I mentioned - an optional setting to change how NULL values are represented in the grid.
-
sleipner (8/13/2014)
Easy to fix this - make it an setting that one can change in options. That way DBA purists can see their NULL text and people who have to actually produce reports that don't say "NULL" everywhere can tell it to go away permanently.You could even easily cover both by just having the NULL value not be copied when you do a copy/paste...or have it represented by a visual cue of some sort that isn't copied along with the actual data.
Leaving it as is is lazy and a huge mistake on the part of Microsoft.
SSMS is NOT a reporting machine nor a report builder. SSRS however, is intended for this and would not show NULLS in this way. Which is ultimately what you want.
Honestly, using SSMS to copy/paste into excel and expecting that to be your final report needing no alterations is the Lazy part. .. Just saying. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
sleipner (8/13/2014)
As I mentioned a while back - I deal with extremely large ad-hoc reports that often have LOTS of null values in them. When you're trying to do a copy/replace of several million NULLs in an Excel spreadsheet that's several dozen meg, it takes forever and often can and has crashed Excel. It's a regular recurring major issue for me.This sounds like a call for SSAS, PowerPivot, and some user training... not redesigning the text interpreter for easy review of results during development of queries.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Why on earth would I go to the bother of using SSRS to develop a report for something I want to run once? Our company only moved to Office 2007 back in 2011 (some of us are on 2013 now) so PowerPivot wasn't an option until pretty recently, and still probably isn't since most people have no clue what it is, haven't installed it, and some still have older versions of Office.
Note that SQL Server 2000 did NOT represent nulls as "NULL", and that was what I had used for a very long time.
-
That's somebody's semantics, somebody's abstract definition.
In the real world, it is that nothing is entered. Its state is that it contains nothing. There is, in fact, nothing there.
The word null came into use as a label for this state because its definition includes, "being or amounting to nothing; nil; lacking; nonexistent."
So calling NULL "nothing" is actually exactly correct.
-
Gentlemen,
You can all argue the merits, or lack thereof, of SSMS all day long, but it's not going to help anyone with anything. There ARE some facts that can't be avoided.
1.) SSMS was never designed to be used as a basis for copying and pasting large volumes of data into Excel. The reason that so many people do this is because of a failure on someone's part to do a better job of training them on the right way to achieve that result.
2.) "One-time adhoc queries" are a rather bad way to go about looking at data. If the data needed is not already being either warehoused for cube viewing or placed in a reporting tool, then someone is costing such a company money because they're not doing their job. Ultimately, blame the CIO.
3.) Arguing the functional equivalent of your company being 10 years behind the curve, or wouldn't know IT if it hit them over the head with a server, suggests rather strongly that it's time to find a job somewhere else. Find a way to improve your skills, despite the foolishness. Rise above the petty crap.
I came to this rant when I saw someone argue that they'd had to "deal with this for the last 4 years".... I can't imagine letting that much time go by without solving the problem for myself by adjusting the queries to include the ISNULLs, and developing a tool to grab all the column names and create a base query for me from sources like INFORMATION_SCHEMA and such. Letting it go for 4 years is lazy, no matter how you slice it. SPIN ain't gonna make it any prettier. Somebody needs to start taking responsibility for their own behavior and stop whining. Maybe we should be asking if they'd like some cheese with that whine?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I tend to agree with Steve (sgmunson) here. Most of the responses are accurate, but annoying.
I agree this should be a setting in SSMS. Regardless of the technical aspects of how things work, this should be an easy one since we know that all kinds of data gets pasted into Excel. Heck, it's why PowerPivot exists and ProClarity died. Excel rules.
Now, practically, there are add-ins or tools to help. If you can't install them, either get an exception approved or find another solution. You can continue to bash SSMS and bemoan the state of things, but it is what it is.
My solution? Stop pasting NULLs into Excel. If you are running these queries, use drag/drop to get the columns into your query and spend a few minutes pasting in ISNULL or COALESCE functions for the columns. Actually, it would be easy to write a piece of code that returns all columns for a table with the functions in there for you to paste. IF you're doing SELECT *, no problem.
The other thing? Don't cut/paste. That's silly. Save the SSMS results as a CSV. Then open in an editor and do the search/replace. Then open it in Excel.
Note that you say this should have been fixed, because it used to work. The issue is that SSMS depends on the VS Shell. The tools team (of which there seem to be dwindling resources over time), is dependent on the VS team. If they change behavior, it's possible that this wasn't caught because it's not on their radar. Not sure it should be with other things to fix, but you're welcome to file a Connect item. I'd vote for it.
-
Sean, that definition comes from the the dictionary. That makes it explicitly not abstract.
-
eval_stuff (8/14/2014)
Sean, that definition comes from the the dictionary. That makes it explicitly not abstract.That is the definition of the word in common usage. It is not the same thing as it relates to technology.
From dictionary.com. http://dictionary.reference.com/browse/null?s=t
null in Technology
programming
A special value used in several languages to represent the thing referred to by an uninitialised pointer. database
A special value that may be stored in some database columns to represent an unknown, missing, not applicable, or undefined value. Nulls are treated completely differently from ordinary values when evaluating SQL expressions and there are several SQL constructs for dealing with nulls.
You have to scroll down to the bottom of the page because dictionaries always put the most common types of usage at the top and the least common (IT) near the bottom.
_______________________________________________________________
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/ -
The answer is to use bcp. I used the queryout, -T and -c options and found that it omits the NULL text. If a field's contents might contain the character you are using as a field delimiter or might contain a newline character, use the QUOTENAME() function for that field.
Immediate need solved. 🙂
-
That actual state of a NULL field is that nothing is entered. So saying it is or has nothing is completely valid. The meaning of that state could be interpreted as or considered as "unknown, missing, not applicable, or undefined value."
The semantics here is a little like a person saying the state of something is without light because it has or reflects no light, then someone else arguing that they are wrong, that the only right thing to call it is either black or dark.
A NULL field contains nothing (there isn't anything there), regardless of what else you call it or label it.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply