| Author |
 Topic  |
|
|
MCPietersTP
Starting Member
Netherlands
12 Posts |
 Posted - 02/20/2013 : 03:23:09
|
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
47023 Posts |
Posted - 02/20/2013 : 03:34:26
|
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/
|
 |
|
|
MCPietersTP
Starting Member
Netherlands
12 Posts |
Posted - 02/20/2013 : 03:47:17
|
Thanks for your reply, but this does not solve the problem, still same result.
What else could be wrong?
Matthijs |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/20/2013 : 03:58:25
|
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/
|
|
|
|
MCPietersTP
Starting Member
Netherlands
12 Posts |
Posted - 02/20/2013 : 03:58:39
|
| 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/20/2013 : 04:23:50
|
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/
|
|
|
|
MCPietersTP
Starting Member
Netherlands
12 Posts |
Posted - 02/21/2013 : 06:12:13
|
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? |
|
|
|
MCPietersTP
Starting Member
Netherlands
12 Posts |
Posted - 02/21/2013 : 07:21:48
|
there is something wrong with the 'for xml' part, because when i leave it out the bcp operation succeeds.
But what? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/21/2013 : 22:49:00
|
can you use for xml path instead and try?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
MCPietersTP
Starting Member
Netherlands
12 Posts |
Posted - 02/25/2013 : 03:34:45
|
| No, that does not help either??? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/25/2013 : 04:17:45
|
first run for xml path alone and see if it gives you intended xml result
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
BCP query export to xml problem
