Solved

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

Posted on 2004-10-19
11
621 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now