Board index » delphi » Transfering data from access to SQL7

Transfering data from access to SQL7

I'm trying to transfer data from Access to SQL7. Have ruled out using DTS
since some of my customers are using MSDE.
I have made two ADO connections and am using stored procedures to transfer
data via a ADOcommand control
Stored proc is
Insert into table1
   (field1,Field2..)
values
   (@field1,@Field2.)

Then using the proc
with not Table2.eof. do begin
   ADOCommand1.Parameter.ParambyName('@Field1').value :=
AccesstableField1.AsString
  ect.
   Table2.Next;
   end;

The inserting of 75,000 records takes about 15 minutes.

On using DTS the flow takes about 1 minute.

Any way on speeding up the transfer of records?

Stephen K. Miyasato

 

Re:Transfering data from access to SQL7


DTS uses the fastload OLE DB interface to Bulk Insert to gets its perf in
this case, this is not available from ADO.

Why have you chosen to reject DTS because of MSDE? DTS (not the UI) is in
MSDE...

-Euan

"Stephen K. Miyasato" <miya...@flex.com> wrote in message
news:3b02799b$1_2@dnews...

Quote
> I'm trying to transfer data from Access to SQL7. Have ruled out using DTS
> since some of my customers are using MSDE.
> I have made two ADO connections and am using stored procedures to transfer
> data via a ADOcommand control
> Stored proc is
> Insert into table1
>    (field1,Field2..)
> values
>    (@field1,@Field2.)

> Then using the proc
> with not Table2.eof. do begin
>    ADOCommand1.Parameter.ParambyName('@Field1').value :=
> AccesstableField1.AsString
>   ect.
>    Table2.Next;
>    end;

> The inserting of 75,000 records takes about 15 minutes.

> On using DTS the flow takes about 1 minute.

> Any way on speeding up the transfer of records?

> Stephen K. Miyasato

Re:Transfering data from access to SQL7


? DTS (not the UI) is in  MSDE...

Thank for the info, I thought only OSQL is in MSDE. Do you access DTS by
calling RUN DTS Script.DTS?

Thanks again.

Stephen

Quote
"Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message

news:3b02e37c$1_1@dnews...
Quote
> DTS uses the fastload OLE DB interface to Bulk Insert to gets its perf in
> this case, this is not available from ADO.

> Why have you chosen to reject DTS because of MSDE? DTS (not the UI) is in
> MSDE...

> -Euan

> "Stephen K. Miyasato" <miya...@flex.com> wrote in message
> news:3b02799b$1_2@dnews...
> > I'm trying to transfer data from Access to SQL7. Have ruled out using
DTS
> > since some of my customers are using MSDE.
> > I have made two ADO connections and am using stored procedures to
transfer
> > data via a ADOcommand control
> > Stored proc is
> > Insert into table1
> >    (field1,Field2..)
> > values
> >    (@field1,@Field2.)

> > Then using the proc
> > with not Table2.eof. do begin
> >    ADOCommand1.Parameter.ParambyName('@Field1').value :=
> > AccesstableField1.AsString
> >   ect.
> >    Table2.Next;
> >    end;

> > The inserting of 75,000 records takes about 15 minutes.

> > On using DTS the flow takes about 1 minute.

> > Any way on speeding up the transfer of records?

> > Stephen K. Miyasato

Re:Transfering data from access to SQL7


You can access DTS via DTSRun or via the COM objects, do a search in the
delphi.database.sqlservers newsgroup for DTS and you will see some previous
threads on people using DTS from Delphi. There should also be some sample
code I wrote in the .attachements newsgroup.

-Euan

"Stephen K. Miyasato" <miya...@flex.com> wrote in message
news:3b039e8a$1_2@dnews...

Quote
> ? DTS (not the UI) is in  MSDE...

> Thank for the info, I thought only OSQL is in MSDE. Do you access DTS by
> calling RUN DTS Script.DTS?

> Thanks again.

> Stephen

> "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> news:3b02e37c$1_1@dnews...
> > DTS uses the fastload OLE DB interface to Bulk Insert to gets its perf
in
> > this case, this is not available from ADO.

> > Why have you chosen to reject DTS because of MSDE? DTS (not the UI) is
in
> > MSDE...

> > -Euan

> > "Stephen K. Miyasato" <miya...@flex.com> wrote in message
> > news:3b02799b$1_2@dnews...
> > > I'm trying to transfer data from Access to SQL7. Have ruled out using
> DTS
> > > since some of my customers are using MSDE.
> > > I have made two ADO connections and am using stored procedures to
> transfer
> > > data via a ADOcommand control
> > > Stored proc is
> > > Insert into table1
> > >    (field1,Field2..)
> > > values
> > >    (@field1,@Field2.)

> > > Then using the proc
> > > with not Table2.eof. do begin
> > >    ADOCommand1.Parameter.ParambyName('@Field1').value :=
> > > AccesstableField1.AsString
> > >   ect.
> > >    Table2.Next;
> > >    end;

> > > The inserting of 75,000 records takes about 15 minutes.

> > > On using DTS the flow takes about 1 minute.

> > > Any way on speeding up the transfer of records?

> > > Stephen K. Miyasato

Re:Transfering data from access to SQL7


Euan

Thanks for the info. I have downloaded your files but am unable to get it to
work

