Solved

writing dataset into xml

Posted on 2006-11-09
18
229 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

12 Experts available now in Live!

Get 1:1 Help Now