保存 sql server sql 代理作业时出错

发布于 2024-11-29 00:02:25 字数 771 浏览 2 评论 0原文

我现在创建一个游标，我想在 sql server 作业调度程序中调用该游标。对于 SP，您需要调用 EXEC。如何调用游标？

当我尝试将 T-SQL 脚本保存到作业中时，出现以下错误：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

败给现实 2024-12-06 00:02:25

SQL 代理作业步骤可以有多种类型：如果您选择 T-SQL 步骤类型，则应该能够将 T-SQL 脚本内容直接放入作业步骤详细信息/内容中。对于涉及游标或任何其他 T-SQL 的脚本来说，这应该没有什么不同。

游标不是像存储过程那样的持久数据库对象，您只需在创建它后调用它即可；游标是在单个 SQL 连接的上下文中创建、使用和销毁的临时对象。通常是这样的：

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

如果您有一个使用游标的 T-SQL 脚本，那么您只需将整个 T-SQL 脚本复制到 SQL 代理作业中即可。

如果这不起作用，请提供更多信息，包括您尝试过的操作、您遇到的问题类型、您收到的任何错误消息的详细信息等。

提供 SSMS 后的更新错误详细信息：

我发现在 SSMS 2008 中R2（至少在 64 位计算机上），SQL 代理作业编辑 UI 有时会开始抛出类似于您刚刚提供的错误。发生这种情况时，有时会出现错误，或者有时双击要编辑的作业步骤时没有任何反应。

根据我的经验，如果您完成关闭 SSMS（关闭您打开的所有作业、关闭活动监视器、关闭 ssms）、再次启动它并直接转到您想要编辑的作业，这些问题就会消失。

尝试这样做，如果仍然有问题，请描述您正在采取的确切步骤、T-SQL 脚本的大小以及您可以考虑添加的任何更多信息。

此错误的参考，并确认 Microsoft 的错误： http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps

唯一已知的此时的解决方法是完全关闭 ssms，再次打开它，然后编辑您要编辑的一项作业（之后，您可能会再次开始出现错误，需要再次关闭）

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)

回复收藏 0 原文