ApexSQL Knowledgebase

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Data Diff
Apex SQL Debug
Apex SQL Diff
Apex SQL Diff API
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Log API
Apex SQL Recover
Apex SQL Refactor
Apex SQL Report
Apex SQL Script
Apex SQL Source Ctl
Apex SQL SSIS Compare

Knowledgebase Home

MOST RECENT

Download links to old versions

Can ApexSQL Edit 2005 and 2008 use the same Source Control project?

How do I get my Hardware ID?

ApexSQL Log Pre-Installation Considerations

How to synchronize data from various off-line sources into a central data repository?

How to use ApexSQL Script C# solutions to execute ApexSQL Diff synchronization scripts

Managing the ApexSQL ConnectionMonitor 2008.exe process

SITE FEED

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

Download links to old versions

DESCRIPTION
Here are the download links to the 2005 versions of our ApexSQL tools if you want to (or have been advised to by ApexSQL support) downgrade to the 2005 version:

LINKS
ApexSQL Audit Viewer 2005.01
ApexSQL Audit 2005.07
ApexSQL Edit 2005.26
ApexSQL Diff 2005.14
ApexSQL Doc 2005.13
ApexSQL Code 2005.13
ApexSQL Code 2005.05 - Supports legacy VBScript templates
ApexSQL Clean 2005.09
ApexSQL Log 2005.11
ApexSQL Script 2005.11
ApexSQL Report 2005.02

AUTHOR
Alexey Daryin

LAST REVIEW DATE
26 June 2009

Labels: General

...

posted by Alexey Daryin on Friday, June 26, 2009 0 comments

Can ApexSQL Edit 2005 and 2008 use the same Source Control project?

DESCRIPTION
This article describes how to map your database to a Source Control system project if you want to use ApexSQL Edit 2005 and ApexSQL Edit 2008 at the same time.

SOLUTION
If you want to run ApexSQL Edit 2005 and 2008 at the same time and use the Source Control functionality for a database mapped to a Source Control project, then you should map your database to different Source Control projects in each ApexSQL Edit version.
One Source Control project should be used by ApexSQL Edit 2005 and the other one by ApexSQL Edit 2008 as on the screen shot below:

Such a configuration is required because both application versions handle database mappings in a different way. Using the same Source Control project for both application versions will result in a non consistent status of database objects mapped to a Source Control system.

Note: If you upgrade ApexSQL Edit 2005 to 2008 you should remap your database to allow the new application version manage correctly a database mapped to a Source Control system. Just remove database mapping and then map your database to a Source Control again.

SEE ALSO
Configuring ApexSQL Edit to work with ApexSQL Source Control

AUTHOR
Anna Lesniak

LAST REVIEW DATE
22 June 2009

Labels: Apex SQL Edit, Apex SQL Source Control

...

posted by Anna Lesniak on Monday, June 22, 2009 0 comments

How do I get my Hardware ID?

DESCRIPTION
This article describes the different ways you can get your hardware ID.

What is a hardware ID?
A hardware ID is a string containing a set of numbers, characters, and/or symbols that uniquely identifies your computer/server. A Hardware ID may look like this: kfEgHSUTRK=.

It is used by ApexSQL to identify the machine you activated your ApexSQL software on.

How do I get my machine's hardware ID?

1) You can get your hardware ID from the Activation Form that pops up when you start your ApexSQL software if you're using the trial version:

2) If the trial period of not software has not yet expired or if you're using an already activated software, you can start the application and go to Help | About to get the hardware ID:

3) If for any reason you can't access any of the above forms because you can't start your ApexSQL Software, install another ApexSQL application using the ApexSQL Installer. Next, use #1 or #2 to get the hardware ID.

4) For ApexSQL Log and ApexSQL Recover, what's activated is the server. To get the hardware ID of the server, you must first be able to connect to the server successfully. Next, use #1 or #2 to get the hardware ID.

If the trial period has expired and you are unable to successfully connect to the server, execute the following commands on your SQL Server:

