Solved

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

Posted on 2004-10-19
11
620 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

12 Experts available now in Live!

Get 1:1 Help Now