Mirth 1.5 - Create mirthdb on SQL Server 2000?

[nshaik]

How do I create mirthdb on SQL Server 2000? I was looking for a .sql file with scripts to create the database, tables, index etc.

I do see derby-database.sql and postgres-database.sql but do not find one for SQL Server 2000.

I can take a look at postgres-database.sql and write equivalent scripts for SQL Server 2000 but would like to use if Mirth 1.5 provides one.

Thanks in advance!

[asmith]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

The file for MS SQL Server 2005 is called "sqlserver2005-database.sql", and is found in the root directory of your Mirth installation. I believe there is a similar file for earlier versions of MS SQL Server.

[nshaik]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

Thank you very much for the reply.

I did a windows search under Mirth directory and it only shows the two files, derby-database.sql and postgres-database.sql.

Looks like the Windows installation for 1.5 (mirth-1.5.0.2272-setup.exe) did not include the additional .sql files. I downloaded Linux/Unix/Solaris
"mirth-1.5.0.2272.tar.tar" and this does have .sql files for MySQL, Postgres, Oracle, and SQL Server databases.

The "sqlserver-database.sql" extracted from the tar archive throws script errors. I should be able to fix those and post an update.

Thanks again for the reply.

[chrisl]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

Thanks for catching this, the installer will be updated shortly.

[nshaik]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

Updated script file for SQL Server 2000 and SQL Server 2005 atttached.
Following were fixed in the script (did a find-and-replace).

SYS.DATABASES changed to SYSDATABASES
sys.objects chagned to sysobjects
"WHERE OBJECT_ID = " changed to "WHERE ID = "

Ran the CREATE DATABASE part first and then executed the rest of the script.
Mirth1.zip (3665 bytes)

[brendanh]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

Interesting, can you explain why OBJECT_ID does not work? I ran this script on my SQLServer2005 instance, and it works fine. Is OBJECT_ID something that is unique to SQLServer2005?

[brendanh]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

I also get an error on your script because it needs to be SYS.DATABASES not SYSDATABASES for SQL server 2005.

Post edited by: brendanh, at: 05/29/2007 11:50

[nshaik]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

1. OBJECT_ID is a metadata function that returns the database object identification number of a schema-scoped object. This functions exists in both SQL Server 2000 and 2005.

If I understand it right, SQL Server 2005 has System views on system tables with a column name OBJECT_ID. This worked when used in WHERE clause like WHERE OBJECT_ID = OBJECT_ID(N'ALERT'). Whereas in SQL Server 2000 the system table had column called id and the WHERE clause had to be WHERE ID = OBJECT_ID(N'ALERT')

More Info:
Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
http://msdn2.microsoft.com/en-us/library/ms187997.aspx

2. I extracted the sqlserver2005-database.sql (from the UNIX tar file) and tried again in Microsoft Management Studio.

If I hit "Execute" it works fine. But if I hit "Parse" (usually done to check for errors before executing) I get the following error.

"Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'MIRTHDB'. No entry found with that name. Make sure that the name is entered correctly."

This should be expected as MIRTHDB has not yet been created and the script is doing "USE MIRTHDB;"

I think what probably may have happened earlier was I must have hit "Parse" after loading the 2005 file and got this error and assumed it required the same fix done for SQL Server 2000.

Summary:

1. The original sqlserver2005-database.sql is good. If this file is loaded in Microsoft Management Studio, user should just hit "Execute" and ignore the error when hit "Parse".

2. The sqlserver-database.sql does require the "Find-and-replace" changes mentioned earlier.

[brendanh]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

Yes, I like your OBJECT_ID change since it is more backwards compatible. I changed it back to SYS.DATABASES for the SQL 2005 script. Thank you for your patch!!

[chrisl]

Re:Mirth 1.5 - Create mirthdb on SQL Server 2000?

The missing scripts from the Windows installer are available here: http://www.mirthproject.org/index.ph...d=24&Itemid=43