How to transfer data from one sql server to another sql server 7.0

Dear All
i have small data to transfer, let me explain it,
I have to transfer some small data from head office (sql server) to a branch
office (sql server)
the main objective is that the task should be automated via front end
developed in VB 6.0
The user at head office just choose option from front end to transfer data
and the user at branch office just select option to receive data, the
remainin task should be performed at the back end.
I just want to know the best technique to transfer the data, i have built
same type of solution using foxpro by sending data in text file. as i have
some ideas in my mind that data can be transfered via text file, via access
mdb file etc. etc., but what is the best technique.
please help if u can
Regards
Abdul Hameed
hsbilal@.gmail.comHi Abdul,
I feel the best option for you will be using Data Transformation Servics.
DTS not only helps to transfer data, it also helps to transform data. You
will be able to invoke the DTS package from your Visual Basic Program using
the COM interface provided by DTS.
Thanks & Regards
Shri.DBA
"Abdul Hameed" wrote:
> Dear All
> i have small data to transfer, let me explain it,
> I have to transfer some small data from head office (sql server) to a branch
> office (sql server)
> the main objective is that the task should be automated via front end
> developed in VB 6.0
> The user at head office just choose option from front end to transfer data
> and the user at branch office just select option to receive data, the
> remainin task should be performed at the back end.
> I just want to know the best technique to transfer the data, i have built
> same type of solution using foxpro by sending data in text file. as i have
> some ideas in my mind that data can be transfered via text file, via access
> mdb file etc. etc., but what is the best technique.
> please help if u can
> Regards
> Abdul Hameed
> hsbilal@.gmail.com
>
>|||Hi
How often users can click to transfer?
What is db's size?
Also , look at backup/restore option
"Abdul Hameed" <hsbilal@.gmail.com> wrote in message
news:u1LXijasEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Dear All
> i have small data to transfer, let me explain it,
> I have to transfer some small data from head office (sql server) to a
branch
> office (sql server)
> the main objective is that the task should be automated via front end
> developed in VB 6.0
> The user at head office just choose option from front end to transfer data
> and the user at branch office just select option to receive data, the
> remainin task should be performed at the back end.
> I just want to know the best technique to transfer the data, i have built
> same type of solution using foxpro by sending data in text file. as i have
> some ideas in my mind that data can be transfered via text file, via
access
> mdb file etc. etc., but what is the best technique.
> please help if u can
> Regards
> Abdul Hameed
> hsbilal@.gmail.com
>
>|||Dear
Thankx for reply
I can use the DTS but how i will pass certain paramaters to dts package, as
i have to get the data based on certain paramaters.
Anyway i found another solution, please let me know will it be appropriate.
To Export data:
I will make a stored procedures which will accept certain paramaters, this
proce will create a linked server (access mdb)
then it will get data from sql server and update the mdb file, and at the
end the linked server will be destory, i will call this procedure form my vb
app with paramaters.
To Import data:
i will make a same stored procedure with reverse technique i.e. reading data
from mdb file and update sql server data
Kindly give ur comments.
Regards
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:D4AD3EAE-BFC5-4DC9-BFD3-8050CD48826D@.microsoft.com...
> Hi Abdul,
> I feel the best option for you will be using Data Transformation Servics.
> DTS not only helps to transfer data, it also helps to transform data. You
> will be able to invoke the DTS package from your Visual Basic Program
using
> the COM interface provided by DTS.
> Thanks & Regards
> Shri.DBA
> "Abdul Hameed" wrote:
> > Dear All
> > i have small data to transfer, let me explain it,
> > I have to transfer some small data from head office (sql server) to a
branch
> > office (sql server)
> > the main objective is that the task should be automated via front end
> > developed in VB 6.0
> > The user at head office just choose option from front end to transfer
data
> > and the user at branch office just select option to receive data, the
> > remainin task should be performed at the back end.
> >
> > I just want to know the best technique to transfer the data, i have
built
> > same type of solution using foxpro by sending data in text file. as i
have
> > some ideas in my mind that data can be transfered via text file, via
access
> > mdb file etc. etc., but what is the best technique.
> > please help if u can
> > Regards
> > Abdul Hameed
> > hsbilal@.gmail.com
> >
> >
> >
> >|||See inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Abdul Hameed" <hsbilal@.gmail.com> wrote in message
news:O5uAQbdsEHA.2560@.tk2msftngp13.phx.gbl...
> Dear
> Thankx for reply
> I can use the DTS but how i will pass certain paramaters to dts package,
as
> i have to get the data based on certain paramaters.
> Anyway i found another solution, please let me know will it be
appropriate.
You can create a DTS package, and even save it as a BAS file and include it
in your project, or simply call it using the object model... You can set
global variables and pass in your parameters that way.
> To Export data:
> I will make a stored procedures which will accept certain paramaters, this
> proce will create a linked server (access mdb)
> then it will get data from sql server and update the mdb file, and at the
> end the linked server will be destory, i will call this procedure form my
vb
> app with paramaters.
I wonder if creating the access database isn't overkill... you could simply
use DTS to bcp out the files. or better simply leave the data in the source
database and when the user wishes to import, move the data directly from the
source database to the destination database.
> To Import data:
> i will make a same stored procedure with reverse technique i.e. reading
data
> from mdb file and update sql server data
> Kindly give ur comments.
> Regards
> "Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
> news:D4AD3EAE-BFC5-4DC9-BFD3-8050CD48826D@.microsoft.com...
> > Hi Abdul,
> >
> > I feel the best option for you will be using Data Transformation
Servics.
> > DTS not only helps to transfer data, it also helps to transform data.
You
> > will be able to invoke the DTS package from your Visual Basic Program
> using
> > the COM interface provided by DTS.
> >
> > Thanks & Regards
> > Shri.DBA
> >
> > "Abdul Hameed" wrote:
> >
> > > Dear All
> > > i have small data to transfer, let me explain it,
> > > I have to transfer some small data from head office (sql server) to a
> branch
> > > office (sql server)
> > > the main objective is that the task should be automated via front end
> > > developed in VB 6.0
> > > The user at head office just choose option from front end to transfer
> data
> > > and the user at branch office just select option to receive data, the
> > > remainin task should be performed at the back end.
> > >
> > > I just want to know the best technique to transfer the data, i have
> built
> > > same type of solution using foxpro by sending data in text file. as i
> have
> > > some ideas in my mind that data can be transfered via text file, via
> access
> > > mdb file etc. etc., but what is the best technique.
> > > please help if u can
> > > Regards
> > > Abdul Hameed
> > > hsbilal@.gmail.com
> > >
> > >
> > >
> > >
>