Solved

writing dataset into xml

Posted on 2006-11-09
18
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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