Solved

writing dataset into xml

Posted on 2006-11-09
18
231 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

825 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