Database vendors like to bash each other, sponsoring dubious “objective industry studies” to “prove” they are better than others. All of them do it. In my opinion, Oracle is particularly bad in this regard, compared against IBM DB2 or Microsoft Sql Server. Talking about “unbreakable Oracle” and software full of bugs, and in many cases you need to pay the Metalink membership to simply get to know that the issue you are dealing with is a bug. That is not to say Sql Server and DB2 don’t have bugs in their software, though.

Each of them wants you to convert your database to their platform. To that end, they provide some program to help you along. Oracle has a thing called Oracle Migration Workbench. Sql Server has something similar called Sql Server Migration Assistant.

More often than not, marketing people from those companies will tell you how great their migration program is. That it is easy to migrate, you will see performance improvement, etc. Don’t believe them.

A couple of months ago, I worked on a project to migrate a database from Sql Server to Oracle. This database has a lot of stored procedures, user defined functions, and linked server stuff. I talked to Oracle people, and they recommended their own Migration Workbench, with Sql Server plug-in. I downloaded them and started working.

Basically, the Migration Workbench tries to go through database code in T-Sql. For code that uses built-in T-Sql functions, the Migration Workbench creates functions with the same name in Oracle, and try to writes something similar in PL/Sql that makes an attempt to do what T-Sql function does. I didn’t look too deep into this, but I am suspicious at all of them. The reason I didn’t look too deep into them was that the whole effort was derailed by a bigger problem. Let me explain.

In T-Sql, all variable names start with @ symbol. In fact, in many places, variable names are simply column names prefixed with the @ symbol. This can be pretty easy to read. And it actually works very well.

However, PL/Sql variable names don’t follow that convention. So, as Migration Workbench goes through T-Sql stored procedures, it simply strips off the @ symbol from the variable names. This basically renders all the code useless.

This is just very, very dumb. Oracle is a multi-billion dollar company, and you would think they should know this. Instead of stripping off the @ symbol, it could replace it with some kind of prefix. But it does not do that.

Another problem is with the identity field. Once again, one would expect that the Migration Workbench converts it to Oracle sequence, but it doesn’t. It changes that to NUMBER.

Eventually, the project was canceled. The moral of the story: migration from one RDBMS to another is not as easy as it sounds. It is doable. Sometimes it is probably easier to just write everything from scratch.

Sometimes you want to know how long you Sql Server has been running. There are a number of ways to find that out.

You can start from Sql Server error log. Go all the way to the beginning of your currect error log, assuming you have not recycled the error log manually, and look at the time stamp there. That is the time when Sql Server was last started. Note that Sql Server start time may not necessarily be consistent with server start time. There is the lag, of course, since Sql Server has to wait until the operating system is up first, assuming it is set as auto start. More importantly, it is possible to restart Sql Server service without rebooting the system.

To get it programmatically, you can run this script. It checks the creation time of your tempdb, since tempdb gets reinitialized every time Sql Server is started.

-- Sql Server 2000 and Sql Server 2005
select crdate from sysdatabases where name = 'tempdb'
-- Sql Server 2005
select create_date from sys.databases where name = 'tempdb'

To make it more intuitive, you can run the script below, which will tell you how many days and hours Sql Server has been running. Minutes and seconds information will be truncated. If you need that, modify the script to get it yourself.

-- Sql Server 2000 and Sql Server 2005
select 'Sql Server Service has been running for about '
+ cast((datediff(hh, crdate, getdate()))/24 as varchar(3)) + ' days and '
+ cast((datediff(hh, crdate, getdate())) % 24 as varchar(2)) + ' hours'
from sysdatabases where name = 'tempdb'

-- Sql Server 2005
select 'Sql Server Service has been running for about '
+ cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ' days and '
+ cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ' hours'
from sys.databases where name = 'tempdb'

In this post, I mentioned that you can restore a Sql Server backup file to a Sql Server 2005 server.

You cannot do it the other way, though. A backup taken on Sql Server 2005 cannot be restored on a Sql Server 2000 server. If you try, this is the likely message you will get:

Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

I found out recently that a schema owner does not necessarily have right to create objects for its own schema, but can drop and grant permissions for its objects, which is kind of strange.

Try this:

1. Create 2 logins:

USE [master]
GO
CREATE LOGIN [Login1] WITH PASSWORD=N’xxx’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
CREATE LOGIN [Login2] WITH PASSWORD=N’xxx’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [AdventureWorks]
GO
CREATE USER [Login1] FOR LOGIN [Login1]
CREATE USER [Login2] FOR LOGIN [Login2]
GO

2. Grant create schema permissions to both Login1 and Login2.

use adventureworks
go
grant create schema to login1
grant create schema to login2
go

3. Connect to Sql Server as login1, do

create schema Login1Schema

4. With the same connection, do

create table Login1Schema.T1 (C1 int)

