January 3, 2008 at 1:27 pm
I have a SQL 2000 stored procedure that selects data from several joined SQL 2000 views and inserts rows into a table. The SQL views are selecting data using OpenQuery on a linked server to a foxpro database. A crystal report (crystal 10) fires the stored procedure.
The problem is that the user running the report receives an out of memory error. At this point, the SQL service must be restarted or the server restarted. This does not happen 100% of the time but at least 75% of the time the report is run.
The server OS is Windows Server 2003.
SQL Server is SQL Server 2K Standard Edition.
The server is configured with 4gb of memory, standard edition SQL 2K will use a maximum of 1.8gb of memory. SQL is set to dynamically allocate memory.
The openquery results of each views is a large amount of data.
Any ideas would be appreciated. Thanks.
January 3, 2008 at 8:39 pm
If I were you, I would like to try the following.
1. Try to optimize the queries.
2. Retrieve data in parts, i.e., separate your data migration in parts.
January 4, 2008 at 7:17 am
Thanks. I will try to optimize the openquery selections in the SQL Views.
The primary SQL Stored Procedure is
insert into aaa_payroll_pay_summary_WRK --This table is always truncated prior to this insert
select b.p_level3 as Department,
a.empno as Employee,
b.p_lname as LastName,
b.p_fname as FirstName,
a.detailtype as DetailType,
a.code as Code,
case a.detailtype
when 't' then d.taxdesc
else c.stubdesc
endas Description,
sum(a.units) as Hours,
sum(a.amount) as Amount,
e.[desc] as DepartmentDesc,
0,
0,
0
from prdehist a --THE FOLLOWING ALL LINKED SERVER SQL VIEWS USING OPENQUERY ON A FOXPRO DB
left outer join hrpersnl b on a.empno=b.p_empno
left outer join prcodes c on a.code=c.code
left outer join prtaxcds d on a.code=d.taxcode and d.company='XXX'
left outer join hrtables e on b.p_level3 = e.code and e. = 'L3'
left outer join prckhist f on f.chknumber=a.chknumber and a.empno=f.empno
where a.amount <> 0
andDATEADD(d,DATEDIFF(d,0,@StartDate),0) <= f.chkdate --a.chadate
andDATEADD(d,DATEDIFF(d,0,@EndDate),0) >= f.chkdate --a.chadate
group by b.p_level3,
a.empno,
b.p_lname ,
b.p_fname ,
a.detailtype ,
a.code,
d.taxdesc,
c.stubdesc,
e.[desc]
order by b.p_level3,
a.empno,
a.detailtype,
a.code
A typical example of one of the linked server views is:
CREATE VIEW dbo.prdehist
AS
SELECT * FROM OPENQUERY( xxxx_link, 'SELECT * FROM PRDEHIST' )
I can change it to return only the fields I need and perhaps limit it by date but I still think the openquery reads through the foxpro DB sequentially so I am not sure what exactly is using up all of the memory.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply