Help on Oracle RDB Migration to SQL Server.

[Chandra Mohan]

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra Mohan

[Hal Berenson]

On Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.
There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.
3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Use Check constraints.

Hal