and it will fail. It is a little strange. Login1 is indeed the owner of the schema, yet it cannot create objects within the schema it owns.

5. Connect to Sql Server as sa, do

Use AdventureWorks
go

create table Login1Schema.TableCreatedBySa (C1 int)
go

grant create table to Login1
go

6. Connect to Sql server as Login1, do

create table Login1Schema.T1 (C1 int)
go

grant select on Login1Schema.T1 to Login2
grant select on Login1Schema.TableCreatedBySa to Login2
go

7. Connect to Sql server as Login2, do

select * from Login1Schema.T1
select * from Login1Schema.TableCreatedBySa
go

8. In the same session, do

create schema Login2Schema
go

9. Connect to Sql Server as sa, do

use AdventureWorks
go
create table Login2Schema.T1 (C1 int)
go

10. Connect to Sql Server as Login2, do

select * from Login2Schema.t1
go
drop table Login2Schema.T1
go
drop schema Login2Schema
go

So, what did our little test prove?

1. By default, a schema owner cannot create objects in its own schema;
2. By default, a schema owner can grant permissions to other users on objects it owns;
3. By default, a schema owner can drop objects it owns, even if it cannot create them in the first place;
4. By default, a schema owner can drop its own schema, provided it is empty.

To clean up, drop all those tables, users, schemas, and logins.

In Sql Server 2000, C2 auditing is a US government standard that monitors database security. When it is enabled, a trace will be created to collect all Security Audit related events, 21 or so of them. You can find what those events are by browsing through them in Profiler. By default, the trace file will be saved at your default data folder. It is named in the format of audittraceYYYYMMDDHHMMSS.trc. Its file size is 200 mb, and rolls over automatically when that limit is reached.

In Sql Server 2000, C2 is off by default and you cannot enable it using Enterprise Manager. You will have to do it using sp_configure and set the “c2 audit mode” property. It needs a server restart for it to take into effect.

C2 auditing still exists in Sql Server 2005, and you can enable it through Management Studio. Just right click on the server, pick properties, then go to Security tab to enable it. It works the same way as C2 in Sql Server 2000. A service restart is needed in order for it to take into effect. Although you cannot enable C2 for Sql Server 2000 in Enterprise Manager, you can enable it with Sql Server Management Studio.

Starting with Service Pack 2 for Sql Server 2005, in addition to C2, Sql Server 2005 can also use Common Criteria Compliance. You can pick this option by looking at the property page of the server, security tab. You can also do it with script by changing a new parameter using sp_configure. The new parameter is called “common criteria compliance enabled”. Again, a service restart is needed for it to take into effect. Common Criteria is a standard developed by a few countries and adapted by ISO.

Simply making the above change does not make the server Common Criteria compliant. You will also need to run a trace to audit security events, just like C2. The audit script is available here.

Here are a few handy tips I learned lately:

1. When using sp_configure, you don’t need to type the whole parameter string. You just need to type enough of it for Sql Server to be uniquely identify it. For example,

sp_configure ’show ad’

will resolve to

sp_configure ’show advanced’

2. In Query Analyzer or Management Studio, to get help on a keyword, system stored procedure, DDL/DML statements, etc, highlight them, then press Shift-F1;

3. When a process takes a long time to finish, sometimes you want to kill it. Killing a process will roll back all the changes. Keep in mind the roll back can take a long time also. To find out the roll back status, use

kill with statusonly

In Oracle, there are 3 places that I know of that are important for monitoring: the bdump, where background process error is stored; udump, where user trace error is dumped; and cdump, the core dump, where Oracle internal error is dumped. cdump is in the binary format, you can use “strings -a” to look at things inside. All other trace and log files are text files that you can open up and read for yourself.

On Unix/Linux/Solaris based systems, these folders are located under:

$ORACLE_BASE/admin/$ORACLE_SID/bdump, udump, and cdump

To find out your $ORACLE_BASE and $ORACLE_SID, you can do:

echo $ORACLE_BASE and echo $ORACLE_SID

There is also the alert log, usually in the format of alert_$ORACLE_SID.log format, under the bdump folder.

There can be numerous trace and log files in these folders. To troubleshoot and correlate events with some problems, you can do:

ls -ltr

to sort files in ascending order according to date and time, and open up the file that is closest to the time when problem occurred. Those trace and log files may hold telltale signs of what you need to examine further.

For Sql Server, by default, the error log file is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. And that is the only place you need to worry about. If you turn on certain trace, the trace info will also be logged into error log.

Don’t confuse Oracle’s .trc file with Sql Server’s .trc file. Oracle’s trace file is in text format, generated when certain trace flags are turned on. Sql Server’s trace file are in binary format, and can be read using Sql Server Profiler.

I talked about one issue when setting up Sql cluster here. Recently I came across another problem while setting up a Sql Server 2000 cluster on a 2-node Windows 2003 cluster.

