Link to home
Start Free TrialLog in
Avatar of Star Gazr1
Star Gazr1Flag for United States of America

asked on

SQL Server export data to a file

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.
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

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
Avatar of Star Gazr1

ASKER

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.
SOLUTION
Avatar of rocky_lotus_newbie
rocky_lotus_newbie
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My solution is what worked after researching on other websites.