Search code examples
sql-serverdatabaseescapingado

Connecting to Sql Server Database with hyphenated name

I don't seem to be able to find a way to escape this hyphenated database name so that I can open a connection to it. The database name is EDMS-Lok and my code looks like this (ADOcon is an ADO connection object): 

strConBase="DRIVER=SQL Server;SERVER=LOKDB;UID=secret;PWD=secret;APP=LokENG_TESTS;WSID=LOKSPEC1;DATABASE=[EDMS-Lok];Network=DBMSSOCN;Trusted_Connection=NO;
ADOcon.ConnectionString = strConBase
ADOcon.Open"

I've tried the database name without enclosing brackets. I've tried enclosing it in square brackets, curly brackets, parentheses, back-ticks, single quotes, double quotes, etc. I've tried backslash and exclamation points. I can't find a way to escape the database name so that I can connect to it.

The error message I get is:
Run-time error '-2147467259 (80004005)': [Microsoft][ODBS SQL Server Driver][SQL Server]Cannot open database "[EDMS-Lok]" requested by the login. The login failed.

The database name and credentials are correct. I can access it if I omit the database name argument from the connection string, and then use the fully qualified name in my SQL statements (i.e. SELECT * FROM [EDMS-Lok].dbo.tablename)

However, I really don't want to do this, as I'm trying to use modular code that does the same actions on multiple databases. What I want is to open the connection to one database, run some SQL procedures, close the connection, then open a connection to another database and run the same SQL procedures. Lather, rinse, repeat.

Also, before someone asks, I do not have the option of changing the database name.

As a test, I used the exact same connection string to connect to another database on the server that did not have a hyphenated name and it worked without error, so I'm pretty sure it's the stupid hyphenated name that is the problem.

Solution

  • I could not get this to work the way I wanted to through an ODBC connection using the MSDASQL provider.

    I ran some tests and I seem to be able to connect through OLE DB using the SQLOLEDB provider. So, I think I will just go back and modify the existing code to use an OLE DB connection.

    Using OLE DB, I've found I can leave the database name un-escaped, i.e. INITIAL CATALOG=EDMS-Lok; or I can escape using double quotes, i.e. INITIAL CATALOG="EDMS-Lok";. Square brackets however threw an error when I tried them, so for now I will stick to double quotes or un-escaped database names.

    Thanks!