Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Keeping tables updated

Status
Not open for further replies.

tbode2006

Programmer
Aug 24, 2006
62
US
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

Code:
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?

 
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:
Code:
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:
Code:
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.
 
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.

Code:
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.]

Code:
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:

Code:
DBA Table1              DBB Table1
Rotation_Num Status     Rotation_Num Status
1001           2          1001         2
1002           1          1002         1
1003           1          1003         1
 
Then maybe you have no rights to write in linked server.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Code:
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!
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top