Just Learned - SQL Server - Space separated database names

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

SQL Server - Space separated database names

Nov 16 2011 1:22AM by Malathi Mahadevan

We had a contract dba who was asked to create a database.From outside look the database looked normal, but for some reason code would not recognize it and we kept getting error that database does not exist or user does not have permissions. We tried putting db name in square brackets - it simply would not work. Finally i found that the database name had been created with leading and trailing spaces. This error is not visible to naked eye and needs very sharp looking.I could get rid of both leading spaces and trailing spaces easily by renaming the database.It is a simple issue that can consume many hours of time since it is just very difficult to spot. Why SQL Server allows it is not really clear.

4 Comments

Right. At first glance, we may not able to check the issue so we can check the available databases by executing:

select name from sys.databases

commented on Nov 16 2011 2:28AM

Hardik Doshi

20 · 9% · 2839

This happens only when you create db with leading and trailing spaces in SSMS using New Database option. It is not possible when you create the same name using CREATE DATABASE statement if you dont use squre braces. Just like other objects, it is a valid name though spaces does not add any value

commented on Nov 16 2011 2:37AM

Madhivanan

3 · 39% · 12472

Brings back old memories - I had spent about half a day trying to figure out the exact issue about 6 years ago! It was the time when SQL Server 2000 was still in production and later on we found that one of the developers has restored the database with a space in the name (as Madhivanan mentioned, via the Enterprise Manager).

commented on Nov 17 2011 12:05PM

Nakul Vachhrajani

4 · 33% · 10680

It looks like the contract DBA was feeling insecure and he/she does not want you to fire him/her before the completion of the project. In case you fire him/her she/he wanted the application to break or wanted to give you some trouble :-) )

(and I guess you pre-terminated the contract)

commented on Nov 17 2011 7:54PM

Jacob Sebastian

1 · 100% · 32004

Your Comment

Sign Up or Login to post a comment.

"SQL Server - Space separated database names" rated 5 out of 5 by 23 readers