Hello All,
I am currently working on a migration project from Oracle to SQL
Server 2000 database.I have some academic background on database
concepts,SQL and Oracle.
The migration is not a major part.It has few sql scripts for
creation of database/tables and loading of data.So we didn't thought
of using DTS component of SQL Server 2k.
I am reading through some guides on porting as well some books(SQL
Server Books Online) on SQL Server 2000 and Oracle.
I have some queries which these guides doesn't seem to address.
These may seem basic to experts.May someone clarify my doubts and
correct me if I am wrong in my understanding.I would be really
thankful if someone could help me.
Below are the queries:
- To run a sql script in Oracle one uses _at_<sql_script_file_name>
I think the equivalent utility in SQL server 2000 is isql/osql
utility.But isql/osql expects me to provide the 'dbname'as
well 'user' and 'password' along with the script file name.
Can't I provide them inside the script file?If yes,may I know
the ways to do it?
- The script in Oracle contains the lines below
Stmt:1)CONNECT system/manager;
Query:2.1
I think this statement is used to connect to the database.
Do we need to provide any such statement in case of SQL
Server 2000.I found that CONNECT TO exists.But I am not
sure whether I can use the same.
Stmt:2)DROP USER $DB_USER_NAME$ CASCADE;
Query:2.2
The DROP USER takes the value from the config variable
DB_USER_NAME.I found that in SQL Server similalry a system
procedure called sp_dropuser exists.Does it serves the same
purpose?
Stmt:3) DROP TABLESPACE $TABLE_SPACE$ INCLUDING CONTENTS;
Query:2.3
If I am not wrong,in SQL Server this one is similar
DATABASE.
Stmt:4) CREATE TABLESPACE $TABLE_SPACE$
DATAFILE '$ORACLE_HOME$/disk1/oradata/app/$TABLE_SPACE$01.dbf'
SIZE 100M REUSE
AUTOEXTEND ON
NEXT 50M MAXSIZE 500M;
Query:2.4
I use CREATE DATABASE command in SQL Server similar to
the one above with options provided for CREATE DATABASE command.
Stmt:5)CREATE USER $DB_USER_NAME$ IDENTIFIED BY $DB_USER_PASSWORD$
DEFAULT TABLESPACE $TABLE_SPACE$
TEMPORARY TABLESPACE temp;
Query:2.5
AFAIK,SQL Server provides the procedure sp_adduser which
may serve the purpose but it doesn't provide options to mention
the default tablespace.Are there any equivalent statement in SQL
Server?
Stmts:6)GRANT CREATE SESSION TO $DB_USER_NAME$;
7)GRANT CREATE SEQUENCE TO $DB_USER_NAME$;
8)GRANT CREATE TRIGGER TO $DB_USER_NAME$;
9)GRANT RESOURCE TO $DB_USER_NAME$;
10)GRANT CONNECT TO $DB_USER_NAME$;
Query:
In SQL Server I know sp_grantdbaccess and sp_grantlogin
procedures are there.But I don't think they serve a similar
purpose in this case?Are there any equivalent ways in SQL
Server 2000 to do this?
3. In place of the statements below(to CREATE SEQUENCE/CREATE TRIGGER
in Oracle).Here sysUserId is the PRIMARY KEY in SysUser Table.
CREATE SEQUENCE seq_SysUser START WITH 4;
CREATE TRIGGER trg_sysUserId BEFORE INSERT ON SysUser FOR EACH ROW
BEGIN
IF (:new.sysUserId IS NULL) THEN
SELECT seq_SysUser.nextVal INTO :new.sysUserId FROM DUAL;
END IF;
END;
I used IDENTITY COLUMNS in case of SQL Server.Is that correct
way of doing it?
4. Finally,tables are locked in ROW SHARED MODE in Oracle as below
e.g LOCK TABLE Users IN ROW SHARE MODE;
I studied that there is no explicit LOCK TABLE command in case
of SQL Server but it seems one can use table hints to lock a
table.I also studied that lock in case of Oracle is for a
transaction whereas in case of SQL Server it is for a statement.
But if the lock needs to be extended for a transaction one has to
use the SET TRANSACTION ISOLATION LEVEL statement with REPEATABLE
READ/SERIALIZABLE option.
Is this the correct way to lock a table similar to Oracle?
