Archiving Report Data

MS SQL Server express is limited to 4 GB database size. If your file approaches that size you can easily move report data (that takes the most storage space) to a new database to free up space in your main database. The moved data can still be accessed by Medchart although it is located in the new database. Follow the steps below:

• Start Microsoft SQL Server Mabagemment Studio Express
• backup your database before proceding
• Right click Databases and click "create new database"
• Enter a name for the database such as “medical2”
• click OK to save chages
• right click the new database and select "new query"
• enter the command below in the query window:

CREATE TABLE [dbo].[rep_im](
[rep_id] [int] NULL,
[ole] [image] NULL,
[id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

• click the "execute" (!) button to run the command

• right click the new database *eg. medical\2) and select "new query"
• enter the command below in the query window:

INSERT INTO rep_im
(ole, rep_id, id)
SELECT ole, rep_id, id
FROM medical.dbo.rep_im AS rep_im_1

• click the "execute" (!) button to run the command (may take a few minutes to run as it copies data from the old to new database)

• right click you old "medical" database and select "new query
• enter the command below:

DELETE FROM rep_im

• click the "execute" (!) button to run the command (may take a few minutes)
• in the old "medical" database right click programability then right click "stored procedures"
• right click the procedure named "stp_get_report" and select "modify"
• find the lines below:

NSERT INTO #rep_search
(ole, rep_id)
SELECT ole, rep_id
FROM rep_im
where rep_id= @rep_id

if @@rowcount=1
begin
goto skip
end

• immediately folowing the lines above- insert the text below (change the database name to the same as the new database you created):

INSERT INTO #rep_search
(ole, rep_id)
SELECT ole, rep_id
FROM medical5.dbo.rep_im
where rep_id= @rep_id

if @@rowcount=1
begin
goto skip
end

• click the "execute" (!) button to run the command
• right click the "medical" databse and select "tasks" then "Shrink" to reclaim databse space
• backup the new database

These steps can be repeated indefinately to allow unlimited storage of reports.

updated May 13, 2009