Need help with bcp export - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Need help with bcp export

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-02 : 09:42:59

It's my first time doing this, so if anyone could provide me with guidance, I would appreciate it.

I have a very simple temporary table called #test which I am trying to export as a tab-delimited text file. However, I get various error messages when I do this and whose meanings are cryptic to me.

I am using:

bcp #test out c:\test.txt -c -T

(note that at the moment I don't really care about the format, I would be happy just being successful at exporting).

However, I get this error:

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

I get the same error message when using a permanent table and specifying

bcp database.owner.mytable out c:\test.txt

Can anyone point me in the right direction, please?

Thank you.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 09:45:06

A temp table is exclusive to your connection, so BCP won't be able to see it.

Download Byrmol's very cool helper app to help you with the bcp command line options.
http://weblogs.sqlteam.com/davidm/archive/2004/03/31/1151.aspx

Damian
Ita erat quando hic adveni.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-02 : 09:49:09

Hi, thanks for this response. However, I get the same thing when working with permanent tables. Any ideas?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 09:53:55

You are running this from the command line aren't you ? That looks like a Query Analyzer message.

Try the helper app.

Damian
Ita erat quando hic adveni.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 09:58:49

To run bcp from Query Analyser, use this

Exec master..xp_cmdShell 'bcp "select * from DBname..#test" queryout "C:\text.txt" -c'

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 10:00:35

Still won't work. #test isn't available in bcp's context.

Damian
Ita erat quando hic adveni.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-02 : 10:02:06

quote:
Originally posted by madhivanan

To run bcp from Query Analyser, use this

Exec master..xp_cmdShell 'bcp "select * from DBname..#test" queryout "C:\text.txt" -c'

Madhivanan

Failing to plan is Planning to fail

I don't think that'll work....

Can you describe to us how your temp table is being created?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-02 : 10:03:05

You gotta be quick today Brett

Damian
Ita erat quando hic adveni.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-06-02 : 10:05:32

Yes, it's one part of a large query. I suppose I can create a 'permanent' table (although I'd rather not), but I do need to run something like this from Query Analyzer (Thanks Merkin, that was my problem, by the way...yes I am a git).

Any ideas on how to do this with a temp table? If not, should I go perm and use the same line that madhivanan kindly suggested?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-03 : 00:36:29

Move the result set to temporary table and run bcp from Query Analyser and drop that table

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-03 : 01:27:39

How many times do you need to be told that won't work Madhivanan ?

Damian
Ita erat quando hic adveni.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-03 : 02:03:53

quote:
Originally posted by Merkin

How many times do you need to be told that won't work Madhivanan ?

Damian
Ita erat quando hic adveni.

Merkin, Actually I meant

Select * into mytemptable from (Large Query) T
Exec master..xp_cmdShell 'bcp "Select * from DBname..mytemptable" queryout "C:\testing.txt" -c'
Drop table mytemptable

Am I missing something?

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-03 : 02:13:11

That isn't a temporary table, so your use of language was misleading.

Damian
Ita erat quando hic adveni.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-03 : 02:25:53

Yes You are correct Merkin. Still I have to learn more. Thanks for pointing that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources