Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Exporting Data Programatically with bcp and xp_cmdshell
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Starting Member

2 Posts

Posted - 07/09/2008 :  13:12:46  Show Profile  Reply with Quote
I have created the output file using BCP successfully. I am using a BCP query on a temp table created by a more complex query. This is all embeded in a command to run using xp_cmdshell.

bcp "select * from ##WISCTBL" queryout "c:\temp\WISC_FILE_20080709.txt" -T -c

When I look at the output there is a row of data, then a blank row, and then a row of data, etc... The user wants to see the data fixed width (done this) with row after row of data.

My question is how do I get rid of the extra blank row?
Go to Top of Page

Starting Member

3 Posts

Posted - 07/08/2009 :  04:45:23  Show Profile  Reply with Quote
Can i use the following
EXEC master..xp_cmdshell
'bcp "EXEC sp_helptext SPNAME" queryout "c:\data.txt" -T -c' ?
Here I'm getting the error as Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#CommentText'.
Go to Top of Page

Constraint Violating Yak Guru

280 Posts

Posted - 07/10/2009 :  05:44:24  Show Profile  Visit henrikop's Homepage  Reply with Quote
Ok, Now I reply to an old topic, but hey can't resist it.

xp_cmdshell is for the whole server while TRUSTWORTHY is for one database only. I tend to think CLR is a lot safer (when used properly) than letting xp_cmdshell being used.

There's no place like
Go to Top of Page

Starting Member

1 Posts

Posted - 06/24/2011 :  14:06:28  Show Profile  Reply with Quote
Is there a way to create a bcp format file programatically from a store procedure using xp_cmdshell or isql or osql for a given database.schema.table?

steve kirchner
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000