transfer data between two servers

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

SQL Server Development (2000)

transfer data between two servers

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-11-30 : 12:35:33

hello,

I have two servers. Two different databases. I know I can use DTS than to insert data from server A into server B on time based.
Will it work with a SQL script that running then on one of the two servers? And if so how do adressthe table/records on server B when proc is running on server A...

A short example would help ..

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-30 : 13:02:10

Easiest to create a linked server then

insert tbl (...)
select ...
from srv2.dbname.dbo.tblname
where ...

You could also use openquery or openrowset if you don't want to create a linked server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-12-01 : 08:57:13

Oke , cretaed a linked server called OLNSQL03
The database there is called APPL_WTP
Table name = INFO_TRS with a join on INFO_SHIFT_ID

This is my proc, but it gives an error

'The column prefix 'OLNSQL03.APPL_WTP.dbo' does not match with a table name or alias name used in the query.'

Any idea's

ALTER PROCEDURE [dbo].[Proc_Insert] AS
BEGIN
DECLARE @ActualTRS as [decimal](5, 2),
@PlannedTRS as [decimal](5, 2),
@ActualCO as int,
@PlannedCO as int

SELECT @ActualTRS = ACTUAL_MOVING_TRS,
@PlannedTRS = PLANNED_MOVING_TRS,
@ActualCO = ACTUAL_CO,
@PlannedCO = PLANNED_CO
FROM [OLNSQL03].[APPL_WTP].[dbo].[INFO_TRS] INNER JOIN
[OLNSQL03].[APPL_WTP].[dbo].[INFO_SHIFT_ID] ON
[OLNSQL03].[APPL_WTP].[dbo].[INFO_TRS.SHIFT_ID] =
[OLNSQL03].[APPL_WTP].[dbo].[INFO_SHIFT_ID.ID]
WHERE [OLNSQL03].[APPL_WTP].[dbo].[INFO_SHIFT_ID.DAY] =
CONVERT(varchar(10), GETDATE(), 121)
AND [OLNSQL03].[APPL_WTP].[dbo].[INFO_TRS.LINE_ID] = 6

INSERT INTO RUNTIME.DBO.HISTORY(DateTime, TagName, Value, QualityDetail)
VALUES (getdate(), 'H1_Actual_TRS', @ActualTRS , 192)

INSERT INTO RUNTIME.DBO.HISTORY(DateTime, TagName, Value, QualityDetail)
VALUES (getdate(), 'H1_Planned_TRS', @PlannedTRS , 192)

END

The Enigma
Posting Yak Master

179 Posts

Posted - 2003-12-01 : 10:23:03

Try this

quote:

ALTER PROCEDURE [dbo].[Proc_Insert] AS
BEGIN
DECLARE @ActualTRS as [decimal](5, 2),
@PlannedTRS as [decimal](5, 2),
@ActualCO as int,
@PlannedCO as int

SELECT @ActualTRS = ACTUAL_MOVING_TRS,
@PlannedTRS = PLANNED_MOVING_TRS,
@ActualCO = ACTUAL_CO,
@PlannedCO = PLANNED_CO
FROM [OLNSQL03].[APPL_WTP].[dbo].[INFO_TRS] [INFO_TRS] INNER JOIN
[OLNSQL03].[APPL_WTP].[dbo].[INFO_SHIFT_ID] [INFO_SHIFT_ID] ON
[INFO_SHIFT_ID].[SHIFT_ID] =
[INFO_SHIFT_ID].[ID]
WHERE [INFO_SHIFT_ID].[DAY] =
CONVERT(varchar(10), GETDATE(), 121)
AND [INFO_TRS].[LINE_ID] = 6

INSERT INTO RUNTIME.DBO.HISTORY(DateTime, TagName, Value, QualityDetail)
VALUES (getdate(), 'H1_Actual_TRS', @ActualTRS , 192)

INSERT INTO RUNTIME.DBO.HISTORY(DateTime, TagName, Value, QualityDetail)
VALUES (getdate(), 'H1_Planned_TRS', @PlannedTRS , 192)

END

Edited & corrected :
[Edit]

quote:

ALTER PROCEDURE [dbo].[Proc_Insert] AS
BEGIN
DECLARE @ActualTRS as [decimal](5, 2),
@PlannedTRS as [decimal](5, 2),
@ActualCO as int,
@PlannedCO as int

SELECT @ActualTRS = ACTUAL_MOVING_TRS,
@PlannedTRS = PLANNED_MOVING_TRS,
@ActualCO = ACTUAL_CO,
@PlannedCO = PLANNED_CO
FROM [OLNSQL03].[APPL_WTP].[dbo].[INFO_TRS] [INFO_TRS] INNER JOIN
[OLNSQL03].[APPL_WTP].[dbo].[INFO_SHIFT_ID] [INFO_SHIFT_ID] ON
[INFO_TRS].[SHIFT_ID] =
[INFO_SHIFT_ID].[ID]
WHERE [INFO_SHIFT_ID].[DAY] =
CONVERT(varchar(10), GETDATE(), 121)
AND [INFO_TRS].[LINE_ID] = 6

INSERT INTO RUNTIME.DBO.HISTORY(DateTime, TagName, Value, QualityDetail)
VALUES (getdate(), 'H1_Actual_TRS', @ActualTRS , 192)

INSERT INTO RUNTIME.DBO.HISTORY(DateTime, TagName, Value, QualityDetail)
VALUES (getdate(), 'H1_Planned_TRS', @PlannedTRS , 192)

END

[/Edit]
Attitude is everything

{The Enigma}

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-12-01 : 11:29:12

tried it and then I get following error

Invalid column name 'SHIFT_ID'

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-12-01 : 11:34:29

Oeps found the error

SHIFT_ID belongs to INFO_TRS instead of INFO_SHIFT_ID

Thanks for the help, it seems to work now..

The Enigma
Posting Yak Master

179 Posts

Posted - 2003-12-01 : 11:43:56

quote:
Originally posted by dyckwal

tried it and then I get following error

Invalid column name 'SHIFT_ID'

My Bad !!!!
Edited and corrected i stand.

Attitude is everything

{The Enigma}

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-12-02 : 03:01:12

But now the next problem. If I run the proc from within QA, it runs fine. But when I create a job and let the proc run it failed.

this is the error

[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'INSQL' IRowsetChange::InsertRow returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources