ApexSQL Knowledgebase

Download links to old versions

2009-06-26T00:50:00.000-04:00

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

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

2009-06-22T05:02:00.023-04:00

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

How do I get my Hardware ID?

2009-06-15T12:44:00.011-04:00

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

ApexSQL Log Pre-Installation Considerations

2009-06-12T14:12:00.007-04:00

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

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

2009-06-11T18:16:00.009-04:00

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

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

2009-06-11T06:56:00.007-04:00

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

Managing the ApexSQL ConnectionMonitor 2008.exe process

2009-06-10T14:43:00.010-04:00

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

How to configure What Is dialog to remain opened even if it loses focus

2009-06-10T08:16:00.011-04:00

In ApexSQL Edit 2008, What Is dialog closes when it loses focus. This is default behavior. However, users might want to change such behavior and keep What Is dialog opened even if it loses focus.

SOLUTION
To keep What Is dialog opened even if it loses focus, you should perform the following steps:

1) Open "AllSettings" dialog using Ctrl + Shift + S hot-key combination
2) Locate property "WhatIsCloseOnDeactivate"
Fastest way to accomplish this is by filtering "Setting" column by entering part of the property name/full name:

3) Change property value in "Value" column from "true" to "false" (without quotes) and close the dialog.

After this procedure, What Is window should remain open even if it loses focus.

AUTHOR
Dejan Apostolovic

LAST REVIEW DATE
10 June 2009

How to automatically change the case of SQL keywords into uppercase when they are typed in a Query Editor window?

2009-06-08T10:34:00.035-04:00

DESCRIPTION
This article describes how to configure ApexSQL Edit to automatically change the case of SQL keywords in SQL statements to uppercase without using the reformatting feature.

SOLUTION
It is possible to configure ApexSQL Edit to change automatically SQL keywords typed in a Query Editor window into uppercase. Just press the Ctrl+Shift+S keyboard shortcut to display the All Settings dialog box.
Find the UpperCaseReservedWords setting (you can filter all settings by typing the text ‘*Upper*’ in the filter row of this dialog box, like displayed on the screen shot below) and change its value to True.

From now on, all your SQL keywords will be changed to uppercase after they are typed in a Query Editor window and after the Enter key is pressed at the end of the line, like displayed on the screen shots below:

SQL keywords before pressing the Enter key:

SQL keywords after pressing the Enter key:

Note: You can also change the case of the whole text typed in a Query Editor window to uppercase, lowercase and ‘proper’ case using the Ctrl+U keyboard shortcut. This keyboard shortcut can be applied to comments included into your SQL scripts or to scripts themselves, if your database is not case sensitive.
Just select the text you want to change and press Ctrl+U:

The marked text will be changed to uppercase.

Press the U key again holding down the Ctrl key and the text will be changed to lowercase:

Press the U key again holding down the Ctrl key and the text will be changed to ‘proper’ case:

The Ctrl+U keyboard shortcut works in a round robin way.

SEE ALSO
ApexSQL Refactor
ApexSQL Edit Help - Formatting options

AUTHOR
Anna Lesniak

LAST REVIEW DATE
15 June 2009

How to start logging feature of ApexSQL Edit 2008

2009-06-04T08:00:00.005-04:00

In ApexSQL Edit 2005, logging feature was always active, recording problems encountered when using the application. This log file was helpful to the developers to locate cause of problems experienced by customers. In ApexSQL Edit 2008, this feature is turned Off by default.

This article should help you turning ON logging feature of ApexSQL Edit 2008.

SOLUTION
To turn ON logging feature in ApexSQL Edit 2008, you should perform the following steps:

1) Locate at your desktop the shortcut you are using for starting the application
2) Highlight it and open its properties (right-click the shortcut and click the Properties command from the context menu)
3) Locate the Target input box and modify its content by adding " log" (without quotes) at the end.
In example, change:
"C:\Program Files\ApexSQL\ApexSQLEdit2008\ApexSQLEdit.exe"
to
"C:\Program Files\ApexSQL\ApexSQLEdit2008\ApexSQLEdit.exe" log

4) Click the OK button to save changes

Next time you start the application, "apexsqledit.log" file will be created in the installation folder (in the example: "C:\Program Files\ApexSQL\ApexSQLEdit2008")

AUTHOR
Dejan Apostolovic

LAST REVIEW DATE
04 June 2009

How to subscribe to a forum topic

