SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 BCP query export to xml problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/20/2013 :  03:23:09  Show Profile  Reply with Quote
Hi Everyone,

I am trying to export a query to a xml file, but I'm stuck.
this is the code:
declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout ' + @Filename + ' -w -T -S SERVICES'

execute xp_cmdshell @SQLcmd

The query itself is correct it has xml data as output.

Result of above code is this:
output
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

Does someone know what I'm doing wrong?

Thank You

Matthijs

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/20/2013 :  03:34:26  Show Profile  Reply with Quote
can you try like this?


declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -T -S SERVICES'

execute xp_cmdshell @SQLcmd


I assume SERVICES is your server name and its the default instance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/20/2013 :  03:47:17  Show Profile  Reply with Quote
Thanks for your reply, but this does not solve the problem, still same result.

What else could be wrong?

Matthijs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/20/2013 :  03:58:25  Show Profile  Reply with Quote
hope QODBC linked server is created in same server SERVICES

try this too


declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -T'

execute xp_cmdshell @SQLcmd



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/20/2013 :  03:58:39  Show Profile  Reply with Quote
The problem is with authentication, because when I connect to sql server with windows authentication, it works. But I want to use sql server authentication with user 'Pietje' and password 'oops' for example, How do i change the sql code?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/20/2013 :  04:23:50  Show Profile  Reply with Quote
your bcp command is also trying to use windows authentication itself. Then wats the issue?

Anyways ,if you want to use sql account change like

declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -U username -P password'

execute xp_cmdshell @SQLcmd


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/21/2013 :  06:12:13  Show Profile  Reply with Quote
I still have problems exporting query to xml.
When I execute following code it works:
declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select 27378 as supdata for xml raw, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -T'
execute xp_cmdshell @SQLcmd

But when I exexute this code, i still get above result(see earlier message):
declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -T'
execute xp_cmdshell @SQLcmd

the query itself has xml as a result...

does someone know what's wrong?
Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/21/2013 :  07:21:48  Show Profile  Reply with Quote
there is something wrong with the 'for xml' part, because when i leave it out the bcp operation succeeds.

But what?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/21/2013 :  22:49:00  Show Profile  Reply with Quote
can you use for xml path instead and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/25/2013 :  03:34:45  Show Profile  Reply with Quote
No, that does not help either???
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/25/2013 :  04:17:45  Show Profile  Reply with Quote
first run for xml path alone and see if it gives you intended xml result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.11 seconds. Powered By: Snitz Forums 2000