I import data from FoxPro on a regular basis(until we can convert legacy applications to use SQL Server).
The following code works fine once or twice but then our server begins to run low on memory. I know that this is not unusual with SQL Server as it is designed to use as much memory as it needs and then release the memory when the O/S requests it back
BUT
the hourly tran log backups begin to fail, I can't even save DTS packages to the server, ...
Sometimes stopping and restarting SQL Server does the job but sometimes we have to reboot the Windows server to get the backup jobs to run again(using SQL LiteSpeed)
Running Windows 2003 Server, SS2000 Enterprise Ed. SP3a,
4GB RAM, Quad Xeon Processors.
Thanks in advance for your help,
gc
The Code below,
(the first 3 SET lines were inserted to correct the problem but have had no effect)
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET REMOTE_PROC_TRANSACTIONS OFF
insert into dest_table
select
*
from openquery(VFP_FOXPROD,'
select
claimno,
dtoc(date_occur) as date_occur, dist_code, dist_name, owner, o_add, repair, r_add,
eng_org, reg_no, pro_r_hrs, full, fr_go_to, rec_date, app_date, rel_date, eng_cred,
prorataamt, totlistpr, instantcrd, crmemonum, cred_amt1, memo_num1,
dtoc(memo_date1) as memo_date1, credsaved1, cred_amt2,
memo_num2, memo_date2, credsaved2, cred_amt3, memo_num3, memo_date3, credsaved3, cred_amt4, memo_num4,
memo_date4, credsaved4, appl_prt, appl_desc, prt_serl, prt_in_svc, prt_acc_hr, code_gp, code_comp, code_cond1,
code_cond2, code_loc, cd_comp_hs, cd_source, cd_review, o_add2, o_city, o_state, o_cntry, o_zip, r_add2,
r_city, r_state, r_cntry, r_zip, eng_n_s, condrpt, condapp, cond, tcm_whom, mag_serial,
dtoc(mag_in_s) as mag_in_s, mag_hrs,
dtoc(eng_in_s) as eng_in_s, editflag, newflag, tbo_hrs, freight, frght_all,
corevalue, reviewed, direct, direct_n, tcm_app, retgdate, rettype, carrier, waynumber, mag_lh, mag_rh, ig_har,
turbo_bk, exh_sys, intercool, fr_brk, log_book, ecrpt, c_cal_for, anal_date, analytical, analdispo,
recdispo, ship_out, ab_shipno, ab_recdate, disassem, arecwklst, aprecwklst, atrecwklst, rechold
from source_table
')
The following code works fine once or twice but then our server begins to run low on memory. I know that this is not unusual with SQL Server as it is designed to use as much memory as it needs and then release the memory when the O/S requests it back
BUT
the hourly tran log backups begin to fail, I can't even save DTS packages to the server, ...
Sometimes stopping and restarting SQL Server does the job but sometimes we have to reboot the Windows server to get the backup jobs to run again(using SQL LiteSpeed)
Running Windows 2003 Server, SS2000 Enterprise Ed. SP3a,
4GB RAM, Quad Xeon Processors.
Thanks in advance for your help,
gc
The Code below,
(the first 3 SET lines were inserted to correct the problem but have had no effect)
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET REMOTE_PROC_TRANSACTIONS OFF
insert into dest_table
select
*
from openquery(VFP_FOXPROD,'
select
claimno,
dtoc(date_occur) as date_occur, dist_code, dist_name, owner, o_add, repair, r_add,
eng_org, reg_no, pro_r_hrs, full, fr_go_to, rec_date, app_date, rel_date, eng_cred,
prorataamt, totlistpr, instantcrd, crmemonum, cred_amt1, memo_num1,
dtoc(memo_date1) as memo_date1, credsaved1, cred_amt2,
memo_num2, memo_date2, credsaved2, cred_amt3, memo_num3, memo_date3, credsaved3, cred_amt4, memo_num4,
memo_date4, credsaved4, appl_prt, appl_desc, prt_serl, prt_in_svc, prt_acc_hr, code_gp, code_comp, code_cond1,
code_cond2, code_loc, cd_comp_hs, cd_source, cd_review, o_add2, o_city, o_state, o_cntry, o_zip, r_add2,
r_city, r_state, r_cntry, r_zip, eng_n_s, condrpt, condapp, cond, tcm_whom, mag_serial,
dtoc(mag_in_s) as mag_in_s, mag_hrs,
dtoc(eng_in_s) as eng_in_s, editflag, newflag, tbo_hrs, freight, frght_all,
corevalue, reviewed, direct, direct_n, tcm_app, retgdate, rettype, carrier, waynumber, mag_lh, mag_rh, ig_har,
turbo_bk, exh_sys, intercool, fr_brk, log_book, ecrpt, c_cal_for, anal_date, analytical, analdispo,
recdispo, ship_out, ab_shipno, ab_recdate, disassem, arecwklst, aprecwklst, atrecwklst, rechold
from source_table
')