2009-06-04T07:32:00.012-04:00

DESCRIPTION
This article should help you subscribing to the desired forum topic if you are registered on the forum.

SOLUTION
To subscribe to the desired forum topic you need to perform the following steps:
1) Open forum topic link in internet browser application (all popular browsers are supported, like IExplorer 6.x - 8.x, Firefox 3.x ...)
2) Check the bottom of the page and locate "Watch the topic for reply" link:

3) Click on the link, and after short period confirmation message box will pop up:

As result of subscribing process, you will get notified of the forum update via email.

AUTHOR
Dejan Apostolovic

LAST REVIEW DATE
18 June 2009

Getting started with ApexSQL Report 2008 source code

2009-06-01T08:37:00.004-04:00

DESCRIPTION
This article should help you getting started with ApexSQL Report 2008 source code.

SOLUTION
The fastest way to get start working with the ApexSQL Report 2008 source code is to perform the following steps:

1. Create database used by ApexSQL Report 2008 by:
a) Creating empty database on your SQL Server 2005 (name it as you like)
b) Executing ApexSQLReportSetup.sql (found in Depending/Files folder)

2. Open web.config file (you can find it in the "ApexSQL Report 20008" solution folder), locate "apexsqlreport" tag and edit highlighted sections:

NOTE: You can also choose “Windows” as authentication mode but please make sure that sql server built in account ASPNET has dbo privileges for database used.

Initial credentials when you login for the first time are:
Username: Admin
Password: 123

We have bypassed activation component by adding compiler directive NOACTIVATION to project (it is used in Global.asax.cs file). If needed you may remove reference to activation dll and just comment out couple more lines of code.

AUTHOR
Dejan Apostolovic

LAST REVIEW DATE
01 June 2009

Welcome to the ApexSQL Knowledgebase

2009-05-28T15:25:00.014-04:00

Welcome to the ApexSQL Knowledgebase. This is an online resource that contains Technical Tips, How-to articles, and other tutorials about ApexSQL Tools.

What kind of additional information I can send about the problem I encountered in ApexSQL applications?

2009-05-26T08:49:00.019-04:00

DESCRIPTION
If you find a defect inside our applications, it would help us to duplicate the issue if you provide us additional information.

SOLUTION
Could you please send us the following information:

General:
• What is the exact application name and version you are using– please select the Help | About … command to verify them.
• What operating system is installed on the same machine as ApexSQL application?
• What is your processor and how much RAM your machine has?
• Do you work in a domain or in a workgroup?
• What is the SQL Server version you are working with. Use the following query to check it: SELECT @@version

Issue specific:
• Can you duplicate repeatedly the problem encountered?
• Does duping require a particular script/code/database structure?
• What actions were done prior the issue occurred?
• Can you send us the description of steps and screen shot, which will allow easily reproducing the problem?

Classified:
• Could you please provide us a database snapshot or ddl scripts of particular objects required for reproducing the issue? Please send any classified files to the support@apexsql.com email address.

SEE ALSO
What should I do when I encounter a problem in ApexSQL Log?

AUTHOR
Anna Lesniak

LAST REVIEW DATE
26 May 2009

How to transfer ApexSQL Edit formatting options between computers

2009-05-26T07:18:00.005-04:00

This Knowledgebase article discusses how to transfer ApexSQL Edit formatting options between computers. This can be very useful if you are using several computers (in example, work station in the office and laptop at home).

SOLUTION
In ApexSQL Edit 2008, to be transfer formatting options between computers, you must perform the following steps:

1) Open Formatting options dialog on "source" computer (computer where you have desired formatting options). You can do this via Tools | Formatting Options command or by pressing Ctrl + F8 key combination.

2) Click Export button to create and save FormattingSettings.xml file on desired location:

Default location for saving formatting options file is ApexSQLEdit2008 folder in My Documents\ApexSQL folder, like:
C:\Documents and Settings\{windows_user_name}\My Documents\ApexSQL\ApexSQLEdit2008\

3) Copy file created to "destination" computer (computer which formatting options you wish to make equal to formatting options on "source" computer).

4) Open Formatting options dialog on "destination" computer.

5) Click Import button and browse to FormattingSettings.xml file you copied.

6) Click Apply command and close Formatting Options dialog.

As result of this command, new formatting options will be applied and you will have formatting options on "destination" computer equal to formation options on "source" computer.

