SQL server database collation explained

When we installing a new SQL server instance, we have to choose a database collation. This will be the default collation for all databases that going to be created on this instance later on.

However, this default database collation can be overridden in following ways.

1. When creating a new database, we can specify a different collation for the database.

For example

CREATE DATABASE DbWithNewLanguage COLLATE SQL_Latin1_General_CP1_CI_AS

2. When creating a new table, we can specify different collations for individual column.

For example

CREAT TABLE MixLanguageTable (
ID INT IDENTITY,
EnglishName nvarchar(150) COLLATE Latin1_General_CS_AS,
GreekName nvarchar(150) COLLATE Greek_CS_AS_KS,
JapaneseName nvarhcar(150) COLLATE Japanese_90_CI_AS_KS_WS
)

3. When querying the tables, we can specify different collations.

For example

--EnglishName column in the example above is case sensitive. But we can change the collation so when we query the table on this column, we can set it to be case insensitive.

SELECT * FROM dbo.MixLanguageTable WHERE EnglishName = ‘test name’ COLLATE Latin1_General_CI_AS

Things to know about TRY...CATCH in T-SQL

With TRY…CATCH statement in T-SQL, I attempted to use it for a table name resolution the other day. However, I found the statement will not handle following errors, so it will not work if we are using this statement for checking the table name if it exists or not.

1. Compile errors, such as syntax errors, that prevent a batch from running.
2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Meanwhile, when using SET XACT_ABORT ON setting, if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. However, this will not help if we want to resolve the “table name does not exist” error.

Reference:

http://stackoverflow.com/questions/15015051/sql-try-catch-doesnt-catch-an-error-when-attempting-to-access-a-table-that-it

Why the application desktop icons are not refreshed?

I found an issue with the desktop icons lately. The application we are using does not show the right icons after re-installation and icons shown in the previous version but the icon file is in the new version on the server. I googled and found the solution for this problem is to flush the Windows IconCache.db. I am using Windows 8 and will record the solution here as well.

1. Remove the IconCache.db file at C:\Users\Username\AppData\Local\ and restart
2. If not working, you can follow the steps below:

• Kill Explorer.exe process via the Task Manager.
• Open the command prompt window, type each of the following and after every command, hit the Enter button:

          cd /d %userprofile%\AppData\Local
          attrib –h IconCache.db
          del IconCache.db
          start explorer

• Your Windows Icon Cache would have been rebuilt.

Refer to the web page that I found this solution here.

Windows Update for KB3025390 causes (Selenium) automation testing to fail in IE

Our Selenium Java project stopped working in IE just before Christmas and it was very frustrating as we did not change anything in our code for a long while. It works in Chrome, however, IE is our main browser we use for automation testing. We are currently using IE 11 + Windows 8.1 for our testing.

The last time we ran our automation test was several months ago. So I tracked down the issue and found it was very clear that IE stopped understanding in any way how to find an element. As soon as reaching the code for finding an element either by id or name or xpath, etc., the element cannot be found and a NoSuchElement exception was thrown.

It is a special day today as we found the root cause of this problem (thank you my dear team mate). She found the issue is related to a Windows update. By chance, she has been using Selenium-Grid and running the automation test on another VM. She used yesterday and found it worked and then it stopped working today as the VM got updated via Windows update. She tracked down and found the problematic update is related to the Update for Microsoft Windows (KB3025390). 

So after uninstalling this Windows update, our automation test started working again. We then googled and found this update causes some other issues as well. 

Why all icon images are missing in Crystal Reports Web Viewer?

There is an issue happens to Crystal Reports Web Viewer very often. It is about the icons on the interface all disappeared at certain point of time. It does not affect the functionality of the buttons or viewing of the report contents. But it does not look professional for a business application.

We found there are several options can be used to solve this problem:

Option 1: add the site in the Compatibility View list in IE. This solution works when not only the icons are missing but also the report format looks wrong and different than designed.

Option 2: reinstall the Crystal Reports runtime. Usually, this happens when the .NET framework installed after the Crystal Reports runtime was installed. So we need to reinstall the Crystal Reports runtime again to ensure all related contents are in the correct place.

Option 3: an alternate way of Option 2 without reinstallation is to find the C:\inetpub\wwwroot\aspnet_client\system_web\ folder on the web application server. There will be several folders named after different versions of .NET framework e.g., 2_0_50727*, 4_0_30319*, etc.

As usually the highest .NET framework is used by the web application, open the lower version folder e.g., 2_0_50727* and there will be a folder called CrystalReportWebFormViewer4. In this folder, there would be all the images used on the Crystal Reports Web Viewer. You can copy the entire folder to the highest version numbered folder such as 4_0_30319* in our example. By saying so, you will still need to check which .NET framework your web application uses just in case and operate accordingly.

Specific server name used by sub-reports in Crystal Reports that running in a web application

We use .NET application and Crystal Reports plug-in to run Crystal Reports in the .NET web application. Recently, we discovered that one of the reports we built throws SQL column missing error while running in the web application. It is unusual as all our reports are built in the same way.

At the end, we figured out that it was due to the report uses sub-reports and all sub-reports require to be saved with . as the server name in the Crystal Reports file. So that when it runs from the web application, it would then work successfully. In addition, we found this is the only solution and it will fail even when replacing the . with localhost.

This is an unusual solution that we normally do not see. So I record this here for future references.

Crystal Reports do not export page headers in Microsoft Excel (Data Only) format

Recently, I found when using Crystal Report to output to Microsoft Excel (Data Only) format. The page headers are not exported in this format.

I have done some researches on the Internet and found the export of page headers to Microsoft Excel (Data Only) format is not supported.

I figured out that the only workaround to this (in my case) is to put all required page header content in report header. If they are required to be in multiple lines, we just need to create one report header for each line and put the related content in each report header. So the content will be displayed in multiple lines (rows) in the Microsoft Excel (Data Only) format.

Meanwhile, we need to make sure that the following two options are selected in Microsoft Excel (Data Only) Format Options in Crystal Report:

• Export page header and page footer
• Simplify page headers

This solution has fixed our internal reporting issue to Microsoft Excel (Date Only) format. Hope it will also benefit the others.