Monday, December 1, 2014

Error... “Value can not be null” while opening SSMS (SQL Server management studio):

Today Morning, When I reached to office and opened Training server SSMS and tried to query SP_WHO2.  I found the error something like below.

"Directory is Invalid".

I was feeling little bit sleepy as this was "Khuni Monday" so did not focus on error and closed the SSMS and re-connected it. When I tried to expand the SQL server instance it was giving a very fresh beautiful error window, from which i was not able to get what it want to say exactly. The error was:

"Value cannot be null".
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer) 




I ignored it once again and tried to open a new query window and again it was giving another error which was: 

"The filename, directory name, or volume label syntax is incorrect. (Exception from HRESULT: 0x8007007B) ".

As the first error was related to "invalid directory". So, the first  thing which came to my mind was may be someone has changed the path for SSMS. Then I tried to open %Temp% from "run" of windows but it was giving error that location does not exist.
I told one of my team member to access the same server with client connecting tool of SSMS. He did and there was no error like above. Now I was 100 % sure that it is because of some file path as sql server access the %temp% location to store temporary files. As i had read some articles of Mr. Pinal Dave about SQL server environment variables so i was having some idea for environment variables.

The next thing which i did was : Typed "SET T" without quotes on cmd prompt and it displayed the location of temp file:

TEMP=C:\Users\dbadmin\AppData\Local\Temp\2
TMP=C:\Users\dbadmin\AppData\Local\Temp\2


But the folder "2" was missing that is why %temp% was not able to get accessed. I had created a new folder "2" and checked for permission.

Then opened Control Panel> System> Advanced> Environment variable:


Replaced the TEMP and TMP parameter with below:

%USERPROFILE%\AppData\Local\Temp\2




Restarted the Sql server service....Connect... Bingo.. Now Its working perfectly.

The above things worked  perfectly for me but still I'm not sure that The location got deleted  by someone or there was some other problem.

Thanks!!!!

1 comment: