Cutting down what I've found / been showed I get:
set safety off
close all
LOCAL connectionString
connectionString = "Driver={SQL
Server};Server=sageserver;Database=APAC;Uid=admin-account;Pwd=admin-pwd;"
LOCAL sql
** sql8 = "SELECT * from dbo.BOOKER"
sql8 = "SELECT * from dbo.SLCustomerAccount"
connHandle = SQLSTRINGCONNECT(connectionString)
= SQLSETPROP(connHandle, 'asynchronous', .f.)
= SQLEXEC(connHandle, sql8, 'MYTABLE')
= SQLDISCONNECT(connHandle)
Select MYTABLE
Browse
Where it's a table it works perfectly... when I reverse the comments to my now
renamed view dbo.BOOKER it falls over on the select MYTABLE with alias MYTABLE
not found...
-----Original Message-----
From: ProFox [mailto:[email protected]] On Behalf Of Adam Buckland
Sent: 30 November 2012 14:27
To: ProFox Email List ([email protected])
Subject: VFP to SQL Server View
Ok totally new to this so hopefully someone has done this before...
I have a view on SQL Server 2008 (It's a Sage accounts database if that
matters) which I need to get at from foxpro 6 and later V9
The view spans 8 tables and basically gets invoicing details that I need to
make a consolidated invoice to end up in a SAP system but to start with... is
there a way to get data from this view completely under Foxpro to a local
foxpro database without having to download individual tables and reconstruct
the view?
The database is called APAC on a server called sageserver and the view is
dbo.M2M_BOOKER and is as follows:
SELECT TOP (100) PERCENT dbo.SLCustomerAccount.CustomerAccountNumber,
dbo.SLCustomerAccount.CustomerAccountName, dbo.SOPInvCredAddress.PostalName,
dbo.SOPInvCredAddress.AddressLine1,
dbo.SOPInvCredAddress.AddressLine2, dbo.SOPInvCredAddress.AddressLine3,
dbo.SOPInvCredAddress.AddressLine4,
dbo.SOPInvCredAddress.City,
dbo.SOPInvCredAddress.County, dbo.SOPInvCredAddress.Country,
dbo.SOPInvCredAddress.PostCode,
dbo.SOPInvoiceCredit.DocumentNo AS [Invoice Number],
dbo.SOPInvoiceCredit.SecondReference AS Refrence2,
dbo.SOPInvoiceCredit.DocumentDate,
dbo.SOPInvoiceCreditLine.PrintSequenceNumber,
dbo.SOPInvoiceCreditLine.InvoiceCreditQuantity, dbo.SOPOrderReturnLine.ItemCode,
dbo.SOPInvoiceCreditLine.ItemName,
dbo.SOPOrderReturnLine.ItemDescription, dbo.StockItem.Code, dbo.StockItem.Name,
dbo.StockItem.Description,
dbo.SOPDocDelAddress.Description AS DELADDRESSDESC,
dbo.SOPDocDelAddress.PostalName AS DELADDRESSPOSTALNAME,
dbo.SOPDocDelAddress.AddressLine1 AS DELADDRESS1,
dbo.SOPDocDelAddress.AddressLine2 AS DELADDRESS2,
dbo.SOPDocDelAddress.AddressLine3 AS DELADDRESS3,
dbo.SOPDocDelAddress.AddressLine4 AS DELADDRESS4,
dbo.SOPDocDelAddress.County AS DELADDRESSCOUNTY,
dbo.SOPDocDelAddress.Country AS DELADDRESSCOUNTRY,
dbo.SOPDocDelAddress.PostCode AS DELADDRESSPOSTCODE,
dbo.SOPOrderReturnLine.LineQuantity, dbo.SOPOrderReturnLine.LineTotalValue,
dbo.SOPOrderReturnLine.LineTaxValue,
dbo.SOPOrderReturnLine.UnitSellingPrice,
dbo.SOPOrderReturnLine.SellingUnitMultiple,
dbo.SOPOrderReturnLine.PricingUnitMultiple
FROM dbo.SOPOrderReturn INNER JOIN
dbo.SOPInvoiceCreditLine ON
dbo.SOPOrderReturn.SOPOrderReturnID = dbo.SOPInvoiceCreditLine.SOPOrderReturnID
INNER JOIN
dbo.SOPInvoiceCredit ON
dbo.SOPInvoiceCreditLine.SOPInvoiceCreditID =
dbo.SOPInvoiceCredit.SOPInvoiceCreditID INNER JOIN
dbo.SLCustomerAccount ON
dbo.SOPInvoiceCredit.CustomerID = dbo.SLCustomerAccount.SLCustomerAccountID
INNER JOIN
dbo.SOPOrderReturnLine ON
dbo.SOPOrderReturn.SOPOrderReturnID = dbo.SOPOrderReturnLine.SOPOrderReturnID
AND
dbo.SOPInvoiceCreditLine.SOPOrderReturnLineID =
dbo.SOPOrderReturnLine.SOPOrderReturnLineID INNER JOIN
dbo.StockItem ON dbo.SOPOrderReturnLine.ItemCode =
dbo.StockItem.Code INNER JOIN
dbo.SOPInvCredAddress ON
dbo.SOPInvoiceCredit.SOPInvoiceCreditID =
dbo.SOPInvCredAddress.SOPInvoiceCreditID INNER JOIN
dbo.SOPDocDelAddress ON
dbo.SOPOrderReturn.SOPOrderReturnID = dbo.SOPDocDelAddress.SOPOrderReturnID
WHERE (dbo.SLCustomerAccount.SLCustomerAccountID = 18598)
ORDER BY [Invoice Number], dbo.SOPInvoiceCreditLine.PrintSequenceNumber
--- StripMime Report -- processed MIME parts --- multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
