Can't access data from localDb

Oct 13, 2012 at 9:48 AM

I've been trying to use this tool to upload all the data froma localDb database to Azure. I'm a bit of a novice with databases so it took me a while to realise that the server name should be "(localdb)\v11.0". Perhaps the GUI could be updated in some way to offer this as an option?

It finds the database and tables OK, and scripts their structure, but it fails every time on accessing the data with BCP:

bcp.exe "[PilotQuiz].[dbo].[webpages_UsersInRoles]" out "c:\SQLAzureMW\BCPData\dbo.webpages_UsersInRoles.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

Can anyone advise what I can do to fix this? I've tried shutting down everything that might be holding open a connection to it.

thanks in advance for any answers, and thanks also to the creators of this wizard which looks like it will be very useful for me if I can get it working

Mark

 

 

Coordinator
Oct 13, 2012 at 5:20 PM


Hi Mark,

 

I would like for you to do a couple of test for me:

 

1)       Check your BCP version.  Start a command windows and type in “bcp /v”.  You should see version 10.50.1600.1 or later.

2)       Run the BCP command that was output by SQLAzureMW (in the command window):

 

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

 

You should get the same error as before I just want to verify that you get the same error.

 

3)       Modify the bcp command to look like this and then run:

 

bcp.exe "[PilotQuiz].[dbo].[webpages_UsersInRoles]" out "c:\SQLAzureMW\BCPData\dbo.webpages_UsersInRoles.dat" -E -n –S “(localdb)\v11.0” –T

 

You will notice that the difference in bcp command from #2 to #3 is that I put quotes around the server name.  If that works for you, please let me know and I will modify the code to put the server name in quotes.

 

4)       If #3 above does not work, try a couple simple variations and see if you can get it to work and let me know if anything works.

 

Regards,

George

Oct 13, 2012 at 6:09 PM

1) 10.50.2500.0

2) yes, same error as before

3) same error again unfortunately

4) I've tried everything I could think of. I read this thread but I think (localdb)\v11.0 really is the right server name. I tried double-escaping the backslash, tried (localdb)\. and appending \PilotQuiz on the end. I even tried (LocalDB)\v11.0

...

but I finally got it working thanks to this page which says that "If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB."

To get the named pipe:

C:\Users\Mark\Code\Web>sqllocaldb info v11.0
Name:               v11.0
Version:            11.0.2318.0
Shared name:
Owner:              Mark-XPS-2\Mark
Auto-create:        Yes
State:              Running
Last start time:    13/10/2012 18:53:59
Instance pipe name: np:\\.\pipe\LOCALDB#6111F11B\tsql\query

then use that as the server name:

C:\Users\Mark\Code\Web>bcp.exe "[PilotQuiz].[dbo].[webpages_UsersInRoles]" out "c:\SQLAzureMW\BCPData\dbo.webpages_UsersInRoles.dat" -E -n -S "np:\\.\pipe\LOCALDB#6111F11B\tsql\query" -T

Starting copy...

6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 16     Average : (375.00 rows per sec.)

So its a bit convoluted, but maybe this could be incorporated into the utility. I guess BCP is using an old version of .NET?

Mark

Coordinator
Oct 13, 2012 at 6:32 PM

Hi Mark,

Are you running SQLAzureMW 3.9.6 or SQLAzureMW 4.0.9?

Thanks,
George

Oct 13, 2012 at 6:44 PM

hi George

3.9.6 (I just clicked the big download button on the front page, didn't pay too much attention to what version I got). Should I have been using 4.0.9?

Coordinator
Oct 13, 2012 at 10:04 PM

Hi Mark,

Yes, SQLAzureMW 4.0.9 was built on SQL Server 2012 bits.  Check it out and see if that works.

Thanks,
George

Oct 14, 2012 at 6:57 AM

hi George,

4.0.9 has the same problem

Mark

Coordinator
Oct 14, 2012 at 12:47 PM

Hi Mark,

You might want to switch over to named instances and that should work.  Did you try putting this: np:\\.\pipe\LOCALDB#F365A78E\tsql\query as your server name and see if SQLAzureMW could connect and work?

Thanks,

George

Oct 14, 2012 at 1:23 PM

hi George,

Using the named pipe as server name doesn't work in either version of SQLAzureMW. I got my data onto Azure the manual way by scripting each table from within VS 2012 in the end (there weren't many, and the site was only for demo purposes). If I need to do the same thing again, I'll probably use a standard SQL Express database instead of localdb.

thanks for all your help

Mark