I have a sql query where it compiles good with data and i was able to drop the file to MRS-dr01 server where all the tables and stored procedures are stored on the same server, but I'm trying to change the path to drop the files on different shared network path.
I already tried changing path but it didn't work
This is the problem...
".... -t "|" -S str-fshare...."
The -S option is used to specify the SQL Server to connect to to pull data FROM (in your case because you are using "queryout"). The -S option should still be the sse-dr01 server... that is still where the data is, correct? You just want to change where the sse-dr01 data is being sent out to file?
If you want to change where the data is going OUT to, just change the path as you already have, but leave the -S option alone.
exec master..xp_cmdshell 'bcp "select * from testfdb.dbo.Batch_1" queryout "\\str-fshare\Departments\IT\Documentation\Daily Operations\Experian\EX_BCH.csv" -T -c -t "|" -S sse-dr01'
My guess is there is NO sql server running on str-fshare and that is why you are getting the error you did. BCP is looking for a SQL Server called "str-fshare"