troubleshooting Question

Insert SQLProfiler Trace Output XML file into database

Avatar of kworks
kworks asked on
Microsoft SQL ServerVisual Basic.NET
6 Comments2 Solutions293 ViewsLast Modified:
Hi Gurus,

I have an SQLProfiler  XML output file with size 2 GB. I need to know the best way to insert that file into a database using VB.Net, but not as an XML file; I need to populate the database table with its fields. I will *not* be using XQuery.

One thing else: I need to know the T-SQL syntax required to monitor SQL performance and have such tables generated without the need to do the task above. I know, I could have done that from the start, but it was an emergency case that I needed to monitor right-away.


<TraceData>
  <Events>
    <Event id="65534" name="Trace Start">
      <Column id="14" name="StartTime">2009-10-26T09:14:05.357+00:00</Column>
    </Event>
    <Event id="15" name="Audit Logout">
      <Column id="11" name="LoginName">Name</Column>
      <Column id="15" name="EndTime">2009-10-26T09:14:05.39+00:00</Column>
      <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
      <Column id="12" name="SPID">150</Column>
      <Column id="14" name="StartTime">2009-10-26T09:14:05.39+00:00</Column>
      <Column id="16" name="Reads">76932</Column>
      <Column id="18" name="CPU">0</Column>
      <Column id="9" name="ClientProcessID">5920</Column>
      <Column id="13" name="Duration">0</Column>
      <Column id="17" name="Writes">0</Column>
    </Event>
    <Event id="15" name="Audit Logout">
      <Column id="11" name="LoginName">Name</Column>
      <Column id="15" name="EndTime">2009-10-26T09:14:05.42+00:00</Column>
      <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
      <Column id="12" name="SPID">55</Column>
      <Column id="14" name="StartTime">2009-10-26T09:14:05.093+00:00</Column>
      <Column id="16" name="Reads">15271414</Column>
      <Column id="18" name="CPU">0</Column>
      <Column id="9" name="ClientProcessID">1172</Column>
      <Column id="13" name="Duration">326</Column>
      <Column id="17" name="Writes">112</Column>
    </Event>
    .............
</Events>
</TraceData>
ASKER CERTIFIED SOLUTION
kworks

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros