SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor Not Working As Expected


Cursor Not Working As Expected

Author
Message
donne4real
donne4real
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
Here is my code but for some reason, it seems not to be working correctly. It is not going through the loop as expected and will appreciate it if you guys can help me with it. I added some print statements within the code to be able to see where it is getting stuck and it seems to be within the loop where the issue is.

BEGIN

declare @Cmd varchar(255)
declare @filename varchar(255)
declare @expectedfilename varchar(255)
declare @batfilename varchar(255)
declare @sys_error int
declare @outputcount int

declare DailyMail_Cursor cursor local fast_forward
for
select filename from dailymail
open DailyMail_Cursor

fetch next from DailyMail_Cursor into @filename
while @@fetch_status = 0


begin
select @expectedfilename = expectedfilename from FILEDIRECTORY
select @batfilename = batfilename from FILEDIRECTORY where @expectedfilename =@filename
print 'test'

select @outputcount = count(*) from filedirectory where @expectedfilename =@filename
print @outputcount

if @outputcount <>0
--if @expectedfilename = @filename
print 'after if stmt'
begin
print 'after the inner begin stmt'
select @Cmd = 'C:\test\' + @batfilename
print @cmd
EXECUTE @sys_error = master.dbo.xp_cmdshell @cmd--, no_output
end
fetch next from DailyMail_Cursor into @filename
end

print 'after begin'
--
END

close DailyMail_Cursor

deallocate DailyMail_Cursor


SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63545 Visits: 18570
donne4real (1/6/2010)
Here is my code but for some reason, it seems not to be working correctly. It is not going through the loop as expected and will appreciate it if you guys can help me with it. I added some print statements within the code to be able to see where it is getting stuck and it seems to be within the loop where the issue is.

BEGIN

declare @Cmd varchar(255)
declare @filename varchar(255)
declare @expectedfilename varchar(255)
declare @batfilename varchar(255)
declare @sys_error int
declare @outputcount int

declare DailyMail_Cursor cursor local fast_forward
for
select filename from dailymail
open DailyMail_Cursor

fetch next from DailyMail_Cursor into @filename
while @@fetch_status = 0


begin
select @expectedfilename = expectedfilename from FILEDIRECTORY
select @batfilename = batfilename from FILEDIRECTORY where @expectedfilename =@filename
print 'test'

select @outputcount = count(*) from filedirectory where @expectedfilename =@filename
print @outputcount

if @outputcount <>0
--if @expectedfilename = @filename
print 'after if stmt'
begin
print 'after the inner begin stmt'
select @Cmd = 'C:\test\' + @batfilename
print @cmd
EXECUTE @sys_error = master.dbo.xp_cmdshell @cmd--, no_output
end
fetch next from DailyMail_Cursor into @filename
end

print 'after begin'
--
END

close DailyMail_Cursor

deallocate DailyMail_Cursor




I recommend changing this to a set based method.

You are missing a ' at the end of your select @Cmd statement.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

donne4real
donne4real
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
I think I should have added more information. I have 2 tables. It loops through the table to see if a filename exists in both. And if it does, calls the specified batch file (@batfilename). There are different batch files for the different filenames.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17999 Visits: 10039
donne4real (1/6/2010)
I think I should have added more information. I have 2 tables. It loops through the table to see if a filename exists in both. And if it does, calls the specified batch file (@batfilename). There are different batch files for the different filenames.


It really depends on what those batch files are doing - but I wouldn't use SQL Server to control this process. I would look at controlling the process from the OS level - and just using calls to SQL Server to get the data from the tables that is needed.

Powershell would be ideal for this - as well as using SQLCMD and would not require opening up access to xp_cmdshell to process the batch files.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4172 Visits: 3427
I strongly agree with Jason, and stop using a Cursor.

They are only a curse on a database.

Andrew SQLDBA
donne4real
donne4real
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22

I recommend changing this to a set based method.

You are missing a ' at the end of your select @Cmd statement.


Jason, please can you elaborate what you mean by the set based method? I am a newbie and I am still learning.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63545 Visits: 18570
Jeff Moden has written some good articles here at SSC about triangular joins, set based method, and RBAR. I think those articles would be better able to fully explain it.


In short, set-based queries perform an action against a group of records (1 or more records) at the same time. Cursors and loops (RBAR) perform an action to one record at a time. This kind of processing can be intensive on resources and cause other issues.

My sig has one such article by Jeff. Check it out.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203472 Visits: 41949
As a side bar for everyone else... a cursor isn't such a bad thing for such a control, here. You could concatenate all of the commands together in one big string, but it won't buy you much for performance because the "loop" isn't the slow thing in this case.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203472 Visits: 41949
donne4real (1/6/2010)
I think I should have added more information. I have 2 tables. It loops through the table to see if a filename exists in both. And if it does, calls the specified batch file (@batfilename). There are different batch files for the different filenames.


Apparently it doesn't because you said it get's stuck... of course, we have no idea what you mean by that. Can you be a wee bit more descriptive?

Since you're new here, you might want to take a look at the first link in my signature below... makes people respond to your problems quicker provided that you give more of a description than "it's stuck". ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search