asked on

Insert SQLProfiler Trace Output XML file into database

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>

Open in new window

Raja Jegan R

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

If you want to get that stored into the database as a table, then you can direct the profiler output to the table itself directly instead of saving as an XML file and then parsing the XML file to store it in database again as a table.

Stored procedures below would help you in creating the profiler trace along with configuring profiler events.

http://msdn.microsoft.com/en-us/library/ms187346.aspx

kworks

ASKER

rrjegan17:

A more accurate intro would have been this..

http://msdn.microsoft.com/en-us/library/ms191443.aspx

since there is a direct link to description of how to store into table.

But this only answers part-II of my question. I still need answer to part-I.

SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zberteoc

The easiest and fastes way ti import XML into SQL is to use the SQLXMLBulkLoad object. A description of how to use it can be found here:

http://support.microsoft.com/kb/316005

and in more detail here:

http://msdn.microsoft.com/en-us/library/ms171806.aspx

SQLXMLBulkLoad object is usually installed with the SQL server and with .NET Framework if not it can be found here:

http://www.microsoft.com/DOWNLOADS/details.aspx?FamilyID=51d4a154-8e23-47d2-a033-764259cfb53b&displaylang=en

ASKER CERTIFIED SOLUTION

kworks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kworks

ASKER

The thing is, even with OPENXML, the memory needed is too much.