I am running SQL Server 2000 and would like to automate exporting data to a text or .csv file.
I connect remotely to the SQL Server with Managment Studio.
I am able to connect and run SELECT commands successfully.
However, when I try to run the following command to export results to a text file:
Exec master..xp_cmdshell 'bcp "SELECT field FROM table WHERE field=1" out filename.txt -U user1 -P password /S SQLServer1 /c'
When I run this I get the following error:
SQLState = 37000, NativeError = 4060 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'SELECT field FROM table'. Login fails.
Note: The user id has the same rights as the SA. Any ideas? Do I need local administrative rights on the SQL Server to export the file as well?
I tried to export the file to my local computer where I have Management Studio running but I get a different error:
SQLState = 08001, NativeError = 17 Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Any feedback is very appreciated.