Get EXEC rowcount into local variable or control log file on deletes

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)) + ')'
  print ''
 
  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
  While (@numdeletes>0)
  Begin
    --Do Deletes Here
    EXEC ('delete From ' + @webDB + ' Where (Userid not in (select  userid from OPENDATASOURCE (''SQLOLEDB'','''+ @rssqlDB + ')) and (instanceid=' + @instID + ')')
    @numdeletes = @numdeletes - 10000
  End

DavehudsnAsked:
Who is Participating?
 
HilaireConnect With a Mentor Commented:
Both EXEC(@sql)
and sp_executesql(@sql)
allow execution of DYNAMIC sql, ie code that is defined at run time.
They create their own scope of execution/environment, wich means you don't "see" #temp tbales and local variables defined in the parent scope/environment
eg
declare @a int
set @a = 1
exec('selec @a') --> fails because @a is not declared in the new scope

The main differences between the two is that sp_executesql accepts parameters to declare variables you can "link" between the two environments.

here's a working exemple
declare @a int
exec sp_executesql N'select @a=count(*) from sysobjects', N'@a int out', @a out
select @a

There was a mistake in my previous post : to get it to work, the executed string must be declared as NVARCHAR (unicode string), and the string constants must be prefixed with N

It should be

exec sp_executeSQL N'select @numdeletes = count(*) From ' + cast(@webDB as nvarchar(200)) + N' Where (Userid not in (select  userid from OPENDATASOURCE (''SQLOLEDB'','''+ cast(@rssqlDB as nvarchar(200)) + ''')) and (instanceid=' + cast(@instID as nvarchar(200))+ N')', N'@numdeletes int out', @numdeletes OUT

Hilaire
0
 
JaffaKREECommented:
roundabout, but this should work...

(Earlier in script)
DECLARE @NumVar INT
Select @NumVar = 400
CREATE TABLE #MyTemp (Num INT)
Insert into #MyTemp
(Num)
Values (@NumVar)

(Later)
Select Num from #MyTemp
0
 
DavehudsnAuthor Commented:
Will this create an actual temp table?  Or is that a virtual type of table?

you said:

CREATE TABLE #MyTemp (Num INT)
Insert into #MyTemp
(Num)
Values (@NumVar)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
JaffaKREECommented:
Temp Table.
0
 
HilaireCommented:
use sp_executesql instead of EXEC

exec sp_executeSQL 'select @numdeletes = count(*) From ' + @webDB + ' Where (Userid not in (select  userid from OPENDATASOURCE (''SQLOLEDB'','''+ @rssqlDB + ')) and (instanceid=' + @instID + ')', '@numdeletes int out', @numdeletes OUT
  Print 'Number of Deletes to Be Performed = ' + cast (@numdeletes as VarChar)
0
 
JaffaKREECommented:
Hey Hilaire,

  Can you describe the differences between exec and sp_executeSQL ?

0
 
DavehudsnAuthor Commented:
Hilaire -

I am getting the same error I was getting before .... Incorrect syntax near '+'

Are you sure you are allowed to pass Object Names as variables via this method?   That was why I had to use EXEC to begin with....
0
 
DavehudsnAuthor Commented:
Hilaire -

I was getting close, and ended up here.  I created a variable called "@sqlcommand" and stuck my command in there.... seems to execute, but is having problems with the "parameters" now:

select @sqlcmd = 'select @numdeletes = count(*) From ' + @webDB + ' Where (Userid not in (select  userid from OPENDATASOURCE (''SQLOLEDB'','''+ @rssqlDB + ')) and (instanceid=' + cast (@instID as varchar) + ')'
   
  exec sp_executeSQL @sqlcmd, '@numdeletes int out', @numdeletes OUT

Yields:

Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 37
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
0
 
JaffaKREECommented:
Should the Unicode 'N' prefix always be used when passing strings to apps ?
0
 
DavehudsnAuthor Commented:
-Ah!  Success!

I had to do a combination of your scripting and mine to get it to work.  I broke out the sql command into a separate string and then it worked - brilliant!

Here is the final result - notice that I did not have to put the N' in my version of the sqlcmd string.....

select @sqlcmd = 'select @numdeletes = count(*) From ' + @webDB + ' Where (Userid not in (select  userid from OPENDATASOURCE (''SQLOLEDB'','''+ @rssqlDB + ')) and (instanceid=' + cast (@instID as varchar) + ')'

exec sp_executeSQL @sqlcmd, N'@numdeletes int out', @numdeletes OUT

Yields Success! -->

Number of Deletes to Be Performed = 3   (don't worry, this is my testDB - we didn't go through all this just for 3 deletes ;)                                                
0
 
HilaireCommented:
>>notice that I did not have to put the N' in my version of the sqlcmd string.....<<
OK if your @sqlcmd is declared as nvarchar, there's an automatic casting
since nvarchar has a higher precedence than varchar

Glad I could help and thks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.