a) For ApexSQL Log 2005
EXEC master.dbo.xp_ApexSqlLog 20061002

b) For ApexSQL Log 2008
EXEC master.dbo.xp_ApexSqlLog2008 20061002

c) For ApexSQL Recover
EXEC master.dbo.xp_ApexSqlRecover2008 20061002

Note that ApexSQL Log or ApexSQL Recover must be installed to execute these statements.

AUTHOR
Janice Lee

LAST REVIEW DATE
18 June 2009

Labels: General

...

posted by Janice Lee on Monday, June 15, 2009 0 comments

ApexSQL Log Pre-Installation Considerations

DESCRIPTION
This article describes things you should know before installing ApexSQL Log's server-side components on your SQL Server. It enumerates what exactly is installed on your server. ApexSQL Log's installation requirements are fairly small and their overall effect on performance is negligible.

What does ApexSQL Log install on my SQL Server?

A. At the file level, ApexSQL Log installs the following; these are saved in the SQL Server instance’s ‘Binn’ folder.

For ApexSQL Log 2005:

1) ApexSqlServerXprocs.dll - the DLL containing all extended procedures used by ApexSQL Log and other ApexSQL products with server-side components.
2) ApexSqlConnectionMonitor.exe - the executable for Connection Monitor component of ApexSQL Log.
3) ApexSqlServerHelper.exe - the executable used by ApexSQL Log extended procedures for task done externally to SQL Server process.
4) ApexSqlServerHelper.sys - the service executable used by ApexSQL Log extended procedures.
5) ApexSqlLogActivation.exe - the executable used by ApexSQL Log to keep track of activation and evaluation states.
6) ApexSqlLogApiActivation.exe - the executable used by ApexSQL Log API to keep track of activation and evaluation states.

For ApexSQL Log 2008, the names were changed and ApexSQL Recover activation was added:

1) ApexSqlServerXprocs2008.dll
2) ApexSqlConnectionMonitor2008.exe
3) ApexSqlServerHelper2008.exe
4) ApexSqlServerHelper2008.sys
5) ApexSqlLogActivation2008.exe
6) ApexSqlLogApi2008Activation.exe
7) ApexSqlRecover2008Activation.exe - the executable used by ApexSQL Recover to keep track of activation and evaluation states.

B. At the server-object level, the following are added to the master database:

For ApexSQL Log 2005--
xp_ApexSqlConnectionMonitor
xp_ApexSqlConnectionMonitor_Info
xp_ApexSqlConnectionMonitor_Stop
xp_ApexSqlConnectionMonitor_Enable
xp_ApexSqlConnectionMonitor_Disable
xp_ApexSqlLog
sp_ApexSqlConnectionMonitor_Start

For ApexSQL Log 2008--
xp_ApexSqlConnectionMonitor2008
xp_ApexSqlConnectionMonitor2008_Info
xp_ApexSqlConnectionMonitor2008_Stop
xp_ApexSqlConnectionMonitor2008_Enable
xp_ApexSqlConnectionMonitor2008_Disable
xp_ApexSqlLog2008
sp_ApexSqlConnectionMonitor2008_Start
xp_ApexSqlLogApi2008
xp_ApexSqlRecover2008

C. New table added to the MSDB database
One table called msdb..APEXSQL_LOG_LOGIN is also added. It's used for storing login information as part of ApexSQL Log’s Connection Monitor (see below for more information on the connection monitor).

What is the ApexSQLConnectionMonitor.exe for?
To obtain NT user name and other connection specific information, ApexSQL Log uses active connection monitoring through the ApexSQLConnectionMonitor.exe process. This process is designed to be very lightweight, fast, and non-intrusive so it does not interrupt the normal functioning of Microsoft SQL Server. Once the Connection Monitor is installed (it is a part of installed server side components), it will, for each new connection, store a row into the APEXSQL_LOG_LOGIN table. During auditing, ApexSQL Log queries the login table to obtain connection specific information for each transaction. ApexSQL Log returns this information as blank if information cannot be obtained.

