SQL Server export data to a file

fjkaykr11
fjkaykr11 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron ShiloChief Database Architect

Commented:
hi

when you connect to a server and execute any kind of cmdshell your ALLWAYS running serverside
this means that when you try to dump data into a TXT file on the OS your acctually creating an TXT file on the server side so you need the proper privileges.

you could try using SSIS it will make your life much easyer.

read this for some ssis help : http://www.codeproject.com/Articles/155829/SQL-Server-Integration-Services-SSIS-Part-1-Basics

the best regards
Aaron

Author

Commented:
SSIS runs on SQL Server 2000? Can I use SSIS in Mangement Studio for SQL Server 2000?
On another forum someone told me to change from out to queryout and it seems to run now but I have to get folder acces to verify. In the meantime, if you have anymore info on SSIS with SQL 2000 please let me know. Thanks.
SELECT field FROM table WHERE field=1" out filename.txt -U user1 -P password /S SQLServer1 /c

Is the syntax right? Shouldn't it be.....

SELECT field FROM table WHERE field=1" out filename.txt -U user1 -P password -S SQLServer1 -c
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I got it work using queryout and I am able to retrieve the file now (syntax below).
Exec master..xp_cmdshell 'bcp "SELECT field1 FROM table WHERE field1=10" queryout c:\filename.txt -U UserName -P password /S SQLServerName /c'
However, I need to change the WHERE statement value from a number to a string, like below:
Exec master..xp_cmdshell 'bcp "SELECT field1 FROM table WHERE field1='x'" queryout c:\filename.txt -U UserName -P password /S SQLServerName /c'
-- If I do it with the 'x', I get a syntax error. If I remove the quotes and set WHERE = x
I get a generate bcp usage display error.  Any ideas?
Commented:

Author

Commented:
Interesting link.  I don't see where it shows me how to resolve my issue though.  I am not using enterprise manager and I can't export the entire table to a .bcp file and then select on that. I am looking to do something different. I am not sure I can use BCP to run the query that I need to run, so that is why I am searching for alternatives. I realize I might be limited, given I am working with SQL Server 2000, but I don't have an choice at this stage. Thanks.
I got it to work using QUERYOUT and then also I needed to create a view with the SQL code and call the View with XP_CMDSHELL

Author

Commented:
My solution is what worked after researching on other websites.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial