No Data

Aug 22, 2013 at 3:21 PM
I'm having some trouble getting SQL Database Migration Wizard to work - it's generating a script with all the schema but no data. I've searched the discussions and issues and can't find anyone else having this issue, so I assume it's something dumb that I'm doing...

I'm using version v3.9.12 from the binary release.
Exporting from SQL Express 2008 R2
Importing to SQL Azure

I'm using:
Analyze/Migrate, Database
Script DROP and CREATE
Script Table/Data -- Table Schema with Data (I've also tried just Data - same result)
All objects included and all other settings at default.

I generate the SQL script then inspect it (yes, I did try running it directly first, and ended up with an empty database... but this is a staging DB). Whatever I try, no data.

The only workaround I've found is to generate the insert statements from SQL Management Studio and paste them into the middle of the script, then run it from SMS or a VS publish job (the SMS script doesn't take care of foreign keys adequately for this import, which your tool does).

What am I doing wrong?

TIA,
Geoff
Coordinator
Aug 22, 2013 at 4:58 PM
Hi Geoff,

It would be my first guess that you are running on SQL Server 2008 R2. Note that SQLAzureMW requires SQL Server 2008 R2 SP1 or later to run. If you bring up a command window and type in "BCP /v" you should see a version => 10.50.4000.0.

If you are running on SQL Server 2008 R2 SP1 (or later), maybe you can send me the results of the export process so I can look for errors.

Sorry for any issues!
George
Aug 29, 2013 at 9:50 PM
Hi George,

I get 10.50.2500.0, which according to MS documentation is the version number for SP1 (http://www.microsoft.com/en-gb/download/details.aspx?id=26728, under details). I do have SQL Server Express 2008 R2 Service Pack 2 installed (that's 10.50.4000.0), so not sure whether that doesn't upgrade BCP?

I've just successfully deployed the same database using SQLAzureWM, though I can't for the life of me find anything I've set differently or see any significant difference in the scripts it's generating...I assume I must have fiddled with something I shouldn't have before!

If I export the script and run it from a Visual Studio publish job then it fails to import the data, guess that's something to do with the context it's running in.

Thanks,
Geoff
Coordinator
Aug 29, 2013 at 10:59 PM
Hi Geoff,

So, you are good to go now? In regards to running the script generated by SQLAzureMW in Visual Studio, you are correct in that no data will be uploaded. Basically, I put a special tag (which is commented out) that I look for. I.E:

-- BCPArgs:18287660:[dbo].[Businesses] in "c:\SQLAzureMW\BCPData\dbo.Businesses.dat" -E -n -b 10000 -a 16384
GO

SQLAzureMW looks for "-- BCPArgs" and when it find that it generates the upload command. You will note that the number after BCPArgs is the number of rows that were exported. Thus when I upload, I count the rows and verify that what was downloaded is what is uploaded. The rest is BCP args. Note that what was new in BCP that I used was the -d argument. Older versions of BCP does not recognize that.

Anyway, let me know SQLAzureMW is causing you any problems.

Thanks for your time,
George
Coordinator
Aug 29, 2013 at 11:04 PM
Oh, as a side note, if you have many versions of SQL Server installed on your machine, then it could be that your "PATH" is set to an old version of BCP. I know that I have at least 3 versions on my machine. Anyway, you can either modify the "PATH" or specify the BCP directory you want to use in SQLAzureMW.exe.config. Look for:
<add key="BCPExe" value="bcp.exe"/>     
Just change the value to have the full path in it and you can point to any version that you want.

Regards,
George