Store Procedure Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Store Procedure Problem

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-10 : 09:57:45

I am having problems with my store procedure. This works when I am in SQL, but when I put it into my Reporting Services it keeps timing out or it says Exception of type 'System.OutOfMemoryException' was thrown.

What do I need to do to fix this so I dont' get that problem?

@bottomdate datetime,
@topdate datetime,
@claimTypes Varchar (50)

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
)

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]

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 and
claimTypes = @claimTypes ----'HCFA'
group by PROVIDERTAXIDNUMBER

HAVING COUNT (PROVIDERTAXIDNUMBER) >= 10)
and claimTypes = @claimTypes ---'HCFA'
ORDER BY PROVIDERTAXIDNUMBER,claimTypes

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
Order by PROVIDERTAXIDNUMBER

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 10:13:28

How many records does the SP return?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-06-10 : 10:22:01

Hmm....

I just ran this,

USE [ExecReports]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[P_Historic_TaxID_Report]
@bottomdate = N'05/01/2010',
@topdate = N'06/01/2010',
@claimTypes = N'HCFA'

SELECT 'Return Value' = @return_value

GO

and it didn't just give me that date range.

There was over 286,660

Sachin.Nand

2937 Posts

Posted - 2010-06-10 : 10:26:00

Can you try with a higly selective parameters that can give very less no of records and then try whether the same error gets replicated in SSRS?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources