DTS and Oracle

I want to import a table called emp(user name scott/tiger) from oracle database to MSSQL database

Step 1
login to scott/tiger in oracle

Step 2
Create a table called emp

create table emp (empno int, empname varchar(20))

Step 3
insert into emp (empno, empname)
values(1,'aaa')

insert into emp (empno, empname)
values(2,'bbb')

insert into emp (empno, empname)
values(3,'ccc')

Step 4
Now I want to import this table to mssql using DTS.

I have used Data Transmission task in DTS for source query. I have given "select * from scott.emp"

I am able to get the exact result set. I am able to get the same values in MSSQL.

Step 5
Now I am creating another user called "test" and give access to "emp" table of "scott" user.

Step 6
Now i want to pass on "test" instead of "scott" in the Data Transmission Task.(i.e).
"SELECT * FROM test.emp" instead of "scott.test". I want to pass "test" dynamically.

how to do this.use a linked server to oracle.

create a stored proc with dynamic sql.

pass the table owner as a paramter to the stored procedure.|||Hi
I have given you only a sample. I dont have permissions to create tables or create stored procedures. I have only permissions for select statement.

Thanks

I want to import a table called emp(user name scott/tiger) from oracle database to MSSQL database

Step 1
login to scott/tiger in oracle

Step 2
Create a table called emp

create table emp (empno int, empname varchar(20))

Step 3
insert into emp (empno, empname)
values(1,'aaa')

insert into emp (empno, empname)
values(2,'bbb')

insert into emp (empno, empname)
values(3,'ccc')

Step 4
Now I want to import this table to mssql using DTS.

I have used Data Transmission task in DTS for source query. I have given "select * from scott.emp"

I am able to get the exact result set. I am able to get the same values in MSSQL.

Step 5
Now I am creating another user called "test" and give access to "emp" table of "scott" user.

Step 6
Now i want to pass on "test" instead of "scott" in the Data Transmission Task.(i.e).
"SELECT * FROM test.emp" instead of "scott.test". I want to pass "test" dynamically.

how to do this.|||how are you going to step 2 without create table permissions?|||Hi
I have given you only a sample. I dont have permissions to create tables or create stored procedures. I have only permissions for select statement.

Thanks

I think Thras meant to create the stored procedures on the SQL Server side.

Regards,

hmscott