Wednesday, December 11, 2013

xSQL Schema Compare SDK 4 released

We just released xSQL Schema Compare SDK 4 with full support for SQL Server 2012 - implementing database schema comparison and synchronization functionality in your .NET application has never been easier. Built from the ground up with you (the developer) in mind the new SDK exposes a very intuitive object model that allows you to write your first schema comparison and synchronization app in minutes.

The new version of the SDK significantly improves the performance, robustness and scalability while giving you a lot more control over the process through numerous options that you can tweak, events that you can subscribe to and extensive logging.

xSQL Schema Compare SDK 4 supports SQL Server 2012, SQL Server 2008 and SQL Server 2005

Download the xSQL Schema Compare SDK 4 now and check it out.

Wednesday, December 4, 2013

2013 holiday specials are on

The rolling product slides on the main page of our site have been replaced by the "holiday" slide that invites you to unveil that special holiday present that we have in store for you. Don't hesitate, go ahead and "open the box" to see what today's special is, you will be pleasantly surprised. 

If you like what you see, don't delay to take advantage of it – if you delay you might have to wait a whole other year before that same deal is offered again. 

Season's Greetings and Happy Holidays!

Monday, September 16, 2013

SQL Data Compare Free - last chance

Last week when we ran the SQL Server Data Compare free promotion for a day we had quite a few complaints from users, especially from our European users, who got the email notification too late in the day and were not able to take advantage of this awesome deal. Therefore, we have decided to give everybody one more chance, and this time we are letting you know ahead of time. Here are the details:
- the window of opportunity opens on September 17, at 00:01:00 (UTC-05:00) and closes on September 17, at 23:59:00 (UTC-05:00)  - please do the time zone calculations before you contact us complaining that the code is not working.
- one license per user only - if you get more than one all your licenses will be invalidated and you will get into our naughty list.
- again, you will need to find the discount code which this time is composed of the first letter of the LAST feature (under the "Features" section on the product page) of the following products in order: xSQL Schema Compare, xSQL Data Compare, xSQL Script Executor, xSQL Profiler, xSQL Documenter, xSQL Builder, xSQL RSS Reporter, xSQL Object Search - so the code will have 8 letters. Once you find the code then do the following:
  • go to the SQL Server Data Compare page
  • click on "Order" button on the right hand side
  • under the "Licensing Options" select the first item "DPN2-1U"
  • on the next page, by default we include the 1 year maintenance which is not part of this deal so if you don't want to pay anything select the "No maintenance" option. Consider this step carefully as there is a new release with support for SQL Server 2014 coming in a few months.  
  • click on the "Add to Cart" button
  • on the shopping cart page plug in the discount code that you identified above and click on "Apply Discount" - that will subtract the full $349 from the price
  • proceed with the checkout to obtain your license.
You can start here: http://www.xsql.com/

Friday, August 30, 2013

How to create a stored procedure on multiple databases

I noticed this particular question asked on one of the SQL forums the other day, however, this same question can apply to any database objects, like how to create a function on multiple databases, how to create a view on multiple databases, how to create a table on multiple databases, etc. If you think about it just for a bit you will realize that in fact the generic question that covers all those particular cases and more is: how to execute a t-sql script against multiple databases.
Well the best, safest and most efficient way for executing or deploying t-sql scripts on multiple databases is to utilize xSQL Software's Script Executor tool. Here is the breakdown of the time you would need to spend to accomplish your task:
  • Download and install Script Executor -> 2 minutes max
  • Create a database group and add your servers and databases into the group -> 30 seconds per server
  • Create one or more script containers and add your t-sql scripts to those containers -> less than 2 minutes depending on where the scripts are and how you might need to organize them
  • Map scripts to databases and set execution priorities -> 2 to 3 minutes depending on how complicated your deployment scenario is.
  • (optional) Create a deployment package -> 10 seconds (you would create a deployment package if you wish to executed the scripts from a machine where you might not have the Script Executor tool installed)
  • (optional) Create a batch file that executes the deployment package created above OR that executes a saved deployment project from the command line -> 2 minutes
  • (optional) Create a scheduled task that executes the batch file -> 1 minute
So, in about 10 minutes you will have created an automated job that deploys the scripts you want to the servers and databases you want, when you want! Now whenever you might wish to deploy one or more scripts that you have created to those databases all you need to do is drop the scripts to the folder(s) to which the Script Containers created above are pointing to and you are done - your scheduled task will take care of the rest.
 
