Fastest Way to Transfer Data from SQL Server 7.0 to Oracle 8 Database

Fastest Way to Transfer Data from SQL Server 7.0 to Oracle 8 Database
Trying to Do..

Transfer Data from a SQL 7 Database on NT Server via DTS job to Oracle 8iDatabase Table on another NT Server.

This Oracle 8i DB Table has no indexes.

Currently i can transfer about 8000 rows in 1 minute, i am seeking to lower this time by as much as possible

Ive also tried using ole db in dts job also, but with no significant increase in time,

Again any advice/pointers are most welcome.BCP OUT of SQL Server and then use Oracles' IMPort tool.

I guess you could get this down to about 5 seconds...|||No exp. in using Oracle's tools, but BCP is the fastest method to export rows from a database.

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1Are these two boxes in the same location?
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:533A6B9900AD9@.SQLMonster.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via SQLMonster.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
Rob
Rob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com
|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com
|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>
|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
--
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how to
>> help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> David,
>> Thanks for the info. I already can sequence the SubID's in my
>> application. I am trying to find a way to cheat (ie faster) by using SQL.
>> In a related topic is there a way to get the select the ROWID?
>> Rob
>>
>> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how
>> to help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>

Failure sending mail: The system cannot find the path specified.

I have had a development XP workstation with RS2000 and IIS sending reports via email for 6 months straight with no problems.

Today my subscriptions are failing with

Failure sending mail: The system cannot find the path specified.

Been searching and digging for hours, and if anyone has any ideas they are greatly appreciated.

I need some info to troubleshoot...

Replicate the issue, then go the Report Server log (ReportServer_xxx_xxx.log) and email the log to me for review. markcam80@.hotmail.com

What I will be looking for is explicit error messages versus the displayed message.

|||Thanks for the help but it seems we had a domain controller misbehaving.