Tip on using the SQL Azure migration wizard

If you’ve been developing or just playing around for Windows Azure and using the SQL Azure, inevitably you’ve been using SQL Azure Migration Wizard. If not – go ahead and download it! It’s the ultimate tool to migrate your SQL Server data to and from SQL Azure and vise-versa.

I would like to share a tip, that most probably you have noticed but you are not sure what it is. Since couple of release SQL Azure Migration Wizard relies on SQL Server 2008 R2 bits (Express also works). There is small problem when you also have earlier version of SQL Server and/or Management Studio. The tool from management studio that is useful to SQL Azure Migration Wizard is called bcp. It is a command line tool to bulk coping SQL Server tables. And there is a difference in version that comes with SQL Server 2008 R2, and the one that comes with SQL Server 2008 (-). The most recent one has command-line option “-d”, while the others don’t. The trick is to change your PATH environment variable. Remove anything related to older version of SQL Server like this:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn

Where the old BCP resides. If you run BCP from that folder you will see:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]

There is no option “-d” which is to select a database, which option exists in R2 version of SQL Server 2008:

C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]

The point that having earlier version of SQL Server Management Studio it’s folder appears earlier in the PATH environment variable. When you run SQL Azure Migration Wizard it tries to launch bcp.exe without specifying folder relaying on the fact that the folder will be part of the PATH environment variable. But the earlier version will come first and that bcp will be executed, so you will get errors in SQL Azure Migration Wizard. To avoid that errors and run everything smoothly, just remove the old folder from the PATH variable.

1. How to edit PATH variable?
2. Click on START then navigate to “Computer”
3. Right click on “Computer” and select “Properties”
4. From properties window select “Advanced system settings”:

1. On the new window that will popup, a tab “Advanced” will be selected. There is a button “Environment Variables” at lower right corner. Click on it:

1. Edit the “PATH” variable, which is under “System variables”. Do not edit the one under “User variables”:

Good luck and enjoy the cloud!