| Author |
 Topic  |
|
karanlik
Starting Member
2 Posts |
 Posted - 10/09/2003 : 09:53:37
|
Hi All,
I am trying to use the bcp utility to save the query results as XML file. But i am getting the following error. Can anyone offer a solution?
Thanks,
Bahtiyar KARANLIK
-----------------------
My bcp statement:
EXEC master..xp_cmdshell 'bcp "SELECT CustID,CustName,CustSurname,CustEmail FROM aDB..T_Customers FOR XML RAW" queryout "c:\customers.xml" -fc:\bcp.fmt -Sserver -Usa -Ppwd -C RAW -r -t'
Format File:
8.0
4
1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS
With the error :
Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server
|
 |
|
|
karanlik
Starting Member
2 Posts |
Posted - 10/09/2003 : 10:17:00
|
Hi All,
I am trying to use the bcp method to create an XML file from the data in SQL Server 2K. But there is a wierd problem with it. Any idea is welcomed..
Thanks
Bahtiyar KARANLIK
bcp statement:
EXEC master..xp_cmdshell 'bcp "SELECT CustID,CustName,CustSurname,CustEmail FROM aDB..T_Customers FOR XML RAW" queryout "c:\customers.xml" -fc:\bcp.fmt -SserverName -Usa -Ppwd -C RAW -r -t'
Format File:
8.0
4
1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS
Error String:
Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server
Any other way of retrieving data from SQL server is also welcomed. But i will be really glad if the method uses a stored procedure to do that:) |
|
|
|
robvolk
Most Valuable Yak
USA
15732 Posts |
Posted - 10/09/2003 : 13:03:06
|
| You cannot use a format file when outputting XML this way. XML is a serialized string and does not have columns. |
|
|
|
jawg62
Starting Member
1 Posts |
Posted - 11/20/2003 : 06:59:57
|
Hi,
I was able to add a root element to my xml without resorting to the "copy rootopen.txt+myfile.xml+rootcls.txt myfile.xml" method suggested by robvolk.
To add the root element, I had to modify the query I was using.
The original query that produced output without a root element looked something like this:
SELECT 1 AS Tag,
null AS parent,
dateEnd AS [import!1!dateEnd],
duration AS [import!1!duration],
dateLabel AS [import!1!date],
diagFile AS [import!1!diagFile!element],
claimFile AS [import!1!claimFile!element],
FROM ##files
ORDER BY num
FOR XML EXPLICIT
The modified query that produced output with a root element:
SELECT 1 AS Tag,
null AS parent,
null AS [import!2!dateEnd],
null AS [import!2!duration],
null AS [import!2!date],
null AS [import!2!diagFile!element],
null AS [import!2!claimFile!element],
0 AS [root!1!order!hide]
UNION ALL SELECT 2 AS Tag,
1 AS parent,
dateEnd,
duration,
dateLabel,
diagFile,
claimFile,
num
FROM ##files
ORDER BY [root!1!order!hide]
FOR XML EXPLICIT
It's a little hacky, but it writes a well formed xml document in one step.
Jake |
|
|
|
emccarty
Starting Member
2 Posts |
Posted - 09/08/2004 : 12:28:26
|
use pubs
create table root ( data char(1) NULL )
execute master..xp_cmdshell 'bcp "select top 10 * from pubs..root right outer join pubs..authors on 1 = 1 for xml auto" queryout \\stagingsql\Prod_bak\CC7335\bcp.xml -T -c -r -t'
/*
<pubs..root>
<pubs..authors au_id="172-32-1176" au_lname="White" au_fname="Johnson" phone="408 496-7223" address="10932 Bigge Rd." city="Menlo Park" state="CA" zip="94025" contract="1"/>
<pubs..authors au_id="213-46-8915" au_lname="Green" au_fname="Marjorie" phone="415 986-7020" address="309 63rd St. #411" city="Oakland" state="CA" zip="94618" contract="1"/>
<pubs..authors au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl" phone="415 548-7723" address="589 Darwin Ln." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="267-41-2394" au_lname="O'Leary" au_fname="Michael" phone="408 286-2428" address="22 Cleveland Av. #14" city="San Jose" state="CA" zip="95128" contract="1"/>
<pubs..authors au_id="274-80-9391" au_lname="Straight" au_fname="Dean" phone="415 834-2919" address="5420 College Av." city="Oakland" state="CA" zip="94609" contract="1"/>
<pubs..authors au_id="341-22-1782" au_lname="Smith" au_fname="Meander" phone="913 843-0462" address="10 Mississippi Dr." city="Lawrence" state="KS" zip="66044" contract="0"/>
<pubs..authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham" phone="415 658-9932" address="6223 Bateman St." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="427-17-2319" au_lname="Dull" au_fname="Ann" phone="415 836-7128" address="3410 Blonde St." city="Palo Alto" state="CA" zip="94301" contract="1"/>
<pubs..authors au_id="472-27-2349" au_lname="Gringlesby" au_fname="Burt" phone="707 938-6445" address="PO Box 792" city="Covelo" state="CA" zip="95428" contract="1"/>
<pubs..authors au_id="486-29-1786" au_lname="Locksley" au_fname="Charlene" phone="415 585-4620" address="18 Broadway Av." city="San Francisco" state="CA" zip="94130" contract="1"/>
</pubs..root>
*/
--Erik McCarty erik@texastar.com www.TexaStar.com
|
|
|
|
emccarty
Starting Member
2 Posts |
Posted - 09/08/2004 : 12:33:07
|
use pubs
execute master..xp_cmdshell 'bcp "select top 10 * from ( select 1 root ) as root right outer join pubs..authors on 1 = 1 for xml auto" queryout c:\bcp.xml -T -c -r -t'
/*
<root root="1">
<pubs..authors au_id="172-32-1176" au_lname="White" au_fname="Johnson" phone="408 496-7223" address="10932 Bigge Rd." city="Menlo Park" state="CA" zip="94025" contract="1"/>
<pubs..authors au_id="213-46-8915" au_lname="Green" au_fname="Marjorie" phone="415 986-7020" address="309 63rd St. #411" city="Oakland" state="CA" zip="94618" contract="1"/>
<pubs..authors au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl" phone="415 548-7723" address="589 Darwin Ln." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="267-41-2394" au_lname="O'Leary" au_fname="Michael" phone="408 286-2428" address="22 Cleveland Av. #14" city="San Jose" state="CA" zip="95128" contract="1"/>
<pubs..authors au_id="274-80-9391" au_lname="Straight" au_fname="Dean" phone="415 834-2919" address="5420 College Av." city="Oakland" state="CA" zip="94609" contract="1"/>
<pubs..authors au_id="341-22-1782" au_lname="Smith" au_fname="Meander" phone="913 843-0462" address="10 Mississippi Dr." city="Lawrence" state="KS" zip="66044" contract="0"/>
<pubs..authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham" phone="415 658-9932" address="6223 Bateman St." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="427-17-2319" au_lname="Dull" au_fname="Ann" phone="415 836-7128" address="3410 Blonde St." city="Palo Alto" state="CA" zip="94301" contract="1"/>
<pubs..authors au_id="472-27-2349" au_lname="Gringlesby" au_fname="Burt" phone="707 938-6445" address="PO Box 792" city="Covelo" state="CA" zip="95428" contract="1"/>
<pubs..authors au_id="486-29-1786" au_lname="Locksley" au_fname="Charlene" phone="415 585-4620" address="18 Broadway Av." city="San Francisco" state="CA" zip="94130" contract="1"/>
</root>
*/
--Erik McCarty erik@texastar.com www.TexaStar.com |
|
|
|
chrisgin
Starting Member
8 Posts |
Posted - 11/15/2004 : 17:51:45
|
Hi,
I'm getting the following error when running the "bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -t" command:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (464 bytes) exceeds host-file field length (0 bytes) for field (1). Use prefix length, termination string, or a larger host-file field size. Truncation cannot occur for BCP output files.
Any ideas how to get around this?
Thanks,
Chris
|
|
|
|
James Birch
Starting Member
2 Posts |
Posted - 04/08/2005 : 10:27:51
|
This is a warning message that you will always get if you set the line break character to nothing. If you are outputting XML (which is the only reason I can think of for setting the line break to nothing!) then you can safely ignore these warning messages. It simply indicates that the line length has gone over what the ODBC driver can normally handle (in the normal column / row output).
Personally, I'm still struggling with the lack of UTF-8 support in bcp...
|
|
|
|
dj_sandro
Starting Member
1 Posts |
Posted - 05/18/2005 : 15:31:28
|
| Hello there! i'm new on this site! i was surfing around on the web looking for information how to connect MS Excel to MS SQL so i can access the stored procedure via XML maybe you guy's could help me out cause this is the only site with alot of sql info |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1537 Posts |
Posted - 05/18/2005 : 15:36:34
|
dj_sandro
Excell is a spread sheet not a database, You could import a sheet into a SQL table but you could not "connect" them.
There have been a lot of posts on how to do this, use the search.
Jim
Users <> Logic |
|
|
|
spdhiva
Starting Member
13 Posts |
Posted - 05/20/2008 : 15:44:21
|
Hi,
I have been doing some research on this XML file format for last 1 week and accidently got into this case. u guys rock!
it helps me in a bigtime. I was stugging with xml file format and unicode issue.
Thanks a lot. |
|
|
|
dmusson17
Starting Member
3 Posts |
Posted - 07/25/2008 : 14:45:25
|
| How do I handle a select statement that has double quotes in it? |
|
|
|
BlueBeacon
Starting Member
India
3 Posts |
Posted - 06/22/2011 : 03:32:46
|
Thanks for sharing us nice information.
Thanks & Regards
unspammed |
|
|
|
Topic |
|
SQL Server 2000 XML