LINKS
General
Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Data Diff
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Recover
Apex SQL Report
Apex SQL Script
Knowledgebase Home
Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Data Diff
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Recover
Apex SQL Report
Apex SQL Script
Knowledgebase Home
MOST RECENT
Silent installation and un-installation of the ApexSQL products
Use the C# projects to execute the synchronization script
How to Sychronize a Database on a Schedule
How can I email the output of a scheduled CLI job (e.g. reports, log files)?
Regular expressions patterns for objects filtering
How to Create a SQL Server/Data Source Alias
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 Silent installation and un-installation of the ApexSQL products SUMMARY How to perform a silent installation and un-installation of the ApexSQL products. RESOLUTION To do the ApexSQL application installation/un-installation in silent mode, the following instructions should be done: a. Installation: To use the silent mode installation, use CLI with the following switches: /VERYSILENT – this suppresses all installation wizard dialogs /SUPPRESSMSGBOXES – this suppresses all messages from the installer /LOADINF="filename" – this will load the previously saved installer settings (such as the installation directory, shortcuts and so forth…) /SAVEINF="filename" – this will save all of your choices during an “ideal” installation (these choices will then be used in LOADINF switch) Create an .inf file that will retain all choices of the wizard: 1) Start ApexSQL installer with SAVEINF switch ApexSqlEdit.exe /SAVEINF="edit.inf" This will save all your choices to the For example, the edit.inf contains the following info (then it can be edited manually): [Setup] Lang=default Dir=D:\Program Files\ApexSQL\ApexSQLEdit1456 Group=ApexSQL\ApexSQL Edit1459 NoIcons=0 2) Use the following switches and the For example: ApexSqlEdit.exe /VERYSILENT /SUPPRESSMSGBOXES /LOADINF ="edit.inf" b. Un-installation: To un-install your ApexSQL software, use the uninstaller program (unins000.exe) that accepts optional command line parameters. These can be useful to system administrators, and to other programs calling the uninstaller program. /SILENT, /VERYSILENT When specified, the uninstaller will not ask the user for startup confirmation or display messages stating that uninstall is complete. Shared files that are no longer in use are deleted automatically without prompting. Any critical error messages will still be shown on the screen. When '/VERYSILENT' is specified, the un-installation progress window is not displayed. If a restart is necessary and the '/NORESTART' command isn't used (see below) and '/VERYSILENT' is specified, the uninstaller will reboot without asking. /SUPPRESSMSGBOXES This parameter instructs the uninstaller to suppress message boxes. It has effect only when it’s combined with '/SILENT' and '/VERYSILENT'. See '/SUPPRESSMSGBOXES' under Setup Command Line Parameters for more details. /LOG Causes Uninstall to create a log file in the user's TEMP directory detailing file uninstallation and [UninstallRun] actions taken during the un-installation process. This can be a helpful debugging aid. The log file is created with a unique name based on the current date. (It will not overwrite or append to existing files.) The information contained in the log file is technical and therefore not intended to be understandable by end users. Nor is it designed to be machine-parseable; the format of the file is subject to change without notice. /LOG="filename" Same as /LOG, except it allows you to specify a fixed path/filename to use for the log file. If a file with the specified name already exists it will be overwritten. If the file cannot be created, Uninstall will abort with an error message. /NORESTART This parameter instructs the uninstaller not to reboot even if it's necessary. So you can just specify /VERYSILENT and /SUPPRESSMSGBOXES switches to the ApexSQL uninstall program. Example: Unins000.exe /VERYSILENT AUTHOR Konstantin Pogosov LAST REVIEW 25 August 2008 Labels: General ...Use the C# projects to execute the synchronization script DESCRIPTION Sometimes the ability to simply run a MSI or MSP executing synchronization script is needed in the process of upgrading and patching databases. "ApexSQL Script" allows to create the original database by running the compiled C# project as part of MSI. "ApexSQL Diff" creates scripts to migrate the database from one schema to another, however lacks the C# project generation. Suggested workaround uses synchronization script generated by ApexSQL Diff with a C# project that was produced by ApexSQL Script to make it possible compile a program that is to be run as part of a MSI/MSP to upgrade or patch the server. SOLUTION Please create a C# solution using ApexSQL Script:  You will be prompted to save it somewhere. Save it in the desired directory. Then create a synchronization script using ApexSQL Diff (or take the existent one) and save the script to the directory with the ApexSQL Script C# Solution as Script.sql and replace the existent file. Then please open the saved solution in Visual Studio and build it. You will have .Net executable file as a result. This executable can be distributed to your clients without a problem. It will contain your synchronization script. Now you can use this solution (you may not generate it from ApexSQL Script anymore) for all your ApexSQL Diff synchronization scripts. Just replace Script.sql with the desired script and then rebuild the solution. NOTE I1004-KB for Use the C# projects to execute the synchronization script(exist as forum post) AUTHOR Irene Sanford LAST REVIEW DATE 04 March 2008 Labels: General ...How to Sychronize a Database on a Schedule DESCRIPTION There are times when you want to run ApexSQL Diff operations on a schedule. For example, you may want to sychronize your testing and production databases every night (during downtime). This KB article describes how to schedule a database sychronization in ApexSQL Diff. (Note: You can use the same approach in, for example, ApexSQL Log if you want to read the transaction log on a desired frequency and schedule). SOLUTION To schedule database sychronization, use the free command line interface that comes with ApexSQL Diff (and all other ApexSQL Tools) together with Windows'/SQL Server's scheduler. To illustrate, let's use 2 databases: TestDB (testing database) & ProdDB (production database): UID/PWD FOR TESTDB: TESTDBSA/PASSWORD1 UID/PWD FOR PRODDB: PRODDBSA/PASSWORD2 To sychronize TestDB with the ProdDB, the following command needs to be executed: APEXSQLDIFF.COM /S1:SERVER_NAME /D1:PRODDB /U1:PRODDBSA /P1:PASSWORD2 /S2:SERVER_NAME /D2:TESTDB /U2:TESTDBSA /P2:PASSWORD1 /SYNC To run this command every Sunday, 10 pm, schedule it using SQL Server or Windows' scheduler. To schedule using SQL Server: Verify that SQL Server Agent is running. Open SQL Server and create a new job: a. In the Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand SQL Server Agent. Right-click Jobs, and then click New Job.  b. On the General page, in the Name box, type a name for the job. Specify Owner and Category of a job. In the Description box, enter a description of what the job does. The maximum number of characters is 512. Clear the Enabled check box if you do not want the job to be run immediately following its creation.  c. In the Steps page click the New button to create a new job step. The New Job Step dialog is opened.  d. In the General tab of New Job Step dialog specify a job Step Name. In the Type list, choose Operating system (CmdExec). In the Command box, enter the ApexSQL Diff CLI command:  In Advanced tab of New Job Step dialog the following job step options can be specified: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file where SQL Server Agent can write the job step output. e. To run the job every Sunday, 10PM, create a new schedule in the New Job dialog’s Shedule tab:  Specify the schedule options in the New Job Shedule dialog as shown below:  To do this using Windows' Scheduler: 1. Open Scheduled Tasks and double-click Add Scheduled Task.  2. In the Scheduled Task Wizard, click Browse and go to ApexSQL Diff's application folder and select apexsqldiff.com  3. Assign a name for the synchronization job and specify frequency.  4. Select the time and day you want the task to start.  5. Specify login credentials as necessary.  6. Check Open Advanced Properties before clicking Finish.  7. In the Run textbox of Advanced Properties, type in the following and click OK. "C:\Program Files\ApexSQL\ApexSQLDiff\ApexSQLDiff.com" /S1:SERVER_NAME /D1:PRODDB /U1:PRODDBSA /P1:PASSWORD2 /S2:SERVER_NAME /D2:TESTDB /U2:TESTDBSA /P2:PASSWORD1 /SYNC  You may use any of the above approaches to schedule your database synchronization NOTE I1001-Update KB and send to Alexey and Janice for review: Comprehensive step-by-step description of using SQL Server Jobs or Windows Scheduler to execute CLI command on regular basis AUTHOR Irene Sanford LAST REVIEW DATE 04 March 2008 Labels: ApexSQL Diff, General ...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 ...Regular expressions patterns for objects filtering DESCRIPTION Regular expressions are used to specify patterns for text searching and processing. This KB discusses regular expressions and how to use them for object filtering in ApexSQL Tools. Definitions A regular expression (regex or regexp for short) is a special text string for describing a search pattern. A match is the piece of text, or sequence of bytes or characters that pattern was found to correspond to by the regex processing software. A word character is an alphanumeric character in the alnum class or an underscore (_). A word boundary is generally defined as a location where there is a "word character" on one side, and not on the other and designated by \b. Assertions Assertions specify the position of match for the pattern: \b - A match must occur at a word boundary a. \b(Cu) matches the tables with the names starting with “Cu”  b. (Currency)\b matches the tables finished with “Currency”  \B - Specifies that the match must not occur on a \b boundary. a. (History) matches tables with names containing “History” substring anywhere  b. (History)\B matches tables containing “History” in the middle  ^ - Specifies that match must occur at the beginning of a string.  $ - Specifies that match must occur at the end of a string.  For more information on assertions please refer to: http://msdn2.microsoft.com/en-us/library/h5181w5w(VS.71).aspx Character Class A Character class is a pattern in a regular expression that matches any of a set of characters. To match an “a” or an “e”, use [ae]. You could use this in gr[ae]y to match either “gray” or “grey”. The order of the characters inside a character class does not matter.  a. The \b(C[ou][ulnr])\w+ pattern matches the strings that start with the “C” character followed by second “o” or “u” characters followed by third “u”, “l”, “n” or “r” characters and followed by one or more word characters.  b. The Sales…son pattern matches the strings containing substring “Sales” followed by any three characters followed by “son” substring.  Grouping Constructs Grouping constructs allow you to capture groups of sub-expressions.  a. The \b(Sales) pattern matches to strings started with “Sales”  b. The \b(?!Sales)\w+\b matches strings that not started with “Sales”  c. The Sales(?!Person) pattern matches strings containing “Sales” substrings followed by any but “Person” substring.  Quantifiers Quantifiers add optional quantity data to a regular expression. A quantifier expression applies to the character, group, or character class that immediately precedes it. The .NET Framework regular expressions support minimal matching ("lazy") quantifiers.  a. The (Product){2} pattern matches strings with exact two “Product” substring repetitions.  b. The (Product)+ pattern matches the strings with at least one appearance of “Product” substring.  Escape Characters An escape character signals to the regular expression parser that the character is not an operator and should be interpreted as a matching character. The escaped character \b is a special case. In a regular expression, \b denotes a word boundary (between \w and \W characters) except within a [] character class, where \b refers to the backspace character. In a replacement pattern, \b always denotes a backspace.  Filtering objects with the regular expressions in ApexSQL applications The result object selection set is a union of those subsets that are specified by the regular expressions patterns. For example the pattern S[^h]\w+ specifies a string where “S” character is followed by any character but “h” after which any word character may exist:  The pattern S[^a]\w+ specifies a string where “S” character is followed by any character but “a” after which any word character may exist the:  As a result the total objects selection specified by applying both of the patterns includes sub-sets that were excluded by each of the patterns.  More information 1. Teach Yourself Regular Expressions in 10 Minutes by Ben Forta 2. Mastering Regular Expressions by Jeffrey Friedl 3. MSDN Library: Regular Expressions Language Elements AUTHOR Irene Sanford LAST REVIEW DATE 31 August 2007 Labels: General ...How to Create a SQL Server/Data Source Alias DESCRIPTION This KB article describes how to create a data source alias. This is especially useful when you want to create your own connection strings or when you want easier-to-remember server names. SOLUTION 1. Open SQL Server Configuration Manager:  2. On SQL Native Client Configuration, right-click Aliases and click "New Alias":  3. Below is an example of a filled-up Alias properties dialog. Click "Apply":  4. You can now type the Alias on any of ApexSQL tools' server list:  SEE ALSO How to Create a Server Alias AUTHOR Janice Lee LAST REVIEW DATE 21 August 2007 Labels: General ...What to do with large log files due to index rebuilds DESCRIPTION Index rebuilds can create very large log files. This KB discusses what to do when this happens. SOLUTION 1) Make sure the database has a LOT of empty space in it. As in 1 to 2 YEARS worth of estimated growth if it is an active database, or double current space if it is slowly changing. This will allow the index rebuild/reorg process to lay the data/index pages down in contiguous order - as in not fragmented right off the bat. Trying to defrag a full database is a fruitless excercise! 2) Defrag only stuff that is currently highly fragmented. The standard for this is > 30%. See BOL for a script you can use plug-and-play. I also skip very small tables (< ?? pages in size, where ?? is either 50 or 100 depending on the phase of the moon :) 3) Assuming you have a very fragmented system due to not having sufficient space, open up free space and then CONTROL the first few rebuilds. Split up your tables into different runs so as not to create a huge log at one time. Be sure to backup the tlog between runs to flush committed entries and free space. SEE ALSO Excessive DAT Files AUTHOR TheSQLGuru LAST REVIEW 30 August 2007 Labels: ApexSQL Log, General ...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 ... |
© 2008 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use | Privacy Policy