Solved

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
physical_device_name field in SQL 3 39
populate value based on what is selected in lov 2 39
Star schema daily updates 2 39
Access control a form field in Lotus Notes 3 29
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

697 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