Getting error "log in failed for user 'sa'. I notice that the Constant
SQL_USERID is 'sa', but I have my
SQLServer set for "Use Windows Authentication"

If  I change to constant to blank, I still get the error.
any Ideas on how to get authentication?

Thanks again,

Stephen K. Miyasato

Quote
"Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message

news:3b043892$1_1@dnews...
Quote
> You can access DTS via DTSRun or via the COM objects, do a search in the
> delphi.database.sqlservers newsgroup for DTS and you will see some
previous
> threads on people using DTS from Delphi. There should also be some sample
> code I wrote in the .attachements newsgroup.

> -Euan

> "Stephen K. Miyasato" <miya...@flex.com> wrote in message
> news:3b039e8a$1_2@dnews...
> > ? DTS (not the UI) is in  MSDE...

> > Thank for the info, I thought only OSQL is in MSDE. Do you access DTS by
> > calling RUN DTS Script.DTS?

> > Thanks again.

> > Stephen

> > "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> > news:3b02e37c$1_1@dnews...
> > > DTS uses the fastload OLE DB interface to Bulk Insert to gets its perf
> in
> > > this case, this is not available from ADO.

> > > Why have you chosen to reject DTS because of MSDE? DTS (not the UI) is
> in
> > > MSDE...

> > > -Euan

> > > "Stephen K. Miyasato" <miya...@flex.com> wrote in message
> > > news:3b02799b$1_2@dnews...
> > > > I'm trying to transfer data from Access to SQL7. Have ruled out
using
> > DTS
> > > > since some of my customers are using MSDE.
> > > > I have made two ADO connections and am using stored procedures to
> > transfer
> > > > data via a ADOcommand control
> > > > Stored proc is
> > > > Insert into table1
> > > >    (field1,Field2..)
> > > > values
> > > >    (@field1,@Field2.)

> > > > Then using the proc
> > > > with not Table2.eof. do begin
> > > >    ADOCommand1.Parameter.ParambyName('@Field1').value :=
> > > > AccesstableField1.AsString
> > > >   ect.
> > > >    Table2.Next;
> > > >    end;

> > > > The inserting of 75,000 records takes about 15 minutes.

> > > > On using DTS the flow takes about 1 minute.

> > > > Any way on speeding up the transfer of records?

> > > > Stephen K. Miyasato

Re:Transfering data from access to SQL7


Don't have the code handy right now but there is a constant in the
loadfromsqlserver method (which is where I am guessing you have the issue)
that will be something like DTSSQLStgFlag_UseDefault, change this to the
other option which should be something like
DTSSQLStgFlag_UseTrustedConnection

-Euan

"Stephen K. Miyasato" <miya...@flex.com> wrote in message
news:3b04e1b7$1_1@dnews...

Quote
> Euan

> Thanks for the info. I have downloaded your files but am unable to get it
to
> work

> Getting error "log in failed for user 'sa'. I notice that the Constant
> SQL_USERID is 'sa', but I have my
> SQLServer set for "Use Windows Authentication"

> If  I change to constant to blank, I still get the error.
> any Ideas on how to get authentication?

> Thanks again,

> Stephen K. Miyasato

> "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> news:3b043892$1_1@dnews...
> > You can access DTS via DTSRun or via the COM objects, do a search in the
> > delphi.database.sqlservers newsgroup for DTS and you will see some
> previous
> > threads on people using DTS from Delphi. There should also be some
sample
> > code I wrote in the .attachements newsgroup.

> > -Euan

> > "Stephen K. Miyasato" <miya...@flex.com> wrote in message
> > news:3b039e8a$1_2@dnews...
> > > ? DTS (not the UI) is in  MSDE...

> > > Thank for the info, I thought only OSQL is in MSDE. Do you access DTS
by
> > > calling RUN DTS Script.DTS?

> > > Thanks again.

> > > Stephen

> > > "Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message
> > > news:3b02e37c$1_1@dnews...
> > > > DTS uses the fastload OLE DB interface to Bulk Insert to gets its
perf
> > in
> > > > this case, this is not available from ADO.

> > > > Why have you chosen to reject DTS because of MSDE? DTS (not the UI)
is
> > in
> > > > MSDE...

> > > > -Euan

> > > > "Stephen K. Miyasato" <miya...@flex.com> wrote in message
> > > > news:3b02799b$1_2@dnews...
> > > > > I'm trying to transfer data from Access to SQL7. Have ruled out
> using
> > > DTS
> > > > > since some of my customers are using MSDE.
> > > > > I have made two ADO connections and am using stored procedures to
> > > transfer
> > > > > data via a ADOcommand control
> > > > > Stored proc is
> > > > > Insert into table1
> > > > >    (field1,Field2..)
> > > > > values
> > > > >    (@field1,@Field2.)

> > > > > Then using the proc
> > > > > with not Table2.eof. do begin
> > > > >    ADOCommand1.Parameter.ParambyName('@Field1').value :=
> > > > > AccesstableField1.AsString
> > > > >   ect.
> > > > >    Table2.Next;
> > > > >    end;

> > > > > The inserting of 75,000 records takes about 15 minutes.

> > > > > On using DTS the flow takes about 1 minute.

> > > > > Any way on speeding up the transfer of records?

> > > > > Stephen K. Miyasato

Other Threads