Error creating dbschema file with VSDBCMD.exe

I have a VS 2010 database project that needs a database reference to a Dynamics CRM database. I have remote access to the 32-bit Windows 2003 R2 SP2 server. My strategy is to create a .dbschema file and use it as the reference.

The two best resources I have found for creating the .dbschema file are from stackoverflow and MSDN.

The error I received is:

The database schema provider could not be determined from the supplied connection string. Check if the connection string is valid for the ADO.NET provider used for your database provider and that you have the privileges necessary to connect to the server.

The steps I have done are:

1. Create a temporary folder C:\temp
2. Copy contents of folder C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy to C:\temp
3. Copy some DLLS from the list below of the folder C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5 to C:\temp

At this point the MSDN instructions say that I should have the files

• DatabaseSchemaProviders.Extensions.xml
• Microsoft.Data.Schema.dll
• Microsoft.Data.Schema.ScriptDom.dll
• Microsoft.Data.Schema.ScriptDom.Sql.dll
• Microsoft.Data.Schema.Sql.dll
• Microsoft.SqlServer.BatchParser.dll
• Sqlceer35en.dll
• Sqlceme35.dll
• Sqlceqp35.dll
• Sqlcese35.dll
• System.Data.SqlServerCe.dll
• VSDBCMD.EXE

This is almost true.

• DatabaseSchemaProviders.Extensions.xml is actually located in C:\temp\Extensions\SqlServer\ so I also copied it to C:\temp in case it needs to be at the same directory level

• Microsoft.SqlServer.BatchParser.dll was found in the GAC, so presumably I don't need to copy it to C:\temp but I did so anyways, taking the 32-bit version because my other DLLs came from Program Files (x86).

  4. I tried running VSDBCMD locally from C:\temp with CMD run as administrator. I have also copied C:\temp to the server and tried running it there.

VSDBCMD /a:Import /cs:"Provider=SQLOLEDB.1;Passwo开发者_StackOverflow中文版rd=********;Persist Security Info=True;User ID=sa;Initial Catalog=VANILLA_MSCRM;Data Source=********" /dsp:Sql /model:VANILLA_MSCRM.dbschema

I have tried all combinations of Data Sources = {., (local), localhost, the IP address, the machine name, the domain name} with Providers = {SQLOLEDB.1, SQLNCLI10.1, SQLNCLI.1}. All result in the same error. Obviously ., (local) and localhost were only used when ran from the server. I tested my connection strings by building them via UDL files (\http://msdn.microsoft.com/en-us/library/e38h511e%28VS.71%29.aspx). As suggested by this post \http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/dabd14fa-a805-4855-b3f1-33b37b16c5b1 I tried sqlcmd and could successfully run it.

How can I troubleshoot this problem? Is my strategy of creating a .dbschema file to reference my CRM database a wrongheaded approach?

I've tried this too, and found the following worked for me (run from the VS2010 command prompt, and run using an account that has sysadmin priviledges on the database):

vsdbcmd /action:import /connectionstring:"Integrated Security=True;Data Source=<SERVER>;Initial Catalog=<DATABASE>" /modelfile:"<DATABASE>.dbschema"