Automate the configuration of the Query Log

Hi

I am looking at automating an AS 2005 build which will include setting up the Query Log. Can this step be automated.

Thanks

There are a couple of server properties for QueryLog settings (you can see all of them with SQL Management Studio: right click on the server item, chose 'Properties' and look for 'Log \ QueryLog' items). The query log settings are per entire server.

To automate setting up query log settings, here are some options:

1. Script the Server Properties dialog (from SQL Management Studio - the dialog has a 'Script' button at the top) and then run the script with an Integration Services task or with AMO. You can remove from the script all the other server properties that you don't want to touch (keeping the script small, clear and safe).

To run the script with AMO, this is sample C# code:

(in your C# project, add a reference to Microsoft.AnalysisServices.DLL from '%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies')

...
using Microsoft.AnalysisServices;
...

Server s = new Server();

s.Connect("localhost");

try
{
XmlaResultCollection results = s.Execute("Put here the script copy-pasted from SQL Management Studio");

foreach (XmlaResult result in results)
{
foreach( XmlaMessage message in result.Messages )
{
Console.WriteLine(message.Description);
if (message is XmlaError)
{
// FAILURE: the script failed, you need to do something here
}
}
}
}
finally
{
s.Disconnect();
}

2. Use AMO to change the server properties. Sample code:

...
using Microsoft.AnalysisServices;
...

Server s = new Server();

s.Connect("localhost");

try
{
ServerProperty sp;

// setup the query log file path
sp = s.ServerProperties[@."Log\QueryLog\QueryLogFileName"]; // this throws exception if lookup fails
sp.Value = @."c:\MyQueryLog.txt";

// setup the query log file size
sp = s.ServerProperties[@."Log\QueryLog\QueryLogFileSize"]; // this throws exception if lookup fails
sp.Value = "10"; // 10 MB

// Now save everything; the properties we set don't require server restart.
s.Update();
}
finally
{
s.Disconnect();
}

Adrian Dumitrascu

|||

Adrian

Cheers i'll give it a try.