The Ji Village News

季庄新闻–Haidong Ji’s Blog

In Java, the char data type is 16-bit, so it can handle Unicode characters.

In a mixed environment where Windows and *nix (Unix, Linux, OpenBSD, Solaris, etc.) co-exist, when an app server needs to talk to Sql Server, it is pretty common that the app server is written in Java, and the connection is done via JDBC. The prevalent JDBC drivers seem to come from either BEA’s Weblogic or Microsoft’s own JDBC driver.

In both cases, by default, the driver will send string data as Unicode to Sql Server. Depending on your environment and data volume, this can have serious performance consequences. This is especially true in the North American market, because a lot of data in North America in Sql Server is stored as varchar or char.

The problem with data type inconsistency is two-fold: firstly the engine has to do a cast/convert operation, which is unnecessary in this case; secondly, probably more importantly, it will throw off query optimizer, in that a potential index seek becomes index scan, because data type have to be converted for comparison.

To verify this problem, you can use Profiler to capture some results, and you will find snippet like below in the TextData column:

exec sp_prepexec @P1 output, N’@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000)

Note that the parameter is nvarchar, the Unicode data type. The difference is subtle, but important.

To change this, you will need to reconfigure JDBC so all code will be sent in as ASCII. In both drivers mentioned above, there is a setting called SendStringParametersAsUnicode. You will need to set it to false.

When that is done, to confirm the change is in effect, run Profile again, and you will see things similar below in the TextData column:

exec sp_prepexec @P1 output, N’@P0 varchar(8000),@P1 varchar(8000),@P2 varchar(8000)

Depending on the volume of your operation, you could see significant performance boost because of this simple configuration change.

Of course, if your character data in Sql Server is stored as Unicode characters, then this should not be a concern.

In addition, I am interested in your experience setting up JDBC connection pool to Sql Server. You could sharing your experience setting that up by commenting below. Thanks.

The Declaration of Independence is a truly inspirational document. It signified the birth of a young, confident, and optimistic nation at the time. My son and I watched and participated Independence Day parade organized by the local government and other organizations on July 4th.

On a personal level, I feel deeply, deeply grateful to America and the benefits and opportunities I found here. I am also very grateful to a lot of people who helped me over the years. I wouldn’t forget my brother who helped me to get here and provided financial support during my first year in 1995.

I am extremely fortunate, and, for the most part, enjoy the life I have here. Since I am a permanent resident in this country, I want to participate in our community life and make wherever I am at a better place to live, although I wouldn’t rule out the possibility of living and working in other parts of the world. In fact, that is one of my dreams.

America, at its best, is fun-loving, free, optimistic, innovative, confident, and entrepreneurial. Having said that, I am absolutely disgusted by the current Bush administration. Its manipulation of public fear is appalling. Its blatant attack on the Constitution and civil liberties is scary. Its shallowness, smartassness, arrogance, self-centeredness, non-cooperativeness, self-righteousness, hypocrisy is on display in many parts of the world. I couldn’t believe a lot of things this administration did could be happening in a free country. It feels surreal at times.

But I digress. I struggled quite a bit with whether or not I should post this, for fearing of offending people, but this is my medium, and I shall treat it that way.

Here are some pictures from the parade.

Security model for Sql Server Agent 2005 has been revamped. DBAs will have better granular control over job creation, execution, and browsing. There are newly created roles in msdb for those purposes: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. For more information, go to this url in Book On Line:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

A lot of times, you may want to grant existing users permission to create and schedule their own jobs. Below is the script to accomplish that. Replace MyUser user and login with your own and it will work. In my experience, Management Studio for Sql Server Express cannot see Sql Server Agent in its UI, therefore will not be able to create jobs, even if the login has been granted that right. So you do need the Management Studio for the full version of Sql Server.

USE [msdb]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser]
GO
USE [msdb]
GO
EXEC sp_addrolemember ‘SQLAgentUserRole’, ‘MyUser’
GO

