VSDBCMD: fix possible reason for “The target database schema provider could not be determined” error

As you probably know VSDBCMD is utility which can be used in order to create database from the output of Sql Server 2008 database project (.dbproj file). Command line which creates database may look like this:

vsdbcmd /a:Deploy /dsp:SQL /dd+ /manifest:MyDatabase.deploymanifest /p:DeploymentConfigurationFile=MyDatabase_Database.sqldeployment /p:SqlCommandVariablesFile=MyDatabase_Database.sqlcmdvars /cs:"Data Source=(local);Integrated Security=True;Initial Catalog=MyDatabase"

(you may find full reference here).

However this particular command may cause error when you run it on the Sql server instance which doesn’t contain MyDatabase database yet: “The target database schema provider could not be determined.  Deployment can not continue”. Interesting that if Sql server instance contains the database this command works. Recently I faced with similar problem.

Event log contained the following information message from MSSQLSERVER component: “Login failed for user 'username'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]”. However if I deployed this project from VS which was ran using the same user account everything worked successfully.

Then I tried to run the command with less arguments:

vsdbcmd /a:Deploy /dd:+ /manifest:MyDatabase.deploymanifest

It was run successfully, i.e. database was created. If you will check .deploymanifest file you will find that it contains deployment properties including the following:

<TargetConnectionString>Data Source=(local);Integrated Security=True;Pooling=False</TargetConnectionString>
<TargetDatabase>MyDatabase</TargetDatabase>

Note that connection string passed to the command which doesn’t work contains “Initial Catalog” setting:

Initial Catalog=MyDatabase

which specifies the actual database. So I checked the command line reference for VSDBCMD and found that there is separate parameter for the target database:

/p:TargetDatabase="String"

So I removed “Initial Catalog” from the connection string parameter (/cs) and used /p:TargetDatabase with database name:

vsdbcmd /a:Deploy /dsp:SQL /dd+ /manifest:MyDatabase.deploymanifest /p:DeploymentConfigurationFile=MyDatabase_Database.sqldeployment /p:SqlCommandVariablesFile=MyDatabase_Database.sqlcmdvars /cs:”Data Source=(local);Integrated Security=True” /p:TargetDatabase=”MyDatabase”

In this case database was created successfully. Hope it will be helpful for you as well.