Error Using SQL Database Migration Wizard for (LocalDB)/v11.0

Jan 24, 2013 at 1:24 PM

I am using the SQL Database Migration Wizard, version 4.0.12, for the first time on a LocalDB database.  When I select my database and then script all database objects, the process starts, but I get an error:

Process started at 1/24/2013 5:53:13 AM -- UTC -> 1/24/2013 1:53:13 PM ...
Using BCP to get data from table [dbo].[Items]
bcp.exe "[My_DB].[dbo].[Item]" out "c:\SQLAzureMW\BCPData\dbo.Item.dat" -E -n -S (LocalDB)\v11.0 -T
******************
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired

This repeats 3 times for the 3 tables in my database.  A SQL Script is actually generated for the 3 tables, but the data in the database does not get scripted.  Any suggestions what might be causing this?  Thank you.

Coordinator
Jan 24, 2013 at 2:39 PM

Hi,

 

SQLAzureMW does things in two parts:

 

1)      Script database objects

2)      Uses BCP to export the data

 

The scripting of the objects do not depend upon the BCP process.  Thus, if BCP fails, the object scripting will still work.  Anyway, I think I have an idea as to the problem.  If you go to the bottom of the Result Summary page you will see a summary of the BCP output commands.  You should be able to highlight one of these commands, copy it, and then past it into a command window and run it.  It should fail with the same error that you saw.  Now do an up arrow to bring the command back up and then back arrow so that you can put quotes (“) around your server name.  So in your case, you started with this:

 

bcp.exe "[My_DB].[dbo].[Item]" out "c:\SQLAzureMW\BCPData\dbo.Item.dat" -E -n -S (LocalDB)\v11.0 -T

 

and you should end up with this:

 

bcp.exe "[My_DB].[dbo].[Item]" out "c:\SQLAzureMW\BCPData\dbo.Item.dat" -E -n -S “(LocalDB)\v11.0” -T

 

Try to run that again.  Let me know if that works.  I think the problem is that I didn’t put quotes around the server name and since you have a period in your server name, this is what is causing the error.  Let me know what happens.  I can make a very quick change to fix this problem if that is what the problem is.

 

Regards,

George

 


Jan 24, 2013 at 3:22 PM

Thanks George.  When I run the command from the bottom of the results window, I get the same error, as you mentioned.  I then repeated the command, but with quotes around "(LocalDB)\v11.0", but I still get the same error (as posted above).  So it doesn't look like it is being caused by the server name in this case.

Coordinator
Jan 24, 2013 at 8:18 PM


Well, that is a bummer.  I tried to reproduce your environment and the bad thing is that everything worked for me.  I installed SQL Server 2012 LocalDB and use the default V11.0 instance.  I created BusinessProcess database and put some data into it.  Here is part my SQLAzureMW summary:

 

Process started at 1/24/2013 1:40:41 PM -- UTC -> 1/24/2013 7:40:41 PM ...

Using BCP to get data from table [dbo].[BusinessProcessActivities]

bcp.exe "[BusinessProcess].[dbo].[BusinessProcessActivities]" out "c:\SQLAzureMW\BCPData\dbo.BusinessProcessActivities.dat" -E -n -S (localdb)\v11.0 -T

*

Starting copy...

3 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 1      Average : (3000.00 rows per sec.)

 

BCP output file: "c:\SQLAzureMW\BCPData\dbo.BusinessProcessActivities.dat"

 

Adding clustered index ci_azure_fixup_dbo_Activities on [dbo].[Activities].  You may want to change this index.

 

 

Summary of BCP output commands

bcp.exe "[BusinessProcess].[dbo].[BusinessProcessActivities]" out "c:\SQLAzureMW\BCPData\dbo.BusinessProcessActivities.dat" -E -n -S (localdb)\v11.0 -T

bcp.exe "[BusinessProcess].[dbo].[Activities]" out "c:\SQLAzureMW\BCPData\dbo.Activities.dat" -E -n -S (localdb)\v11.0 -T

bcp.exe "[BusinessProcess].[dbo].[BusinessProcessMetadata]" out "c:\SQLAzureMW\BCPData\dbo.BusinessProcessMetadata.dat" -E -n -S (localdb)\v11.0 -T

bcp.exe "[BusinessProcess].[dbo].[FormMetadata]" out "c:\SQLAzureMW\BCPData\dbo.FormMetadata.dat" -E -n -S (localdb)\v11.0 -T

bcp.exe "[BusinessProcess].[dbo].[BusinessInfo]" out "c:\SQLAzureMW\BCPData\dbo.BusinessInfo.dat" -E -n -S (localdb)\v11.0 -T

Analysis completed at 1/24/2013 1:40:50 PM -- UTC -> 1/24/2013 7:40:50 PM

Any issues discovered will be reported above.

Total processing time: 0 hours, 0 minutes and 9 seconds

 

Anyway, as you can see, I had (localdb)\v11.0 for my server and BCP was happy.  Now the problem that I have is that I have SQL Server 2005, SQL Server 2008 R2 SP1, SQL Server 2012, and SQL Server 2012 LocalDB installed on my machine so no telling what I have that you don’t.  So, I think the next step is to get BCP working.  What version of BCP do you have running on your machine?  On my machine when I run “bcp –v” from a command window I get:

 

BCP - Bulk Copy Program for Microsoft SQL Server.

Copyright (C) Microsoft Corporation. All Rights Reserved.

Version: 11.0.2100.60

 

What version do you have? 

 

Regards,

George

 

Jan 25, 2013 at 3:09 PM

I also have SQL Server 2005, 2008, 2008 R2, and 2012 installed on my computer.  I checked my BCP version and it is 10.50.1600.1.  I installed SQL Server 2012 Service Pack 1 (although it may have already been installed) thinking that that would upgrade me to 11.0.2100.60, but it didn't.  I seem to be having trouble finding out how to upgrade BCP.  Could you tell me how I upgrade the BCP to 11.0.2100.60?  Thanks George.

Coordinator
Jan 25, 2013 at 4:24 PM

Good news, I was able to duplicate the problem!  I ran an older version of BCP (v10.50.1600.1) and I got the error.  So, you need the latest BCP.  Now as to where to find it.  Here is where it is at on my machine:

 

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe

 

Now there are two ways to solve this problem.

 

1)      You have to change your PATH (open up a command windows and type “set” and return.  Then look for PATH and you will see the directories set.  You need to change the path to point to the latest version of SQL Server.

2)      Easy way.  In the directory where you have SQLAzureMW installed, edit the SQLAzureMW.exe.config file.  Look for this:

 

<add key="BCPExe" value=" bcp.exe"/>

 

Change it to this:

 

<add key="BCPExe" value="C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe"/>

 

Then rerun SQLAzureMW.  It will use the BCP.exe that you specified above.  This should get you going.

 

Let me know if it does not work.

 

Regards,

George 


Jan 26, 2013 at 3:18 PM

It works now!  That was the problem, an older version of BCP.  I used the first method, fixing the PATH, to fix the problem.  I just removed some path strings to older versions of the BCP directory.  The new one was already in there (same place as yours).  The Migration Wizard now generates the full script in about 5 seconds.

Thanks for solving the problem George (and creating the tool)!