Solutions Products Download Purchase Support News Members Company
SITE FEED
Support Forum
The fastest and most effective to get answers to your questions quickly.
FAQs
Quick answers to common questions.
Troubleshooting
Known issues and basic troubleshooting techniques for problems or unexpected behaviors.
Knowledgebase
Technical tips, How-to articles, and other tutorials about ApexSQL Tools.
Online Documentation
All of our help files for download or convenient viewing online.
Videos
Watch product demos, training videos, or tutorials of our products' main features. 

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools


How to send email alerts when a change is made to a database record

DESCRIPTION
This article discusses how to customize the ApexSQL Audit architecture so that email alerts are sent out when changes are made to a database record.

These instructions make use of Database Mail and can only be used if you're using MS SQL Server 2005 or MS SQL Server 2008.

SOLUTION

1) Configure Database Mail.

2) Download ApexSQL2049_Mail.audx here. This architecture is specifically customized to allow for the sending of email alerts when a change occurs. Save this file in ApexSQL Audit's application folder. For example, C:\Program Files\ApexSQL\ApexSQLAudit2008.

3) Start ApexSQL Audit and connect to the database you want to audit.

4) Click File | Select Audit Architecture and select ApexSQL2049_Mail.audx.

5) Click File | Template Editor to modify the template.

4) Next, find and modify the following lines of code. Base the values you set here on how you set up your Database Mail.

'Customize values below
Mail_Profile = "John"
Mail_Recipients = "John@mymail.com"
Mail_Body = "Please verify database change."
Mail_Subject = "Unauthorized Database Change"

5) Click Run | Process and save the architecture file.

6) Close the Template Editor.

7) Create your triggers. If you have triggers set up already, they would have to be recreated if you want them to use the new architecture template.

What happens next? Triggers created using this template, when fired, will not only store audit data but will also send email alerts indicating that a change has been made.

Written by:
Janice Lee

Last Modified Date:
2 Feb 2010

Labels:

...


Object-level differences in ApexSQL Diff CLI

    Sometimes it is helpful to see objects differences while comparing data sources in CLI mode of ApexSQL Diff application. This feature is available in ApexSQL Diff

Comparison summary.
ApexSQL Diff now shows comparison results in CLI in text mode.


Object level differences.







Object level differences information is displayed in form of table with three columns “Name”, “Owner” and “Result” for each compared object. These tables are grouped by object type that are compared the way objects are grouped in ApexSQL Diff GUI.

Result column can have one of the following values:

1) == - if compared objects are equal;

2) <> - if compared objects differs;

3) >> - if object exists only in source data source and does not exist in target data source;

4) << - if object exists only in target data source and does not exist in source data source;


In case if database objects have different owners and mapped, both owners will be displayed in result objects table.







In this case tables “AWBuildVersion” and “ErrorLog” has “Person” owner in source data source and “dbo” owner in target data source.


Script-level differences.







For each compared object, ApexSQL Diff displays its script. If objects are different, it also shows exact differences on script level (the way similar to ApexSQL Diff GUI).

First two symbols of each line of the script have one of the following values:

1) == - if lines are equal in source and target scripts;

2) >> -displays line from source script (in case lines if source and target scripts are different);

3) << - displayed line from target script (in case lines if source and target scripts are different);


For example, String data type is displayed like:







From this screen you can see that String data type differ in second line of script.

Source script contains FROM [varchar](150) line

Target script contains FROM [varchar](100) line

All other lines are equal for both scripts.

When object exists only in source or in target data source, the whole object script is displayed using “>>” and “<<” prefixes.

The summary and details information are displayed if /v switch is specified for CLI comparison. The information is displayed in standard output stream, so it can be redirected to the file for further analysis and saving.


Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info.

Labels:

...


How do I upgrade ApexSQL Refactor?

DESCRIPTION
This article describes how to upgrade ApexSQL Refactor

SOLUTION
Download the latest version of ApexSQL Refactor here. The free version is no longer offered for download but a freeware version of the current ApexSQL Refactor may be available soon.

Before starting ApexSQL Installer, be sure that SQL Server Management Studio is closed. Start Setup wizard and install ApexSQL Refactor add-in over existing one.

