Error 120

Hello.
I'm trying to run the following statement:
insert into [destination table]
([fields])
select [fields] from [source table]
When I try to run it, I get the following error messages:
Server: Msg 120, Level 15, State 1, Line 8
The select list for the INSERT statement contains fewer
items than the insert list. The number of SELECT values
must match the number of INSERT columns.
Server: Msg 8180, Level 16, State 1, Line 8
Statement(s) could not be prepared.
The select list matches the insert list exactly.
The source and destination tables are located on two
different (linked) servers.
Both servers are running MSSQL 2000 SP3a.
What am I doing wrong?
Thank you.
Without seeing the actual insert statement we can only guess. My guess
would be you have a space in one of the column names.
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:12a5601c4432a$dcaeb0e0$a301280a@.phx.gbl...
> Hello.
> I'm trying to run the following statement:
> insert into [destination table]
> ([fields])
> select [fields] from [source table]
> When I try to run it, I get the following error messages:
> Server: Msg 120, Level 15, State 1, Line 8
> The select list for the INSERT statement contains fewer
> items than the insert list. The number of SELECT values
> must match the number of INSERT columns.
> Server: Msg 8180, Level 16, State 1, Line 8
> Statement(s) could not be prepared.
> The select list matches the insert list exactly.
> The source and destination tables are located on two
> different (linked) servers.
> Both servers are running MSSQL 2000 SP3a.
> What am I doing wrong?
> Thank you.
>
|||No, the statement is definitely OK, it works on other
servers, there's only one server it doesn't work on. And
it works if I run it locally, but it doesn't work when I
run it from another server.
Here are the statements:
declare @.IdSpatiu int,
@.IdTran int
set @.IdSpatiu = 15
set @.IdTran = 20
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select @.IdSpatiu, @.IdTran, Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9
If I replace "select @.IdSpatiu, @.IdTran, Numar"
with "select 15, 20, Numar", it works. It doesn't like
the variables in the select list.
If I'm connected to the server [ts-331], it works. But if
I'm connected to another server (to which ts-331 is
linked), it doesn't work.
It must be something about the ts-331 server. At first I
thought it must be the service pack, because it had no
service pack installed, but then I installed SP3a and the
problem persisted.
Thank you.

>--Original Message--
>Without seeing the actual insert statement we can only
guess. My guess
>would be you have a space in one of the column names.
>--
|||It looks fine to me as well. Maybe there is something strange with the way
the Linked server is set up on that machine. Why do you want to do an
insert like that remotely anyway? It would be much cleaner if you had a
stored proc on the linked server that you call and pass in the 2 parameters.
That way the sp does not have to make any linked server calls at all when
actually doing the Insert.
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:1318b01c443b8$d05fa8b0$a401280a@.phx.gbl...
> No, the statement is definitely OK, it works on other
> servers, there's only one server it doesn't work on. And
> it works if I run it locally, but it doesn't work when I
> run it from another server.
> Here are the statements:
> declare @.IdSpatiu int,
> @.IdTran int
> set @.IdSpatiu = 15
> set @.IdTran = 20
> insert into [ts-331].Ploiesti.dbo.Factura
> (IdSpatiu, IdTran, Numar)
> select @.IdSpatiu, @.IdTran, Numar
> from [ts-331].Deva.dbo.Factura
> where IdTran = 11 and IdSpatiu = 9
> If I replace "select @.IdSpatiu, @.IdTran, Numar"
> with "select 15, 20, Numar", it works. It doesn't like
> the variables in the select list.
> If I'm connected to the server [ts-331], it works. But if
> I'm connected to another server (to which ts-331 is
> linked), it doesn't work.
> It must be something about the ts-331 server. At first I
> thought it must be the service pack, because it had no
> service pack installed, but then I installed SP3a and the
> problem persisted.
> Thank you.
> guess. My guess
>
|||I had the same problem. Try this. I don't know why it works but it
does.
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select (select @.IdSpatiu), (select @.IdTran), Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9