Insert problem with linked server

[Adrian]

Both servers running SQL 2000

I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.

The following three queries work:

insert into LinkedServer.db name.dbo.Table2
select *
from LinkedServer.db name.dbo.Table1

select *
into LocalTable
from LinkedServer.db name.dbo.Table1

insert into LocalTable
select *
from LinkedServer.db name.dbo.Table1


This query, which is what we really want to do, does not work:

insert into LinkedServer.db name.dbo.Table1
select *
from LocalTable

and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'

I am new to all this and would welcome some help.

Adrian

[Adrian]

Re: Insert problem with linked server

I believe I have now resolved this

In fact the example below would work
[color=blue]
> insert into LinkedServer.db name.dbo.Table1
> select *
> from LocalTable[/color]

I was trying to insert into a table on the linked server that was not owned
by the dbo but by the remote username. It seems that providing the owner of
the table is dbo it will be OK.

Adrian.


"Adrian" <NoSpam@hotmail .com> wrote in message
news:ANReb.6576 $8_4.54623402@n ews-text.cableinet. net...[color=blue]
> Both servers running SQL 2000
>
> I have set up on our local SQL server (using Enterprise Manager) a linked
> server running on our ISP. Just did new linked server and added remote
> password and login.
>
> The following three queries work:
>
> insert into LinkedServer.db name.dbo.Table2
> select *
> from LinkedServer.db name.dbo.Table1
>
> select *
> into LocalTable
> from LinkedServer.db name.dbo.Table1
>
> insert into LocalTable
> select *
> from LinkedServer.db name.dbo.Table1
>
>
> This query, which is what we really want to do, does not work:
>
> insert into LinkedServer.db name.dbo.Table1
> select *
> from LocalTable
>
> and returns the error: 'The cursor does not include the table being[/color]
modified[color=blue]
> or the table is not updatable through the cursor.'
>
> I am new to all this and would welcome some help.
>
> Adrian
>
>[/color]