Report Services - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Report Services

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-03 : 13:43:36

Not sure if I am in the right forum, but I will try and ask my question anyway.

I created a report in Reporting Services, but this time instead of using a query or a table, I am using the storeprocedure. My one report works fine with it, but i have one report that is not working like this. It says that it keep running out of memory.

This is what I keep getting.....

exception of type 'system.outofmemoryexception' was thrown

This is what I have in the one line....

EXEC P_Historic_TaxID_Report @bottomdate, @topdate, @claimTypes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-05 : 06:53:01

is the sp returning large resultset? whats the execution time for sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-07 : 10:09:28

Yeah the sp is returning large results. I have one sp that works okay like this, but my other one does not. See my sp below. Maybe I can do something to make it not do this. This is my first time trying to write sp with Scalar.

@bottomdate datetime,
@topdate datetime,
@claimTypes Varchar

AS

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[Historic_TaxID_Report]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[Historic_TaxID_Report]

Create Table Historic_TaxID_Report(

[claimTypes] [varchar](4) NOT NULL,
[PROVIDERTAXIDNUMBER] [varchar](9) NULL,
[PROCESSEDDATE] [datetime] NULL,
[clm_att1] [varchar](2) NULL,
[clm_att2] [varchar](2) NULL,
[clm_att3] [varchar](2) NULL,
[clm_att4] [varchar](2) NULL,
[claimnumber1] [varchar](20) NULL,
[CLAIMNUMBER] [varchar](20) NULL,
[CLAIMSTATUS] [varchar](4) NULL,
[TYPEOFBILL] [varchar](3) NULL,
[STATEMENTFROMDATE] [datetime] NULL,
[STATEMENTTODATE] [datetime] NULL,
[TOTALCHARGES] [decimal](10, 2) NULL,
[ALLOWEDCHARGES] [decimal](10, 2) NULL,
[SAVINGS] [decimal](10, 2) NULL,
[PCT of Savings] [decimal](27, 13) NULL,
[ProviderFullName] [varchar](45) NULL,
[PROVIDERSTATE] [varchar](2) NULL,
[ProviderCity] [varchar](30) NULL,
[Providerpostalcode] [varchar](13) NULL,
[PROVIDERCOUNTY] [varchar](25) NULL,
[PROVIDERTAXIDNUMBERKEY] [varchar](9) NULL,
[EMPLOYERGROUPNAME] [varchar](30) NULL,
[CLIENTNAME] [varchar](35) NULL,
[PROVIDERTYPECODE] [varchar](4) NULL,
[ProviderOptios] [varchar](4) NULL,
[EMPLOYERNAME] [varchar](30) NULL,
[PROVIDERGROUPNAME] [varchar](35) NULL,
[PROVIDERID] [varchar](8) NULL,
[MEMBERNUMBER] [varchar](30) NULL,
[LASTNAME] [varchar](20) NULL,
[CLIENTNUMBER] [varchar](17) NULL,
[PATIENTLASTNAME] [varchar](20) NULL,
[PATIENTFIRSTNAME] [varchar](15) NULL,
[clientID] [varchar](8) NULL,
[cli_altid] [varchar](30) NULL,
[clm_adjto] [varchar](8) NULL,
[clm_adjfm] [varchar](8) NULL,
[clm_adjsc] [varchar](2) NULL,
[Pro_County] [varchar](25) NULL,
[New3digitZip] [varchar](3) NULL,
[PrimaryDX] [varchar](6) NULL,
[Specialty] [varchar](3) NULL,
[SpecialtyDescription] [varchar](35) NULL
)

select * INTO #Historic_TaxID_10_Greater from execreports.dbo.VW_Historic_TaxID_Report
where PROVIDERTAXIDNUMBER in (

Select
PROVIDERTAXIDNUMBER

From execreports.dbo.VW_Historic_TaxID_Report
where [PROCESSEDDATE] >= @bottomdate and [PROCESSEDDATE] <= @topdate
group by PROVIDERTAXIDNUMBER

HAVING COUNT (PROVIDERTAXIDNUMBER) >= 10)
ORDER BY PROVIDERTAXIDNUMBER

Insert INTO Historic_TaxID_Report( claimTypes,PROVIDERTAXIDNUMBER,PROCESSEDDATE,clm_att1, clm_att2, clm_att3, clm_att4, claimnumber1,
CLAIMNUMBER,CLAIMSTATUS,TYPEOFBILL,STATEMENTFROMDATE,STATEMENTTODATE,TOTALCHARGES, ALLOWEDCHARGES,
SAVINGS,[PCT of Savings],ProviderFullName,PROVIDERSTATE,ProviderCity,Providerpostalcode,PROVIDERCOUNTY,
PROVIDERTAXIDNUMBERKEY,EMPLOYERGROUPNAME,CLIENTNAME,PROVIDERTYPECODE, ProviderOptios, EMPLOYERNAME,
PROVIDERGROUPNAME,PROVIDERID,MEMBERNUMBER,LASTNAME,CLIENTNUMBER,PATIENTLASTNAME,PATIENTFIRSTNAME,
clientID,cli_altid,clm_adjto,clm_adjfm,clm_adjsc,Pro_County,New3digitZip,PrimaryDX,Specialty,
SpecialtyDescription)

SELECT DISTINCT

claimTypes,
PROVIDERTAXIDNUMBER,
PROCESSEDDATE,
clm_att1,
clm_att2,
clm_att3,
clm_att4,
claimnumber1,
CLAIMNUMBER,
CLAIMSTATUS,
TYPEOFBILL,
STATEMENTFROMDATE,
STATEMENTTODATE,
TOTALCHARGES,
ALLOWEDCHARGES,
SAVINGS,
PCT_of_Savings,
ProviderFullName,
PROVIDERSTATE,
ProviderCity,
Providerpostalcode,
PROVIDERCOUNTY,
PROVIDERTAXIDNUMBERKEY,
EMPLOYERGROUPNAME,
CLIENTNAME,
PROVIDERTYPECODE,
ProviderOptios,
EMPLOYERNAME,
PROVIDERGROUPNAME,
PROVIDERID,
MEMBERNUMBER,
LASTNAME,
CLIENTNUMBER,
PATIENTLASTNAME,
PATIENTFIRSTNAME,
clientID,
cli_altid,
clm_adjto,
clm_adjfm,
clm_adjsc,
Pro_County,
New3digitZip,
PrimaryDX,
Specialty,
SpecialtyDescription

From #Historic_TaxID_10_Greater

select * from Historic_TaxID_Report

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources