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 /advice appreciated.


BC

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" <goober@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 /advice appreciated.
>
>
> BC

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" <goober@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 /advice appreciated.
>>
>>
>> BC
>
>

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" <goober@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 /advice appreciated.
>>
>>
>> BC
>
>

On Mar 1, 7:51 am, Blasting Cap <goo...@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" <goo...@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 /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

Mahesh -

Thank you for the -

I tried what you said.

It generated the following error:


"Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"mydb"."dbo"."sales_order_log"' 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.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT
VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA..."

Which - all that being said - was something I could fix. I had the id
field be the key, but it was not the primary key.

Thanks for your .

BC






lucky wrote:
> On Mar 1, 7:51 am, Blasting Cap <goo...@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" <goo...@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 /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
>