SQL proc doesnt run in Crystal--temp tables? Set FMTONLY?

[smv929]

The procedure at the bottom of this message works only when run in Query Analyzer, but does not work when run in Crystal. It just hangs. Might have something to do with the way I'm using temp tables and possibly the table variable. I found one comment regarding this situation: It said try calling the procedure by wrapping with SET FMTONLY OFF/ON as shown below...

SET FMTONLY OFF
dbo.usp_AR_UnpaidInvoices 45,'08R'
SET FMTONLY On

This made it work in Crystal, but later quit working after I made some changes to the procedure (only changing the name of two variables).

One other funny thing happend during debugging: In Query Analyzer, I would drop the procedure and re-execute the CREATE proc command. But the change would not take effect unless I opened a new window/connection and executed it (with same username) or modified it in Enterprise Manager. The same thing happened with permissions. The Grant EXECUTE command wouldn't really take effect unless I opened a new window or used EM.

1. Can anyone see why the following wouldn't work in Crystal?
2. Does anyone know why SET FMTONLY OFF/ON would make a diffrence and when it's necessary?
3. Do you see anything that might cause the connection to quit actually executing/updating the database.

--Procedure----------------------------------------------------
Create procedure dbo.usp_AR_UnpaidInvoices
@Days tinyint = 30,
@RegionCodeList varchar(500)
as
SET NOCOUNT ON
SET FMTONLY OFF
DECLARE @TempList table (RegionCode varchar(10))
DECLARE @RegionCode varchar(10), @Pos int

/*** For Testing ***********************************
DECLARE @RegionCodeList varchar(500)
DECLARE @Days tinyint
Set @RegionCodeList ='3M'
Set @Days =45
***************************************************/

--Parse the @RegionCode string to get each customer number. It extracts individual RegionCodes
--from the comma separated list, inserts the RegionCodes into a table variable, and then
--joins the table variable with the SQL table, to get the requested results.
SET @RegionCodeList = LTRIM(RTRIM(@RegionCodeList))+ ','
SET @Pos = CHARINDEX(',', @RegionCodeList, 1)

