I am building a tiny toolset for my application to do some database deployments and such. I thought I’d use Sql Server Management Objects (SMO) to consume some sql scripts and NHibernate output manually.

First, I needed to make sure remote connections were enabled. Here are some links to help with that:

• http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/c488cf76-2515-440f-b3f8-9cfad689c5b6/
• http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx

The last one is a great link to lots of resources to check for when connections can’t be made remotely to sql server. Also this shows enabling the SMO stored procs.

After making sure my settings were correct and restarting the server, I referenced microsoft.sqlserver.connectioninfo.dll in my project.

Then I wrote this code:

string sqlConnectionString = "Data Source=(local);Initial Catalog=MyDb;Integrated Security=True";
FileInfo file = new FileInfo("create_db.sql");
string script = file.OpenText().ReadToEnd();
Server server = new Server(new ServerConnection(sqlConnectionString));
server.ConnectionContext.ExecuteNonQuery(script);

Can you spot the problem?

Server server = new Server(new ServerConnection(sqlConnectionString));

This expects to be handed a connection not a connection string, but the exception in this case sent me on a wild goose chase wondering whether my server was somehow configured incorrectly or my connection string was bad.

While it is true this was careless on my part, the exception that resulted here was just plain misleading and the stack trace didn’t provide any real hints either. This makes me want to be more diligent in relaying meaningful exceptions at appropriate times in my own code.