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

Solution 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)

Solution 2

this worked for me

http://www.mssqlforum.com/threads/error-creating-an-instance-of-the-com-component-with-clsid-from-the-iclassfactory-failed.49/

just a register file issue

Comments

[Saroop Trivedi]

Thanks to reply me you can call ur SP through the t-SQL where u need to write EXEC <SP Name> but how can i call the cursor... even not getting any idea to call cursor on sql server query page like SP execute

[Tao]

No problem - if you're still having issues, comment on or edit your question; if you've solved your problem and this answer helped you achieve what you were trying, you can also consider "accepting" it with the checkbox on the left (would also be nice for future searches to explain what was wrong in a comment).

[Tao]

@user166694: I've edited my answer to better highlight the difference between a stored procedure, which you create once and then call with EXEC <SP Name>, and a cursor, which you create, use and deallocate every time you need it. Does this help?

[Saroop Trivedi]

hey i m not idiot i know will that u need to declare the cursor not create i think u not get my question if u create job in sql server they ask the Steps in which u need to call your function and code.. my question is that when i want to call SP i write in that EXEC <SP Name> now point how to call cursor...... Directly put all code in commands........ or something Else....

[Tao]

@user166694: sorry if my attempt at clarification implied you're an idiot, that was certainly not my intent. If you have a SQL script, with some cursor-related code in it, and you want to run that script on a schedule, then yes, directly put all the code in the "Command" box of the SQL Agent Job Step.

[Saroop Trivedi]

@Tao: Thanks now i think u understand my question..but i did that think but now i can't able to change the property of that job........it's give me big big Error........Error is i put here.......

[Saroop Trivedi]

ITLE: 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 ------------------------------

[Tao]

@user166694: I've requested an edit to include the information you provided in your original question, and I've edited my answer: try completely closing SSMS, starting again, and tell us the exact steps if you still have issues.