IF REPLACE(@RegionCodeList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
--For cust num - pad with spaces
--SET @RegionCode = right(' ' + LTRIM(RTRIM(LEFT(@RegionCodeList, @Pos - 1))),7)
--For region code -- don't pad
SET @RegionCode = LTRIM(RTRIM(LEFT(@RegionCodeList, @Pos - 1)))
IF @RegionCode <> ''
BEGIN
INSERT INTO @TempList (RegionCode) VALUES (@RegionCode) --Use Appropriate conversion
END
SET @RegionCodeList = RIGHT(@RegionCodeList, LEN(@RegionCodeList) - @Pos)
SET @Pos = CHARINDEX(',', @RegionCodeList, 1)

END
END

--Look at temp table of parsed RegionCodes
--select * from @TempList


--Drop temp tables if they already exist
IF OBJECT_ID('tempdb..#temp_AR_UnpaidInvoices') IS NOT NULL
Begin
Drop Table #temp_AR_UnpaidInvoices
End
IF OBJECT_ID('tempdb..#temp_AR_UnpaidInvoices_Overdue') IS NOT NULL
Begin
Drop Table #temp_AR_UnpaidInvoices_Overdue
End


--Get Unpaid Invoices
select [inv-num],
Region = customer.[charfld3],
TotalInvoice$ = sum (case when type='I' then amount else 0 end),
Paid$ = sum (case when type='P' then amount else 0 end),
Credits$ = sum (case when type='C' then amount else 0 end),
Debits$ = sum (case when type='D' then amount else 0 end),
Unpaid$ = ( sum (case when type='I' then amount else 0 end)
- sum (case when type='P' then amount else 0 end)
- sum (case when type='C' then amount else 0 end)
+ sum (case when type='D' then amount else 0 end))
into #temp_AR_UnpaidInvoices
from reports.dbo.vw_artran vw_atran
left join corp.dbo.customer customer on vw_atran.[Cust-Num] = customer.[Cust-Num] and customer.[Cust-seq] = 0
--Join with the table variable containing the specified (input) region codes
JOIN @TempList TempList ON customer.[charfld3] = TempList.RegionCode

-- where [charfld3]='3M '
-- [cust-num] in (' 1',' 2')
--and site in ('cvky','rkil')
group by [inv-num], customer.[charfld3]
having ( sum (case when type='I' then amount else 0 end)
- sum (case when type='P' then amount else 0 end)
- sum (case when type='C' then amount else 0 end)
+ sum (case when type='D' then amount else 0 end)) >0


--Create index on temp table
create index temp_idx_unpaidInv01 on #temp_AR_UnpaidInvoices([inv-num])

--Determine Unpaid Invoices that are overdue
select
vw_artran.Site,
#temp_AR_UnpaidInvoices.*,
InvDate_Oldest = (select min(a.[inv-date])
from vw_artran a
where a.[inv-num] = vw_artran.[inv-num] and a.site = vw_artran.site)
into #temp_AR_UnpaidInvoices_Overdue
from #temp_AR_UnpaidInvoices #temp_AR_UnpaidInvoices
join vw_artran vw_artran on #temp_AR_UnpaidInvoices.[inv-num] = vw_artran.[inv-num]

--Create index on temp table
create index temp_idx_unpaidInv02 on #temp_AR_UnpaidInvoices_Overdue([inv-num])

--Return info about Unpaid Invoices that are overdue
select distinct
CustomerNum = [inv-hdr].[cust-num],
#temp_AR_UnpaidInvoices_Overdue.*,
CustPO = [inv-hdr].[cust-po],
DaysOld = datediff(d, InvDate_Oldest,dateadd(dd,0,getdate())),
MaxInvDate = convert(char,dateadd(dd,-@Days,getdate()),101),
[Ship Date] = convert(char,dateadd(dd,-@Days,[inv-hdr].[Ship-Date]),101),
LineItemNum = [inv-item].[co-line],
Item = [inv-item].[item],
ItemDesc = [item].[description],
LineQtyInvoiced = [inv-item].[qty-invoiced],
[InvoiceLineTotal] = [inv-item].[qty-invoiced] * [inv-item].[price]
from #temp_AR_UnpaidInvoices_Overdue #temp_AR_UnpaidInvoices_Overdue
left join reports.dbo.vw_invhdr [inv-hdr] on [inv-hdr].[inv-num] = #temp_AR_UnpaidInvoices_Overdue.[inv-num] --and [inv-hdr].[cust-num] =rs1.[cust-num]
left join reports.dbo.vw_invitem [inv-item] on [inv-item].[inv-num] = [inv-hdr].[inv-num] and [inv-item].[inv-seq] = [inv-hdr].[inv-seq] and [inv-hdr].site = [inv-item].site
left join corp.dbo.item [item] on [inv-item].[item] = [item].[item]
where #temp_AR_UnpaidInvoices_Overdue.invDate_Oldest < dateadd(dd,-@Days,getdate())

SET FMTONLY ON
--Allow reportuser to run this
Go

 

[synapsevampire]

The last thing in your proc should be the select (in most cases), that might be the problem.

A nicely thorough post, yet you make no mention of Crystal version, Database type (of course Qeury Analyzer tipped me off) or it's version.

Do you have to have the SET FMTONLY ON and GO after the code?

I'm a little rusty on SQL Server now as I've been back on Oracle for the past few years.

-k

 

[vidru]

I ran some tests in QA, trying to create a procedure playing around with SET FMTONLY ON/OFF, and had some flaky results. For example, if FMTONLY is ON in a QA session, nothing will execute. A SELECT statement will result in nothing but the column headers, and a CREATE PROC statement will result in 'The command(s) completed successfully', although the procedure didn't get created.

I don't see anything obvious in the structure of your procedure that would keep Crystal from getting a result set. I think the problem is directly related to the use of FMTONLY.

Get rid of the FMTONLY stuff, and report back.

-dave

 

[smv929]

Thanks guys. By the way, I'm using Crystal 10 and SQL Server 2000 with sp3.

vidru/Dave: you are correct. FMTONLY is what was causing the flaky results I saw. The only reason i used it was because someone mentioned that it was a way to solve a problem with temp tables/crystal. It allowed the crystal report to work. But then later, it quit working -- don't know why. I suspect that I left the setting to ON which causes problems or Crystal when referring to the procedure. Anyway, I have regrouped, created a new Crystal Report file and commented out the FMTONLY statements. It works fine. For whatever reason, I'm not getting the error from Crystal Reports that drove me to seek out the FMTONLY command. Can't explain. Works now.

 

[synapsevampire]

OK, I became curious and read up on the FMTONLY.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_2ttl.asp

The first remark is "No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON."

I guess I don't understand why it would ever work.

-k