June 6, 2005 at 2:51 pm
I have been tasked to dynamically move data from the Messages database to the MessagesArchive database. The table name in Messages is ActivityLog. The table in MessagesArchive will have the year and month attached. At the start of the month, the stored procedure will create a new table and begin archiving the the data to the new table. I have a variable declared and am creating this statement:
Insert MessagesArchive.dbo.ActivityLog2005June Select * from ActivityLogView Where SessionID = '{9C49937F-4533-4540-B121-0A776191A54A}'
This statement works if executed in the Query Analyzer. But in the stored procedure, I use EXEC and get this error:
Could not locate entry in sysdatabases for database 'Insert MessagesArchive'. No entry found with that name. Make sure that the name is entered correctly.
The EXEC thinks that MessagesArchive is the filename instead of MessagesArchive.dbo.ActivityLog2005June. Any ideas???
June 6, 2005 at 2:55 pm
Can you post the stored proc?
Why are you splitting the data into multiple tables??
The only valid time to use this technic is when you have a few M rows and that the performance is really bad, is this the case?
June 7, 2005 at 7:50 am
The archive table is extremely large. By splitting the table into multiple tables, we can backup the previous tables to CD/tape and remove from SQL.
June 7, 2005 at 7:59 am
Define extremely large??
Will you have the need to reuse the archived data later for reporting tasks?? If this is the case then I would suggest you move that data elsewhere on the db/server so that you don't need to do restores to access that data.
Also keep in mind that you can always move the old "unsused" data to another disk drive or even a linked server if you need to keep this data online.
June 7, 2005 at 8:00 am
Just to pre answer your problem, have you tried just printing the statement instead of executing it??? You'll probably see the error then.
June 7, 2005 at 8:08 am
If I do a print, I get the message:
Insert MessagesArchive.dbo.ActivityLog2005June Select * from ActivityLogView Where SessionID = '{9C49937F-4533-4540-B121-0A776191A54A}'
I can run the statement in the Query Analyzer and it will work. But if it runs from the stored procedure, I get this message:
Could not locate entry in sysdatabases for database 'Insert MessagesArchive'. No entry found with that name. Make sure that the name is entered correctly.
June 7, 2005 at 8:12 am
Have you tried :
Insert into MessagesArchive.dbo.ActivityLog2005June (col1, col2, coln)
Select col1, col2, coln from ActivityLogView Where SessionID = '{9C49937F-4533-4540-B121-0A776191A54A}'
And you didn't answer these questions :
Define extremely large??
Will you have the need to reuse the archived data later for reporting tasks?? If this is the case then I would suggest you move that data elsewhere on the db/server so that you don't need to do restores to access that data.
Also keep in mind that you can always move the old "unsused" data to another disk drive or even a linked server if you need to keep this data online.
June 7, 2005 at 8:24 am
No I have not tried that statement yet.
The archived data is no longer needed. Right now we can free up 60G by moving the off to another storage media.
June 7, 2005 at 8:28 am
Could you try that version to at least see if it works?... that way you won't have to come back here if you run into this situation again.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply