Archive Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Archive Stored Procedure

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-12-04 : 10:43:04

I have a stored procedure that archives data from a live database into an archive database using a series of INSERT INTO ArchiveDB..TblName SELECT FROM TblName. The problem is that the name of the archive db can change and should optimally be passed into the stored procedure as a parameter. But I can't use INSERT INTO @DbName..TblName, and I am shying away from dynamic SQL. Any other ideas?
I am using SQL 7.

Thanks.

Sarah Berger MCSD

1fred
Posting Yak Master

158 Posts

Posted - 2003-12-04 : 11:12:47

You can use a language like VB to build your stored procedure with your database name then execute that stored procedure

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-04 : 11:31:26

This may be an option if your proc is < varchar(8000)


--create proc template
create proc up_archive 
as 
select '<!Template_Variable!>' as Variable
GO
exec up_archive
GO

--change proc template
drop proc up_archive

declare @script varchar(7800),
	@osql varchar(8000)

set @script =  '"' + 'create proc up_archive as select ''<!Template_Variable!>'' as Variable"'
set @script = replace(@script,'<!Template_Variable!>','SomeOtherVariable')

--rebuild the proc using osql
set @osql = 'osql -U u37pr32 -P u37pr32*01 -S DetroitSQL02 -d Ford300 -q ' + @script

--select @osql
exec master..xp_cmdshell @osql, no_output
go

--View changed results
exec up_archive
drop proc up_archive

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-04 : 12:18:24

Building your stored procedure in VB or anywhere else is the same thing as dynamic sql. You'll get the same performance hit. The same thing is true for the osql option. You want the stored procedure to already be built. So you are going to get the performance hit either way, so just build the stored procedure with dynamic sql.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-12-04 : 12:27:59

If this is a procedure that you run once a day who cares. If it runs every minute, then you need to reconsider using code that needs to be parsed each run.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-04 : 12:29:54

How about just creating a stored procedure for each database? And when new ones appear, create more.

Tara

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-12-04 : 12:36:58

This proc doesn't run too often, maybe once a year

, so I suppose I'll just do it the dynamic SQL way in SQL Server.

Tara, making separate stored procedures in each database isn't really an issue here. The scenario I'm describing is that this is a distributed database solution, so each clients live database is named differently and of course their archive database is named something like ClientNameArchive. Also, a client can have more than one archive database if they have more than one live database.

Sarah Berger MCSD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-04 : 12:57:27

It seems to me that the database name should be consistent like Archive if you have one archive database for each live database. If you have multiple live databases, then have an instance for each database that way you can have an Archive database.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-04 : 13:11:20

quote:
Originally posted by tduggan

Building your stored procedure in VB or anywhere else is the same thing as dynamic sql. You'll get the same performance hit. The same thing is true for the osql option. You want the stored procedure to already be built. So you are going to get the performance hit either way, so just build the stored procedure with dynamic sql.

Tara

My understanding was that the sp is recreated once for each unique db where the archive needs to occur.
So you could use vb or qsql to create the proc once, replacing the template values with the specific DB values.

And then run the newly created proc for you archiving needs.

This is not the same as dynamic SQL.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-04 : 13:15:06

I know it's not the same as dynamic sql. But it has the same performance hit when you build things on the fly. If it is only to be built once, then just build it in Query Analyzer. Why bother with VB or any other tool?

Tara

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-12-04 : 15:38:28

Scenario: I have a model database distributed to many clients. Client renames the db to whatever they want and fill it with data. Some clients may need several databases, like if they operate several sites and do not want data to be shared (think franchise). Then they have databases like this: DBHello,DBGoodBye,DBNiceDay,NiceDayArchive,GoodByeArchive,HelloArchiveor maybe only 1 archive called Archive, depending on how they want it. I don't want to have to be on top of things to always force the naming convention of DBNameArchive.
In each of the MySite (nonarchive) databases, there is this stored procedure, which gets called from the front end whenever a user wants to archive a single customer or multiple customers. That's why it's a stored procedure and not something to be run in QA. Also, its only built once, but run more than once.

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-12-04 : 15:43:26

Which brings me to another problem:

On the payments and order tables in the regular (nonarchive) databases, I have DELETE triggers that logs the deleted data out to a log table in the regular (nonarchive) database. But since the archiving procedure uses INSERT INTO archive .... DELETE FROM current statements, it causes archived rows to be logged into the log table, which is incorrect because technically those rows are not deleted, but archived. I am aware I could do something like ALTER TABLE DISABLE TRIGGER but this causes problems until DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS are run because the table schema changes and all open front-end cursors become invalid. Setting the database to Single-User is also out of the question because archiving can be done for a single customer at any time, it's not like a major job that only gets run at night.

Sarah Berger MCSD

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2003-12-04 : 15:56:13

Just read the last message of the thead...

You could use a specific login to run the archiving stored procedure, and code your trigger to not log the delete, if the DELETE is executed by a specific user.

For example:

CREATE TRIGGER TriggerName
ON TableName
FOR DELETE
AS
BEGIN
IF USER_NAME() <> 'Archiver'
BEGIN
--Log the data here
END
END

--
HTH,
Vyas
http://vyaskn.tripod.com

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-12-04 : 16:09:16

Humph...

Sarah Berger MCSD

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-04 : 16:15:50

quote:
Client renames the db to whatever they want and fill it with data.

Would it be possible to create a Template Archive Procedure which is sent to the Client DBA as part of the "System Setup" which the client modifies to match their specific <DB..Table>.

quote:
I have DELETE triggers that logs the deleted data out to a log table in the regular (nonarchive) database. But since the archiving procedure uses INSERT INTO archive .... DELETE FROM current statements, it causes archived rows to be logged into the log table, which is incorrect because technically those rows are not deleted, but archived.

How about creating flags on those tables which indicate delete/archive and evaluate this in the trigger.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-04 : 16:24:45

When I mentioned Query Analyzer, I meant that the stored procedure should be built there, not execute there.

Tara

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-12-04 : 22:08:17

Ehorn, thanks for the flag idea. I think I might go with that.
As for the Archive template idea, it's possible but very difficult. Clients are non-technical medical people, and they don't have DBA's. We (the software vendor) would have to be on top of them to set the whole thing up properly, and some of them would be using MSDE which makes the thing more difficult as it has no client tools.

Tara, good point about QA. I always do my scripting in there, occasionally sneaking into Enterprise Manager for some graphical interface.

Sarah Berger MCSD

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-05 : 07:19:41

Haven't read the whole of the thread but have a look at
http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources