Error: Contains more than the maximum number of prefixes. The max

I am running an update statement in query analyzer to update a database with
data from another database on another server.
I am running into the error : Error: Contains more than the maximum number
of prefixes. Maximum is 3
How do I overcome this error. I am the admin on both servers.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44062
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||I was trying to do something similar by doing an update and "pushing" the da
ta across to a linked server with the following:
update ServerName.AAD.dbo.t_employee
set ServerName.AAD.dbo.t_employee.work_shift = wa.work_shift
from t_employee wa
where ServerName.AAD.dbo.t_employee.id = wa.id
and wa.id = '105'
I was also getting the following:
Server: Msg 117, Level 15, State 2, Line 4
The number name 'ServerName.AAD.dbo.t_employee' contains more than the maxim
um number of prefixes. The maximum is 3.
It's a simple, but not obvious, answer. Put the table you are updating in th
e FROM clause with an alias and update the alias, as follows:
update la
set la.work_shift = wa.work_shift
from ServerName.AAD.dbo.t_employee la, t_employee wa
where la.id = wa.id
and wa.id = '105'
You have to love simple answers. Finding them is the challenging part. :)sql

error,again

tentaively, got:
create #table(a,b,c,d)
insert into #table
select A,B,C,D
from s,t,x,y,z where y.price > 20

select *,
(select max(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c),
(select min(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c)
from #temp w
drop Table #temp

This works, but if y.price > 20 condition is not met,
unsupported error message gets produced within crystal
report. i expected blank field report instead in this
case,

included If exists around the select statement ,
still same error.

BTW, is there a way to find max(D) and min(D),
two highests, can be improved?.

--
Sent by 3 from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comalexqa2003@.yahoo.com (u128845214@.spawnkill.ip-mobilphone.net) writes:
> tentaively, got:
> create #table(a,b,c,d)
> insert into #table
> select A,B,C,D
> from s,t,x,y,z where y.price > 20
> select *,
> (select max(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c),
> (select min(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c)
> from #temp w
> drop Table #temp
> This works, but if y.price > 20 condition is not met,
> unsupported error message gets produced within crystal
> report. i expected blank field report instead in this
> case,

If the condition is not met, the temp table is empty, and you will
get no rows back at all.

You could do:

IF EXISTS (SELECT * FROM #temp)
SELECT *, ...
ELSE
SELECT NULL, NULL, NULL, NULL, NULL, NULL -- As many as needed.

Although, it would be better to rewrite the report to handle an
empty result set...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

error,again

tentaively, got:
create #table(a,b,c,d)
insert into #table
select A,B,C,D
from s,t,x,y,z where y.price > 20
select *,
(select max(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c),
(select min(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c)
from #temp w
drop Table #temp
This works, but if y.price > 20 condition is not met,
unsupported error message gets produced within crystal
report. i expected blank field report instead in this
case,
included If exists around the select statement ,
still same error.
BTW, is there a way to find max(D) and min(D),
two highests, can be improved?.
--
Sent by 3 from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comalexqa2003@.yahoo.com (u128845214@.spawnkill.ip-mobilphone.net) writes:
> tentaively, got:
> create #table(a,b,c,d)
> insert into #table
> select A,B,C,D
> from s,t,x,y,z where y.price > 20
> select *,
> (select max(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c),
> (select min(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c)
> from #temp w
> drop Table #temp
> This works, but if y.price > 20 condition is not met,
> unsupported error message gets produced within crystal
> report. i expected blank field report instead in this
> case,
If the condition is not met, the temp table is empty, and you will
get no rows back at all.
You could do:
IF EXISTS (SELECT * FROM #temp)
SELECT *, ...
ELSE
SELECT NULL, NULL, NULL, NULL, NULL, NULL -- As many as needed.
Although, it would be better to rewrite the report to handle an
empty result set...
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp