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
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 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 ...Quickly Edit Queried Data Using Drill Down DESCRIPTION Using the new features of ApexSQL's Drill Down, the process of editing data from query results just got a lot easier. SOLUTION This solution will show how to use the the Drill Down feature to quickly edit data from queries. Using the Northwind database, we search the row from the Order table with Customer ID = VINET, Shipped Date = 1996-07-16, and Unit Price = $9.80. We need to update this record's unit price to $10.50. To begin, we run the following query: select Ord.OrderID, Ord.CustomerID, Ord.ShippedDate, Detail.UnitPrice This gives us the following results:  By examining the rows shown, we see that the Shipped Date and Unit Price we are looking for is in the second row. At this point, we would need to start constructing an update statement. Instead of manually typing the update statement, we use ApexSQL's Drill Down. To do is, we simply find the column(s) that is/are unique to the table containing the record we want to update. In this example, the column is OrderID. Right click on the row and select Drill Down from the context menu. This will launch the Drill Down - Multi-Column dialog. Select the column(s) you wish to use to perform the Drill Down on and click OK.  If only one column is needed for the Drill Down, you can bypass the Multi-Column dialog by double clicking on the column you want to use (in this case, OrderID). The Drill Down window (shown below) lists the tables that have the columns that you are "drilling" on. As you click a table in the list on the left, column values from the row you started the Drill Down from will be listed out on the grid.  Click on the Order Details table and we will see in row 2 the Unit Price that we need to change. Click on the cell and type in the new value of $10.50.  After you change the value, scroll down to the changed row. Notice that the background of the cell that was just changed has changed.  This has not been committed to the database. Press the Update button to commit the change or the Refresh button to discard your changes. Once you press the Update button you are done. Click OK to close the window. Your record has been updated. AUTHOR Stephen Schmidt LAST REVIEW DATE 27 August 2007 Labels: Apex SQL Edit ...Visually Differentiating Databases through Tab Colors DESCRIPTION Many workplaces replicate the same database several times for Development, Testing, and Production purposes. Even when being "really careful," it is easy to mistake the active Production database window for the Development database. ApexSQL Edit allows you to visually differentiate your databases through colored tabs. This KB article discusses how you can use this feature to avoid the mistake of executing an SQL statement against the wrong version of your database. SOLUTION To illustrate, we use three versions of the Northwind Database: NorthwindProd (production DB), NorthwindTest (testing/validation DB), and Northwind (development DB). 1. To begin, right-click on a database and select "Set Query Tab Colors":  2. In the Database Tab Color dialog box, select a color that is indicative of the environment you have selected (e.g. Green for Dev, Blue for Validation and Red for Production):  3. Select the OK button and open a "New Query Window" for that database. You will see that the tab at the top of the screen has changed colors:   Repeat this process for each of your databases. Tip: You can change the gradient (fade) color of the active tab to be more easily recognizable. Simply press Ctrl+Shift+s and find the option "SelectedTabGradient" and change it to a different color (Black is used in the screen shot below):  5. When done, the different tab colors enable you to quickly and easily differentiate between your open editor windows:  AUTHOR Stephen Schmidt LAST REVIEW DATE 21 August 2007 Labels: Apex SQL Edit ...Using Snippets to create data-based Intellilist functionallity DESCRIPTION ApexSQL Edit has a very powerful Intellilist feature for helping you write SQL Syntax and database objects. When the intellilist information you need comes from the database itself, ApexSQL Edit's feature comes in handy. ApexSQL Edit’s snippet feature can be used to create Intellilist-values that comes from the data in your database. This feature can be taken one step further to substitute a display value for a key value especially for identity foreign keys. Most stored procedures do not used hardcoded data values and so the ability to create data-based Intellilist functionality is very useful for data mining or creating ad hoc queries. SOLUTION To illustrate, we will create a new snippet that lists Categories from the Northwind Database. Once a Category is chosen, its Category ID will be inserted to the editor. Go to Tools > Manage Snippets to open the snippet manager:  Click on the “Advanced” button to launch the Custom Snippet Replacement Management Editor.  Custom Replacements can be thought of as custom list-based variables to be used in Snippets. This solution will define one that will show a list based on a result queried from the database. Select the New button from the bottom right of the Custom Snippet Replacement Management window to add a new replacement. Create a new Replacement filling in the values as shown below:  For the Replacement Type select “DataList” (“List” is used to hard code your list values).  Fill in the “DataList Query” with the following text:  The values returned in the "as display" column will be displayed in the drop down list.The values returned in the "as value" column will be substituted for the display value when it is selected.(If you do not want any substitution, select only one column.) Leave Values List empty. (Note: To hard code a list of values you would enter them there.This is used in conjunction with List as a Replacement Type.) Select “OK” to close the Custom Snippet Replacement Management window and return focus to the Snippet Manager. Now that the Custom Replacement is made, we need a snippet that will hold it. To do this, right click on the Snippet folder and select “Add Snippet”.  Enter the name you want to save your snippet as in the Create New Snippet save dialog.After you save the new snippet, enter a Description, Shortcut and Title in the Snippet Manager datagrid.  Click on ellipse button next to Snippet Literal Replacments to lauch the Snippet Literal Colelction Editor.(The ellipse button is not present until you click on that row.) Snippet literals can be thought of as variables for your snippets.If they are not named the same as a Custom Replacment then the user will be prompted to enter free text for the variable when the snippet is run.Click the “Add” button to create a new literal and set the Literal ID to the same value as your Custom Replacement.  The last step is to add your Custom Replacement/Snippet Literal to the Snippet text.In the editor below the data grid, add the text “$CategoryID$” (without the quotes). You can add more Custom Replacements, Literals, and static text to construct a large snippet. For this example, we will simply use the custom replacement. Note that surrounding a word with the $ tells ApexSQL Edit that the word is a literal. Select the "Save and Close" button to close the snippet manager. Back in the main edit window, at the end of a line, type in your snippet shortcut followed by a tab (the shortcut invocation of a snippet only functions if there is no text after it). A drop down will appear showing all the display values from the custom replacement query. (Ctrl+Alt+S or Right Click->Insert Snippet can also be used to select a snippet if you forget the shortcut.)  After a display item is selected from the list, the corresponding Value Item is entered in the query.  SEE ALSO ApexSQL Edit Snippets LAST REVIEW 28 August 2007 Labels: Apex SQL Edit ...ApexSQL Edit's Centralized Mapping DESCRIPTION There are source codes for the client application; then there are source codes for database objects (create scripts). In this KB, we discuss how to use ApexSQL Edit to maintain a Source Code control system for the latter. What is centralized mapping? ApexSQL Edit offers you two methods of storing source code information: 1. Local 2. Central Simply put, these two methods differ in the location where data is stored. The "Local" method literally uses a location on the workstation, while the "Central" method uses a dedicated SQL Server database to store the data. For a development team working simultaneously on one project, the Central method should be used. How do you set up centralized mapping? To set up centralized mapping, open the Options dialog by choosing Tools -> Options... from the Main Menu:  Here we find the Source Control Options:  This Source Control Option group is comprised of two items:  Our focus on this KB is "Mapping Options". By clicking on this item, you'll see the various mapping options and settings currently available in ApexSQL Edit:  The above screenshot shows how this dialog initially looks like with the default values. The first thing you need to do to set up centralized mapping is to select the central SQL Server database that should be used as source code repository. Place the cursor in the "Central Database" field and click  .This opens the Login form where you can select the server and the database and provide your login credentials:  Once this is done, click on the Ok button. Next, set the "Source Control Data Store Type" from "NA" (this is only initially displayed and then disappears unless manually typed in) to "Central":  The general options for centralized mapping have now been set. Mapping databases Next, we map a database to our central data store. Using the Schema Explorer in ApexSQL Edit, you can easily check the mapping state of any database. Simply move the mouse over that particular database and ApexSQL Edit displays the mapping state of the database in a tooltip:  This shows that the SQL Server Northwind sample database is currently unmapped. To initiate the mapping process, right-click on the Northwind database node in the Schema Explorer and select Source Control... -> Map Database from the Context Menu:  This invokes the Source Control Mapping Wizard:  Click here for more information on the Source Control Mapping Wizard. Since we have already specified a central database, the Mapping Wizard displays the Setup Source Control Mapping Data Store page:  Selecting the "Centralized Data Store" option will activate the "Choose Connection" and "Create DB Schema" buttons. The Choose Connection button Clicking this button  enables you to choose your centralized data storage. There is no need to do this if you prefer to use the database already specified in the General option.The Create DB Schema button Clicking this button  enables you to initially set the central database that holds the source control data. All the necessary database objects will be created: Follow the remaining steps of the Source Code Control Mapping Wizard as described in the online help. After the Mapping Wizard has successfully finished, you can see the changed mapping state when you move the mouse of the database in the Schema Explorer:  You have successfully set up a centralized data store in ApexSQL Edit. AUTHOR Frank Kirkland LAST REVIEW 26 August 2007 Labels: Apex SQL Edit ...Deployment Projects DESCRIPTION Writing source code (no matter if client or server-side) in a development environment is one thing. Deploying this source code from a development environment to a production environment is another. Most companies enforce a change management process to protect their running production environments from poorly written and/or tested code. For such purpose, ApexSQL Diff can be the used.However, there might be situations or even environments where a quicker procedure is appropriate. Such as bringing source code from a development environment to a quality assurance and acceptance environment or smaller companies in which the developer and quality tester is one and the same person. For such cases ApexSQL Edit offers a feature named "Deployment Projects", which I'm going to describe in this blog post. SOLUTION As usual I'm using SQL Server's Northwind sample database along with my 1:1 copy of it, a database named Seattle. And since the room in such a blog entry like this is very limited, I'm focusing on a very basic scenario in which I'm going to write a new stored procedure in the Northwind database (this stands for my development environment) and then going to deploy this stored procedure to the Seattle database (which stands for my production/testing environment). Result of this operation will be that both databases are identical with respect to stored procedures. Creating a new Deployment Project In order to create a new deployment project, you need to select File -> New -> New Deployment Project from the Main Menu:  This will open a new tab in ApexSQL Edit's query panel:  Let's focus for now on the grey area. Here you can enter general information for your deployment project, such as a name and a description. Next you can specify an SQL Server connection that will act as default connection whenever a connection to SQL Server needs to be established unless it is explicitly overriden on the deployment item level. Note that this connection needs to point to your database to which you want to deploy your objects from the development environment (in my case this is my Seattle database). In order to create a connection, just click on the "Set Connection" link:  This will open the typical ApexSQL Edit SQL Server Logon form in which you can specify the target server and database:  The next option deals with the source from which objects are deployed:  Here's an extract from the ApexSQL Edit help file explaining the meaning of each option: Use Current File: this will use the current file from the work folder of Source Control for deploying a database from. Get Latest Version: this will use latest file version for deploying a database from. Get Labeled Version: this will use labeled file version pointed in the right edit box for deploying a database from. For example, in the above graphic we entered “v. 1100.11” file version we want to use. Finally, you can specify how ApexSQL Edit should deal with errors that might happen during a deployment process:  It is not all that hard to guess that marking this option checked makes use of SQL Server's transactional features, e.g. the deployment script is wrapped within a transaction, so that in case of any error all changes applied up to that point are rolled-back leaving the database in a consistent state again. Let's now focus on the blue area of the deployment projects tab:  This part can be used to set the project's options. You can add custom groups and deploy your project from here. But let's have a look at it step by step. Below you can see a screenshot of the project options group:  How to use Deployment Groups? Deployment Projects in ApexSQL Edit consists of one or more "deployment groups" by means of which of can organize your projects. Deployment groups act as containers for items that you want to deploy. Especially for larger projects it makes sense to use more than one of these groups and organize the items to be deployed among these groups, by object type for example. You could have a group for stored procedures, another one for UDF's, and yet another one for views, or other objects. By default, ApexSQL Edit deployment projects contain one group when you create a new deployment project. It's named "Default Deployment Project" and is displayed right below the project option's group. To add a second, just click on the "Add Group" hyperlink:  Immediately a new group is displayed right below the last group. You can edit this group's properties by clicking on the "Edit Group" hyperlink. This opens a similar property page as the one you have already seen above when I explained the property page of a deployment project:  You can rename each group so that the names are more meaningful to you. In the above case I have chosen to rename to group to "My New Deployment Group". By default, each new group inherits the connection settings of the containing project and the setting from which source objects of this group should be deployed. Both settings can be changed individually for each group. The last control is probably the one that requires the most explanations, because we haven't had yet a screen control with a similar meaning: The Execution Prerequisites list. What is this list used for? Well, think of a situation where you're going to deploy a new stored procedure that accesses a new table. In this case the table would be a required prerequisite for the procedure. Not necessarily for the deployment process, as the stored procedure alone could be deployed just fine. However, executing this stored procedure would yield you nothing, as the underlying base table is missing. To avoid such a situation you would add the table to this list. Deploying a database object As mentioned in my introduction, I'm going to write a new stored procedure in my development environment which I then will deploy to my production/testing environment. So, here's is my new stored procedure:  Nothing fancy, but that's not all that relevant here at all. First of all I'm executing this script in my development database to create the procedure. Then I'm saving the script as FetchSingleOrder.prc to my filesystem and add it to Source Control. This last step of adding an item to Source Control is essential when working with Deployment Projects. It is the Source Control Explorer from which you drag items to your deployment groups. So, after finishing this steps, my Source Control Explorer looks like this:  As already mentioned above, adding items to be deployed to a deployment group is simple. Just drag this item from the Source Control Explorer to a particular deployment group and drop it there:  In the above screenshot I'm dragging the FetchSingleOrder.prc treeview item from the Source Control Explorer to the "My New Deployment Group" deployment group and drop it there. The result of this operation is:  Immediately after dropping the item, it is added to the list of objects in this deployment group. By clicking on a list item, you can access this item's deployment settings:  This action will open this items deployment property page:  Some values on this property page cannot be edited, while others can be edited. You cannot edit the Name, Source Control Path, and Local Path values. These are just displayed for informational purposes. You can however edit the connection settings and the setting from which source object this item should be deployed. By default, ApexSQL Edit will assume that the parent settings from the deployment project settings will be used. You can also see here the Execution Prerequisites list. You can specify on the object level which items are also required for a deployment if this item. In my simple scenarion there is no need for any additional step and so I'm now ready to deploy my stored procedure. This operation is initiated by clicking on the "Deploy Project" link in the deployment project option group.  This will start the deployment process. ApexSQL Edit displays a confirmation message box:  After confirming this message all items to be deployed are embedded in a script generated by ApexSQL Edit and then this script is executed against the target database of the deployment project, respectively the database(s) specified on the other levels on which you can set a connection property. Once the deployment process has finished ApexSQL Edit displays a report which informs you about the final deployment status of each single item that was deployed in this project:  In the above screen you can see that my stored procedure was successfully deployed to the Seattle database. To verify this you can check this via the Schema Explorer:  You can see that both databases (Northwind and Seattle) are identical with respect to stored procedures. Done. Conclusion There is probably much more to say about deployment projects. You can save such projects, open, and modify them just like any other project in ApexSQL Edit. But I would suggest that you take a look at this feature of ApexSQL Edit yourself. Deployment Projects offer a simple, yet effective way to safely push objects from one environment to another. AUTHOR Frank Kirkland LAST REVIEW DATE 31 August 2007 Labels: Apex SQL Edit ...Welcome to the ApexSQL Knowledge Base Welcome to the ApexSQL Training Knowledge Base. Here you'll find tips, reviews, videos, and articles on how to get the most out of ApexSQL Tools and Microsoft SQL Server. Labels: Apex SQL Audit, Apex SQL Edit, Apex SQL Script, ApexSQL Clean, ApexSQL Code, ApexSQL Diff, ApexSQL Doc, ApexSQL Enforce, ApexSQL Law, ApexSQL Log, ApexSQL Report, General ... |
© 2007 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use