Transfering Data from Server 1to Server 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Transfering Data from Server 1to Server 2

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-14 : 04:26:25

Hi I want to transfer data from one server to another, so i've decided to use a DTS package, initally i will bring all the data over but from that day i only want updated data in server 1 to transfer to server 2 were their is new data added to server 1, i can use a date to get this (getdate()-1)

I want to used a insert/select statement (See below) in the Transform Data Task Properties but seeing as i am using 2 different servers am getting a error on this, saying it cannot find the the server name ([WSC reporting data].[dbo].[tbl_Voy_tabCSATSurvey]) its the server where i am insterting the data into.. any ideas on how i can get around this or another way of doing it, as i do not want to be bring all the data over every time i run the package.

INSERT INTO [WSC reporting data].[dbo].[tbl_Voy_tabCSATSurvey]([CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active])
SELECT [CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active]
FROM [wscReporting].[DBO].[tabCSATSurvey]
WHERE (CreateDT >= convert(datetime,convert(char(8),getdate()-1,112)))

Kristen
Test

22859 Posts

Posted - 2006-04-14 : 04:39:45

You need to set up a Linked Server - lets call it TargetServer.

Then you can do:

INSERT INTO TargetServer.TargetDatabase.dbo.TargetTable( ... columns ...)
SELECT ... columns ...
FROm LocalDatabase.dbo.LocalTable
WHERE ...

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-14 : 05:02:05

Thanks, i've never had to use this option before and it looks very easy to use and setup.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-14 : 05:27:19

Hi Kristen, I've a Quick Question as am still getting a error...
My Linked Server is {IRLDUNAS46}

My DTS Data is going from IRLDUNAS46 ------------------ > IRLDUBSAN07
Am still getting a error regards this server name IRLDUBSAN07, its not my linked server..

Here is my insert now ... (The connection am working on is (IRLDUNAS46 ) so this is where i will be doing my SQL Statement)

INSERT INTO [WSC reporting data].[dbo].[tbl_Voy_tabCSATSurvey]([CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active])
SELECT [CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active]
FROM [IRLDUNAS46].[wscReporting].[DBO].[tabCSATSurvey]
WHERE (CreateDT >= convert(datetime,convert(char(8),getdate()-1,112)))

Kristen
Test

22859 Posts

Posted - 2006-04-14 : 13:05:19

INSERT INTO IRLDUBSAN07.[WSC reporting data].[dbo].[tbl_Voy_tabCSATSurvey]([CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active])
SELECT [CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active]
FROM ~~[IRLDUNAS46].~~[wscReporting].[DBO].[tabCSATSurvey]
WHERE (CreateDT >= convert(datetime,convert(char(8),getdate()-1,112)))

and IRLDUBSAN07 will need to be set up as a Linked Server (on IRLDUNAS46)

IRLDUNAS46 is optional, as it is the server you are on. But no harm to leave it in if you like.

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-05 : 07:40:29

I just wanted to say i found a easier way to do my transfer instead of using a linked server, initally i wanted to select my data and then insert it into my other database which is on another server, so i thought i need a linked server but after talking to my DBA we decided the the Transform data task will do the copy column and all i need to do was to write a select statement in the SQLquery part of the Transform data task

Code example is BEFORE
Insert Into [WSCdata].[dbo].[tbl_Voy_tab]
([CSATSurveyGUID], [SerialNum], [CountryCd3], [CreateDT], [Active])
Select [CSATSurveyGUID],
[SerialNum],
[CountryCd3],
[CreateDT],
[Active]
From [wscRep].[DBO].[tabCSAT]
Where (CreateDT >= convert(datetime,convert(char(8),getdate()-1,112)))

AFTER

Select [CSATSurveyGUID],
[SerialNum],
[CountryCd3],
[CreateDT],
[Active]
From [wscRep].[DBO].[tabCSAT]
Where (CreateDT >= convert(datetime,convert(char(8),getdate()-1,112)))

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-05-05 : 08:00:38

Both methods will work fine. Kristen's method needs a linked server as otherwise the INSERT INTO won't work because you may not be authenticated. In some ways this is a better method that a DTS package.

For DTS packages (as you have discovered) you use a SELECT statement on the source and then in the Destination you specify where you want it to go. I think that the main advantage of DTS is that it can cope with lots of different types of data sources, also the DTS package doesn't have to be on either the source or the destination servers which may or may not be a good thing.

steve

-----------

Oh, so they have internet on computers now!

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-10 : 03:12:41

Hello friends..i am also facing similar problem but here i want to transfer data from two tables on one server into two tables on anothere server but using single DTS.so what i done is prepare one connection for one transformation(one table) and existing connection for another transformation of data(second table)......is this ok or another way to transfer two tables data???

please guide me if i m going in wrong direction????

T.I.A

Papillon

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-05-10 : 03:56:41

Hi Papillon

Yes, you can do what you suggest, i.e. you setup a single data source and a single data destination with two data transformation tasks between them (one for each table) and it will work.

You could also - if you wanted - setup the data source connection twice and the data destination connection twice and then create single data transformation tasks i.e. SourceConn1 -> DestinationConn1 AND SourceConn2 -> DestinationConn2 . This will also work and is more flexible if you need to make changes in the future.

With this second option, if the connections are named differently the package elements will run in parallel.

My comments assume that you are using DTS i.e. that in SQL Server 7 or 2000, I'm not sure about SQL Server 2005.

Hope this helps

steve

-----------

Oh, so they have internet on computers now!

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-10 : 04:25:38

Hi...steve

great work from u....i got some smile on my face now!!!!! :) :)

Thanks

Papillon

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-17 : 04:35:31

Just to get back to one point you mentioned Steve, about transfering the data, i've got about 6 tables that i need to transfer, but i am using a single data source and destination. I've 6 data transforms between the 2 connections, would you say it best to seperate these out into single data transforms?.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources