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.

Click menu element in Selenium is not always reliable

Have just gone through an issue that similar as this one reported on stackoverflow http://stackoverflow.com/questions/21028145/selenium-doesnt-click-menu-element-even-though-its-there and it is about click a menu element in Selenium for automating the web application testing. The element is found as per debug shows, however, the click action was not performed on the actual element but the menu option above. Googled through many sites and found this issue seems related to IE web driver and particularly to the menu that structured to show sub menu by clicking the parent menu option (same in my case).

It is not a timing issue as the click action works fine in other browsers and there was no exception on cannot find the element. The click action was not correctly performed at first click but it seems working if to be clicked more than twice and usually, the third time works correctly. However, as for building the automation testing, this solution is not good. Unfortunately, IE is our major browser to test the application against and cannot move away. So at the end, I found a solution is to use Actions class that to manually move the cursor several pixels down in order to perform the click action correctly at the first attempt. The code would look like below:

actions.moveToElement(menuElement, 0, 20).click().perform();

In my case, I only had problem with one menu option. So for a quick solution, I hard-coded the name of the menu option to perform the above special click action. It worked for me although it is not perfect. I will continue looking for better solution to cope with issue or hope the next version of IE web driver would fix this.

Lesson learnt on nvarchar v.s SqlString

I had gone through an issue that related to nvarchar(max) data type. During resolving the issue, I had learnt below:

1. The maximum length we can specify in an nvarchar type is nvarchar(4000) whereas the maximum for varchar is varchar(8000)
2. As for nvarchar(max) the maximum data it can hold is 2GB chars.
3. In C# for managed stored procedure, if to specify nvarchar(max), we need to declare the SqlString as [SqlFacet(MaxSize = -1)]SqlString. If only use SqlString, it will automatically default to nvarchar(4000) instead.

It was tricky to figure this out but the resolution on the issue has benefited me to learn more.

How to rename a database

I had to rename a database recently and googled the result. There are many usual resources and with ready-to-use examples. I will record them here for my own references and at mean time, I will add more details on what the commands do.

With renaming database, we need to set the database to single user mode first. Once the name is changed, we will set the multi-user mode back to the database.

This script does just that for SQL Server 2000:

ALTER DATABASE orig_db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_renamedb 'orig_db_name', 'new_db_name'
ALTER DATABASE new_db_name SET MULTI_USER

SQL Server 2005+ should use this slightly different version:

ALTER DATABASE orig_db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE orig_db_name MODIFY NAME = new_db_name
ALTER DATABASE new_db_name SET MULTI_USER

First of all, I would like talk about the single user mode. If this mode is on, this means only one user can connect to the SQL server at one time. It will enable the user to change any server configuration or recover a damaged master database or other system database. Single user mode can also be set via the main plan configured on the SQL instance with Check Database Integrity task in the plan along with the “attempt to repair minor problems” option enabled. This will put the database in single user mode before executing CHECKDB.

However, sometimes, it is also can be a pain as if the connection is overtaken by another user, the actual user will not be able to logon and we need to seek other solution to break in e.g., kill the other process first, etc.

Meanwhile, the single user mode can also be stuck and you may not able to set the multi-user mode back on. See this post for a good example http://blogs.msdn.com/b/dfurman/archive/2012/01/20/getting-out-of-single-user-mode.aspx.

The next is the WITH ROLLBACK IMMEDIATE clause. It is a clause that we do not use usually but it is quite helpful. This simply means that if you would like to roll back immediately or not. There are another two options here.

Option 1: WITH ROLLBACK AFTER integer [SECONDS] – this means the transaction will still roll back if it encounters problem but within the seconds specified.

Option 2: WITH NO_WAIT – it simply means the transaction will not wait and if it encounters problem it will fail immediate without wait for roll back or commit.