Keeping tables updated

[tbode2006]

The concept:
I have two separate SQL 2K databases: DBA & DBB. A current VB app updates a column in table1 in DBA via a SP. I need to write a SP that will update the same column in table1 in DBB. The SP will be triggered on a timed basis by an external piece of hardware and will look for:
DBA-Table1-key1 = DBB-Table2-key1
DBA-Table1-stat1<>DBB-Table2-stat2 then
SET DBB-Table2-stat2=DBA-Table1-Stat1

The actual implementation using actual names:
(DBA would local DB, DBB would be CR_IT_A)
What I tried so far

update [CR_IT_A].[Production].dbo.Table1
set [CR_IT_A].[Production].dbo.Table1.status = dbo.Table1.status
where (([CR_IT_A].[Production].dbo.Table1.status = dbo.Table1.status) and 
([CR_IT_A].[Production].dbo.Table1.rotation_num = dbo.Table1.rotation_num))

The error message I received

The number name 'CR_IT_A.Production.dbo.Table1' contains more than the maximum number of prefixes. The maximum is 3.

Can someone give me some guidance as to what I am missing?

 

[bborissov]

What is [CR_IT_A]?
Linked server?

That update is wrong.
Why you UPDATE status field in
[CR_IT_A].[Production].dbo.Table1 when it is ALREADY equal to dbo.Table1.status?
See your WHERE clause:

where (([COLOR=red][b][CR_IT_A].[Production].dbo.Table1.status = dbo.Table1.status) [/b][/color]and
([CR_IT_A].[Production].dbo.Table1.rotation_num = dbo.Table1.rotation_num))

Also I prefer JOINs:

update ProdTable
       SET Status = Local.status
FROM [CR_IT_A].[Production].dbo.Table1 ProdTable
INNER JOIN dbo.Table1 Local 
      ON (ProdTable.rotation_num = Local.rotation_num)

the code above is not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[tbode2006]

Yes, CR_IT_A is a linked server.
I meant the equality to be a non-equality.
I know that your code was untested. I have been playing with it without success.

select * FROM [CR_IT_A].[Production].dbo.Table1 ProdTable

This code worked.

I have gotten the following message with your code:

Server: Msg 7306, Level 16, State 2, Line 2
Could not open table '"Production"."dbo"."Table1"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

Sample data
DBA Table1              DBB Table1
Rotation_Num Status     Rotation_Num Status
1001           2          1001         1
1002           1          1002         1
1003           1          1003         1

SP would do following:

DBA Table1              DBB Table1
Rotation_Num Status     Rotation_Num Status
1001           2          1001         2
1002           1          1002         1
1003           1          1003         1

 

[bborissov]

Then maybe you have no rights to write in linked server.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[tbode2006]

insert into [CR_IT_A].[Production].dbo.Table1 (rotation_num,status) values('2001',1)

allowed me to write to the linked server.

I am still trying other things. Any thoughts are greatly appreciated!

 

[tbode2006]

Also, server instances are in VM sessions, not a production environment at this time. I am just testing the concept for implementation in the field next week.