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
---
