April 2, 2009 at 2:35 pm
Barry,
Actually, I got around that. I created a web service that called SMO, then used the web service within my CLR function. It was not "easy", but it did work.
April 2, 2009 at 2:51 pm
Hi Timothy
Nice approach to handle it with a web-service!
As you previously posted:
... but I have more fun building!
If this is possible it is always the best solution!! 🙂
Greets
Flo
April 3, 2009 at 1:41 pm
RBarryYoung (4/1/2009)
As of SQL Server 2005 and later, the only time that you should use Cursors is when you actually want a procedure to run slower.
I disagree wholeheartedly. The much maligned cursor is an extremely useful tool that proves its value almost daily in my environment. Yes, there is not much that cursors do that can't be rewritten using strictly set-based logic, but I have seen some of these "solutions" and they are monsters. Case statements nested within case statements with subqueries everywhere. Many of these are Faustian solutions where you have gotten a 10% better performance but you have abandoned all hopes of maintainability.
One very convenient use I have found for cursors is to use them as an array. The following is edited from a script I have to occasionally send to get executed on the production server. It performs a sequence of operations on a list of items.
declare InputList Cursor for
-- ItemID First Last Action FinalStatus
select 1234, 'Joe', 'Smith', 'Action1', 'Status1' union all
select 1243, 'Sam', 'Brown', 'Action2', 'Status1' union all
select 12789, 'Mary', 'Jones', 'Action3', 'Status6';
declare @ItemID int,
@Firstname varchar( 50 ),
@Lastname varchar( 50 ),
@PrevFirstname varchar( 50 ),
@PrevLastname varchar( 50 ),
@Action varchar( 50 ),
@status varchar( 50 ),
@ActionID int,
@DetailID int,
@StatusID int,
@user-id int;
open InputList;
fetch next from InputList into @ItemID, @Firstname, @Lastname, @Action, @status;
while @@Fetch_Status = 0 begin
-- Place each attempt in its own transaction
begin tran;
if not exists( select 1 from dbo.Items where ItemID = @ItemID) begin
Print 'Error: No such Item #' + Convert( varchar, @ItemID );
end;--if
else begin
-- Handle each different kind of action
end;--else
-- Update audit tables
...
commit; --or rollback if warranted.
-- finally...
fetch next from InputList into @ItemID, @Firstname, @Lastname, @Action, @status;
end;--loop
There is a lot more going on here but I have edited it for brevity. All I have to do is edit the body of the cursor to include the items being changed and then submit the script. I have used this technique in many scripts where diverse operations must be performed on a list of "items" and find it very handy.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
April 3, 2009 at 3:07 pm
Folks:
I would love to rebut every single argument that I have seen here that attempts to excuse the use of Cursors. I have heard every one of them before and I believe them all to be flawed and demonstrably so. And those who have followed me on this know that normally have no hesitation to take on all-comers on this subject.
However, I am afraid that I must defer for now because of the crush of responsibilities over the next 10 days. I have a new SIG presentation on SQL Injection that I have to write by, and present on Wednesday, I have 4 PASS proposals that are due by this Friday, I am a week behind on a promised article to Steve, and most importantly I am 3-5 days behind on my customer's SSIS project. That's the one that I get paid for, so I have to attend to it. And if all this was not enough we are coming into Easter week, which means a bunch of additional personal responsibilities as well.
Somethings have got to give, and for now this has to be one of them, I just cannot give these discussions and arguments the attention that they deserve. However, I can offer you this: I have an article on this very subject coming out on the 14th (already written), when I will have much more time to attend to this. So bring the brickbats, examples exceptions, etc., and I promise to answer all questions and respond to all disagreements then.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 3, 2009 at 3:07 pm
So after reading this thread and the one from the mailing list today (http://www.sqlservercentral.com/Forums/Topic675554-8-1.aspx). I still have a few questions on how some things are accomplished without cursors. For example, I find myself constantly re-running a script that creates sql jobs for an application. In the past, I've either manually deleted all the jobs or used a cursor:
DECLARE @myjob varchar(100)
DECLARE jobs_cursor CURSOR FOR (select name from msdb.dbo.sysjobs_view where date_created > '2009-04-03')
OPEN jobs_cursor
FETCH NEXT FROM jobs_cursor INTO @myjob
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @myjob, @delete_unused_schedule=1
FETCH NEXT FROM jobs_cursor INTO @myjob
END
CLOSE jobs_cursor
DEALLOCATE jobs_cursor
I'm not asking anyone to rewrite my script, I'm looking for a new way to do something I have to do in several situations (i.e. execute a stored procedure against multiple rows in a table).
April 3, 2009 at 3:26 pm
Johnny Wyskiel (4/3/2009)
I'm not asking anyone to rewrite my script, I'm looking for a new way to do something I have to do in several situations (i.e. execute a stored procedure against multiple rows in a table).
There are several ways to do this. My post here, demonstrates the most straightforward.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 3, 2009 at 3:43 pm
RBarryYoung (4/3/2009)
Johnny Wyskiel (4/3/2009)
I'm not asking anyone to rewrite my script, I'm looking for a new way to do something I have to do in several situations (i.e. execute a stored procedure against multiple rows in a table).There are several ways to do this. My post here, demonstrates the most straightforward.
Perfect. I am now that much closer to getting rid of cursors.
April 3, 2009 at 3:48 pm
Well, all you've done is convert an execute statement that's in a loop to a series of execute statements -- which seems to me to be largely a distinction without a difference. This one over here is six and that one over there is a half dozen. Plus now you have to take on all those people who oppose the use of dynamic SQL with as much furor as you oppose cursors.
Don't answer right away, Barry! I pledge not to interpret silence for agreement and we all here promise not to solve this issue in the next three weeks without you. 😀 Get your work done. As much fun as this is, it's not so important you need to play havoc with a busy schedule.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply