export data into oracle by odbc connection porblem

hi master

sir i create odbc connection with oracle

connection test and work right

but when i export data form sqlserver 2000 to oracle with odbc connection and user scott/tigger that time syetem not export data and give me error

Failed to copy 1

Double-click the error row for a detailed description of the error

When click on error then show blow error

[oracle][ODBC][ora]ORA-01918: user ‘scott’ does not exist

sir please give me idea how i export my data to oracle

thank

aamirmaster

sir i create odbc connection with oracle

connection test and work right

but when i export data form sqlserver 2000 to oracle with odbc connection and user scott/tigger that time syetem not export data and give me error

Failed to copy 1

Double-click the error row for a detailed description of the error

When click on error then show blow error

[oracle][ODBC][ora]ORA-01918: user ‘scott’ does not exist

sir please give me idea how i export my data to oracle

thank

aamir|||Make sure that you use a valid user, Scott/tiger is in Oracle just a sample user which should be deactivated for security reasons. Create a new user assign him the appropiate permissions in oracle and use this user to export data to the Oracle system. Make sure that you don′t hit a quota during the import of the data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
--sql

exporing a table from one dattabse to another

I have a database which is on a network and I want to transfer a table form this databse onto my local machine. Is this possible as in Access they have an export function but I can't find such a thing on sql server expresss.

I have tried copying and pasting the data across but there are over 200,000 rows so I imagine it will take me for ever.

As anyone any suggestions?

Cheers

hi,

SSMSE does not provide SSIS (SQL Server Integration Service) features to allow a task like that, but you can perform the very same activity via standard Transact-SQL code...

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.originalTB (

Id int NOT NULL PRIMARY KEY,

data varchar(10) NULL

);

GO
PRINT 'populate it with some data';

DECLARE @.i int, @.data varchar(10);

SET @.i = 1;

WHILE @.i < 11 BEGIN

IF @.i % 5 = 0

SET @.data = NULL;

ELSE

SET @.data = RIGHT(REPLICATE('0', 9) +CONVERT(varchar, @.i), 10);

INSERT INTO dbo.originalTB VALUES ( @.i, @.data);

SET @.i = @.i + 1

END;

GO

--SELECT * FROM dbo.originalTB;

GO

PRINT 'Create the destination table on the fly using SELECT INTO statement';
PRINT 'see http://msdn2.microsoft.com/en-us/library/ms189499.aspx as well';

PRINT 'you have to later add all required constraints (check, default, primary key)';

PRINT 'on the destination table as the SELECT INTO does not perform it..'
PRINT 'see SSMSE or further tools like my amScript, available for free.';

SELECT *

INTO dbo.destinationTB

FROM dbo.originalTB

ORDER BY Id;

SELECT * FROM dbo.destinationTB;

GO

DROP TABLE dbo.destinationTB;

GO

PRINT 'Explicitely create the destination table, again, see the "Scripting features"';

PRINT 'of SSMSE or further tools like my amScript, available for free.';

PRINT 'and populate it via a "standard" INSERT SELECT';

CREATE TABLE dbo.destinationTB (

Id int NOT NULL PRIMARY KEY,

data varchar(10) NULL

);

INSERT INTO dbo.destinationTB

SELECT Id, data

FROM dbo.originalTB

ORDER BY Id;

SELECT * FROM dbo.destinationTB;

GO

DROP TABLE dbo.originalTB, dbo.destinationTB;

--<-

Create the destination table on the fly using SELECT INTO statement

you have to later add all required constraints (check, default, primary key)

on the destination table as the SELECT INTO does not perform it..

Id data

-- -

1 0000000001

2 0000000002

3 0000000003

4 0000000004

5 NULL

6 0000000006

7 0000000007

8 0000000008

9 0000000009

10 NULL

Explicitely create the destination table, see the "Scripting feature"

of SSMSE or further tools like my amScript, available for free at

http://www.asql.biz/en/Download2005.aspx

and populate it via a "standard" INSERT SELECT

Id data

-- -

1 0000000001

2 0000000002

3 0000000003

4 0000000004

5 NULL

6 0000000006

7 0000000007

8 0000000008

9 0000000009

10 NULL

regards

Expects parameter which was not supplied....need help....

I wanted to add new record into two different tables. By using one web form...
Not so sure I coded it correctly. And I got this error trying to insert new record

" Procedure 'sp_insert' expects parameter '@.mid', which was not supplied. "

Isn't it i have include @.mid in my code ...??

<STORED PROCEDURES>
sp_insert

(
@.mid char(10),
@.status char(10),
@.name char(20),
@.dj char(20),
@.rank char(20),
@.nric char(14),
@.dob char(20),
@.age char(10),
@.add char(80),
@.school char(50),
@.hp char(10),
@.mp char(10),
@.email char(30),
@.pgname char(20),
@.relationship char(20),
@.contact char(10)

)
AS
insert into Table1(mid,status,name,date_joined,rank,nric,dob,age,address,school,house_phone,Mobile_phone,email,[P/G_name],[P/G_relationship],[P/G_contacts])
values (@.mid,@.status,@.name,@.dj,@.rank,@.nric,@.dob,@.age,@.add,@.school,@.hp,@.mp,@.email,@.pgname,@.relationship,@.contact)

insert into Table2 (name) values (@.name)

RETURN

<WEB FORM>
mycommand = New SqlCommand("sp_insert", mycon)
mycommand.CommandType = CommandType.StoredProcedure

Dim midpar As New SqlParameter("@.mid", SqlDbType.Char, 10)
midpar.Direction = ParameterDirection.Input
midpar.Value = Tb1.Text

Dim statuspar As New SqlParameter("@.status", SqlDbType.Char, 10)
statuspar.Direction = ParameterDirection.Input
statuspar.Value = ddl1.SelectedItem.Text

---
---
---Your webform code is wrong, needs to be something like this:

Dim sqlPar As SqlParameter= myCommand.Parameter.Add("@.mid", SqlDbType.Char, 10)
sqlPar.Direction = ParameterDirection.Input
sqlPar.Value = Tb1.Text
sqlPar = myCommand.Parameter.Add("@.status", SqlDbType.Char, 10)
sqlPar.Direction = ParameterDirection.Input
sqlPar.Value = ddl1.SelectedItem.Text

etc.
etc.
etc.