I have a problem that needs a solution, and I'm not particular about how it is solved.
Long story short, I have a script that querries 1 aggregated database against 24 other databases to check for "deleted" information. The problem I have is this:
1) I have to use the EXEC command because I am using variables in the database object names
2) I will have over 400,000 deletes to perform and I will run into log size problems if I don't compensate for it
In the past, I have copied the number of rows to be deleted into a variable, then set row count = 10000 and then just looped through the deletes until they were all done. After every delete statement, it committed to the DB and I didn't have a problem with the log file getting too big.
The problem I have is that since I'm using EXEC, I can't get my "rowcount" into a local variable. (at least from what i've read). Is there another way to get the result into a local variable, or another way to control the log file size? The method below returns "@numdeletes needs to be declared as a variable" -obviously this has to do with the scope of the variable... it is previously declared at the beginning of the script:
The part of my script that does the deletes looks like this:
Print 'Starting Deletes for ' + @rssqlServer + ' \ ' + @rssqlDB + ' (Instance ' + cast (@instid as char(2)) + ')'
select @rssqlDB = 'Data Source=' + @rssqlServer + ';User '+ @rssqlLogin + ''').'+@rssqlDB+'.dbo.rsuser'
EXEC ('@numdeletes = select count(*) From ' + @webDB + ' Where (Userid not in (select userid from OPENDATASOURCE (''SQLOLEDB'','''+ @rssqlDB + ')) and (instanceid=' + @instID + ')')
Print 'Number of Deletes to Be Performed = ' + cast (@numdeletes as VarChar)
SET rowcount 10000
--Do Deletes Here
EXEC ('delete From ' + @webDB + ' Where (Userid not in (select userid from OPENDATASOURCE (''SQLOLEDB'','''+ @rssqlDB + ')) and (instanceid=' + @instID + ')')
@numdeletes = @numdeletes - 10000