Solved

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

Posted on 2004-10-19
11
623 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:Davehudsn
  • 4
  • 4
  • 3
11 Comments
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12348589
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
 

Author Comment

by:Davehudsn
ID: 12348683
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
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12348697
Temp Table.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 26

Expert Comment

by:Hilaire
ID: 12349084
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
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12349876
Hey Hilaire,

  Can you describe the differences between exec and sp_executeSQL ?

0
 

Author Comment

by:Davehudsn
ID: 12351855
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12351935
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
 

Author Comment

by:Davehudsn
ID: 12351954
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
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12351977
Should the Unicode 'N' prefix always be used when passing strings to apps ?
0
 

Author Comment

by:Davehudsn
ID: 12352054
-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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12352101
>>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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question