Solved

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

Posted on 2003-10-21
5
493 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 125 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 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