The error occurred at the step where you were asked to provide a login that can get into the remote node(s), server(s) where install is not originated from. Below is the error message:

The specified account cannot be validated to have administrator rights on Node2.  An error occurred: (1203)

No network provider accepted the given network path.

This is most likely caused by the fact that File and Printer Sharing for Microsoft Networks is not enabled. You can verify that by trying to open up \\Node2\c$. You will most likely encounter the “No network provider accepted the given network path”.

To enable that feature, go to your network connection, right click on Properties, and check the corresponding box. Note that for clusters, most likely you will have 2 networks: one public and one private. You need to enable this feature on the public network in order to continue. Reboot is not required.

I mentioned here that SELECT INTO in Sql Server is functionally similar to CREATE TABLE AS in Oracle. Oracle also has SELECT INTO, but it is used for assigning query results to a variable.

Here is a PL/SQL code snippet:

declare MyVariable varchar2(20);

Begin

select ColumnName into MyVariable from MyTable where MyID = SomeInteger;
dbms_output.put_line('Hello ' || MyVariable);

End

In the above example, a column value for a particular record is assigned to MyVariable and printed out.

How do you assign select results into T-Sql variables in Sql Server then? Here is a code sample that does the same thing above:

declare @MyVariable varchar(20)

select @MyVariable = ColumnName from MyTable where MyId = SomeInteger

print 'Hello ' + @MyVariable

I am going through Kalen Delaney and Itzik Ben-Gan’s Inside Sql Server 2005 books. I am surprised and happy to learn that you can always represents date/time value in the format of [YY]YYMMDD[ HH:MI[:SS][.MMM]] in Sql Server, with things inside the square bracket being optional. In fact, that is a recommended practice because you will always get intended results regardless of Windows locale settings, SET LANGUAGE, and SET DATEFORMAT options.

This representation is actually consistent with the convention used in China, so it feels pretty natural to me. In China, when people talk about dates, it is almost always written in the order of year, month, date, and goes more granular as needed such as hour, minutes, seconds, etc.

The same can be said about address. For example “100 Main Street, Oak Park, Illinois, USA” is the proper order of writing address. The same address addressed in Chinese would be “USA, Illinois, Oak Park, 100 Main Street”

I wouldn’t be surprised if Japan, Korea, and other Asian countries use the same convention as China on this. I am not sure, though.

In this blog post, I gave out script to set up database mail for Sql Server 2005, if you already have smtp server up and running.

One thing you need to pay attention to is that Sql Server 2005 logs all those mail messages into msdb database, including mail attachments, if any. You probably can see where I am going now. If the logged mail messages and attachments are not archived or removed properly, you could get space issues in msdb, especially if you have large attachment files.

Here is a possible message you will get in Sql Server 2005 event log:

Could not allocate space for object ‘dbo.sysmail_attachments_transfer’.'PK__sysmail_attachme__7AF13DF7′ in database ‘msdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

This message is somewhat misleading. The reason is that if you go look at sysmail_attachments_transfer, this table alone may not be very big. However, sysmail_attachments, a different table in msdb that holds all mail attachments, can be pretty big.

This is a sample script you can use to schedule a job, that can remove all mail messages (including attachments) that are more than 1 month old. It uses a system stored procedure call sysmail_delete_mailitems_sp in msdb:

declare @CutoffDate datetime
set @CutoffDate = DATEADD(month, -1, GetDate())

exec msdb..sysmail_delete_mailitems_sp @sent_before = @CutoffDate

MSDN has a little more complex way of archiving those messages here.

Service Pack 4 for Sql Server 2000 introduced a bug. After it is installed, if Address Windowing Extentions (AWE) support is enabled, a single instance of SQL Server 2000 can only use a maximum of 50 percent of the physical memory that is on the server.

Microsoft has a hot fix for that problem here. I am happy to report that this hot fix is cluster-aware, based on my experience. You can run it on one node of your cluster, and the changes will be propagated to other node(s) on the cluster.

In my experience so far, all service packs and hot fixes, both for Sql Server 2000 and 2005, are cluster-aware.

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

This is a little note for myself, when overhead projector is involved. Your milage may vary.

Query Analyzer, options window:

General tab: change Query File Directory to c:\MyPreferredFolder
Fonts tab: Editor -> Arial Black, size 16
Fonts tab: Results Text -> Courier New 18

Windows display appearance tab:

Windows and buttons: Windows Classic Style
Color Schema: Windows Standard
Font Size: Extra Large

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

delete from t1 where c1 = 1;

you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.

Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:

delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount

If the statement returns “one row affected”, then the person would know John Doe’s salary. This person can then issue

rollback

If “no row affected” is returned, the person can continue until s/he can find it out.

I did a quick search on ANSI SQL 92 standard, but didn’t find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.