AUTHOR
Dejan Apostolovic

LAST REVIEW DATE
26 May 2009

Why I'm experiencing slowdowns in ApexSQL Edit Schema Explorer after mapping a database to a Source Control.

2009-05-25T06:57:00.022-04:00

DESCRIPTION
After my database is mapped to a Source Control system and actions like:

• adding a new object to a Source Control system or
• refreshing a database folder

are run,
then, ApexSQL Edit can work significantly slower and can consume the substantial amount of system resources if a database contains many mapped objects.

SOLUTION
The current implementation of ApexSQL Edit retrieves many detailed information about the state of each mapped object, when you are working with the Schema Explorer. It checks for example:

• if database objects differ from their Source Control versions,
• if objects are checked out and by whom (and others).

This process can be resource/time consuming, if many database objects are mapped to a Source Control system.

You can do the following to avoid delays:

• Map only the necessary objects to your Source Control system.
• Additionally, when you are mapping your database to a Source Control system, choose NOT to remember a password for a Source Control user. Each next time you start ApexSQL Edit, just CANCEL the dialog box asking about a Source Control user password. Your Source Control system will be unavailable and the application will display the password dialog box at time to time, but the Schema Explorer will work faster.

Note: We are working on a completely new approach to ApexSQL Edit / Source Control integration. The new design should fix many current issues, also those related to slowdowns in the Schema Explorer. Please check our Developers Blog for the newest information about the work progress.

SEE ALSO
Configuring ApexSQL Edit to work with ApexSQL Source Control
How to create a new Source Control project using ApexSQL Edit

AUTHOR
Anna Lesniak

LAST REVIEW DATE
25 May 2009

Geting started with ApexSQL Console

2009-05-22T10:16:00.016-04:00

DESCRIPTION
This article will help you getting started with ApexSQL Console applications or CLI (Command Line Interface) applications.

SOLUTION
The ultimate engineering need today, when working with databases, is to schedule and run different database jobs unattended, overnight, at predefined time etc. directly on database servers...
The perfect way to do that job is to use Console applications, because they consume less memory then GUI counterpart, and typically are easy controllable and manageable just by using several lines of text or even better using the .BAT files or batches.

ApexSQL has a rich set of products coming together with the CLI (Command Line Interface) Support. The CLI applications are designed as separate executables having the .com extension. For instance in the case of ApexSQL Diff we are having the 2 executables:
* One for the GUI (Grapic User Interface) application—ApexSQLDiff.exe, and
* One for the CLI application—ApexSQLDiff.com.

As you may notice these extensions are standard for all ApexSQL applications.

Being practical let us make a simple examination of typical ApexSQL CLI application. We can demonstrate all important elements of the typical ApexSQL application in a case of ApexSQL Diff.

How to instantiate the console application?
* We just need to open the Windows Console (in my case found at C:\Windows\system32\cmd.exe)
* Go to the ApexSQL Diff application folder (or make sure that the environment variable PATH has a link to the ApexSQLDiff.com application)
* Call either

ApexSQLDiff.com ...

ApexSQLDiff ...

followed the switches, parameters we want.

How to dump all the switches ApexSQL Diff provides.
We can use the following line:

ApexSQLDiff.com /help

and we will get...

ApexSQL Diff 2008.07, Copyright (C) 1999-2009 ApexSQL LLC

Connection Options:
 /s1:arg [ /server1 ]          : source server name ("(local)" if omitted)
 /s2:arg [ /server2 ]          : destination server name ("(local)" if omitted)
 /d1:arg [ /database1 ]        : source database name
 /d2:arg [ /database2 ]        : destination database name
 /u1:arg [ /user1 ]            : source login (trusted connection if omitted)
 /u2:arg [ /user2 ]            : destination login (trusted connection if omitted)
...

Additional options:
 /? [ /help ]                  : prints help message
 /af:arg [ /argfile ]          : an XML file with options
 /cw:arg [ /console_width ]    : specifies the width of console output
 /f [ /force ]                 : overrides existing file of same name
 /out:arg [ /out_to ]          : redirects console output to specified file
 /t [ /test ]                  : outputs parsed command line switches without
                                 taking any actions
 /v [ /verbose ]               : prints messages that are suppressed by default

Here we have shortened the whole instruction set and provided only several lines as a showcase.

Organization of the switches
Important to notice; every ApexSQL application has a set of instructions organized into groups.

