Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
521 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

609 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