Solved

writing dataset into xml

Posted on 2006-11-09
18
230 Views
Last Modified: 2010-04-23
Dim sqlCmd As New SqlCommand
sqlCmd.Connection = sqlCon
sqlCmd.CommandTimeout = 1500
sqlCmd.CommandText = sqlQry

How can I  execute this sqlcommand into xml ?  

I know how to do it using selectcommand as follows:

sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)

but this method is not suitable as i load huge dataset which ends with timedout.


Whatever it is i want to write the resultset into xml without timedout error.
0
Comment
Question by:peterdevadoss
  • 7
  • 5
  • 4
  • +2
18 Comments
 
LVL 10

Accepted Solution

by:
Kinger247 earned 50 total points
ID: 17905555
If you set : sqlCmd.CommandTimeout = 0
Does it help ?
0
 
LVL 10

Expert Comment

by:gangwisch
ID: 17905731
have you tried just going into sql server and running the query (sqlQry) for example
seelct * from mytables for raw xml
0
 
LVL 10

Expert Comment

by:Kinger247
ID: 17905793
It'll be SQL dragging out the data thats timing out.
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17905919
use a DataAdapter to fill your dataset first using the command, LATER drop dataset into XML file it should be a breeze.
as far as i know of, there is not TimeOut limit on the WriteXML command...
0
 

Author Comment

by:peterdevadoss
ID: 17906800
the query is working fine in sql QueryAnalyer.

as commented by gangwisch,  if i am using sqlcommand instead of select command there is no timedout problem.  but i dont know how to wrtite into xml using sqlcommand (without data adapter)

if i should follow newyuppie's comments, is the following command lines are correct?
Actually no resultes in the xml with this code, it may need some correction.

sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)


0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17906826
sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlDa.Fill(sqlDs)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)
0
 
LVL 8

Expert Comment

by:razo
ID: 17906830
sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlda.fill(sqlds)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)
0
 

Author Comment

by:peterdevadoss
ID: 17906854
i dont understand what razo wanna say by just copying my comments !!
0
 

Author Comment

by:peterdevadoss
ID: 17906885
opps! sorry guys... sorry again.

I really missed that line - sqlda.fill(sqlds).  promptly corrected by newyuppe and razo.

here is the commplete line of command to fill the dataset and writing to xml

sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlda.fill(sqlds)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)

i get timedout during the 2nd code of line, which  is the question here.. how to avoid this?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:newyuppie
ID: 17906913
what does sqlQry string say?
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17906916
(and maybe increase the points??)
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17906918
;)
0
 

Author Comment

by:peterdevadoss
ID: 17906978
here is the query:

select distinct c.tel_no,customer=max(c.first_name),
first_orderd=min(t.transaction_date),last_ordered=max(t.transaction_date),
total_orders=count(t.transaction_date),total_amount=sum(t.total_amt),
called_date = max(called_date),
max(c.blk_no),max(c.avenue),max(c.street_name_no),max(c.bldg_house_no),
max(c.floor),max(c.flat_apt_num),max(c.landmark),max(m.caller),max(m.promo)
from customer_info c
left join trade_area_dist a on c.area_name = a.trade_area_code  and c.blk_no=a.block_no
left join mstr_trans_header t on c.cust_id=t.cust_id
left join tele_marketing_history m on c.tel_no = m.tel_no
where
c.tel_no not in
(select tel_no from tele_marketing_history where promo='Buy1 Take1 - MED (TM)' and response1=0)
and c.tel_no not in
(select tel_no from tele_marketing_nocall)
and c.tel_no not in
(select tel_no from tele_marketing_wrongno)
and a.store_assign='010' and c.area_name='DAHER' and c.tel_no not like '9%' and c.tel_no not like '6%' and  c.tel_no not like '7%'
group by c.tel_no order by c.tel_no desc
0
 
LVL 10

Expert Comment

by:Kinger247
ID: 17907050
Did you try the : sqlCmd.CommandTimeout = 0 ?????????
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17907051
if you run this query directly on the server, does it produce the result you want? if it does, how many rows does it return?
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17907199
<<Did you try the : sqlCmd.CommandTimeout = 0 ?????????>>
yes, i assumed you tried this to no avail... did you try it????
0
 

Author Comment

by:peterdevadoss
ID: 17912282
finnaly tried the first comment from kinger247 and it works.

            sqlCmd.CommandTimeout = 0
            sqlCmd.CommandText = sqlQry
            sqlDa.SelectCommand = sqlCmd
            sqlDa.Fill(sqlDs)
0
 
LVL 10

Expert Comment

by:Kinger247
ID: 17913101
no problem ;)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now