In the case of ApexSQL Diff we are having the following groups:
* Connection Options:
* Specific Options:
* General Synchronization Options:
* Structure Comparison Options:
* Structure Synchronization Options:
* Structure Reporting Options:
* Additional options:

The instruction groups differ from product to product, but what is important the grouping helps a lot in cases we need to find some particular instruction; it will be easy to navigate the specific instruction by recognizing its group...

Also note that these three instruction groups:
* Connection Options,
* Specific Options, and
* Additional Options
are the “three amigos” (instruction groups being present in every ApexSQL console application).

The Format of Switches
1. Switches are case insensitive, i.e. /S: = /s:
2. Switches are preceded with one forward slash (/), i.e. /db:pubs.
3. Switches are followed with a colon (:), i.e. /db:pubs.
4. There are two types of all switches - abbreviated/alias (1-3 chars) and Full - with full name of switch. i.e. /p and /project
5. Default values for omitted parameters are used in most cases. Default values are specified in the following format in /help switch ("" if omitted), i.e. ("(local)" if omitted) as illustrated in the following example.

Passing Values to switches
1. Values should be passed to switches in the following format (see examples).

apexsqldiff.com /switch:value
apexsqldiff.com /switch:"value with spaces"

2. Use Double quotes for values that contain spaces.
3. Do not put a space between the colon and the value

How can we automate ApexSQLDiff.com?
We can achieve that by the help of .BAT files or batches. See the SEE ALSO section.

Where I should find the help for the CLI instructions
Every ApexSQL CLI supported application has a corresponding Command Line section in its help. For example the help for ApexSQL Diff application has a top level section "Command Line" describing all the command line options, switches, examples, etc.

Is GUI having more instructions then CLI?
No. GUI and the CLI should have the same number of instructions. In general everything you can do from the GUI, it can be done from CLI.

SEE ALSO
How to schedule CLI command execution with Windows Scheduler

LAST REVIEW DATE
May 22 2009

Setting up automated alerts from audit triggers via SMTP in ApexSQL Audit

2009-05-22T01:34:00.012-04:00

DESCRIPTION
The Article describes how to setup automated alerts to be sent from your audit triggers via SMTP

SOLUTION

The automated alerts can be setup with help of MS SQL Server tools in MS SQL 2005 and MS SQL 2008 as there the Database Mail concept was implemented. In SQL 2000 or SQL Server 7.0 if you want to send mail using SMTP in your query, you will have to rely on third party COM components.

To setup automated alerts via SMTP at MS SQL 2005/2008 you have to do several preparation steps:

1) First you need to configure Database Mail on your SQL Server. In the SQL Server Managment Studio right click over the Database Mail node:

In the appeared dialog, please enter your e-mail address, SMTP server address and authentication parameters:

2) Then you have to update your triggers architecture to use this functionality. Make also several steps:

- Open the triggers template

- Prepare necessary variables. To do this in the opened template find such pieces of code:

Insert the code below where the arrow points.

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

- In every trigger(DELETE, INSERT, UPDATE) find such a piece of code:

And Insert the code below where the arrow points:

--Send email alert
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '{% Audit.Print Mail_Profile %}',
@recipients = '{% Audit.Print Mail_Recipients %}',
@body = '{% Audit.Print Mail_Body %}' ,
@subject = '{% Audit.Print Mail_Subject %}'

3) Create your triggers. If you want to create some triggers that do not send alerts, remove this section from the trigger create script before clicking the Play button:

Also you can download the example of such architecture from here

Andrew Guselnikov

LAST REVIEW DATE
22 May 2009

How to span the graphical dependencies report over multiple pages in ApexSQL Clean

2009-05-21T08:44:00.007-04:00

This Knowledgebase article discusses how to span a graphical dependencies report over the multiple pages in ApexSQL Clean 2008. This is very useful if the report contains a lot of database objects making single page layout useless.

SOLUTION
In ApexSQL Clean 2008, to be able to use the multiple pages layout for printing, you must perform the following steps:

1. After your database has been analyzed and object appears in the main application grid, open Dependencies Viewer form. To do this click Dependency Viewer command in Outlook-like toolbar:

2. Open Options dialog for Dependencies Viewer form. To do this click Options menu | Options command or press F8 key:

3. On the General tab, deselect “Fit to Page Printing” option:

4. Modify scale parameter (value entered will inflict pages count displayed in Print Preview)