Download Script Executor now and see what you have been missing.

Wednesday, August 14, 2013

How to get list of tables, number of rows, data and index space

Here is a simple query that returns the complete list of user tables on a SQL Server database - it includes the schema name, table name, the date on which the table was created, the number of rows, the disk space occupied by the data in KB and the disk space occupied by the indexes:

SELECT sschemas.name AS SchemaName
               ,sobjects.name AS TableName
               ,sobjects.create_date AS CreatedOn
               ,sobjects.modify_date AS ModifiedOn
               ,MAX(sstats.row_count) AS NoRows
               ,SUM(CASE
                               WHEN (sstats.index_id < 2)
                                       THEN sstats.in_row_data_page_count + sstats.lob_used_page_count +
                                                   sstats.row_overflow_used_page_count
                                        ELSE 0
                            END) * 8 AS DataSpaceKB
                 ,SUM(CASE
                                 WHEN (sstats.index_id >= 2) THEN sstats.used_page_count
                                 ELSE sstats.in_row_used_page_count - sstats.in_row_data_page_count
                   END) * 8 AS IndexSpaceKB
FROM sys.schemas AS sschemas
            INNER JOIN sys.objects AS sobjects ON sschemas.schema_id = sobjects.schema_id
            INNER JOIN sys.dm_db_partition_stats AS sstats ON sobjects.object_id = sstats.object_id
WHERE sobjects.type = 'U'
GROUP BY sschemas.name, sobjects.name, sobjects.create_date, sobjects.modify_date
ORDER BY sobjects.name

A couple of things you need to know in order to understand this:
  • one page is equal to 8K in size 
  • index_id values are 0 for the heap, 1 for the clustered index and > 1 for nonclustered indexes
  • in_row_data_page_count represents the number of pages used for storing in-row data and it only includes the leaf pages
  • lob_used_page_count represents the number of pages used for storing out of row text, n/varchar(max), n/varbinary(max), image, and xml columns.
  • row_overflow_used_page_count represents the number of pages for storing row overflow
  • in_row_used_page_count represents the total number of pages in use to store in-row data including both leaf and non-leaf pages. Hence, to determine the index space for the clustered index we subtract the in_row_data_page_count from the in_row_used_page_count to get only the non-leaf pages which should be attributed to the index space.
Any questions or comments please leave them here.

If you have not tried our SQL Server comparison download them now http://www.xsql.com/download/sql_server_comparison_bundle/ - quick and easy to install, even easier to use, fast, robust and completely free for SQL Server Express.

Tuesday, August 13, 2013

SQL Schema Compare and xSQL Builder new builds available

New builds of SQL Server Schema Compare and xSQL Builder are available for download.
The following fixes are included in both tools: 
  • an issue related to conversion of varchar(max) data type to image data type;
  • error triggered by foreign keys with the same name created on different tables;
  • issue related to dependencies between a full-text index and the unique key/index associated with it;
  • small corrections related to the conversion of char, varchar data types to float and real data types;
  • issue related to parsing of SQL Server multi-line comments in the object definition;
  • issue with the serialization of user-defined data type binding to rules and stand-alone defaults.
Has our SQL Schema Compare (xSQL Object) saved you time? If yes, please consider recommending it on LinkedIn.

Thursday, August 1, 2013

t-sql list of currencies of the World

On February 15, 2022 we released easy lookups by xSQL Software https://lookups.xsql.com/ - you can now get the currencies list and other commonly used lists for free. You can consume those lists directly as JSON/xml services in your JavaScript apps, or download in one of the following, ready to use formats:
  • JSON
  • XML
  • CSV
  • SQL Server insert statements
  • Oracle insert statements
>>> original article below - links replaced with the new lookup link

