Result in Text file - SQL Server 2005

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Result in Text file - SQL Server 2005

Author

Topic

Christy
Starting Member

7 Posts

Posted - 2007-05-10 : 13:55:00

How do I send the results set to a .csv file by scripting the filename out in the query? I do not want to use the toolbar option.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-10 : 14:08:25

use bcp. look in BOL for more info

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Christy
Starting Member

7 Posts

Posted - 2007-05-10 : 14:36:25

This does not work. I am getting the following error:

bcp "SELECT top 1000 * FROM dbo.tablename" queryout filename.dat -T -c

Error
=========
Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-10 : 14:37:54

you have to use xp_cmdshell to execute it because it's a command line utility.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-10 : 14:53:47

Or run bcp.exe from a cmd window (not from Query Analyzer).

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Christy
Starting Member

7 Posts

Posted - 2007-05-10 : 15:08:01

Ok, how do I run from the cmd line. Not really sure of how to execute from cmd line.

How do I connect to SQL Server 2005 from cmd line to run?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-10 : 15:13:35

looks like a homework question? have you read the books online information about the tool? you will find all your answers there..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-10 : 15:18:42

Go to Start..Run..Type in cmd and hit enter. From there you type in your commands. For example:

bcp "select * from sometable where somecolumn = 1" queryout C:\SomeOutputFile.csv -c -Ssomeserver -T -t, -r\r\n

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources