Update Data through linked views

innmurray · January 17th, 2005, 07:17 AM

Dear All,

I need to figure out how to update data in a remote server, but it must be done through a view in the current server. I am fine updating table data through a view in a local database, and I am fine with updating linked data directly. What I need to do, is use a local view of a single linked table to update data, and it won't!

Example:

Local server/db: server1.mydata1
Remote server/db: server2.mydata2

CREATE a table called test on remote server, with 2 fields: [id] identity(1,1) & department varchar(25)

USE sp_addlinkedserver to connect the sevrer2.mydata2 to server1

Create a view in server1.mydata1 as:
CREATE VIEW v_test
AS
SELECT id,department FROM server2.mydata2.dbo.test

If I run the view it will return the contents of table test

If I update an entry in the department field through the view, the server says that server2.mydata2.dbo.test doesnt exist

If I try the same process but with everything on a local database, it update fine?!

I've check all the linked server setting and permissions and they are fine.. so I'm stuck!

Appreciate Any Suggestions!

Thanks,

Innes

jemacc · January 17th, 2005, 10:09 AM

try using openrowset

example:

Code:

Create VIEW MyView AS
SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=team;UID=nn;PWD=nn',
   pubs.dbo.authors) AS a

You can then create views to access the data on the linked server without having to use the four-part name.

Jaime E. Maccou

innmurray · January 17th, 2005, 10:24 AM

Jamie,

Thanks for the reply, OPENROWSET is a great idea, but I should have mentions my server versions, The server I am creating the view on (server1) is SQL7.0 whereas the server with the table on it (server2) is SQL2000.

I did look at the OPENROWSET as a solution but I understand it isn't supported under SQL7.0

Innes

jemacc · January 17th, 2005, 10:46 AM

you can also create a stored procedure on the remote server that calls the view and call the stored procedure from the SQL 2000 server.

Jaime E. Maccou

innmurray · January 17th, 2005, 01:57 PM

That would deal with the data access side of things well, but would a nightmare from the perspectice of inserts, update and deletions?

Innes

jemacc · January 17th, 2005, 08:54 PM

going back to the original question

Did you check on the linked server the RPC IN and RPC OUT option on the configuration.

Jaime E. Maccou

innmurray · January 18th, 2005, 11:47 AM

Both the options are checked, I assume that is correct?

Innes

Ben · January 18th, 2005, 05:23 PM

Isn't it OPENDATASOURCE thats not supported in v7.0?