2

I'm trying to export my database to a .bacpac file. I get the following error:

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The command I'm using is:

/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:localhost /SourceDatabaseName:MyDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac'

The database I'm trying to back up is on a different ssd than my mssql installation. I don't know if that makes a difference.

I've been googling around for how to fix this and most people using windows had their similar problem solved by making sure that various windows services were running and starting them if they weren't. But I'm not sure how to check for the same thing in linux, but I think all the services I need are currently running because I can connect to the database via sqlcmd as well as through JetBrains' DataGrip application. So I know that a connection is possible. I just don't know why sqlpackage is having trouble connecting...

Update

As suggested, I gave the following command a shot:

/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:tcp:localhost,1433 /SourceDatabaseName:MyDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac'

And the error is slightly different:

(provider: TCP Provider, error: 0 - No such host is known.)
Jake Smith
  • 2,332
  • 1
  • 30
  • 68
  • I tried your suggestion and got a different error that it can't even find the server. Did I call the command correctly? Does this indicate that tcp is not enabled? – Jake Smith Jul 01 '17 at 19:15
  • Unfortunately, I'm getting the same error with both of these alternatives. To clarify, you are suggesting the command should look like `... /SourceServerName:"tcp:localhost,1433" ...` or `... /SourceServerName:"tcp:localhost" ...`, correct? – Jake Smith Jul 01 '17 at 19:21
  • So the documentation for sql server on linux and the sqlpackage and mssql-conf documentation seems to assume that tcp is enabled by default. You can change the port with mssql-conf, but there doesn't seem to be anything about enabling/disabling. Any other ideas? – Jake Smith Jul 01 '17 at 19:24
  • Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See [What topics can I ask about here](http://stackoverflow.com/help/on-topic) in the Help Center. Perhaps [Unix & Linux Stack Exchange](http://unix.stackexchange.com/) or [Database Administrators Stack Exchange](http://dba.stackexchange.com/) would be a better place to ask. – jww Jul 01 '17 at 19:35
  • @jww I'd disagree. Microsoft's documentation asks users of sql server on linux to post questions to Stack Overflow. There are plenty of related questions on Stack Overflow. – Jake Smith Jul 01 '17 at 19:40
  • @jww exporting a backpack is a development question. It's not something DBAs do. SQL Server is running in a container? Does that mean it's not listening on 127.0.0.1? – David Browne - Microsoft Jul 01 '17 at 19:40
  • @DavidBrowne-Microsoft I'm not sure I understand well enough to answer your question. I installed sql server on linux following the instructions in the documentation. I'm able to connect to the database via `sqlcmd` and JetBrains' DataGrip application. Both connections have `localhost` as the host/server parameter. I have a linux host machine (desktop) and a windows guest. But I'm trying things this way because i can't get the windows guest to see anything in host. Let me know if I can answer any more questions – Jake Smith Jul 01 '17 at 19:50
  • 1
    @JakeSmith - Microsoft can state whatever they like, but it does not change the fact Stack Overflow is a site for programming and development questions. You should ask your off-topic question on a more appropriate site. This is not the first time a company has told its users to get support elsewhere. Also see [Is it okay to use Stack Overflow as the support forum for a product or project?](https://meta.stackexchange.com/q/3966/173448), [Why we're not customer support for \[your favorite company\]](https://meta.stackoverflow.com/q/255745/608639), etc. – jww Jul 01 '17 at 20:01
  • @jww, this is a programming and development question. I'm trying to create a bacpac file to import into azure so that my DEVELOPMENT team can access this database. This is NOT an off-topic question. – Jake Smith Jul 01 '17 at 20:03
  • @DavidBrowne-Microsoft - I'm not sure what your arguments have to do with the issue at hand. Stack Overflow is a site for programming and development questions.Off-topic question should be asked on a more appropriate site. – jww Jul 01 '17 at 20:03
  • His argument directly addressed your concerns. It is a development question and told you why suggesting that I put something on one of the sites you suggested is not appropriate. But to appease you, I posted it on there as well. Now if you would like to get into an argument about this, please start a chat and stop polluting this comment thread. – Jake Smith Jul 01 '17 at 20:06
  • I looked through your links and this does not apply to my question. I'm using SQL Server for Linux which is in preview. Microsoft has not outsourced their support to Stack Overflow. Stack Overflow was one of four suggestions on where to go to get help from the community about this preview software. This is not a customer support related question like you suggest. I am seeking help from the community on how to diagnose a problem I'm having. If I need to put this command in a script and run it using ruby or something to make it more programming oriented for you, I'll do that. – Jake Smith Jul 01 '17 at 20:18
  • @JakeSmith - Perhaps I am missing something... You want to backup your schema or database. You are having trouble doing so. You've shown the commands you have run and failed. You asked here because Microsoft told you to do it. None of this passes the sniff test from 10,0000 feet. What does any of this have to do with programming and development? Why is it so important to you to ask here when there are better sites for the question? – jww Jul 01 '17 at 20:25
  • Then please check your sniffer. Are you even reading my responses? If not, can you please stop polluting this comment thread. Why don't you sit patiently and wait for someone else to agree with you rather than wasting your and my time. – Jake Smith Jul 01 '17 at 20:25
  • Can you connect with sqlcmd, eg: [dbrowne@dbrownelinuxsql tmp]$ sqlcmd -S localhost -U sa -P xxxxxxxx – David Browne - Microsoft Jul 01 '17 at 21:09
  • @DavidBrowne-Microsoft - Yep, I've already mentioned that a couple of times. I can connect using `sqlcmd -S localhost -U SA` and I can connect to it via localhost using JetBrains' DataGrip. – Jake Smith Jul 01 '17 at 21:30
  • @JakeSmith - would using the loopback IP address work instead? It sounds like there's an issue resolving localhost from SqlPackage. I'm trying to dig up an old thread with a similar issue, but I think this might have been the core problem – Kevin Cunnane Jul 03 '17 at 17:20
  • @KevinCunnane Thanks for your response. While you are looking for that, could you explain what you mean? I'm not sure what the loopback ip address is? – Jake Smith Jul 03 '17 at 17:23
  • rather than using localhost, use `127.0.0.1` or similar. Or the public IP address – Kevin Cunnane Jul 03 '17 at 18:38
  • @JakeSmith Would $hostname work in your scenario?(See my reply below) – Hello Jul 04 '17 at 01:50

1 Answers1

1

It's quite hard to put code in comment so i'll just post it here. Would this code work in your scenario? It works fine on my test machine(ubuntu 16.04):

hostname=$(hostname)
/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:tcp:$hostname /SourceDatabaseName:MYDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac' /su:sa /sp:Password01!
Hello
  • 632
  • 7
  • 14
  • Thanks for the suggestion. This ended up not working. I think it is resolving to the same server name I've been trying – Jake Smith Jul 04 '17 at 06:53
  • so what's you distro? Is it ubuntu or something else? – Hello Jul 04 '17 at 07:04
  • Not sure whether it supports 17.04 or not, but it should be fine. Could you please post the message starts with "Connecting to database........" when you execute the command? – Hello Jul 04 '17 at 07:10
  • It was the same error as I have in my question. I'm thinking I need to just do this in windows... – Jake Smith Jul 04 '17 at 07:11
  • It is probably something I messed up – Jake Smith Jul 04 '17 at 07:12
  • did you execute hostname=$(hostname) before the sqlpackage statement? – Hello Jul 04 '17 at 07:15
  • Yea I copied your whole snippet. What if it isn't an issue with sqlpackage but an issue with the DB itself?? – Jake Smith Jul 04 '17 at 07:18
  • Doesn't look like it. My script simply replaces localhost with $hostname so sqlpackage would connect by using actual server name. If there's issue with localhost resolving it should bypass it. – Hello Jul 04 '17 at 07:21
  • But localhost works on two other mediums: datagrip and sqlcmd. And I've noticed that I get io errors if I select records further down a table. My db might be corrupted... – Jake Smith Jul 04 '17 at 07:23
  • then you should receive something different.... what does `echo $hostname` return after execute `hostname=$(hostname)` ? is it your hostname? – Hello Jul 04 '17 at 07:27
  • If I remember correctly it was my IP address...not at the computer now – Jake Smith Jul 04 '17 at 07:29