One of my friend emailed me today to troubleshoot a problem with SQL Server Management Studio. Here is the problem statement in his own words.

Hey Balmukund,
I know you are the one you can save me now.

I have a problem with my production SQL Server. When I am trying to connect via management studio, I am getting below error

Once I hit OK, I can get in to management studio but can’t expand any node completely.

Using Bingoogle, I found that this can be rectified by tweaking some registry entries. I do not want to take that route, instead want to uninstall only SSMS and install it back.

One of your reply on MSDN shows that you were not able to reproduce the problem. Let me know if you want to troubleshoot this live with me.

Friend:waiting your comments,
<Name not displayed>

For better search here is the text of the message

TITLE: Microsoft SQL Server Management Studio
——————————
Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)
——————————
BUTTONS:
OK
——————————

Once I reached office, I pinged him today and started my day! I followed my usual ways of troubleshooting with a series of question to nail down the problem. Here we go.

<<< Start of Chat Session>>>

Balmukund: Hey.. Just came to office and saw your email. Are you ready to work now?
Friend: Hey Balmukund, Thanks for pinging back. I am ready to work now.

Balmukund: Are you able to connect the same SQL instance from other machines?
Friend: Never tried, let me try right now! (1 min pause) yes, I can.

Balmukund: Could you please share your desktop and let’s see what’s going on?
Friend: Sure!

Balmukund: Okay, I can see your desktop now. Show me the error again.
Friend: There you go! As you can see, I am unable to expand any node completely. It ends up in same error.

Balmukund: Could you please open “New Query” window?
Friend: Let me try as I never did that after seeing this error. (5 second pause). Ouch, another error!

Balmukund: Hmm.. Please highlight the windows and hit Ctrl + C on your keyboard and then paste in chat windows.
Friend: What that would do? Copy image?

Balmukund: No, copy the text of the message.
Friend: Wow. that’s interesting. Here it is

—————————
Microsoft SQL Server Management Studio
—————————
The filename, directory name, or volume label syntax is incorrect. (Exception from HRESULT: 0x8007007B)
—————————
OK
—————————

Balmukund: Thanks. This sounds interesting.
Friend: Really! What’s next?

Balmukund: Since you confirmed that the issue is happening with the specific machine and you are able to connect to same SQL instance via SSMS on other machine then the error is specific to this machine settings. Agree?
Friend: Absolutely.

Balmukund: Now, lets download process monitor and run that tool. Here is the link http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx or you can go to http://live.sysinternals.com and search for procmon to download exe directly.
Friend: Done. Let me run the tool now.

Balmukund: Yes please. Start the data capture, reproduce the error and stop data capture.
Friend: Okay. Done. The is a lot of information. How would you find problem with this?

Balmukund: Lets apply the filter for process name = ssms.exe using Filter in menu bar

Friend: Okay. that has bought down the information captured to only ssms.exe.

Balmukund: Do you see any access to path which is not valid? Do you see “Name Invalid” in Results column?
Friend: Nope.

Balmukund: Okay. Click on New query windows and get other error. Do you see it now?
Friend: Yes. Its trying to access the path “C:\Users\demouser\AppData\Local\Temp;x:\SQL”

Balmukund: Okay. Let me take a screenshot.
Friend: Sure. I am sure you are going to write a blog.

Balmukund: Yes, of course. I have found earlier that few have hit the same null error.

 

Balmukund: It says temp in the path and also X drive. Do you have that drive?
Friend: No, its not there.

Balmukund: Okay. Could you please open command prompt and type “Set T” and show me the output?
Friend: Sure.

Balmukund: I think that explains the issue. TEMP path of your environment variable is incorrect. Can you please correct it?
Friend: Can you tell me steps to change?

Balmukund: Sure.  My Computer > Right Click > Properties. And then choose “Advanced”. Then click on “Environment Variables”

Balmukund: Now, change TMP and TEMP to valid location. On my machines its “%USERPROFILE%\AppData\Local\Temp”
Friend: Done.

Balmukund: Now, logoff and log back in.
Friend: Sure. Let me Try…

After 2 min….

Friend: You are the man!
Balmukund: I know <smiley>  Is that working now?

Friend: Of course, Yes!!!! Thanks a ton! You saved me from uninstalling SQL Management Studio.
Balmukund: Anytime! Glad I could help.

<<< End of Chat Session>>>

Later, I search on internet and found various peoples getting same error after installing few softwares. Hopefully this blog will help someone in the world! Here are the few items:
http://connect.microsoft.com/SQLServer/feedback/details/573771/value-cannot-be-null
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/7e0287b3-cd37-408e-8145-4b9dba8c7811 
http://connect.microsoft.com/SQLServer/feedback/details/742857/ssms-studio-reports-value-cannot-be-null-when-attempting-to-copy-headers-with-no-rows

Hopefully this would help someone in the world.

Cheers,
Balmukund Lakhani
Twitter @blakhani