Star Gazr1
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]Connectio nOpen (Connect()).
Any feedback is very appreciated.
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]Connectio
Any feedback is very appreciated.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My solution is what worked after researching on other websites.
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