Welcome to another episode of being caught off guard by strange string requirements. Today I discovered (the hard way of course) that Microsoft SQL Server 2000 doesn’t like to have dashes in database names. Fine, I probably should have used an underscore instead, but this was a database being used for something else that I just need to do some quick queries against in PHP.
I knew that the connection between the web server and the SQL Server systems was working because I had scripts talking to other SQL Server databases. My problem wasn’t connecting, mssql_connect() didn’t cause an error, it was selecting which database to use. Fortunately the answer was right there in the docs for mssql_select_db():
To escape the name of a database that contains spaces, hyphens (“-“), or any other exceptional characters, the database name must be enclosed in brackets, as is shown in the example, below. This technique must also be applied when selecting a database name that is also a reserved word (such as “primary”).
So just get in the habit of wrapping your SQL Server database name in brackets ( [ ] ) and you’ll be fine:
$conn = mssql_connect('SQLSERVERHOST', 'username', 'password');
mssql_select_db('[database-name]', $conn);
What I want to know is, why brackets around the whole name instead of traditional character escaping?