Note: Connection monitoring works only for Microsoft SQL Server 2000 and SQL Server 2005. It requires that the account under which SQL Server service is running has login permission to the SQL Server itself.

Connection monitoring also allows user names on operations from linked servers (that occurred while it was alive) to be known. Moreover, with connection monitoring, ApexSQL Log can show user names even for dropped users. As not to consume machine resources, ApexSQL Log stops the process when it fails consecutively for 5 times in 1 minute or less.

The ApexSQL Log Connection Monitor inserts one new row into msdb.dbo.APEXSQL_LOG_LOGIN database for each new login. So this table grows directly in proportion to the number of logins made to the server. The amount of data is dependent on the number of logins. Please see this troubleshooting item for more information: click here.

Do all these cause performance overhead?
ApexSQL Log has no performance overhead during audit trail capture since it is not involved in it at all. The application simply reads the transaction logs to gather audit data. This allows auditing to be performed during low load times or even be offloaded to another server. Additional login information (NT user name, application name, and client host name), which is not available from the transaction log of SQL7 and SQL2000, is the only information that is actively captured. This capturing process is done by ApexSQL's Connection Monitor which has minimal overhead. SQL2005 and SQL2008 database's transaction log contains NT user's SID information from which full user name can be extracted. In this case, if application name and client host name are not necessary, ApexSQL's Connection Monitor can be stopped and disabled.

SEE ALSO
Managing the Connection Monitor process
Here's a KB article that describes exactly what you need to do to stop, start, enable, or disable the connection monitor WITHOUT uninstalling it: Managing the ApexSQLConnectionMonitor2008.exe process.

Uninstalling ApexSQL Log's Server-side Components
To uninstall the server-side components completely, please refer to this article: http://www.apexsql.com/training/2007/08/how-to-uninstall-ape xsqlconnectionmonit_09.htm.

AUTHOR
Janice Lee

LAST REVIEW DATE
12 June 2009

Labels: ApexSQL Log, ApexSQL Log API

...

posted by Janice Lee on Friday, June 12, 2009 0 comments

How to synchronize data from various off-line sources into a central data repository?

DESCRIPTION
This article describes a typical data merging scenario, between the 2 data tables, and explains how to customize that scenario.

SOLUTION
A common problem that can be solved using ApexSQL Data Diff data synchronization tool is synchronizing data from various off-line sources into a central data repository. This is a case when mobile devices (laptops) are used to capture data, and later the data is merged to the central data repository.

Here are defined Central and Mobile databases with the Person table.

-- Create script for the Central database
CREATE DATABASE Central
GO
USE Central
GO
CREATE TABLE Person (
ID int PRIMARY KEY IDENTITY(1,1),
personID int NOT NULL,
name NVARCHAR(255) NOT NULL,
address NVARCHAR(255)
)
-- Insert scripts for table: Person
SET IDENTITY_INSERT [dbo].[Person] ON
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (1, 1001, N'Central 1', N'Kings Place 200')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (2, 1002, N'Central 2', N'Kings Place 200')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (3, 1003, N'Central 3', N'Kings Place 200')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (4, 1004, N'Central 4', N'Kings Place 200')

SET IDENTITY_INSERT [dbo].[Person] OFF
-- Create script for the Mobile database
CREATE DATABASE Mobile
GO
USE Mobile
GO
CREATE TABLE Person (
ID int PRIMARY KEY IDENTITY(1,1),
PersonID int NOT NULL,
NameID NVARCHAR(255) NOT NULL,
Address NVARCHAR(255)
)
-- Insert scripts for table: Person
SET IDENTITY_INSERT [dbo].[Person] ON
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (1, 2001, N'Mobile 1', N'Blumen')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (2, 2002, N'Mobile 2', N'Blumen')
INSERT INTO [dbo].[Person] ([ID], [personId], [name], [address]) 
VALUES (3, 2003, N'Mobile 3', N'Blumen')

SET IDENTITY_INSERT [dbo].[Person] OFF

