Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-19
11
Medium Priority
?
634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

618 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