Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

writing dataset into xml

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
peterdevadoss
Asked:
peterdevadoss
  • 7
  • 5
  • 4
  • +2
1 Solution
 
Kinger247Commented:
If you set : sqlCmd.CommandTimeout = 0
Does it help ?
0
 
gangwischCommented:
have you tried just going into sql server and running the query (sqlQry) for example
seelct * from mytables for raw xml
0
 
Kinger247Commented:
It'll be SQL dragging out the data thats timing out.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
newyuppieCommented:
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
 
peterdevadossAuthor Commented:
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
 
newyuppieCommented:
sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlDa.Fill(sqlDs)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)
0
 
razoCommented:
sqlDa.SelectCommand = New SqlCommand(sqlQry, sqlCon)
sqlda.fill(sqlds)
sqlDs.WriteXml("d:\tele.xml", XmlWriteMode.WriteSchema)
0
 
peterdevadossAuthor Commented:
i dont understand what razo wanna say by just copying my comments !!
0
 
peterdevadossAuthor Commented:
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
 
newyuppieCommented:
what does sqlQry string say?
0
 
newyuppieCommented:
(and maybe increase the points??)
0
 
newyuppieCommented:
;)
0
 
peterdevadossAuthor Commented:
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
 
Kinger247Commented:
Did you try the : sqlCmd.CommandTimeout = 0 ?????????
0
 
newyuppieCommented:
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
 
newyuppieCommented:
<<Did you try the : sqlCmd.CommandTimeout = 0 ?????????>>
yes, i assumed you tried this to no avail... did you try it????
0
 
peterdevadossAuthor Commented:
finnaly tried the first comment from kinger247 and it works.

            sqlCmd.CommandTimeout = 0
            sqlCmd.CommandText = sqlQry
            sqlDa.SelectCommand = sqlCmd
            sqlDa.Fill(sqlDs)
0
 
Kinger247Commented:
no problem ;)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now