Re: Few queries related to migration from Oracle to SQL Server 2000??
Date: Tue, 4 Jun 2002 23:38:03 -0400
Message-ID: <O#LT#IEDCHA.2004_at_tkmsftngp02>
I'll try to help! ;)
2.1 If the user id has the db as it's default, then you need nothing further. Otherwise use "USE dbname". ID/PWD are specified when osql is fired.
2.2. sort of... If the user owns objects in the database, these are dropped also. Its referred to as a schema. In sql server, a user cannot be dropped if they own objects. Objects must be reassigned to other users. sp_dropuser is deprecated...use sp_revokedbaccess .
2.3. yes
2.4.yep
2.5. sp_addlogin will let you create id/pwd/ default db. Tempdb is the temporary space for all users!
use sp_grantdbaccess.
2.6. SQL Server supports grants.
use sp_grantdbaccess to allow a user access to the current db (what ever the dbo user is logged into).
I am not familiar with create_session or create_resource permissions in Oracle. sp_grantdbaccess is similar to grant connect. There are no sequences...Not sure about triggers, but I think only dbo or object owners can create them.
3. yes!
4. the TABLOCK hint might be what your after! Use with caution!
I hope I have helped a little. Its been a few years since I had to mess with Oracle DBA stuff...
Andrew
-- Andrew Swanson CTI Development "More than just stinkin screen pops!" Wachovia "Karthik Dathathri" <karthikd76_at_lycos.com> wrote in message news:e634d37e.0206040720.627976e8_at_posting.google.com...Received on Wed Jun 05 2002 - 05:38:03 CEST
> 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:
>
> 1. 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?
>
> 2. 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?
>
>
> I think the questions above may peek into any newbie's mind who
> works on a database migration.
> Hope I get some help here.
>
> TIA,
> Karthik