Now, try to merge Mobile persons to a Central in the following steps:

Step 1. Select data sources

Set the Mobile database as a source data source and the Central database as a destination data source in ApexSQL Data Diff.

After pressing the Compare button, we will get 3 different rows and a single additional row.

Notice a key sign near the ID field. It means Comparison Key is set to the ID field.
However, this showcase needs to differentiate persons based on personID field, not on ID. The next step will describe how to do that.

Step 2. Set a Comparison Key

From Project Settings -> Object Filter tab find a Person table, and from the Comparison Key column choose to Edit Custom Comparison Key as in the image (set a personID field as a comparison key and click OK).

Missing persons by personID will be automatically selected for synchronization.

The problem is the additional rows. If you left them selected they will be deleted from the Central database. You should unselect them as in the image.

Step 3. Press the Synchronize button to open the Synchronization Wizard

Make sure that the destination is the Central database.

Make sure Missing Records are selected in the Record Filter step.
After you press Synchronize button you will get the following script:

USE [Central]
GO

BEGIN TRAN

-- =======================================================
-- Synchronization Script for Table: [dbo].[Person]
-- =======================================================
Print 'Synchronization Script for Table: [dbo].[Person]'

SET IDENTITY_INSERT [dbo].[Person] ON
INSERT INTO [dbo].[Person] ([personID], [ID], [name], [address]) 
VALUES (2001, 1, N'Mobile 1', N'Blumen')
INSERT INTO [dbo].[Person] ([personID], [ID], [name], [address]) 
VALUES (2002, 2, N'Mobile 2', N'Blumen')
INSERT INTO [dbo].[Person] ([personID], [ID], [name], [address]) 
VALUES (2003, 3, N'Mobile 3', N'Blumen')
SET IDENTITY_INSERT [dbo].[Person] OFF

COMMIT

Problem with this script is that the primary key ID for the Person table is scripted also causing the error:

Violation of PRIMARY KEY constraint 'PK__Person__3214EC277F60ED59'.
Cannot insert duplicate key in object 'dbo.Person'.

Solution to that problem is not to use the ID field when synchronizing—use the Columns column (Project Settings Object Filter tab), and unselect the ID field as in the image.

Now after we repeat the step 3 the synchronization will went fine.

USE [Central]
GO

BEGIN TRAN

-- =======================================================
-- Synchronization Script for Table: [dbo].[Person]
-- =======================================================
Print 'Synchronization Script for Table: [dbo].[Person]'

SET IDENTITY_INSERT [dbo].[Person] ON
INSERT INTO [dbo].[Person] ([personID], [name], [address]) 
VALUES (2001, N'Mobile 1', N'Blumen')
INSERT INTO [dbo].[Person] ([personID], [name], [address]) 
VALUES (2002, N'Mobile 2', N'Blumen')
INSERT INTO [dbo].[Person] ([personID], [name], [address]) 
VALUES(2003, N'Mobile 3', N'Blumen')
SET IDENTITY_INSERT [dbo].[Person] OFF

COMMIT

Summary
ApexSQL Data Diff enables you to:
* Select a Comparison Key (what column or columns ApexSQL Data Diff will track for missing/different/additional records)
* Select Columns you want to synchronize.
* Select row types to synchronize (Different, Missing, Additional)
* Select individual rows to synchronize (using the checkboxes)

Also the other options in ApexSQL Data Diff we haven’t explained here are :

* Disable Foreign Keys and Triggers
* Drop not Unique Indexes
* Disable Check Constraints
* Apply Data Format
* Filtering Rows for Comparison

LAST REVIEW DATE
Jun 12 2009

Labels: Apex SQL Data Diff

...

posted by Dejan Batanjac on Thursday, June 11, 2009 0 comments

How to use ApexSQL Script C# solutions to execute ApexSQL Diff synchronization scripts

DESCRIPTION
This article describes how to use ApexSQL Script C# solutions for execution ApexSQL Diff synchronization scripts that will update your database: once compiled you can use the gotten .Net package to update client databases on different machines