In example, for printing dependencies for AdventureWorks SQL Server 2005 demo database, (all database objects, default settings), you should enter scales:

a) 15.00 % -> 2 pages
b) 20.00 % -> 4 pages
b) 30.00 % -> 6 pages

5. Select Print Preview command. To do this click File menu | Print Preview command:

6. As result, report will spans on multiple pages (depending on data and scale %):

AUTHOR
Dejan Apostolovic

LAST REVIEW DATE
22 May 2009

Performance hit on SQL Server when using ApexSQL Diff

2009-05-14T18:57:00.015-04:00

DESCRIPTION
This article describes ApexSQL Diff impact on SQL Server performance.

THE STORY
In general: ApexSQL Diff cannot harm SQL Server performance even if we are dealing with the biggest SQL Server databases, or even when you are executing biggest SQL Server synchronizing scripts. Microsoft SQL Server is the one that will actually distribute and do the job.

The impact of the ApexSQL Diff comparison phase
In its comparison phase ApexSQL Diff will try to get the metadata of the compared databases. This is not considered as a heavy or "killer" task in almost all cases because in general DDL database data is usually small even for the greatest databases.

The impact of the ApexSQL Diff synchronization phase
In its synchronization phase ApexSQL Diff will just provide the needed scripts for the execution to the SQL Server. These scripts may be 100Gb or so, but in fact Microsoft SQL Server will distribute the job of the scripts execution in the best possible way.

Having said that, depending on the SQL Server you have, you can control different parameters of the SQL Server Environment. Some of these are:

* The number of the SQL Server processors we use
* Maximum number of worker threads we use
* SQL Server process priority value
* SQL Server memory usage
...

By controlling these and other parameters you can control SQL Server availability and overall SQL Server performance comparing to the other processes of the server system...

Here are some figures how much time (measured in seconds) ApexSQL Diff will need to execute different operations for the well-known databases...
Here the databases are: NW = Northwind, AW = AdventureWorks 2005, AW2008 = AdventureWorks 2008.

Platform: Pentium 4 Processor, 2G or RAM, Windows XP

LAST REVIEW DATE
May 15 2009

How can I use ApexSQL Log on server where server side components aren’t allowed?

2009-04-10T09:40:00.003-04:00

DESCRIPTION
Article describes how to use ApexSQL Log to audit production database when server-side components are not allowed to be installed on it for some reasons:
1. Too intensive load of the production server to overload it with audit tasks;
2. Installation of third-party components is not allowed for some security reasons;

SOLUTION
I. First of all, please restore production database on the auditing server, the best solution is detach it on production server, make its files copy and attach these copies to on the auditing server. Another solution is to make Full database and transaction log backups and restore them on the auditing server. This is needed for two reasons:
1. Meta-data on the auditing database needs to match exactly the meta-data on the production database. Just running a script to create the schema on the production database will not, in general case, be sufficient for this as SQL Server randomly assigns table ID so table A would most likely have one ID in the production database and another ID in the auditing database. This could still be handled by the software using manual mapping of old/new table IDs but it's much better if the db is simply restored from the production server.
2. SQL Server logs very differently INSERT/DELETE operations on one hand and UPDATE operations on the other. While INSERT/DELETE operations are logged with entire row state, UPDATE operations are logged only with the minimum of change set data. To reconstruct changes logged by UPDATE statement we sometimes use current database state so it's very important that auditing server reflects some recent database state.

II. Once the database is restored, start creating transaction log backups on the production server and then sending them to auditing server for auditing. The thing to keep in mind here is that the restored database isn't changing so in order to leverage the data in it for UPDATE reconstruction the full change of transaction log backups since the restored db backup needs to be maintained (if it's not the application will still work but UPDATE reconstruction won't be as complete as it could be.).

Suggestion: To avoid building up too many transaction log backups you could frequently restore the most recent state of the production database - that way old transaction log backups can be deleted.

Special Note: One thing that cannot be obtained without server-side components are application and client host names for the connections that made the changes. This information is not logged into transaction log and needs to be actively captured which our server-side components handle. Also, to obtain correct NT user names of the users connecting to the production database, you need to keep auditing server and production server in the same domain.

III. Once all this is setup, database on auditing server is ready for use for responsible person. Just the reference to all relevant transaction log backups should be specified before audit is started.

