Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 17th, 2005, 07:17 AM
Registered User
 
Join Date: Jan 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Data through linked views

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


 
Old January 17th, 2005, 10:09 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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
 
Old January 17th, 2005, 10:24 AM
Registered User
 
Join Date: Jan 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 17th, 2005, 10:46 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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
 
Old January 17th, 2005, 01:57 PM
Registered User
 
Join Date: Jan 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Innes

 
Old January 17th, 2005, 08:54 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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
 
Old January 18th, 2005, 11:47 AM
Registered User
 
Join Date: Jan 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Innes

 
Old January 18th, 2005, 05:23 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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





Similar Threads
Thread Thread Starter Forum Replies Last Post
data views using the architecture forumuser BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 December 5th, 2007 06:03 AM
converting Access 2000 views to Sql views matta Classic ASP Professional 1 January 26th, 2005 03:37 PM
Views-Insert/update/delete lily611 SQL Language 2 November 29th, 2004 03:41 AM
Update linked table penta Access 2 September 28th, 2004 07:58 AM
Update - Linked Server venkatpala SQL Server 2000 3 August 25th, 2003 11:45 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.