The following script (https://lookups.xsql.com/ ) creates a "currencies" table and populates it with the complete ISO 4217 list of international currency codes (edition ISO 4217:2008).
Note that the first two letters of the [AlphabeticCode] in the Currencies table correspond to the [ISO_ALPHA2] code on the Countries table which you can get from https://lookups.xsql.com/, whereas the [NumericCode] in the Currencies table is in most cases the same as the [NumericalCode] in the Countries table.
Please note that the list is current as of the date of this post.

If you have any questions or comments please post them here.

Wednesday, June 26, 2013

How to get a free license for any of our SQL Server tools

As many of you know, our SQL Schema and Data comparison tools are free for SQL Server Express, our xSQL Profiler is free for a single SQL Server instance and so is our RSS Reporter, so in most cases you don’t even need a license. However, if you need a full license but do not wish to spend any money for it there is a way to do that: http://www.xsql.com/community/

If you have any questions or comments please leave them here or email at our support address.

Friday, June 14, 2013

Technical writing contest – July 2013

Working title: “All about SQL Server in 5000 words“
Prize:  $1,000 for the first place winner.
Challenge: an immense amount of information related to SQL Server is available on the public domain, so the real challenge is not finding enough material to include in your article but rather maximizing the amount of information and knowledge that you can impart to the readers in no more than 5,000 words.
Eligibility: open to all.
Rules:
  • You must email us at info@xsql.com before July 1, 2013 if you wish to participate
  • The article you write cannot have more than 5,000 words and no more than 10 images.
  • You will grant us the exclusive right to publish the article on our web sites.
  • The final article should be submitted to us before or on July 25, 2013 via email.
  • The winner will be determined based on and aggregate score made of the following:
    • 70% of the score will be assigned by a panel of experts that will judge your article based on (in no particular order):
      • Comprehensiveness of the coverage
      • Accuracy of the content
      • Organization of the content
      • Clarity (how easy it is to read and comprehend)
      • Creativity
    • 30% of the score will be determined based on the number of people that will read your article from the date it is published until August
  • The winner of the July contest will be announced on September 5, 2013
Please direct any questions and comments to info@xsql.com

Monday, June 3, 2013

SQL Comparison Bundle new build - multiple workspaces

We just published a new build of our SQL Server Comparison Bundle tools with support for multiple workspaces. The new build also contains the following fixes:
  • Fixes an error that occurs when the database name contains unicode characters.
  • Fixes an error related to the columns that participate in an index.
  • Fixes a scripting issue related to database compatibility level.
  • Fixes an issue related to replicated tables.
  • Fixes a refreshing problem with schema comparison grid.
  • Improves the handling of SQL Server multi-line comments.
  • Fixes a scripting error related to "EXECUTE AS" clause of CLR database triggers.
You can download the new build from:  http://www.xsql.com/download/sql_server_comparison_bundle/

Tuesday, April 30, 2013

A better way to license xSQL database deployment tools

Today we are introducing a new, more efficient and much more affordable way to license our database deployment tools, SQL Schema Compare, SQL Data Compare, xSQL Builder and Script Executor - instead of purchasing a perpetual license for each tool you can now purchase a one year Silver Subscription that includes all four tools.

How is the subscription better? The answer is simple - it is a lot cheaper initially, $399 versus $1,497, and the cost differential gets better every year, subscription renewal is only $199 versus the yearly maintenance cost of $250 for the 4 tools.

I have a Comparison Bundle license, can I switch to a subscription? Yes, if you have a Comparison Bundle license you can purchase a Subscription Renewal for only $199. You will need to provide the Comparison Bundle license.

Monday, April 29, 2013

xSQL Profiler prices slashed more than 70%

Recently we took a "deep dive" into the feedback we have received regarding our xSQL Profiler and have come to realize that while the single SQL Server instance license is sufficient for a good percentage of our users, there are many of you that would have liked to trace multiple SQL Server instances simultaneously but found the prices for a 5 or more SQL Server instances license prohibitively expensive.

Today we are changing that, we have slashed the xSQL Profiler prices to the point where we believe anyone who needs a multi-server license can easily afford it. Here are the new prices for xSQL Profiler:
  • 1 SQL Server -> $0 (no change)
  • 5 SQL Server Instances -> $199
  • 10 SQL Server Instances -> $349
  • 20 SQL Server Instances -> $699
  • 100 SQL Server Instances -> $1,299
Keep sending us your comments and suggestions - we greatly appreciate it!

Friday, February 22, 2013

xSQL Builder v4 with support for SQL 2012 released

We just released version 4 of xSQL Builder for SQL Server. The new version is built on top of the new, much more efficient and robust database schema compare API that was released on November of last year. Here is what’s new on xSQL Builder v4:
  • Improved performance thanks to the new schema compare API
  • Support for a new optimized schema snapshot definition
  • Full support for SQL Server 2012, SQL Server 2008 and SQL Server 2005
  • New C# template built for Visual Studio .NET 2010
  • New command line module for generating executable packages from the command line
You can download the new version from our site at: http://www.xsql.com/download/package.aspx?packageid=30
 
Important: if you are running a licensed old version of xSQL Builder and your license was purchased before February 21, 2013 then you might want to hold off on installing the new version until you have made the decision to upgrade since the new version overwrites the old and your license does not support the new version. Of course you can always try the new version on a different machine.

Friday, February 8, 2013

Installing xSQL Comparison Bundle on Windows 8

When installing our comparison tools (SQL Schema Compare and SQL Data Compare) on a Windows 8 machine the installation may fail with the message “SQL Server Comparison Bundle v4 Setup Wizard ended prematurely because of an error” (see screen shot below).

Cause: by default on a Windows 8 machine only .NET Framework 4.5 is enabled whereas previous versions of the .NET Framework are not enabled.  

Solution: enable .NET Framework 3.5 (this includes .NET 3 and .NET 2) on your machine. To do this go to Control Panel / Programs / Programs and Features / Turn Windows Features on or off and then check the “.NET Framework 3.5.1” checkbox and click OK (the machine needs to be connected to the Internet while this is being done. After enabling .NET Framework 3.5.1 re-run the installation of xSQL Comparison Bundle and it should install without a problem.

Wednesday, February 6, 2013

SQL Server Comparison bundle - new build available

A new build of the SQL Server Comparison Bundle is available for download. The changes implemented are relatively minor however, we recommend that if you are using version 4.x of the tools you download and install this new build, which:
  • Fixes an issue with the GUI scaling on different resolution/DPI settings (both, schema and data compare).
  • Fixes an issue with user membership to roles (schema compare).
  • Fixes a bug related to the order of the included columns in an index (schema compare).
  • Adds two new command line options for the data compare where clauses, allowing you to set different filters on each table/view on the pair (data compare).

Friday, January 18, 2013

SQL Schema Compare – schema filters

xSQL Schema Compare for SQL Server allows the user to define schema filters that determine which objects will be included or excluded in the comparison and synchronization process. The process of setting schema filters is fairly straight forward and does not need much explaining however, there is one critically important element that has the potential to create some confusion hence this article.
First, a schema filter expression is defined for a particular object type and contains one or more filter criteria. The schema filter expression has a parameter called "FilterValidationType" that can be set to "AtLeastOneCriteria" or "AllCriteria". Each filter criteria has an "Action" parameter that can be set to "Exclude" or "Include" meaning that the object that meets this particular criteria will be excluded or included.
So, let's consider the schema filter expression defined as below:
<SchemaFilterExpressions>
    <SchemaFilterExpression ObjectType="StoredProcedure" FilterValidationType="AllCriteria">
        <SchemaFilterCriteria Action="Exclude" FilterType="StartsWith" Criteria="sp_MSins" />
        <SchemaFilterCriteria Action="Exclude" FilterType="StartsWith" Criteria="sp_MSdel" />
        <SchemaFilterCriteria Action="Exclude" FilterType="StartsWith" Criteria="sp_MSupd" />
    </SchemaFilterExpression>
</SchemaFilterExpressions>


Here is what might confuse things: whether you have defined the criteria action as"exclude" or "include" behind the scenes xSQL Schema Compare for SQL Server translates the object filter expression into an include statement, in other words the final statement selects objects that will be included in the comparison.

So, the above schema filter expression translates into something like this:
   SELECT all stored procedures WHERE [sp name] NOT LIKE 'sp_MSins%'
   AND [sp name] NOT LIKE 'sp_MSdel%' AND [sp name] NOT LIKE 'sp_MSupd%'

Interpretation: all stored procedures the name of which starts with one of those three strings will be excluded whereas all other stored procedures will be included in the comparison.

What if you had set the FilterValidationType = "AtLeastOneCriteria"? Behind the scenes the expression would then translate into something like:
   SELECT all stored procedures WHERE [sp name] NOT LIKE 'sp_MSins%'
   OR [sp name] NOT LIKE 'sp_MSdel%' AND [sp name] NOT LIKE 'sp_MSupd%'Well, as you see when written this way this filter is as good as not being there at all since every stored procedure, regardless of the name will pass this validation and will be included in the comparison.
xSQL Schema Compare for SQL Server is free for SQL Server Express Edition and you can download it from:  http://www.xsql.com/download/package.aspx?packageid=10