Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursor Not Working As Expected Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 2010 9:00 AM
Points: 6, 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

Post #843104
Posted Wednesday, January 6, 2010 12:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 17,814, Visits: 15,740
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
Post #843107
Posted Wednesday, January 6, 2010 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 2010 9:00 AM
Points: 6, 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.
Post #843115
Posted Wednesday, January 6, 2010 1:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #843176
Posted Wednesday, January 6, 2010 3:31 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
I strongly agree with Jason, and stop using a Cursor.

They are only a curse on a database.

Andrew SQLDBA
Post #843235
Posted Wednesday, January 6, 2010 3:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 2010 9:00 AM
Points: 6, 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.
Post #843236
Posted Wednesday, January 6, 2010 9:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 17,814, Visits: 15,740
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
Post #843291
Posted Wednesday, January 6, 2010 11:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #843328
Posted Wednesday, January 6, 2010 11:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #843330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse