sql-serverbcp

"metadata could not be determined" when importing with bcp from command line


I usually use the SQL Database Migration Wizard (SQLAzureMW) to script tables and then import them to a new database, as due to security rules I don't usually have a connection between the source and the target databases. However, this time I'm failing to use bcp to import some tables.

I dug further on it and created a temporary test database in the same machine that is hosting the source database. I tried to script a single table and import it, all using the SQLAzureMW. It works just fine and it also shows me the command that was used to import the .dat script file:

bcp.exe "dbName.dbo.tableName" in "c:\pathTo\dbo.tableName.dat" -E -n -C RAW -b 1000 -a 4096 -q -S targetServerAddress -U "dbUser" -P "dbPassword"

(Note I've manually redacted some variables above)

However, if I try to execute that very same command from the command line, it fails with the following error output:

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'dbName.dbo.tableName'.
SQLState = 37000, NativeError = 11529
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The metadata could not be determined because every code path results in an error; see previous errors for some of these.

I'm sure there's no mistake in the connection or table name parameters, as in that case it would output "login failed" (wrong server, user, password or database name).

Is there any procedure run by SQLAzureMW before running the bcp command that I should execute too? It would be great if I could use that application just for importing, since I already have the .dat files...


Solution

  • Found the solution. Once finished the generation of scripts for the selected database tables, SQLAzureMW has an "SQL Script" tab. I've copied the whole script and executed in the empty database into which I wanted to import the .dat scripted tables. This script has instructions for creating the tables - apparently, the .dat files aren't doing that for an unknown reason, because I have repeated multiple times the backup process checking the option "Schema table with data".

    SQLAzureMW after scripts generation

    Important to note that I didn't use to need that in older versions of the application. I can't re-test though because I have already updated it in every machine and I don't have access to download older releases anymore.