October 6, 2004 at 8:08 am
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
Go
Grant execute on reports.dbo.usp_AR_UnpaidInvoices to reportuser
smv929
October 6, 2004 at 9:02 am
OK. found the answer why my commands didn't execute: I left SET FMTONLY ON. Once I set it to OFF, my commands worked.
However, I still don't understand why the temp tables/table variable may cause problems for Crystal and why using FMTONLY might help. I'm only returning one result set.
Thanks.
smv929
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply