query
EXEC xp_cmdshell 'bcp "select * from DEV.DBO.visit" queryout "c:\users\visit.txt" -c -T'
above works ok and saves output on the remote server; what i am trying to do here is to save it on my machine that i launched SQL from
Alex
When you run xp_cmdshell, you're literally running a command on the SQL Server: the C: drive is the SQL Server's hard drive. To save the output to your machine, your machine will have to have a network share that's visible to the SQL Server. Then you can run the command like this:
EXEC xp_cmdshell 'bcp "select * from DEV.DBO.visit"
queryout "\\my-computer\my-share\users\visit.txt" -c -T'
However, since the command is running under the credentials of the SQL Server, that share also must be writable by the SQL Server account. Which is probably isn't.
The bigger question is, Why are you running bcp on the server instead of running bcp locally?
Open a command shell, and just say:
bcp "select * from dev.dbo.visit"
queryout c:\my-bcp-output-data.txt
-S some-sql-server-instance
-T