SOLUTION

Before creating a .Net Package that will apply your changes to a database you need a C# solution. To obtain such a solution you can choose among two options:
- Download the ready solution from here. Then you can skip the step 1) below
- Use ApexSQL Script to create it: then please proceed with the step 1)

1) For the result C# solution created by ApexSQL Script you need at least one SQL Server 2000, 2005 or 2008 available at your network. Then you can use any database (for example, AdventureWorks or master database) to manually create a stub solution as follows:

a) Start ApexSQL Script and select any object(s) from any database to script: select a database at the right hand Select Database panel and select an object or objects in the main grid:

b) Run the Script Wizard and select C# Project as Output type:

c) Click the Create button and select the directory where your project will be created:

After the step is passed the stub C# project is created and is ready to use for the next step.

2) If you have already had a ready synchronization script: please skip this step. Otherwise use ApexSQL Diff.

a) Start ApexSQL Diff and select necessary databases for synchronization:

b) Using the Project Settings Wizard define necessary comparison options and object filters

c) Click the Compare button

d) Select the necessary objects to synchronize in the main grid and click the Synchronize tool button to start the Synchronization Wizard

e) In the Synchronization Wizard define all necessary options and then select the Write Script To File Only option at the Output Options Step. Define the directory where your script will be saved. You can save your file in the directory containing C# project made at the previous step:

f) Click the Synchronization button and your script will be created and saved at the chosen folder

3) At this step you should have a stub C# project and a synchronization script you want to apply to your database. To build the .Net package with the synchronization script please proceed with the next steps:

a) Open the directory where your C# project is saved. Enter to the directory with your Project name (in the example it is LOCAL@SQL2005.Aworks2005 Installer). Find the file with the *.dat extension. Actually it is a SQL script with objects scripted from the 1) step that can be deleted without a problem. Then find the synchronization script (file with *.sql extension – in the example “Synchronization.sql”)

and rename it to “LOCAL@SQL2005.Aworks2005 Installer.dat”.

b) Return back to the main project directory and start the *.sln file. Then Microsoft Visual Studio with this C# project will be opened

Now to create a .Net package just build the solution and then it will be ready to use on your environment.

AUTHOR
Andrew Guselnikov

LAST REVIEW DATE
11 June 2008

Labels: Apex SQL Diff, Apex SQL Script

...

posted by Andrew Guselnikov on Thursday, June 11, 2009 0 comments

Managing the ApexSQL ConnectionMonitor 2008.exe process

DESCRIPTION
This article describes how to stop, start, enable, or disable the ApexSQL Log Connection Monitor. It also describes how to get its current status (e.g. Is it running? Disabled? etc.)

For more information about ApexSQLConnectionMonitor.exe and how to uninstall it (and ApexSQL Log's other server-side components), please click here.

SOLUTION
To manage the ApexSQLConnectionMonitor2008.exe process, run the following commands in Microsoft SQL Server's master database:

To start the ApexSQLConnectionMonitor2008.exe process

EXEC MASTER.dbo.Sp_apexsqlconnectionmonitor2008_start

The connection monitor must be enabled before it can be started.

To stop the ApexSQLConnectionMonitor.exe process

EXEC MASTER.dbo.Xp_apexsqlconnectionmonitor2008_stop

When the server restarts, the ApexSQLConnectionMonitor2008.exe process will start again. If you don't want this to happen, disable the process after stopping it.

To disable the ApexSQLConnectionMonitor.exe process

EXEC MASTER.dbo.Xp_apexsqlconnectionmonitor2008_disable

To enable the Connection Monitor

EXEC MASTER.dbo.Xp_apexsqlconnectionmonitor2008_enable

To see the current status of the Connection Monitor

EXEC MASTER.dbo.Xp_apexsqlconnectionmonitor2008_info

AUTHOR
Janice Lee

LAST REVIEW DATE
10 June 2009

Labels: ApexSQL Log

...

posted by Janice Lee on Wednesday, June 10, 2009 0 comments