Error when saving sql server sql agent job

Question

I create one cursor now i want to call that cursor in sql server jobs scheduler. For SP you need to call EXEC <sp name>. How to call Cursor?

When I try to save a T-SQL script into a job, I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------  
Creating an instance of the COM
component with  CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the 
IClassFactory failed due to the following error:  c001f011.
(Microsoft.SqlServer.ManagedDTS) 
------------------------------  
ADDITIONAL INFORMATION: Creating an
instance of the  COM component with CLSID
{AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2}  from the IClassFactory failed
due to the following  error: c001f011.
(Microsoft.SqlServer.ManagedDTS) 
------------------------------  
BUTTONS: OK 
------------------------------

Answer 1

SQL Agent job steps can be of several types: if you choose a T-SQL step type, you should just be able to put your T-SQL script content in the job step detail/content directly. This should be no different for a script involving a cursor, or any other T-SQL.

A Cursor is not a persistent database object like a stored procedure that you just Call once you've created it; a cursor is a temporary object that is created, used and destroyed in the context of a single SQL connection. typically something like this:

DECLARE XXX CURSOR 
FOR
SELECT Something
FROM Somewhere

OPEN XXX

FETCH NEXT FROM XXX
INTO @A

WHILE @@FETCH_STATUS = 0
BEGIN

    --Do something here

    FETCH NEXT FROM XXX
    INTO @A
END 

CLOSE XXX
DEALLOCATE XXX

If you have a T-SQL script that uses a cursor, then you can simply copy the entire T-SQL script into a SQL Agent job.

If this is not working, please provide more information as to what you've tried, what type of problem you encountered, the detail of any error messages you received, etc.

---

Update after SSMS Error detail provided:

I have found that in SSMS 2008 R2 (at least on a 64-bit machine), the SQL Agent Job Editing UI sometimes starts throwing errors like the one you just provided. When this happens, you sometimes get an error, or sometimes when you double-click on a job step to be edited, nothing happens.

In my experience, these issues simply go away if you complete close SSMS (close all jobs you had open, close activity monitor, close ssms), start it up again, and go straight to the job you wanted to edit.

Try doing this, and if you still have a problem, please describe the exact steps you are taking, the size of your T-SQL script, and any more information you can think to add.

Reference for this error, with confirmation of the bug from Microsoft: http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps

The only known workaround at this time is to close ssms completely, open it again, and edit the one job you want to edit (after that, you may start to get errors again and need to close again)