July 30, 2008 at 5:26 am
Hi All,
I want to create a stored procedure, or even better an SQL Agent job that runs at 11:00pm at night.
We are working from a dialler, which records all calls, and stores where each call recording was saved both directory & filename in a table with the call id.
But we have limited space on the server where these calls are kept, and as such need to move them. This is annoying but the real problem is listening to the calls again. We use an application which looks up the directory location & filename in the DB. If we have moved them then this no longer works.
What I want is a one off solution;
1.Looks up recordings in my table which are exactly 31 (Not Under or Over) days old where the directory locations starts with \\10.2.1.20.
2.Cuts & Pastes these call recording to my new location (let’s say Server02\) .
3.Updates the directory in the recording table with the new location.
My SQL for some of the steps is as follows;
SELECTHistoryID, Type, VoxFilePath, VoxFileName
FROM Recording
WHERE(CONVERT(VARCHAR, RECSTART, 103) = CONVERT(VARCHAR, GetDate()-31, 103)) AND LEFT(VoxFilePath, 11) = '\\10.2.1.20'
DECLARE @strSQL as Varchar(8000)
SET @strSQL = 'COPY' ' & VoxFilePath & VoxFileName
exec xp_cmdshell @strSQL
Go
I'm not sure but I guess I will need to alter the select statement to use the top 1 only, and then loop until no more records are avail. starting with \\10.2.1.20 and 31 days old.
July 30, 2008 at 5:39 am
Hi
Use this to change the value in field [VoxFilePath] for all records that are 31 days old.
UPDATE Recording
SET VoxFilePath = REPLACE(VoxFilePath,'\\10.2.1.20','\\serv02')
WHERE DATEDIFF(day, RECSTART, GetDate()) = 31
Put this code in an Agent-job to execute it on a daily base.
July 30, 2008 at 6:05 am
Great, Is there also a solution to SQL cutting and pasting the calls recordings to another location as part of this process?
July 30, 2008 at 6:16 am
In my opinion you should let file handling be done by the OS and not SQL, because each is made for a different purpose.
Most likely you can find programs or code-snippets on the internet to move files from one location to another. Run this within a Windows Scheduled task and you have what you want...:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply