Adding the database name did it... Stephen you're a star..... Don't know why it worked as the view is in the right db area.. but it does...... cheers...
-----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of Stephen Russell Sent: 30 November 2012 17:42 To: ProFox Email List Subject: Re: VFP to SQL Server View On Fri, Nov 30, 2012 at 11:32 AM, Adam Buckland <[email protected]>wrote: > Hi Stephen... > > Inside SQL Server management Console it return 2,000 records and > individually I can access the tables from the SQL console > > Great You can preface the db name in the table in your select statement. select * from myDataBase.dbo.M2M_BOOKER This will get data across any databases. Now what is the USER you are connecting with? Does that USER have rights to myDataBase? That may also be getting in the way. HTH > >From FoxPro, I can access tables no problem but not a view... same > >code , > put in a table name rather than a view and bingo... change it to the > view name and nope > > -----Original Message----- > From: ProFox [mailto:[email protected]] On Behalf Of Stephen > Russell > Sent: 30 November 2012 17:29 > To: ProFox Email List > Subject: Re: VFP to SQL Server View > > 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/[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.