In both Oracle and Sql Server, when creating a foreign key constraint on a child table, the default option is NO ACTION, meaning that this constraint will prevent the deletion or update of a parent key if there is a row in the child table that references the key.

One popular option is CASCADE, meaning when you delete a row in the parent table, the child table’s records that reference the key value of that row will be deleted automatically also.

However, if you try to change a foreign key constraint from CASCADE to NO ACTION in Oracle, you will have to drop the constraint first and recreate it. For example, the code below will not work:

ALTER TABLE Employee MODIFY (
CONSTRAINT Department_ID_FK
FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID)
ON DELETE NO ACTION)

You will get error ORA-00905: missing keyword if you run the script above.

Instead, you need to drop the constraint first using something like below:

ALTER TABLE Employee DROP CONSTRAINT department_id_fk;

Then, add the NO ACTION using the script below:

ALTER TABLE Employee ADD (constraint Department_ID_FK
FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID));

Now, if you try to delete a row in the parent table that is referenced in the child, you will get the error below:
ORA-02292: integrity constraint (Schema.ConstraintName) violated - child record found.

It took me hours of banging my head on the wall before somebody enlightened me that NO ACTION cannot be used in the script to define a NO ACTION constraint in Oracle.

If you are inclined, you can use the scripts below to do some testing of your own. One more way to verify is to browse through Schema view in Toad to see what kind of constraint it is.

create table Department (DepartmentID NUMBER(2) primary key,
DepartmentName varchar(20));

insert into department values (1, ‘Legal’);
insert into department values (2, ‘IT’);
commit;

create table Employee (EmployeeID number(4) primary key,
DepartmentID number(2),
FName varchar2(20),
LName varchar2(20));

alter table Employee add (constraint Department_ID_FK
foreign key (DepartmentID)
references Department (DepartmentID)
on delete cascade);

insert into employee values (1, 1, ‘Bob’, ‘Smith’);
insert into employee values (2, 2, ‘Susan’, ‘Smith’);
commit;

Index Tuning Wizard morphed into Database Engine Tuning Advisor (DTA) in Sql Server 2005. The idea is still the same. You feed it with some profiling results, and it will make some recommendations.

Sql Server Profiler 2005 still has the same look and feel of Profiler 2000. It does have some enhancement when picking event columns to capture. Please note that you can open a Sql Server 2000 trace file with Profiler 2005, but not the other way around.

I recently used Sql Server 2005 Profiler against a Sql Server 2000 database. I used the Tuning template and saved the results to files, allowing file rollover and setting maximum file size to 15 MB.

I then fed the results into DTA. DTA gave me some recommendations on indexes and statistics. You do not need to apply those recommendations right away. You may want to go to Actions menu and choose Save Recommendations… This will give you a nice script that you can review.

One advice: you probably want to run DTA on a more powerful machine, if you can. I ran it first on my workstation, with Intel Centeno 2.1 GHz and 2 GB of memory. I fed DTA with one 15 MB of trace file. After 10 minutes or so, only 1% of the file was analyzed. I then transferred the file to a 64-bit Opteron server with a lot of memory and did the analysis there. The whole thing took less than 1 minute. Really impressive.

Credit card companies are sending junk credit card application offerings now to my first grader. Just unbelievable.

I used this site, https://www.optoutprescreen.com/opt_form.cgi, a few years ago to stop unwanted mail for myself. It did get rid of a lot of my junk mails, although not all of them. Overall, it made a big difference and I am pretty happy. I just opt out my son via that site. It is very simple to do.

The DoNotCall list also made a difference for us. Here is the url to register.

This site lists other ways to simplify your life. Except the two things mentioned above, I didn’t use any other methods listed there. What are your experience and suggestions?

I attended an introductory Perl course recently. I’ve always wanted to learn the language. It is a very powerful scripting tool with excellent regular expression support.

The training delivered by Daina Pettit from LearningTree was excellent. One nugget I got out of the class, that I think is worth half of the class cost, is this:

