On Fri, Nov 30, 2012 at 11:22 AM, Adam Buckland <[email protected]>wrote:
> Thanks Fred, > > Invalid object name... it works ok when I reference a table, so how do I > reference a view in SQL Server is there a magic secret or isn't it possible? > ------------ > SQL will either return rows or not. This is a case of NOT. >From inside SQL Server Management Studio can you select * from yourView? and have it return records? If so then your connection into the server is not pointing to the database that holds your view. WAG here but it is pointing to master instead. :) > > TIA Adam. > > > > > -----Original Message----- > From: ProFox [mailto:[email protected]] On Behalf Of Fred Taylor > Sent: 30 November 2012 17:02 > To: ProFox Email List > Subject: Re: VFP to SQL Server View > > Your SQLEXEC must be failing (returning -1). > > Use AERROR() to see what's failing on the SQL side. > > Fred > > > On Fri, Nov 30, 2012 at 9:48 AM, Adam Buckland > <[email protected]>wrote: > > > 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/CAJidMY+Ls=ygd19n91g05g+lbu+kbm1qatxp3-owuejhiru...@mail.gmail.com ** 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.
