?
Solved

How to join local tables with a linked server table to update the linked server table?

Posted on 2003-10-21
5
Medium Priority
?
499 Views
Last Modified: 2008-02-01
I have two tables (Category & SubCategory tables) residing on my local SQL server and a third table on a SQL server linkedserver called GroupName. If I want to view the three tables, I can easily view them using the following query:

SELECT *
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN OPENQUERY(Mavrick, 'select [Group], ID, Name, Abrev, Price1 from GroupName) AS c ON b.[Group]=c.[Group]
WHERE a.Category='00'

This works perfectly OK. However, if I want to update the Group table after joining the same two tables above, I get an error. Following is the update query I am trying to use:

UPDATE OPENQUERY(Mavrick,
'SELECT *
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN OPENQUERY(Mavrick, ''select [Group], ID, Name, Abrev, Price1 from GroupName) AS c ON b.[Group]=c.[Group]
WHERE a.Category='' 00'''
)
SET Price1 = Price1 + 1

That does not work and gives the following error:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] Syntax error in FROM clause.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].


I tried another way as follows:

UPDATE  c
SET c.Price1 =  c.Price1 + 1
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN OPENQUERY(Mavrick, 'select [Group], ID, Name, Abrev, Price1 from GroupName) AS c
ON b.[Group]=c.[Group]
WHERE a.Category='00'

Again that does'nt work either and the following error returns:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'c'.

I am completely lost and will appreciate if someone can help me out where am I doing wrong. I need to figure this out ASAP

Thank you
0
Comment
Question by:smkkaleem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:joekendall
ID: 9598763
On the last example you have, SQL Server doesn't know what the alias 'c' refers to. What table is 'c'? Is it OPENQUERY?

Joe
0
 
LVL 2

Author Comment

by:smkkaleem
ID: 9599919
'c' refers to the alias for the resultset acquired thru the linked server
0
 
LVL 9

Accepted Solution

by:
xenon_je earned 500 total points
ID: 9600440
read more about openquery......it is launching a SQL on a server (linked server).....

So after you executed it, even if you put an alias, that alias will refer to the result of the sql statement executed by the openquery...and will not refer to the tables from the linked server (even if the sql statement is a select on a single statement.


Now how you should do what you want: (for update you don't use the openquery like you tried):

UPDATE  c
SET c.Price1 =  c.Price1 + 1
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN Mavrick..GroupName as c
ON b.[Group]=c.[Group]
WHERE a.Category='00'

Now the syntax Mavrick..GroupName depends on what your link server is...., if you have a catalog name, if its an access db, an oracle.....

wainting your feedback

good luck,
  xenon
0
 
LVL 2

Author Comment

by:smkkaleem
ID: 9614802
Thanks xenon it worked perfectly...thanks a lot
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9618864
glad it helped you :))

see you
   xenon
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question