On command line, be it *nix or ActiveState on Windows, type:

perl -de 0

to open Perl debugger interactively. Perl will try to run any statement you type. It is a great way to learn.

-d launches the debugger. -e takes one line of program. When given 0, it will take whatever we type through the prompt.

Fellow SqlServerCentral author K. Brian Kelley wrote an excellent article on using Perl and Windows::ODBC module to manage Sql Server here. Brian is apparently a big fan of Perl.

Shanghai Nonstop:

Duck at Shedd Aquarium, behind the glass wall:

Benjamin and the duck:

Service Pack 1 for Sql Server 2005 was released in April. I have installed it on quite a few systems: 32-bit, 64-bit, and 64-bit cluster. SP1 is cluster-aware, meaning that if you want to upgrade a cluster, the installation package can detect that and will apply the changes to all nodes.

Of my installs, all were successful except one. In that instance, I was partially successful: Integration Service was not upgraded successfully, the rest of the system such as Sql Server Service and Native Client were all successful. There appears to be some SSIS related issues with SP1. I will talk about that in a future post.

Anyway, during the upgrade process, there are 2 messages you need to be aware of. They are all related to the fact that the upgrade touches many components and services.

The first message you will see is this:

—————————
Pending Reboot Files Found
—————————
A previous program installation caused unrelated pending file operations. The patch installation will succeed even if your computer is not rebooted.

Do you want to proceed?
—————————
Yes No
—————————
Click Yes to this one.

The second message looks like this:
—————————
Locked Files Found
—————————
The following files are currently locked.

In order to prevent a necessary reboot at the end of the patch install process, close down all other applications before proceeding.

Write locked file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msftesql.exe (msftesql.exe)
Write locked file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msftepxy.dll (msftesql.exe)
Write locked file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msfte.dll (msftesql.exe)

—————————
Cancel Try Again Continue
—————————
For this one, click Continue.

One way to convert Access database to Sql Server database is through Access’ built-in Upsizing Wizard, accessible through Tools -> Database Utilities -> Upsizing Wizard. It works both with Sql Server 2000 and Sql Server 2005.

When upsizing, you are provided with basically 3 choices: converting Access database to a ADP project; converting all tables via Access linked tables, or simply creating a Sql Server database and do nothing.

Choice 1 is probably most desirable but rarely works if you have a lot of forms in your Access application, especially if form names have spaces in them. You may have to recreate all or most of the forms in order to work. This is desirable because when it works, most of the processing will be done on Sql Server, which should be more efficient than Access’ Jet Engine.

Choice 2 is probably the most practical approach initially, as the wizard will create linked tables to Sql Server tables, rename all original Access tables with _local attached to their names. All forms should work as is.

You will also face a choice of using DRI and triggers. If you do not know what DRI is or are not sure about it, use DRI. DRI stands for Declarative Referential Integrity.

When the wizard finishes, you will be presented with a report. Pay special attention to table(s) skipped. Most likely that’s due to data cast error, caused by date data type differences between Access and Sql Server. Access Date field take values from 1 Jan 100 to 31 Dec 9999, whereas SQL Server datetime field accepts dates from 1 Jan 1753 to 31 Dec 9999.

To find out which column in Access caused the problem, you can try running Import data wizard within Sql Server Management Studio on those skipped tables. Below is a typical error message when data import from Access to Sql Server fails.

- Copying to [MySqlServerDb].[dbo].[MyTable] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column “MyProblemColumn” (144) on input “Destination Input” (101). The column status returned was: “Conversion failed because the data value overflowed the specified type.”.
(SQL Server Import and Export Wizard)

From the message above, you can see that MyProblemColumn has the data that caused the cast error. Open up Access, sort value on MyProblemColumn, clean up the invalid data, and you can try again.

I recommend that you make a production copy of your Access database, and run it against Sql Server in test or development environment. If it fails, just blow away the database created in Sql Server, fix the problem in Access, and try again, until you get all kinks worked out.