UPDATE OPENDATASOURCE Issue...

[BradF]

Can someone please tell me why this doesn't work? *'s replace actual secured values.

UPDATE		OPENDATASOURCE('SQLOLEDB','Data Source=*.*.*.*;User ID=*;Password=*').*.dbo.*
SET			ExceptionRanked = a.XCP_RNK_IR,
			SelectionFlg = a.SLE_IR,
			EUDLoadDt = GETDATE()
FROM		TADMSMT a INNER JOIN OPENDATASOURCE('SQLOLEDB','Data Source=*.*.*.*;User ID=*;Password=*').*.dbo.* b ON a.ID = b.ID



SELECT		a.ID, b.ID, ExceptionRanked, a.XCP_RNK_IR,
			SelectionFlg, a.SLE_IR,
			EUDLoadDt, GETDATE()
FROM		TADMSMT a INNER JOIN OPENDATASOURCE('SQLOLEDB','Data Source=*.*.*.*;User ID=*;Password=*').*.dbo.* b ON a.ID = b.ID

The query will update the EUDLoadDt fine, but never changes the value of the two other fields, both which are bit fields on both servers and both tables.

If I change the update, to a definitive value, say:

ExceptionRanked = 1,

Instead of:

ExceptionRanked = a.XCP_RNK_IR,

All the columns get updated with a 1. However it won't set them equal to each other...

I also tried this:

ExceptionRanked = CASE WHEN a.XCP_RNK_IR = 1 THEN 1 ELSE 0 END,

Which didn't work either...

I'm getting extremely frustrated and can't figure out what is going wrong...

 

[BradF]

Here's an example dataset returned from the select portion of the query after a run:

39	39	0	0	0	1	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
40	40	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
41	41	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
42	42	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
43	43	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
44	44	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
45	45	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
46	46	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
47	47	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
48	48	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
49	49	0	0	0	0	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393
50	50	0	0	0	1	2007-12-12 14:40:17.910	2007-12-12 14:40:18.393

 

[gmmastros]

I'm not confident that this will help, but I suggest you try....

UPDATE [!]b[/!]
SET    ExceptionRanked = a.XCP_RNK_IR,
       SelectionFlg = a.SLE_IR,
       EUDLoadDt = GETDATE()
FROM   TADMSMT a INNER JOIN OPENDATASOURCE('SQLOLEDB','Data Source=*.*.*.*;User ID=*;Password=*').*.dbo.* [!]b[/!] ON a.ID = b.ID

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BradF]

George, that gave me:

Invalid object name 'b'.

I don't think updating via aliases works on SQL Server 2000.

 

[BradF]

I still can't get this to work. Is there another way to do it that may be easier?

 

[gmmastros]

Are both databases on the same server? If so, you can just use the 3 part naming convention (Database.Schema.Table).

Otherwise, can you set up a linked server? In that case, you would use the 4 part naming convention (Server.Database.schema.table).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BradF]

George: Unfortunately, I'm unable to setup a true linked server and the databases aren't on the same server.

I knew this was going to be a fiasco!