Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
forefj
Starting Member
10 Posts |
 Posted - 2012-04-16 : 11:57:13
|
| 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-16 : 12:06:30
|
| you're using a query for bcp so you should be using queryout optionExec master..xp_cmdshell 'bcp "SELECT field FROM table WHERE field=1" queryout filename.txt -U user1 -P password /S SQLServer1 /c'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
forefj
Starting Member
10 Posts |
Posted - 2012-04-16 : 12:53:16
|
| 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. |
|
|
|
forefj
Starting Member
10 Posts |
Posted - 2012-04-16 : 21:10:16
|
| 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 errorwhen I replace the SELECT field FROM table WHERE field=1 withSELECT 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. |
|
|
|
forefj
Starting Member
10 Posts |
Posted - 2012-04-17 : 12:45:54
|
| I figured it out with help from another posting. I created a View and then just call the View with XP_CMDSHELL BCP. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
SQL Server 2000 Export Data To File