> On Mar 1, 7:51 am, Blasting Cap christian.net> wrote:

>> I must be missing something.....
>>
>> The code is different on the two machines, I am sure. On Machine1, both
>> the sales_order_log and the sales_ord_curr_yr table are on the local
>> database. On machine2, only the sales_ord_curr_yr table is on the local
>> database. The sales_order_log is still on machine1.
>>
>> I could copy the table from one server to another and do the update, and
>> then copy it back, but there's a chance the app will be being used and I
>> would lose data, plus it seems like a bass-ackwards way of doing this.
>>
>> The only place I'm not referencing a column name is on the update line.
>>
>> When I make the change to reference the update line to the
>> server.database.owner.tablename, I get this:
>>
>> Cannot use the column prefix 'sales_order_log'. This must match the
>> object in the UPDATE clause 'myserver.mydb.dbo.sales_order_log'.
>>
>> If I change them to match, then that's wrong too & I get:
>>
>> "The number name 'myserver.mydb.dbo.sales_order_log' contains more than
>> the maximum number of prefixes. The maximum is 3."
>>
>> On this code -
>>
>> -- update order log non ds/dn
>> update [myserver].[mydb].[dbo].sales_order_log
>> set sales_order_log.ord_no = (select distinct b.ord_no from
>> sales_ord_curr_yr b where sales_order_log.acct_no = b.sold_to_cust_cd
>> and sales_order_log.po_num = b.po_num),
>> sales_order_log.auto_reconciled = 1
>> where sales_order_log.distr_channel not in ('DS','DN')
>> and ((select count(distinct b.ord_no) from sales_ord_curr_yr b where
>> sales_order_log.acct_no = b.sold_to_cust_cd and sales_order_log.po_num =
>> b.po_num) = 1)
>> and convert(char,sales_order_log.date_entered,101) between DATEADD(day,
>> -1, convert(char,getdate(),101)) and convert(char,getdate(),101)
>> and sales_order_log.auto_reconciled = 0
>>
>> which lines need to be changed to reference machine1, where the
>> sales_order_log table resides?
>>
>> Sorry to be so dense, I am just not understanding what the problem is,
>> since each thing I am doing seems to be wrong.
>>
>> BC
>>
>>
>>
>> Russell Fields wrote:

>>> BC,
>>> I am pretty sure that your code is different between the two machines. From
>>> the end of your code:
>>> and [myserver].[mydb].[dbo].sales_order_log.auto_reconciled = 0
>>> A column cannot have a server name associated with it. You should remove
>>> the linked server name from all column references. Or, you could alias the
>>> linked server table as:
>>> FROM [myserver].[mydb].[dbo].sales_order_log AS MyLog
>>> then change the references to:
>>> MyLog.auto_reconciled
>>> That should work, too. I would assume that the code that runs on one server
>>> is missing the [myserver] from the procedures, which is why it works.
>>> RLF
>>> "Blasting Cap" christian.net> wrote in message
>>> news:uQJ1WzAXHHA.3592@TK2MSFTNGP03.phx.gbl...

>>>> I have an application that is a log for purchase orders. Users enter
>>>> customer info & the purchase order info into this application & data is
>>>> collected in a table, sales_order_log, on one of the two databases I use
>>>> (they run on alternating days to load sales info).
>>>> On every other day, I run an update versus this table on one server and
>>>> the next day on the server where the table itself is housed.
>>>> The query is:
>>>> -- update order log non ds/dn
>>>> update [myserver].[mydb].[dbo].sales_order_log
>>>> set [myserver].[mydb].[dbo].sales_order_log.ord_no = (select distinct
>>>> b.ord_no from sales_ord_curr_yr b where
>>>> [myserver].[mydb].[dbo].sales_order_log.acct_no = b.sold_to_cust_cd and
>>>> [myserver].[mydb].[dbo].sales_order_log.po_num = b.po_num),
>>>> [myserver].[mydb].[dbo].sales_order_log.auto_reconciled = 1
>>>> where [myserver].[mydb].[dbo].sales_order_log.distr_channel not in
>>>> ('DS','DN')
>>>> and ((select count(distinct ord_no) from sales_ord_curr_yr b where
>>>> [myserver].[mydb].[dbo].sales_order_log.acct_no = b.sold_to_cust_cd and
>>>> [myserver].[mydb].[dbo].sales_order_log.po_num = b.po_num) = 1)
>>>> and convert(char,[myserver].[mydb].[dbo].sales_order_log.date_entered,101)
>>>> between DATEADD(day, -1, convert(char,getdate(),101)) and
>>>> convert(char,getdate(),101)
>>>> and [myserver].[mydb].[dbo].sales_order_log.auto_reconciled = 0
>>>> When I try to run it, I get this error message:
>>>> Server: Msg 117, Level 15, State 2, Line 6
>>>> The number name 'myserver.mydb.dbo.sales_order_log' contains more than the
>>>> maximum number of prefixes. The maximum is 3.
>>>> The table sales_order_log is on the server myserver. The update I am
>>>> trying to run is on a server, yourserver. The table sales_ord_curr_yr is
>>>> located on the server yourserver.
>>>> The job above runs fine on the same server the table and the curr_yr file
>>>> is on, but not on the alternate day.
>>>> How can I make the table update on alternating days?
>>>> any help/advice appreciated.
>>>> BC- Hide quoted text -

>> - Show quoted text -

>
>
> You can use the FROM clause with ALIAS. This should work.
>
> update S
> set S.ord_no = (select distinct b.ord_no from
> sales_ord_curr_yr b where S.acct_no = b.sold_to_cust_cd
> and S.po_num = b.po_num),
> S.auto_reconciled = 1
> FROM [myserver].[mydb].[dbo].sales_order_log S
> where S.distr_channel not in ('DS','DN')
> and ((select count(distinct b.ord_no) from sales_ord_curr_yr b where
> sales_order_log.acct_no = b.sold_to_cust_cd and sales_order_log.po_num
> =
> b.po_num) = 1)
> and convert(char,S.date_entered,101) between DATEADD(day,
> -1, convert(char,getdate(),101)) and convert(char,getdate(),101)
> and S.auto_reconciled = 0
>
> Thanks
> Mahesh Nayak
> Seattle
>