IV. Another alternative would be to create an automated auditing process where ApexSQL Log would be invoked through its command line interface (CLI) directly on the server. This job would produce auditing results depending on the filters you pass them and then this could be loaded into a specialized auditing database for querying. The auditing tables (there are two of them) can contain auditing data from different servers and databases so this auditing data from all your databases is stored right there and queries for these two tables need to be run only.

AUTHOR
Dmitriy Dyubchenko

LAST REVIEW DATE
10 April 2009

The ApexSQL Installer

2009-04-02T15:28:00.028-04:00

The ApexSQL Installer is a universal installer that installs most of ApexSQL's tools.

Where can I download the Installation Setup
The installer can be downloaded here.

Installation Types
There are several predefined installation types in the setup:

Default installation – All client applications/utilities are selected for installation
Custom Installation – The user selects specific applications to install
ApexSQL Universal Studio (Full Installation) - installs all applications, Add-ins and APIs which are all included in ApexSQL Universal Studio. This is equivalent to a Full Installation
ApexSQL Developer Studio - installs all tools included in ApexSQL Developer Studio
ApexSQL SSMS Plus - installs all tools included in ApexSQL SSMS Plus
ApexSQL Recovery Studio - installs all tools included in ApexSQL Recovery Studio
ApexSQL Comparison Studio - installs all tools included in ApexSQL Comparison Studio
ApexSQL DBA Studio - installs all tools included in ApexSQL DBA Studio

Note: None of the above includes ApexSQL Report, ApexSQL Audit Viewer, ApexSQL Snapshot Utility, and ApexSQL Log and ApexSQL Recover's server-side components.

Which applications cannot be installed using the ApexSQL Installer?
ApexSQL Report, ApexSQL Audit Viewer, ApexSQL Snapshot Utility, and ApexSQL Log & ApexSQL Recover's server-side components (client-side components can be installed using the ApexSQL Installer).

Where can I download the installers for applications that are not included in the ApexSQL Installer?

ApexSQL Report
ApexSQL Audit Viewer
ApexSQL Snapshot Utility

You will be prompted to install the server-side components of ApexSQL Log and ApexSQL Recover when you start using the app and connect to a server.

Which applications should I select to install?

To install individual applications
Check the box corresponding to the application you want to install.

To install a bundle
Select the bundle from the installation type. Below matrix shows which tools will be installed per selected bundle.

To install the server-side components of ApexSQL Log and ApexSQL Recover
Start each application. You will be prompted to install the server-side components when you connect to a server that doesn't have the server-side components installed.

If you encounter problems installing on a remote server, download the individual installers of ApexSQL Log and/or ApexSQL Recover. Read Installing server-side components only to a remote server for more information.

Can I use the ApexSQL Installer to upgrade my application to the most recent version?
Yes. ApexSQL Installer is updated every time there is a new product or a new version. Simply download the installer again and select the application that you want to install.

Can I use the ApexSQL Installer to install an older version of a particular application?
No. The ApexSQL Installer is updated every time there is a new version.

Where can I download the installers to old versions?
Installers to old versions can be found here: http://www.apexsql.com/forum/forum_posts.asp?TID=9711&PN=1.

Setup the minimum rights for ApexSQL Diff user (for the comparison and script generation only)

2009-03-27T19:01:00.021-04:00

DESCRIPTION
Here is explained how to setup the SQL user (login) with minimum rights to be used from ApexSQL Diff for comparison and script generation purpose only; without the need to execute the scripts from ApexSQL Diff.

SOLUTION
For script execution ApexSQL Diff requires the user with db_owner permission granted. However, for comparison and for the script generating only (without executing the scripts) you will need the user with:
* The membership in the public role (this is by default)
* The permission CONNECT SQL granted (this is selected by default)
* The VIEW DEFINITION permission granted

Here are the steps that your DBA has to undertake to create such a user.

1. From the SSMS.exe (Object Explorer) right click the Logins folder to add the new login=user.

2. Set the login name, SQL Server authentication password

3. Make sure the public server role is selected (by default it is so)

4. From the user mappings tab select the databases (usually all) you want to make available to the user.

5. Now click the SQL Sever instance properties from the context menu

6. From the permissions tab make sure "Connect SQL" and "View any definition" persmisions are granted to your user

7. You will have the effective permissions like this

8. Now when you open ApexSQL Diff and right after you choose the data sources with the new login (user) and start the comparison process you will get the prompt like this:

