Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Glen Villar
  Where is Glen Villar?
 Palayan City
 Philippines
 Glen Villar
 Tags
Subject: SPT Question
Thread ID: 365551 Message ID: 365551 # Views: 63 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, December 28, 2012 1:08:36 AM         
   


Hi,

I have created a query in SQL Server 2005 which executes fine in SSMS.
However, when I try to run the query using SPT in VFP, it executes and returns
records but viewing the records with BROWSE or with grid
throws an error: "The data source for this object must be a variable reference."

VFP status bar shows SPT returns records (see image).


I was thinking that it maybe it was due to the length of the query inside the TEXT/ENDTEXT
that is causing the problem so I converted the query to Stored Procedure
and just call it from VFP but I still get the same result.

Here's the query in stored procedure.
USE [MYDATA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[gtv_sp_GetAnySale] 
    @AnyDesc nvarchar(50),
	@curyear integer 
	
AS 

    select t1.prodcode,
	t1.proddesc1, t1.xmonth, t1.[2012], t2.sellingprice,
	t1.[2012]*t2.sellingprice as [total], t1.[Paid] 
	 from
	(select c.prodcode, t3.proddesc1, MONTH(c.transdate) as xmonth, sum(coalesce(c.qtyout,0))
		-sum(coalesce(c.qtyin,0)) as [2012],
		sum(c.paymentamt)-sum(c.DiscountAmt) as [Paid]
		from
		mydata.dbo.ytransstatic as c
	left outer join mydata.dbo.productmaster as t3
	on c.prodcode = t3.prodcode
		where exists
			(select a.prodcode, a.proddesc1 from mydata.dbo.productmaster as a
			where upper(a.proddesc1) like @AnyDesc
			and a.deptcode = '004' 
			and a.groupcode1 = '0402'
			and a.prodcode = c.prodcode) 
		and year(c.transdate) = @curyear
		and UPPER(c.transtype) in ('SAL','INV')
		group by c.prodcode, month(c.transdate), proddesc1
	) as t1
	left outer join mydata.dbo.sellpricemaster as t2
	on t1.prodcode = t2.prodcode
	where t2.pricelevel = 1



Here's how I called it in VFP:
lcDesc = 'POM GRAM%'

	TEXT TO lcQuery NoShow
		Execute mydata.dbo.gtv_sp_GetAnySale ?m.lcDesc, 2012
	ENDTEXT

	norder	= SQLExec(m.lnConnect,m.lcQuery,"mytable",laError)

	nError = AERROR(laError)
	DISPLAY MEMORY LIKE laError


The command DISPLAY MEMORY LIKE laError returns the same message: "The data source for this object must be a variable reference."


As I mentioned, I can run this query in SMSS with no problem and it does run fine if I
call it via VBA in MS Excel.

At the moment, I just used excel sheet to call the stored procedure but I'm wondering
what I could be missing with this in VFP.

Thanks. :-)


(coffee)
It's never too late to be who you might have been.
-George Eliot. English Novelist (1819-1880)


http://thereportersassistant.blogspot.com
http://fruitypc.blogspot.com

ENTIRE THREAD

SPT Question Posted by Glen Villar @ 12/28/2012 1:08:36 AM
RE: SPT Question Posted by Anders Altberg @ 12/28/2012 1:24:19 AM
RE: SPT Question Posted by Glen Villar @ 12/28/2012 1:31:45 AM