Re: Few queries related to migration from Oracle to SQL Server 2000??
Date: 5 Jun 2002 07:51:04 -0700
Message-ID: <e634d37e.0206050651.5a9d3352_at_posting.google.com>
Hi Andrew/James,
Thanks for your reply.I need to explore the SQL Agent way which
James has mentioned in his posting to run SQL jobs.I understand the
concept of
Filegroups in SQL Server(which are similar to tablespaces in Oracle).
I tried writing a SQL script to create database in SQL Server.I used 'osql' utility to run it.
I login to SQL Server 2000 as 'sa' with password also as 'sa'.So I ran the 'osql' utility as follows
d:\>osql -U sa -P sa -i d:\karthikd\createTableSQLServer.sql -o d:\karthikd\output.txt
The contents of the script I wrote are as follows:
CREATE DATABASE myDB ON
( NAME = 'myDB_Data',
FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\myDB.mdf',
SIZE = 3,
FILEGROWTH = 10% )
LOG ON
( NAME = 'myDB_log',
FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\myDB.ldf',
SIZE = 1,
FILEGROWTH = 10% )
exec sp_addlogin _at_loginame = 'karthik',_at_passwd = 'kart',_at_defdb =
'myDB'
exec sp_grantdbaccess _at_loginame = 'karthik'
USE myDB
CREATE TABLE SysUser
(
sysUserId numeric(28,0) IDENTITY(4,1) PRIMARY KEY, emailAuth char(100), userType smallint DEFAULT 0, authSourceId numeric(28,0) DEFAULT 1, authGroupId numeric(28,0) DEFAULT 1, authFieldId numeric(28,0) DEFAULT 1, authFiledValue char(100), typeMap image, privateMap image,
)
COMMIT
EXIT When I run the script it complained that there is no BEGIN for a COMMIT.So I added a BEGIN statement before the CREATE DATABASE Statement.Then I run the script again.The output file didn't showed any errors.But when I checked the Enterprise Manager,I couldn't find the database 'myDB' in the list of databases.
But when I ran the statement CREATE DATABASE alone in Query Analyser it ran successfully and created the database.
I couldn't understand what went wrong when I run the script using osql utility.
Another thing I would like to understand is how to add the FILEGROUP in the CREATE DATABASE statement.I am planning to use only one data file(i.e primary) and a log file(in case of recovery).But the primary data file seems to be associated with the primary file group(default file group).When I used the FILEGROUP clause as below,the SQL Server complained: incorrect usage of FILEGROUP etc...
CREATE DATABASE myDB ON
FILEGROUP myDB_file_group
( NAME = 'myDB_Data',
FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\myDB.mdf',
SIZE = 3,
FILEGROWTH = 10% )
LOG ON
( NAME = 'myDB_log',
FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\myDB.ldf',
SIZE = 1,
FILEGROWTH = 10% )
May I know how I can mention a filegroup for the database with one
primary data file.I don't want to split the data file into a secondary
one.
Another thing is about COLLATE clause.If I would like to support
Unicode data what kind of collation I need to mention.
Next is about the user to access the database.James has mentioned
that users have a default database in SQL Server and if they have
the ability to create objects that's where they go by default.I have
added sp_addlogin and sp_grantdbaccess to an user in my script
above.Is this a correct way to add a user(similar to CREATE USER...
in Oracle)and grant him permission to create objects(tables/indexes
etc..) in the database.If I am wrong kindly guide me how I can
add permissions for the added user to create tables/indexes etc...
Thanks for your help and I think I may trouble you:-) for some more time.But it's a very good learning opportunity for me to wet my hands on the databases.
Regards,
Karthik
"Andrew Swanson" <akswanson_at_earthlink.net> wrote in message news:<O#LT#IEDCHA.2004_at_tkmsftngp02>...
> I'll try to help! ;)
>
> 1. I dont use osql much but...you do need to supply an id/pwd/server when
> you open osql. If you do not, it assumes nt authentication, local server, I
> think. you can switch databases using the USE dbxxxx command. Look at the
> command line parameters for osql for specifics.
>
> 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...
> > 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
Received on Wed Jun 05 2002 - 16:51:04 CEST