LINKS
General
Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script
Knowledgebase Home
Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script
Knowledgebase Home
MOST RECENT
Using ApexSQL's Snapshot Utility for Synchronizing Remote DBs and more
How can I email the output of a scheduled CLI job (e.g. reports, log files)?
How to synchronize data between databases with different settings
Mapping in ApexSQL Diff, Part 3: Columns
Mapping in ApexSQL Diff, Part 2: Tables
How to use ApexSQL Script to deploy a database as a .Net executable
SITE FEED
Support Forum
The fastest and most effective to get answers to your questions quickly. Click Here for more info.
FAQs 
Quick answers to common questions. Click Here for more info.
Troubleshooting
Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.
Knowledgebase
Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.
Online Documentation
All of our help files for download or convenient viewing online. Click Here for more info.
Videos
Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.
|
ApexSQL Knowledgebase Tips and How-to Articles for Apex SQL Tools Using ApexSQL's Snapshot Utility for Synchronizing Remote DBs and more This article discusses how you can use ApexSQL Snapshot Utility to synchronize remote databases. It also discusses other possible uses of the utility. What is a snapshot? A snapshot is a read-only file in a binary format that stores the complete information of a database's schema. It can be used as a data source in ApexSQL applications. In ApexSQL Diff, for example, it can be used in structure comparison. The generated synchronization script can then be applied to a database. Using Snapshots to Synch Remote Databases ApexSQL snapshots are especially useful when dealing with remote databases. A snapshot of a database can be created and easily shared among peers (e.g. through email). The recipient of the snapshot who has ApexSQL Diff can then use it as a data source. The snapshot can be compared with another snapshot or with another database. Upon synchronization, the generated script can be run on another database (remote or otherwise). Using Snapshots for Maintaining Database Versions A snapshot can also be used to "version-control" your database's schema. This can easily be done by capturing a snapshot of the database periodically. These snapshots can then be stored in a source control system--like an ordinary file. Old versions of the database schema can then be used to restore a database to certain point. With ApexSQL Diff for example, you can use a version of the snapshot and compare it with your live database. The generated synchronization script, when run, will synchronize both data source. Can I use the snapshots with ApexSQL's command line interface? Yes. Using ApexSQL Diff's command line, snapshots (as well as databases) can be used as data sources. Note: The command line interface of ApexSQL applications is usually used to automate or schedule procedures that are done in GUI. The ApexSQL Snapshot Utility Before, the only way to create ApexSQL snapshots was through ApexSQL Diff which had the functionality embedded to it. So to take advantage of the full benefits of snapshots, a user would first need to have ApexSQL Diff running on his machine. The ApexSQL Snapshot Utility was created to enable users to create snapshots without ApexSQL Diff. The utility is FREE and can be installed on multiple remote sites. Below are some of its uses (some of which are already mentioned above): 1. To synchronize two or more disconnected databases 2. To spread the changes applied to a development database's schema to number of client–side databases. For example, a client database may not be accessible remotely due to security restrictions 3. To capture and store a remote database schema as part of a database schema change event processing 4. To load a snapshot of a remote database schema into a source control system Creating a snapshot using the Snapshot Utility To create a snapshot, connect to a SQL Server using either Windows or SQL Server authentication, select the database you want to create a snapshot of, and specify the snapshot name and where you want to save it. Creating an ApexSQL snapshot in GUI mode  Creating an ApexSQL Snapshot in CLI mode  ApexSQL Snapshot Utility command line switches  More Information Download ApexSQL Snapshot Utility AUTHOR Irene Sanford LAST REVIEW DATE 10 April 2008 Labels: Apex SQL Diff ...How can I email the output of a scheduled CLI job (e.g. reports, log files)? DESCRIPTION To do this, we will use a SQL Server job. Below steps discuss how to email an audit report generated by ApexSQL Log (through the command line interface) SOLUTION I. In SQL Server 2005, execute the following steps: 1. Configure mail profile and account on your database server: CODE: EXEC SP_CONFIGURE 'show advanced options', 1; GO RECONFIGURE; GO EXEC SP_CONFIGURE 'Database Mail XPs', 1; GO RECONFIGURE GO EXEC MSDB.DBO.SYSMAIL_ADD_PROFILE_SP @profile_name = 'DBProfile', @description = 'Profile used to send mails.' ; GO EXEC MSDB.DBO.SYSMAIL_ADD_ACCOUNT_SP @account_name = 'DBAccount', @email_address = 'DBAccount@localhost', @display_name = 'Mail from Server', @mailserver_name = ' @username = ' @password = ' GO EXEC MSDB.DBO.SYSMAIL_ADD_PROFILEACCOUNT_SP @profile_name = 'DBProfile', @account_name = 'DBAccount', @sequence_number = 1 GO Where: Database Mail may be configured manually from Microsoft SQL Server Management Studio:  More detailed information can be found: here 2. Create a Job for SQL server and schedule it to run every Monday, 11PM. a. Open SQL Server Studio and create a new job:  b. Add new step to the job and specify ‘Operating System (CmdExec)’ as the command type:  c. Paste your CLI command into the ‘Command’ edit box: CODE: APEXSQLLOG.COM /S:TESTSERVER /D:TESTDB /SQL:<FULL PATH>\RESULT.SQL /VERBOSE The results of execution will be outputted to d. Create another step with 'Transact-SQL (T-SQL)' type and paste the following to 'Command' edit field: CODE: EXEC MSDB.DBO.SP_SEND_DBMAIL @profile_name = 'DBProfile', @recipients = ' @subject = 'Mail from DB', @file_attachments = ' Where: @recipients - Is a semicolon-delimited list of e-mail addresses to send the message to. @subject - Is the subject of the e-mail message. If no subject is specified, the default is 'SQL Server Message'. e. To run the job every Monday, 11PM, create a new schedule for the job. On Schedules, click "New":  Next, setup the schedule:  II. Click here to get more information on how to do this in SQL Server 2000. AUTHOR David Dawson LAST REVIEW DATE 06 November 2007 Labels: ApexSQL Log, General ...How to synchronize data between databases with different settings DESCRIPTION How to synchronize data between databases with different settings The behaviour of DATETIME or SMALLDATETIME type of data can be influenced by client, server, and/or Operating System settings. This KB article describes how to use ApexSQL Diff in situations where these settings must be considered. SOLUTION To illustrate, we use three databases located in three different countries. The goal is to synchronize the data between any two of these databases. The main database is hosted on a SQL Server instance with US English settings; the other two databases with France and Germany settings. Below script creates three databases: General, French, and German. Each database contains one table named t. The table t contains only one DATETIME column named TheDate. CREATE DATABASE GENERAL COLLATE LATIN1_GENERAL_CI_AI GO CREATE DATABASE FRENCH COLLATE FRENCH_CS_AI GO CREATE DATABASE GERMAN COLLATE GERMAN_PHONEBOOK_CS_AI GO USE GENERAL CREATE TABLE T ( THEDATE DATETIME ) GO USE FRENCH CREATE TABLE T ( THEDATE DATETIME ) GO USE GERMAN CREATE TABLE T ( THEDATE DATETIME ) GO Common problems with temporal data when working in international environments USA, France, and Germany all use a different date format. To illustrate what happens when data is inserted to above databases, we use the SET LANGUAGE command. To insert data to General database using the French date format DD/MM/YYYY, we execute below script: USE GENERAL SET LANGUAGE ENGLISH INSERT INTO T VALUES('23/08/2007') CHANGED LANGUAGE SETTING TO US_ENGLISH. MSG 242, LEVEL 16, STATE 3, LINE 3 THE CONVERSION OF A CHAR DATA TYPE TO A DATETIME DATA TYPE RESULTED IN AN OUT-OF-RANGE DATETIME VALUE. THE STATEMENT HAS BEEN TERMINATED. Due to the difference in language settings, insertion fails. SQL Server cannot resolve the string '23/08/2007' to a valid date format. To insert the same data to the German database, we execute the following script: USE GERMAN SET LANGUAGE GERMAN INSERT INTO T VALUES('23/08/2007') DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT. (1 ROW(S) AFFECTED) The data is inserted successfully. We do the same thing with the French database using below script: USE FRENCH SET LANGUAGE FRENCH INSERT INTO T VALUES('23/08/2007') LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS. (1 ROW(S) AFFECTED) Insertion to the French database is successful as well. The results would be the same if we attempted to insert data with the German date format DD.MM.YYYY to our three sample databases; insertion would fail on the General database and it'd be successful on the other two. On the otherhand, if we attempt to insert data that's in US date format MM/DD/YYYY to all three database, only the insertion to the General database would be successful. The two other INSERT statements cannot be committed successfully and would raise a conversion error. Using SQL Server's CONVERT statement to apply the correct date format style before performing the insertion resolves these insertion errors. In the next section, we discuss how to synchronize temporal data in international environments that are similar to our example by using ApexSQL Diff AND without the additional "conversion" step. Using ApexSQL Diff to synchronize temporal data when working in international environments Example 1: In this example, the French database has one row that's not in the General database. To synchronize the General database with the French database, ApexSQL Diff generates below synchronization script: USE GENERAL SET LANGUAGE ENGLISH SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF CHANGED LANGUAGE SETTING TO US_ENGLISH. (1 ROW(S) AFFECTED) Example 2: In this example, the German database has one row that's not in the French database. To synchronize the French database with the German database, ApexSQL Diff generates below synchronization script USE FRENCH SET LANGUAGE FRENCH SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS. (1 ROW(S) AFFECTED) Example 3: In this example, the General database has one row that's not in the German database. To synchronize the German database with the General database, ApexSQL Diff generates below synchronization script USE GERMAN SET LANGUAGE GERMAN SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT. (1 ROW(S) AFFECTED) All of the above scripts complete successfully. By default, ApexSQL Diff uses language-neutral date format settings. This default can be modified by clicking on the Synchronization Wizard button in the Outlook bar then choosing Configuration item from the Graphical Treeview:  The "Apply Date Format" option by default uses the unseparated date format:  The most commonly used formats are also readily available:  To add a format that's not in the list or to use one that already is, check "Apply Date Format", select (or enter) the format, and click the Finish button. The selected format will then be used by ApexSQL Diff on all (SMALL)DATETIME data type columns encountered during the scripting process. For example, suppose we use the US date format:  The script output would be: SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '08/23/2007 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF AUTHOR Frank Kirkland LAST REVIEW DATE 07 September 2007 Labels: Apex SQL Diff ...Mapping in ApexSQL Diff, Part 3: Columns DESCRIPTION This is the third and last installment in the three-article KB series discussing ApexSQL Diff's mapping ability. With ApexSQL Diff, you can map users/schemas, tables, and columns. Here, we discuss how to map columns. What does "mapping columns" mean? As mentioned in Mapping Schemas, ApexSQL Diff runs in two different modes: Structure mode compares and syncs database structures and Data mode compares and syncs data. Mapping columns applies only to Data mode. By default, ApexSQL Diff automatically maps columns of the same name in tables of the same name owned by schemas having the same name. That is, when you have a column named "Col1" in a table named "Table" in a schema named "Schema1" in both the source and destination of your project, these columns will be automatically mapped by ApexSQL Diff. When the schemas in both source and destination differ in their names, ApexSQL Diff will not automatically map them. This is the case even when the object names in the schemas are all equal; the tables will still be unmapped. To address this, ApexSQL Diff allows table mapping. The same logic applies for columns. Even when schema and table names are equal, when column names are not, the columns won't automatically be mapped. Mapping columns enable you to compare objects that belong to the same or different schemas and tables. Mapping columns To reproduce the situation, let's issue the following batch: USE MASTER CREATE DATABASE BOSTON GO USE BOSTON GO CREATE SCHEMA BOSTON_USERS AUTHORIZATION DBO GO IF OBJECT_ID('Boston_Users.Employees') > 0 DROP TABLE BOSTON_USERS.EMPLOYEES CREATE TABLE [BOSTON_USERS].[EMPLOYEES]( [EMPLOYEEID_Z] [INT] IDENTITY(1,1) NOT NULL, [LASTNAME_Z] [NVARCHAR](20) COLLATE LATIN1_GENERAL_CI_AS NOT NULL, [FIRSTNAME_Z] [NVARCHAR](10) COLLATE LATIN1_GENERAL_CI_AS NOT NULL, [TITLE_Z] [NVARCHAR](30) COLLATE LATIN1_GENERAL_CI_AS NULL, [TITLEOFCOURTESY_Z] [NVARCHAR](25) COLLATE LATIN1_GENERAL_CI_AS NULL, [BIRTHDATE] [DATETIME] NULL, [HIREDATE] [DATETIME] NULL, [ADDRESS] [NVARCHAR](60) COLLATE LATIN1_GENERAL_CI_AS NULL, [CITY] [NVARCHAR](15) COLLATE LATIN1_GENERAL_CI_AS NULL, [REGION] [NVARCHAR](15) COLLATE LATIN1_GENERAL_CI_AS NULL, [POSTALCODE] [NVARCHAR](10) COLLATE LATIN1_GENERAL_CI_AS NULL, [COUNTRY] [NVARCHAR](15) COLLATE LATIN1_GENERAL_CI_AS NULL, [HOMEPHONE] [NVARCHAR](24) COLLATE LATIN1_GENERAL_CI_AS NULL, [EXTENSION] [NVARCHAR](4) COLLATE LATIN1_GENERAL_CI_AS NULL, [PHOTO] [IMAGE] NULL, [NOTES] [NTEXT] COLLATE LATIN1_GENERAL_CI_AS NULL, [REPORTSTO] [INT] NULL, [PHOTOPATH] [NVARCHAR](255) COLLATE LATIN1_GENERAL_CI_AS NULL, CONSTRAINT [PK_EMPLOYEES] PRIMARY KEY CLUSTERED ( [EMPLOYEEID_Z] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [BOSTON_USERS].[EMPLOYEES] WITH NOCHECK ADD CONSTRAINT [FK_EMPLOYEES_EMPLOYEES] FOREIGN KEY([REPORTSTO]) REFERENCES [BOSTON_USERS].[EMPLOYEES] ([EMPLOYEEID_Z]) GO ALTER TABLE [BOSTON_USERS].[EMPLOYEES] CHECK CONSTRAINT [FK_EMPLOYEES_EMPLOYEES] GO ALTER TABLE [BOSTON_USERS].[EMPLOYEES] WITH NOCHECK ADD CONSTRAINT [CK_BIRTHDATE] CHECK (([BIRTHDATE] The above batch will create a new database named Boston and then switch the database context from master to this new database. Within the Boston database, we create the schema Boston_Users and then copy the structure of the Northwind.dbo.Employees table to the new Boston_Users.Employees table. We also modify the Northwind table a bit by appending a "_z" to the column names. Now, we have two tables with identical table names and partially different column names. We now switch to ApexSQL Diff and create a new data comparison project. As source for the comparison, we choose the Northwind database. For the destination database, we use the Boston database. In the Data Comparison Wizard, we go to the Mapping Columns page where we can select the columns to be matched. The screen will look like the one below:  Let's focus on "Columns" of the above grid:  Opening the list displays the following screen:  The list is divided into two parts. The upper part displays the mapped column pairs. The lower part displays the unmapped column pairs. By default, ApexSQL Diff automatically has mapped the columns with the same name. These pairs are shown in the upper part of the list. The lower part contains the columns that were modified earlier:  As with every Source/Destination visual control element in ApexSQL Diff, on the left side you can see the information pertaining to the Source side of the comparison. The right side shows the information pertaining to the Destination side of the comparison. Unmapped columns will be ignored by ApexSQL Diff during a Data Comparison Project. Thus, they will not be synchronized. In order to include these column in the comparison and sychronization, map the columns as you would map tables. Select the corresponding "Destination Name" from the dropdown box of that field:  In the above example, "EmployeeID" is mapped to ":EmployeeID_z". It can now be found in the upper part where all other mapped items already are:  Once all the unmapped columns are mapped, the result is as below:  The lower part is empty as all previously unmapped columns are now mapped. All columns in both tables will now be included in the comparison and synchronization process. SEE ALSO Mapping in ApexSQL Diff, Part 1: Schemas Mapping in ApexSQL Diff, Part 2: Tables AUTHOR Frank Kirkland LAST REVIEW DATE 30 August 2007 Labels: Apex SQL Diff ...Mapping in ApexSQL Diff, Part 2: Tables DESCRIPTION This is the second installment in the three-article KB series discussing ApexSQL Diff's mapping ability. With ApexSQL Diff, you can map users/schemas, tables, and columns. Here, we discuss how to map tables. What does "mapping tables" mean? As mentioned in Mapping Schemas, ApexSQL Diff runs in two different modes: Structure mode compares and syncs database structures and Data mode compares and syncs data. Mapping tables applies only to Data mode. By default, ApexSQL Diff automatically maps objects of the same object type and object name and have the same schema. That is, when you have a schema "DBO" in both the source and destination of your project, and both own the tables named "MyTable", these tables will automatically be mapped by ApexSQL Diff. However, when the schemas in both source and destination differ in their names, mapping is not automatic. This is the case even when the object names in the schemas are all equal; the tables will still be unmapped. To address this, ApexSQL Diff allows mapping tables manually. Mapping tables To reproduce the situation, let's issue the following batch: USE MASTER CREATE DATABASE BOSTON GO USE BOSTON GO CREATE SCHEMA BOSTON_USERS AUTHORIZATION DBO GO SELECT * INTO BOSTON_USERS.EMPLOYEES FROM NORTHWIND.DBO.EMPLOYEES The above batch will create a new database named Boston and then switch the database context from master to this new database. Within the Boston database, we create the schema Boston_Users and then copy the structure of the Northwind.dbo.Employees table to the new Boston_Users.Employees table. We now have two tables with identical names and identical data. The important difference is, they are contained in different schemas. We now switch to ApexSQL Diff and create a new data comparison project. As source for the comparison, we choose the Northwind database. For the destination database, we use the Boston database. In the Data Comparison Wizard, we go to the Mapping Tables page where we can select the tables to be matched. The screen will look like the one below:  The grid above the red rectangle is empty because ApexSQL Diff cannot automatically map the Employees table. Although the object types and names are identical, their containing schemas differ. The table pair (dbo.Employees/Boston_Users.Employees) is yet unmapped, and therefore is found in the "Unmapped objects" grid:  Below, the two Employees tables are presented on two different rows:  To map the two Employees tables, place the cursor in the Name column on either row where the Employees table is displayed:  Expand the dropdown list:  Notice the red arrow above. This simply shows that the Unmapped Objects grid is a two-way grid. Meaning, you can achieve the same result when you click either on the dropdown box in Name column in the "Destination half" or the Name column of the "Source half" of the Unmapped Objects grid.  To map two tables permanently in this project, click the "Map" button:  Alternatively you can use the "Map" command from the context menu of the Unmapped Objects grid:  The table pair is immediately moved from the Unmapped Objects grid to the Mapped Objects grid.  You have successfully established a "permanent link" between two tables residing in different schemas. The table dbo.Employees and the table Boston_Users have the same set of columns. What if they were different? This is what we will discuss in the third and last installment. SEE ALSO Mapping in ApexSQL Diff, Part 1: Schemas Mapping in ApexSQL Diff, Part 3: Columns AUTHOR Frank Kirkland LAST REVIEW DATE 31 August 2007 Labels: Apex SQL Diff ...How to use ApexSQL Script to deploy a database as a .Net executable DESCRIPTION ApexSQL Script allows you to script your SQL Server Database structure and data and compile it into a .Net executable. This executable can be run from any pc without the need to have the SQL Server client tools installed. SOLUTION To create a .Net executable, select .Net package as your output type in the Script wizard:  Selecting the output type determines the various customizable output options. For a .Net executable, you can configure the output processing options:  Checking the “Use Compression” option will cause ApexSQL Script to try to reduce the size of the executable by using .Net algorithms. The “Run Executable Now” option simply means that the exe will be invoked the moment it has been compiled The Error handling step determines how errors will be handled when the package is executed. For .net executables the errors are handled internally which gives the package greater control and allows you select from the follow options for the treatment of errors:  • Abort All changes which have been applied so far will be rolled back. • Ignore Errors will be ignored and the package will proceed as if nothing happened. • Ask for confirmation A message box will appear when an error is encountered to allow the user to select the appropriate action.  You can also set the default database properties for the package:  If you check the “Indicate Default Database” option, a default database will be recommended on the connection screen when you run the executable:  ApexSQL Script also allows you to customize the details of the About Box:  This information is then displayed when the About button is clicked on the Installer:  Clicking on the create button  will then create a .exe file.To run the newly created package, double click the .exe in explorer:  This opens the installer:  You can create a new database or edit an existing database. Once you have entered the connection details of the database you want to create or edit, click "Run". This will execute the package and create or edit the database, using the script generated by ApexSQL Script:  You can verify that the database has been created using SQL Server Management Studio:  AUTHOR Minette Steynberg LAST REVIEW DATE 01 September 2007 Labels: Apex SQL Script ...Visual Query Editor This KB discusses how ApexSQL Edit's Visual Query Editor can speed up your T-SQL coding. DESCRIPTION In this article, we discuss the Visual Query Editor (VQE) of ApexSQL Edit and how you can get the most out of it. Activating the Visual Query Editor By default, the VQE is not activated automatically in ApexSQL Edit. To activate it, right-click on the menu/toolbar area and select "Customize":  Go to the "Commands" tab and select the "Query" item from the Categories list. Scroll down the list of "Commands" and at the bottom you can find the "Visual Query" item:  Drag and drop this item to the menu/toolbar area. Important: This is a one-time setup. Once set, the VQE can be accessed from the Main Menu or by hitting F11:  The Visual Query Editor The Visual Query Editor can be divided into the following subparts:  1. The Query Building Area: This is the main area where the visual representation of the query is displayed. This area allows you to add database objects, define links among them, and configure properties of tables and links. 2. The Database Tree Pane: In this area, you may select the objects such as tables, views, or procedures that you want to build a query from. To build a query, simply drag and drop them on the Query Building Area. 3. The Columns Pane: This area is used to perform operations on your query output columns and expressions. You can define field aliases, sorting, grouping, and criteria. 4. The SQL Output Area: This is where the textual representation of the query will be displayed. Note that the VQE is a two-way tool. This means that the modifications done in the Query Building Area will always be reflected in the SQL Code Area and vice versa. Getting started To illustrate ApexSQL Edit's VQE, we use the Northwind Database. The first step in using the VQE is selecting the database object that your query will be based on. A database object is selected when, and only when, it is displayed in the Query Building Area. Again, this means that objects only exist in the VQE when they are shown in the Query Building Area. Suppose you have a query that's based on the Orders table. You have two ways to bring up the Orders table in the Query Building Area. The first way is to select the Orders item in the Database Tree Pane, drag it from there, and drop it to the Query Building Area. After dropping the Orders item, it will be displayed in the Query Building Area:  The second way of adding objects to the Query Building Area is to use its context menu:  By selecting the "Add Object" menu item, you open the "Add new object" dialog:  This dialog contains three tabs--one for each object type that can be added to the VQE. You can browse this dialog just like any other list. If a database contains many objects in different schemas, the "Filter objects by Schema name" dropdown box can be used to limit the number of items displayed in the list. Objects can be filtered by the schema to which they belong:  As displayed above, there is no user-defined schema in this version of the Northwind database. By selecting "(All objects)," the filter can be removed and all objects of the database will be displayed again. Once the object to be added to the Query Building Area is found, the corresponding item can be selected in the list. Then, click on the "Add Object" button,  .The Query Building Area now looks more or less like below:  To control the visibility of a column in the final SELECT list, simply check or uncheck it. In above example, CustomerID, OrderDate, and ShippedDate columns are selected and will thus be included in the SELECT list. By marking a column checked, it is instantly added to the Columns Pane and the SQL Output Area. Notice that the Order table item's caption in the in the Query Building Area shows (3)dbo.Orders. ApexSQL Edit automatically displays the number of checked columns. Using more than one table The VQE has the ability to identify the relationships among tables added to the Query Building Area. The result of dragging and dropping the [Order Details] table to the Query Building Area looks like this:  The link between both table objects are shown above. By reading SQL Server's metadata, ApexSQL Edit determines that both table are in a 1:m (1 to many) relationship. Editing a selected database object Each SQL Server source code parser must be able to uniquely identify columns and tables in a query. Typically this is achieved by using the three part naming convention-- schema.table.column. Using this convention, it is immediately obvious which column belongs to which table. The syntax however adds complexity to the query definion and makes code hard to maintain. To simplify this, you may use table aliases for each table participating in the query. Consider the query below: SELECT DBO.[ORDER DETAILS].ORDERID, DBO.[ORDER DETAILS].PRODUCTID FROM DBO.[ORDER DETAILS] With a table alias, the query will look like this: SELECT OD.ORDERID, OD.PRODUCTID FROM DBO.[ORDER DETAILS] OD Although both queries are absolutely identical with respect to their execution plan and resultset, the latter is more readable. The beauty of using table aliases is made more obvious when using 4 or more JOINs. VQE supports table aliases. An alias for an object can be created by right-clicking on the object in the Query Building Area:  From the context menu, choose "Edit...". This opens the "Datasource Properties" dialog:  Enter the desired alias and click OK. The query immediately reflects the change introduced by the alias:  Editing relationship properties This INNER JOIN is the default relationship used by the VQE. This can be changed by right-clicking the link between two tables:  Then, choose "Edit..." from the context menu to add the "Link Properties" dialog:  Above image shows the unmodified link property. The dialog is divided into a left and a right part with a linking operator between them. This is a visual presentation of the underlying SQL source code. Right below the object names, there are two checkboxes. With INNER JOINs, none of these checkboxes will be marked. Below the checkboxes is the column that joins the two tables. To change the INNER JOIN to an OUTER JOIN, mark that checkbox below the left table:  You now have a LEFT JOIN from Orders on [Order Details]. In order to change this condition to another, select the appropriate operator from the dropdown box:  Most of the common operators can be selected from the list. The Columns Pane The Columns Pane is the area in the VQE that's located directly below the Query Building Area:  This grid contains all columns currently selected for output in the SELECT list or the columns used for sorting, grouping, or filtering. Each column has a corresponding checkbox, the check status of which determines its inclusion to the resultset. A column alias can also be specified for each column. You may-- -specify if the column should be included in an ORDER BY clausem  -specify the ordinal position of the column in the ORDER BY clause,  -or specify if an aggregate function should be applied to the column and whether the column should appear in the WHERE, GROUP BY, HAVING clause of the query:  If a column is included in a WHERE clause, you may enter the condition into the "Criteria" field at the right of the "Group By" column:  Editing Query Properties Next we discuss the Q in the Query Building Area:  The "Query Properties" dialog can be reached via this symbol:  In this dialog, you can specify if the TOP clause should be applied to the query, and whether a number of rows or a percentage of rows (with ties or not) should be returned. There are several other different aggregating functions available:  Derived Tables The VQE in ApexSQL Edit fully supports derived tables. A derived table a virtual table that is derived from other base tables or views. Consider this: SELECT ORDERID FROM ORDERS The above query uses the base table Orders in the FROM clause. SELECT ORDERID FROM (SELECT ORDERID FROM ORDERS) AS T1(ORDERID) The second query uses a derived table in its FROM clause. Derived tables can be used where table expressions are allowed. To add a derived table to the Query Building Area, use its context menu:  From the context menu, choose "Add Derived Table". This will add a new and empty table object item to the Query Building Area:  Right-click the object's to bring up its context menu:  Select "Switch to derived table" to add a new tab to the Query Building Area:  The same functions, operations, and restrictions that apply to the main query applies to a derived table.  Use the Customers table as our base table for your derived table. Switching back to the Main tab of the VQE, the following screen will be displayed:  The derived table is now filled and joins Orders with [Order Details]. To relate Query1 to one or more of the other tables, drag the Query1 column to another table object and drop it over the target column. The relationship is now established:  Adding UNION queries UNION queries are added in pretty much the same fashion as derived tables are added. Just open the Query Building Area's context menu and select "Union" -> "New Union Sub-Query":  This again will result in a new tab opened in which you can generate your UNION query:  Directly editing the SQL source code Again, the VQE in ApexSQL Edit is a two-way tool. Hence, editing the generated SQL code also affects its visual representation. Suppose you have an empty UNION sub-query in the Query Building Area, the the SQL code in the SQL Output Area will look like this:  To remove it, simply remove it from the code. This modification immediately shows in the Query Building Area. Conclusion The VQE offers many advanced features that can't be found in other tools and it simplifies the writing of complex queries. AUTHOR Frank Kirkland LAST REVIEW DATE 13 August 2007 Labels: Apex SQL Edit ... |
© 2007 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use