Need help with bcp - 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

Import/Export (DTS) and Replication (2000)

Need help with bcp

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2008-03-13 : 09:21:05

Hi,
I am trying to use bcp in a stored procedure. I could easily do this with DTS, but my company does not want me to use that for this. So, Have been looking around for how to export my results to an Excel sheet using bcp, and have not fond anything particularly using temp tables, can someone please help me with this.
Also, I should also mention, that in this database we use windows authentication.
Here is what I have so far.
Exec bcp "SELECT * FROM Sales..#transD" queryout testing.xls -U -P -c

But this keeps coming back that something is wrong around queryout.

Thank you

ITM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-13 : 09:25:39

BCP is not a statement but a external utility. So you need to call it using xp_cmdshell.

See example here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95671&SearchTerms=xp_cmdshell,bcp

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2008-03-13 : 10:04:46

Hi, thanks for the reply, but Unfortunently like so many things I found on the net nothing here tells me why my code is not working and the new things they have doesnt seem to work either , can there anyone tell me why my code is not working? I also tryed what they have and that does not work either:

master..xp_cmdshell 'bcp "select Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14 from SDOH_Reconciliation..#transD" queryout "c:\testing.csv" -SMyserver -T -c'

ITM

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-13 : 11:07:26

I don't think you can bcp out a temp table

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

Add yourself!
http://www.frappr.com/sqlteam

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-13 : 11:19:34

Local temporary tables you are correct
Global can be done

create table ##tmp (
c1 int
)

insert into ##tmp
select 1 union
select 2 union
select 3 union
select 4

exec master..xp_cmdshell 'BCP "select * from ##tmp" queryout c:\Temp\TEST.csv -T -c'

exec master..xp_cmdshell 'type c:\Temp\TEST.csv '

drop table ##tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2008-03-13 : 12:22:18

The last one here, by jhocutt, doesn't error, but doesnt go to the excel file; but I think it is really close. Any ideas why it may not be wprking for me? Even taken your whole code, and running it dosn't go to an Excel file.
Thanks

ITM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources