CODEWITHSUNDEEP
sqlsql-server
Ashish
Ashish

Reputation: 11

How to save txt File created by BCP command on different server?

I have created text file using following command on the server where database resides

EXEC master.dbo.sp_configure 'show advanced options', 1 
RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 
RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT top 10 macnum, Cus_name,Cus_Email FROM [VBOS_TSP].[dbo].[Cust_file]" queryout "d:\creatFile.txt" -T -c'

But I need to create that text file on another machine. As I don't want to save that text file on production. Please suggest me other way so that by passing Path and/or passing credentials of a particular server. How to generate that file on other server?

Upvotes: 0

Views: 2811

Answers (2)

Suresh
Suresh

Reputation: 21

The path in the other machine should be shared path and accessible from this machine and should have full permissions for the folder to read/write.

I worked on similar scenario and below code format works for me. Try it once after give permissions

@LV_FILE_PATH='\\192.168.1.105\Folder\creatFile.txt'
@LV_SQLTXT='"SELECT top 10 macnum, Cus_name,Cus_Email FROM [VBOS_TSP].[dbo].[Cust_file]"'

SET @LV_CMDTXT =  'BCP ' + @LV_SQLTXT + ' QUERYOUT "' + @LV_FILE_PATH + '" -c -U -T -S -r\n'
EXEC MASTER..XP_CMDSHELL @LV_CMDTXT

Upvotes: 1

Ionic
Ionic

Reputation: 3935

You can't use an unc path for queryout at bcp. You're only solution is to export it on the current machine and copy it afterwards.

Another idea is to run bcp on the remote maschine and connect to your datastore to export the data by using the parameter -S.

I hope this helps you.

Upvotes: 0

Related Questions