You are not a member of db_owner role in databases:
AdventureWorks2005, ...
You can continu but some functions may not work because of lack of rights.
Continue?

After your confirmation you are ready to go, but remember: You will not be able to execute the scripts from the ApexSQL Diff!

SEE ALSO
VIEW DEFINITION permission

LAST REVIEW DATE
March 30 2009

How to prepare an existing SourceGear Vault project to be mapped in ApexSQL Edit multi user environment.

2009-03-25T15:39:00.040-04:00

DESCRIPTION
This article describes how to prepare an existing SourceGear Vault project to be mapped in ApexSQL Edit multi user environment.

SOLUTION
I assume that the axEdit_Repository was created in SourceGear Vault system to store projects for ApexSQL Edit. This repository has its working folder in the C:\Vault_WorkingFolder\axEdit_Repository_WF folder.
I also assume that SourceGear Vault is used by two users.
The first user mapped the axEdit_Dev database to the SourceGear Vault Server and assigned the database objects to the Vault repository.

The second user would like to use already existing project in the Vault Repository.
She should:

a) assign a local working folder to an existing project using Vault Client.

To enable ApexSQL Edit to work with already existing projects within SourceGear Vault, the second user needs first to create locally all Vault repository items (projects/folders and script files).
She uses the Vault Client application and connects to axEdit_Repository. Then she selects the Set Working Folder command for the existing axEdit_Dev project.

And she assigns a local folder as the project’s working folder:

At the end she runs the Get Latest Version command for the axEdit_Dev project. This command will copy all files for the project (and its sub-projects) to the local working folder.
Note: This configuration must be done to allow ApexSQL Edit working with existing projects defined in SourceGear Vault (existing Vault projects are not seen by ApexSQL Edit due to MSSCCI limitations unless they have at least one file inside).

b) map a database and its objects to an existing project using the Map Database command and Source Control Mapping Wizard.

SEE ALSO
How to map a database to a Source Control System via MSSCCI driver using ApexSQL Edit

AUTHOR
Anna Lesniak

LAST REVIEW DATE
25 March 2009

How to configure Microsoft Visual SourceSafe to work with native Source Control drivers

2009-03-17T17:48:00.025-04:00

DESCRIPTION
This article describes important Microsoft Visual SourceSafe post installation steps and how to use it from ApexSQL applications.

SOLUTION
We will cover the usage of Microsoft Visual SourceSafe 2005 but it is very same with the older Microsoft Visual SourceSafe 6.x versions.

In Microsoft Visual SourceSafe you should have databases to work with. Let's say our database name is Production. Each database has a corresponding location on disk. To get a location of your database use the VSS Administrator tool, click CTRL+O and you will get Open Source Save Database Dialog like at the image.

As you can see from the image our Production database is located in the folder

c:\installed VSS

Now we should set this folder as a network share.
If you are using XP system you should set the network easily as follows:

If you are using Vista system you should make something like this.

Now when the network share is set we are able to set the Microsoft Visual SourceSafe Source Control Data Source from ApexSQL applications...

Setting up the connection to a Microsoft Visual SourceSafe project

Here I used ApexSQL Diff application as a showcase, but in general connecting to a Microsoft Visual SourceSafe is similar throughout all ApexSQL applications supporting Source Control.

1. Choose a Source Control data Source type and click "Edit"

2. Select Microsoft Visual SourceSafe native driver

3. Set Microsoft Visual SourceSafe project access parameters

Let’s explain the fields that are in the image:

* The User and the Password are the user credentials for accessing the Microsoft Visual SourceSafe server.
* The Database represents the path to a Microsoft Visual SourceSafe repository. It has the form HOSTNAME/REPOSITORY. HOSTNAME is a name of a Server machine where the Microsoft Visual SourceSafe server is installed; this name should be pingable from the network and it may be either NET BIOS name or the DNS name. REPOSITORY is a network share name from the HOST machine.
* The Project is a Project you made within your Microsoft Visual SourceSafe database (here within Production database)
* The Working Folder is some folder on your hard disk you have chosen to be your Microsoft Visual SourceSafe stub
* The Version can be either "latest version" or some labeled version chosen from the drop down select box.

You can always test the Connection using the Test button. In the case of problems you will receive the descriptive information about the problems when establishing a connection; otherwise you are ready to go.

SEE ALSO
Microsoft Visual SourceSafe

LAST REVIEW DATE
March 18 2009