?
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
?
538 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
  • 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

588 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