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.
That seems to work now. I just need to verify once I get folder access to the SQL Server. I tried to do the QUERYOUT before but got a different error, I must have had the wrong syntax. Thanks for the quick response.
The Query works now and I did verify I can view the output text file. However, when I added in more another item to the query I get another error when I replace the SELECT field FROM table WHERE field=1 with SELECT field FROM table WHERE field='x' I receive an error on the 'x'. Any ideas? I think the ' ' are causing an issue but I am not sure how to fix. Thanks for any feedback.
SQL Server 2000 Export Data To File
Posted - 04/16/2012 : 11:57:13
