Please start any new threads on our new site at http://forums.sqlteam.com. 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
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 BCP export to XML issue
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cooper_32
Starting Member

United Kingdom
3 Posts

Posted - 05/15/2014 :  10:41:50  Show Profile  Reply with Quote
Hi

I am having some problems exporting an XML file via BCP.
I have created a stored procedure that creates the XML file, complete with the version declaration <?xml version="1.0" encoding="utf-8" ?>.

If I run the procedure manually through sql management studio it correctly creates the file complete with the declaration and saves it to my local path.

However when I run it through BCP, it strips off the declaration.
Can anyone assist with the correct BCP options, or suggest an alternative.


BCP code below
**************

Set @BcpCommand = 'BCP ' + @BcpSqlQuery + ' queryout "' + @FileLocation+'\'+@FileName + '" -w -S'+@InstanceName+' -Usa -Psa'
exec xp_cmdshell @BcpCommand, 'NO_OUTPUT'

Edited by - tkizer on 05/15/2014 15:01:51

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/15/2014 :  12:20:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us the output of this: PRINT @BcpCommand

Maybe you need to add -c and -r\r\n?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Cooper_32
Starting Member

United Kingdom
3 Posts

Posted - 05/15/2014 :  14:20:30  Show Profile  Reply with Quote
Hi

Here is the printout, as requested

BCP exec.Benson.dbo.UDEF_SP_XML_DIM_ORDERS_OUT queryout "C:\Upload\OrderUpload.xml" -w -x -SDM6S9W1\SQL2008R2 -Usa -Psa

I should add however, that I posted this to the wrong forum, as it's
SQL 2008 not 2000
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/15/2014 :  15:02:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Cooper_32


I should add however, that I posted this to the wrong forum, as it's
SQL 2008 not 2000



I moved the topic to a 2008 forum.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/15/2014 :  15:03:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did you try adding the -c parameter?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Cooper_32
Starting Member

United Kingdom
3 Posts

Posted - 05/15/2014 :  16:38:48  Show Profile  Reply with Quote
I did but it hasn't made any difference.
Do the options have to be in a set order?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/15/2014 :  16:59:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
After the file path/name, no.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.06 seconds. Powered By: Snitz Forums 2000