I need to create a SQL Trace in C# so that I can capture the database traffic as I would’ve in SQL Profiler, and save it in to a file but I am getting the above error when running my code.
Below is a simple code that creates a ServerTrace and initialises the reader so that a trace can be used. The code compiles but when running the code, trace.InitializeAsReader
The server I have this code and running on it, has Visual Studio (v15 & 22), SSMS (not SQL instance – v17.9) and SQL Profiler (v14) installed and I can connect to a SQL Instance on a different server with SSMS and can open a trace to any database on this instance through SQL Profiler.
Although I have used SQL profiler for a long time, I am new to creating a trace programmatically.
Microsoft.SqlServer.Management
The following are the assemblies I have referenced for these libraries. Also as a test, I have tried referencing these from the project folder as well as from GAC which did not make any difference:
instapi140.dll, Microsoft.SqlServer.Instapi.dll, Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.ConnectionInfoExtended.dll
using System;
using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;
namespace PolMate
{
class Program
{
static void Main(string[] args)
{
ConnectionInfoBase connectioninfo = new SqlConnectionInfo();
//populate connectioninfo with the connection string details
TraceServer trace = new TraceServer();
trace.InitializeAsReader(conninfo, "c:\temp\Standard.tdf")
for (int count = 0; count < 30; count++)
{
while (trace.Read())
{
Console.WriteLine(trace["TextData"]);
}
}
}
public void StartTrace()
{
trace.Restart();
}
public void StopTrace()
{
trace.Stop();
}
public void SaveTraceToFile(string filePath)
{
trace.SaveAs(filePath);
}
}
I have done a lot of websearch on this error but every one pointing me to SQL Tools however I have not seen a practical resolution that helps.
I did also experimented with VS2015 with SQL Trace version 12 and 14.
There are two question on this topic that no one has offered any solution.