SSMs Report 1222 Error

1. Questions

A few days ago in one QQ group, a group of friends in SSMs when attempting to open a table or stored Procedure object list browsing, encountered a 1222 error, can not browse the system object at all.

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/7E/76/wKioL1cBR6egDZ1bAAC_sKAv7Ug475.jpg "title=" 01.jpg "alt=" Wkiol1cbr6egdz1baac_skav7ug475.jpg "/>

Figure-1: SSMs reported 1222 errors

At first glance, my response is probably to connect to a remote SQL Server, and if this error occurs locally, it is estimated that the system object is locked. After the test, I was sure to take this mistake seriously.

2. Reproduce and analyze problems

Creates a stored procedure that uses explicit transactions inside the stored procedure, but does not commit the transaction and creates no error. However, the execution will be error. If this stored procedure is deleted, this error will occur.

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/7E/7A/wKiom1cBSK-hn2u-AAF2r3ol-IY680.jpg "title=" 02.jpg "alt=" Wkiom1cbsk-hn2u-aaf2r3ol-iy680.jpg "/>

Figure-2: Creating a stored procedure for testing

Executes the stored procedure and reported an error. Indicates that begin and commit numbers are not configured.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/7E/76/wKioL1cBSZzS4wtJAAGoEmGN88g120.jpg "title=" 03.jpg "alt=" Wkiol1cbszzs4wtjaagoemgn88g120.jpg "/>

Figure-3: Performing Storage

Finally delete the stored procedure without error.

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/7E/7A/wKiom1cBS6Lw6edMAAD8saPST98930.jpg "title=" 04.jpg "alt=" Wkiom1cbs6lw6edmaad8sapst98930.jpg "/>

FIGURE-4: Deleting stored procedures

At this point, refresh the specified database under SSMs object Exploer, and then open the list of tables or stored procedures and report a 1222 error. Query the associated lock, discover that the stored procedure used by the test is locked by the table and related pages.

Use adventureworks2008r2goselect  lo.request_session_id as [session_id],db_name ( LO.RESOURCE_DATABASE_ID)  as [database_name],lo.resource_type,lo.resource_subtype as [ Subtype],lo.resource_description as [description],lo.request_mode as [mode],lo.request_ Owner_type as [owner_type],lo.request_status as [status],case when lo.resource_ type =  ' OBJECT '  then object_name (lo.resource_associated_entity_id)    WHEN  lo.resource_associated_entity_id is null or lo.resource_associated_entity_id =  0 then null   else object_name (p.[object_id])   END AS  associated_entity,wt.blocking_session_id,wt.resource_descriptionfrom sys.dm_tran_locks as  loleft join sys.partitions as pon lo.resource_associated_entity_id =  P.partition_idleft join sys.dm_os_waiting_tasks as wton lo.lock_owner_address = wt.resource_addresswhere  LO.REQUEST_SESSION_ID&NBSP;&GT;&NBSP;50AND&NBSP;LO.RESOURCE_DATABASE_ID&NBSP;=&NBSP;DB_ID () AND lo.request _session_id <> @ @SPIDORDER  BY session_id ASC,resource_type ASC; GO

Code-1: Query Lock

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/7E/7A/wKiom1cBT3LxwQZUAAMK3-cQImg945.jpg "title=" 05.jpg "alt=" Wkiom1cbt3lxwqzuaamk3-cqimg945.jpg "/>

Figure-5: Query Results

3. Settlement and conclusion

Find the session ID that executes the stored procedure, and then kill it. Therefore, when using explicit transactions, be sure to be careful when transactions are committed or rolled back, especially when multiple begin tran are used. The problem is solved, or can not help the Internet to find out if anyone has encountered the type of problem. Sure enough, people have discussed it a long time ago. Please refer to:

Http://www.sqlservercentral.com/Forums/Topic310216-5-1.aspx

https://support.microsoft.com/en-us/kb/308518

This article is from the "fishparadise" blog, make sure to keep this source http://fishparadise.blog.51cto.com/11284420/1760036

SSMs Report 1222 Error