After upgrading to the latest ApexSQL Refactor 2010 version, in some cases when starting Microsoft SQL Server Management Studio, remove add-in dialog displays with the message that ApexSQL Refactor add-in is failed to load or causes an exception.


Click Yes to remove ApexSQL Refactor add-in. Then please start ApexSQL Refactor uninstaller and from its dialog select Yes for removing all existing ApexSQL Refactor versions.




Execute installer again and install the latest ApexSQL Refactor version again.

After performing these steps you should be able to execute Microsoft SQL Server Management Studio and use ApexSQL Refactor add-on without any problems.

AUTHOR
Dragana Ilic

LAST REVIEW DATE
18 January 2010

Labels:

...


ApexSQL Log update to the latest public version

DESCRIPTION
ApexSQL Log auto-updater updates the client side of the application only. So server-side components and Connection Monitor from the previous version of ApexSQL Log still reside on the SQL Server and are running. This part of the application needs to be updated manually.

Server-side components update could be performed in the two ways:
1. Through the Connection Monitor Manager dialog: the Connection Monitor is fully configurable from the application GUI (Tools | Connection Monitor Manager) starting from the ApexSQL Log 2008.05. Application offers server-side components update, otherwise Connection Monitor management will not be available.

Connection Monitor Manager dialog consists of the two tabs: Connection Status and Data Capture that enable to manage Connection Monitor Log On options as well as a possibility to enable/disable the monitor and to choose a database for storing captured data.


Connection Status tab contains information about selected server, log on type, connection monitoring state and status.

Data Capture
tab contains settings for:
1. Database where user wants to store login information
2. Captured information maintenance


Custom database to store login information could be selected here with the automatic or manual maintenance of the captured information.

2. Application automatically offers server-side components update at first auditing: since old server-side components are not compatible with the latest client, without this update auditing will not start.

However even after the server-side components are updated the Connection Monitor keeps saving data into msdb.APEXSQL_LOG_LOGIN table. This behavior can be changed through the Data Capture tab of the Connection Monitor Manager dialog (click here for more details ).

Note: After the application is auto-updated you need to manually run vcredist_x86.exe which is updated and placed into ApexSQL Log installation folder (e.g. C:\Program Files\ApexSQL\ApexSQLLog2008) on the machine where SQL Server is installed. This installer requires for correct work of server-side components.

AUTHOR
Dmitriy Dyubchenko

LAST REVIEW DATE
12 January 2010

Labels:

...


Where is stored ApexSQL application layout?

DESCRIPTION
This article describes where are saved layout settings of any ApexSQL application.

SOLUTION
While working with ApexSQL applications you will probably have your own preferences such as:
* Whether your application will start minimized or maximized
* Preferred width and height for your application after a startup
* Preferred width and height of other panels, toolbars, and dialogs in your application?
* Preferred layout of toolbars and menus

Those preferences are saved to a Layouts.xml file located in the application installation folder. Every time you close your ApexSQL application Layouts.xml file will be updated to contain latest layout settings.



You can save Layouts.xml file and reuse it later, in a case your preferred application layout gets overwritten after application upgrade or auto-update.

SEE ALSO
Why do I lose my panel/menu/toolbar settings after application upgrade?

AUTHOR
Dejan Batanjac

LAST REVIEW DATE
25 November 2009

Labels:

...


How to audit only some users in ApexSQLAudit?

DESCRIPTION
This article discusses how to audit only chosen users with ApexSQL's Audit

SOLUTION
This is possible through changing the Trigger Template. To access the Trigger Template, click on the Template Editor button found on the Outlook Bar Auditing section:




This will start Template Editor:



Next, you should find the part where trigger for INSERT command is created:

'==================================================
'INSERT INSERT INSERT INSERT
'==================================================

caption = "Create Insert Trigger " & "[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]" & _ ">" for Table " & TableFullName%}
IF OBJECT_ID('{%Audit.Print replace("[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]", "'", "''") %}','TR') IS NOT NULL
BEGIN
DROP TRIGGER {%Audit.Print "[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]" %}
PRINT '{% Audit.Print "Trigger Dropped: " & StartTriggerNameInsert & Replace(objTable.Name,"'","''")%}'
END
GO
-- {%Audit.Print caption%}
Print '{%Audit.Print Replace(caption, "'", "''")%}'
go
CREATE TRIGGER {%Audit.Print "[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]" %}
ON {%Audit.Print TableFullName%}
FOR INSERT
NOT FOR REPLICATION
As
-- "SQLAUDIT GENERATED - DO NOT REMOVE"
-- -------------------------------------------------------------------------------------------
-- Legal: {%Audit.Print Audit.Legal%}
-- Description: INSERT TRIGGER for Table: {%Audit.Print TableFullName%}
-- Author: {%Audit.Print Audit.Author%}
-- Date: {%Audit.Print Now%}
-- -------------------------------------------------------------------------------------------
BEGIN

there you should add following:

IF (SUSER_SNAME() <> N'ComputerName\UserName1') AND (SUSER_NAME() <> N'ComputerName\UserName2') RETURN


Where ComputerName\UserName1 and ComputerName\UserName2 are the user names you want to include in auditing in case they use Windows Authentication on connecting to your server or UserNam1 and UserName2 in case they use SQL Authentication.

These steps you need to repeat for UPDATE (found below this)
'==================================================
' UPDATE UPDATE UPDATE UPDATE UPDATE
'==================================================


and DELETE trigger template.
'==================================================
' DELETE DELETE DELETE DELETE
'==================================================


with the same command:

IF (SUSER_SNAME() <> N'ComputerName\UserName1') AND (SUSER_NAME() <> N'ComputerName\UserName2') RETURN

Then you need to recreate triggers on your tables. First, check tables and columns you want to audit, and then create triggers by clicking on the Create Triggers button:



Script will be built. You just need to execute it:



This will recreate all triggers, and now only users with given names will be audited.

SEE ALSO
How to audit user names in ASP.Net application
How to update ApexSQL Audit without losing custom changes in the architecture
Schedule Audit Report generation
How to Delete Old Data in Audit tables


AUTHOR
Milic Vuletic

LAST REVIEW DATE
19 November 2009

Labels:

...


How to integrate ApexSQL Log API components into custom installer

DESCRIPTION
This article describes solutions for integrating ApexSQL Log API components into custom installer

SOLUTION
ApexSQL Log API components can be integrated into custom installer in two ways. The first one is running original ApexSQL Log API setup with the INNO setup switches for silent mode installation. The second solution is to create simple .NET application which will copy all required files, register them, create extended procedures on a server and check the activation status.
Let’s start with the first solution: running ApexSQL Log API installer from your installer:
To use the silent mode installation, use ApexSQL Log API setup with the following switches:
/VERYSILENT – this suppresses all installation wizard dialogs
/SUPPRESSMSGBOXES – this suppresses all messages from the installer
/LOADINF="filename" – this will load the previously saved installer settings (such as the installation directory, shortcuts and so forth…)
/SAVEINF="filename" – this will save all of your choices during an “ideal” installation (these choices will then be used in LOADINF switch)

1. Start ApexSQL Log API installer with SAVEINF switch from the command prompt (e.g. ApexSQLLogApi.exe /saveinf="logapi.inf"), make all necessary choices and finish the installation. This will save all your preferences to the .inf file In the following format:
[Setup]
Lang=eng
Dir=C:\Program Files\ApexSQL\ApexSQLLogApi2008
Group=ApexSQL\ApexSQL Log API 2008
NoIcons=0
SetupType=full
Components=client_side,server_side
Tasks=


2. Add this call to the custom setup step to run original ApexSQL Log API installer with the INF file created on previous step and the following switches:

ApexSQLLogApi.exe /VERYSILENT /SUPPRESSMSGBOXES /LOADINF =" logapi.inf"

The second solution is more complicated and consists of the below steps. The example of this solution can be found here

1. Create new Console Application project in Visual Studio and add a reference to the ApexSQL.Log.Api.dll in it

2. Add a reference to the ApexSql.Log namespace from within the code file:

      using ApexSql.Log;

3. Depending on how server-side installation will be performed add verification on the number of arguments passed to the console application from the customer setup. Let’s assume that application is receiving just server name

4. Configure engine by calling Config.ConfigureLogging
        static void ConfigureEngine()
{
// Engine configuration has to be done before first reference to Engine class.
// Setup logging file name and folder.
Config.LoggingConfiguration loggingConfig =
new Config.LoggingConfiguration();
loggingConfig.loggingEnabled =
true;
loggingConfig.logFileName = String.Format(
"ApexSqlLogApiInstallation.{0}.log", DateTime.Now.ToString("yyyyMMddHHmmss"));
Config.ConfigureLogging(loggingConfig);
}

5. Add verification on server-side components state on server and perform appropriate actions according to the current their state:
        static bool CheckVersionInformation(Database db)
{
ServerSideComponentsManager manager = Engine.CreateServerSideComponentsManager(db);

ServerSideComponentsState state = manager.ComponentsState;
switch (state)
{
case ServerSideComponentsState.NotInstalled:
{
Console.WriteLine(
"Server-side components are not installed. Installing server-side components to server {0}...",
db.Properties.server);
manager.InstallComponents();
break;
}
case ServerSideComponentsState.IncompleteInstallation:
{
Console.WriteLine(
"Server-side components are not correctly installed. Uninstalling and then installing server-side components on server {0}...",
db.Properties.server);

try
{
manager.UninstallComponents();
}
catch (System.Exception e)
{
Console.WriteLine(
"Exception occurred during uninstallation of server-side components: {0}", e.Message);
}

manager.InstallComponents();
break;
}
case ServerSideComponentsState.EarlierVersion:
{
Console.WriteLine(
"Installed server-side components are out of date (version {0}). Updating components on server {1}...",
manager.ComponentsVersion, db.Properties.server);
manager.InstallComponents();
break;
}
case ServerSideComponentsState.LaterVersion:
{
Console.WriteLine(
"Installed server-side components on server {0} are of later version ({1}). Please update your ApexSQL Log API version.",
db.Properties.server, manager.ComponentsVersion);
return false;
}
}

if (manager.ComponentsState == ServerSideComponentsState.CompatibleVersion)
{
Console.WriteLine(
"Installed server-side components on server {0} are compatible with this version of ApexSQL Log API.", db.Properties.server);
return true;
}
else
{
Console.WriteLine(
"Installed server-side components are still not up to date. Please check application log file for detailed errors.");
return false;
}
}

6. Compile the project

7. Add server-side component files to the custom installer:
  • ApexSqlLog2008XprocsIA64.dll
  • ApexSqlLog2008Xprocsx64.dll
  • ApexSqlLog2008Xprocsx86.dll
  • ApexSqlLog2008Activation.exe
  • ApexSqlLog2008ConnectionMonitorIA64.exe
  • ApexSqlLog2008ConnectionMonitorx64.exe
  • ApexSqlLog2008ConnectionMonitorx86.exe
  • ApexSqlLog2008ServerHelperIA64.exe
  • ApexSqlLog2008ServerHelperx64.exe
  • ApexSqlLog2008ServerHelperx86.exe
  • ApexSqlLogApi2008Activation.exe
  • ApexSqlLog2008ServerHelperIA64.sys
  • ApexSqlLog2008ServerHelperx64.sys
  • ApexSqlLog2008ServerHelperx86.sys

8. Add vcredist_x86.exe, which is part of the ApexSQL Log API setup to the custom setup

9. Add ApexSQL.Log.Api.dll to the custom setup

10. Add just compiled application to the custom setup

11. Extract and run vcredist_x86.exe from the setup

12. Extract compiled application, ApexSQL.Log.Api.dll and server-side components to
some temp location from the custom setup and run compiled application from here with the server specified as parameter

13. Server-side components should be installed on server passed to it as argument.

14. Example of the installation script which is used for ApexSQL Log API installation and created in the INNO you can find here

AUTHOR
Dmitriy Dyubchenko

LAST REVIEW DATE
26 November 2009

Labels:

...

© 2010 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use | Privacy Policy