Solved

Update SQL table from FoxPro OpenQuery SubSelect

Posted on 2009-04-06
5
840 Views
Last Modified: 2012-05-06
Greetings Experts,  

I have a project where I have SQL server tables and Visual Fox Pro tables.  I need to update an inventory qty in the SQL table, based on the qty located in a Visual Fox Pro table.   I created a linked server, but need help with syntax.  I am stuck on matching the SKU in the SQL table to the where condition in the openquery subselect.   Here is what I have, that is not working:

UPDATE [MMan].[dbo].[items]

   SET qty = (select * from openquery(VFPRO, 'Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemno'))
     
GO


I get the following error:  

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemno" for execution against OLE DB provider "VFPOLEDB" for linked server "VFPRO".


Your help would be greatly appreciated.

Best Regards,

Keith
0
Comment
Question by:kdwood
[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 57

Expert Comment

by:Raja Jegan R
ID: 24078892
Try this query:

UPDATE [MMan].[dbo].[items]
SET qty = t2.saq_qty
FROM [MMan].[dbo].[items] t1, (select * from openquery(VFPRO, 'Select saq_sku, saq_qty from scsaqty')) t2
where t1.itemno = t2.saq_sku


     
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24078993
The command  'Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemno' is executed outside of SQL Server so it knows nothing about "[MMan].[dbo].[items].itemno".

I don't know exact syntax of linked server tables usage but I would guess you may use following (suppose saq_sku is of the same data type as items.itemno):

UPDATE [MMan].[dbo].[items]
   SET qty = fox.saq_qty
FROM [MMan].[dbo].[items] i
INNER JOIN (select * from openquery(VFPRO, 'Select saq_qty, saq_sku from scsaqty')) fox ON fox.saq_sku = i.itemno

Another possibility is to update [MMan].[dbo].[items] directly from FoxPro by e.g. SQLEXEC() function call.

0
 
LVL 10

Author Comment

by:kdwood
ID: 24079016
Thanks for the reply rrjegan,

I tried your query and at first I didn't think it would work because the query window in SQL Server Management Studio, was putting red underlines under the following:

t2.saq_QTY  and t2.saq_sku.   It was indicating that they were invalid column names.

However, I executed the query and it ran successfully and updated 214 rows.

Are the red underlines just a quirk because we are using the openquery method?

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24079040
Yes. The server doesn't know those are column names available in in your Foxpro table. But when it executes it through OPENQUERY it finds that it is a proper table and column and hence it executes it fine.

Hope that resolved your issue.
0
 
LVL 10

Author Closing Comment

by:kdwood
ID: 31567104
Thank you for the quick response.  The solution works great.   Best regards.
0

Featured Post

Technology Partners: 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

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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