December 4, 2014 at 5:59 am
A problem might appear if you have more than one instance running on the server or on your local machine. You can solve this problem by adding -SInstance Name
If you know how to import and export files using BCP on a local machine, and you tried the code and found that it works, but when you tried to change to a network path then it didn't work, all what you need to do is to give Read/Write permission to a user called NETWORK[/I]
Here's the syntax. Try it yourself:
------------------------------------
DECLARE @sql VARCHAR(4000) = ''
DECLARE @SCHEMA VARCHAR(MAX) = 'sys'
DECLARE @TBL VARCHAR(MAX) = 'tables'
DECLARE @File_Path VARCHAR(MAX) = '\\MachineName\Folder Name\'
DECLARE @File_Name VARCHAR(MAX) = 'Test 1.txt'
SET @sql = 'bcp "SELECT * FROM ' + DB_NAME() + '.' + @SCHEMA + '.' + @TBL + '" queryout "' + ISNULL(@File_Path, '') + @File_Name + '" -T -c -t -S' + @@SERVERNAME
PRINT 'EXEC xp_cmdshell ''' + @sql + ''''
------------------------------------
EXEC xp_cmdshell @sql
------------------------------------
If you have more than instance and you don't specify the instance name you'll get the following errors :
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
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 config
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
If you don't give enough permission you might receive this error:
Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
I'm writing this because I faced the problem and didn't find any article that tells me the direct short answer. I even found that some people claims you cannot use BCP with network paths. Actually you can and the prove is above. I hope this article helps those people who faced the same problem as mine.
November 7, 2016 at 3:00 pm
Nice! Saved my keester, very much appreciated!
January 22, 2018 at 5:32 pm
Hi Experts -
I'm try to find how data is routed from DB server when exporting to local vs share path. Below is my scenario.
I execute BCP command from ServerA and the database instance is running in ServerB. I have shared folder in ServerC.
DECLARE @File_Path VARCHAR(MAX) = '\\ServerC\Folder Name\'.
Everything works fine and I see data exported to \\ServerC\Folder Name\
In this scenario will SQL Server export data directly to \\ServerC\Folder Name\ (ie. ServerB to ServerC)
or will the data be cached in ServerA and then exported to \\ServerC\Folder Name\ ( ie. ServerB -> ServerA(cached) -> ServerC )?
Thanks
Gopi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply