August 30, 2012 at 10:02 am
This is an interesting error that we are getting on some machines when we push our databases out using scripts.
We have an executable that runs .sql files that control the creation of the DB schema. The exe we are running has a transaction scope. Our creation script (Creates tables and xml Schemas) is the first thing in the transaction scope of the exe
Code in there is similar to this
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
') AND type in (N'U'))
CREATE TABLE [dbo].
(
[Thexml] [xml] NOT NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[XMLtest]')
DROP XML SCHEMA COLLECTION [dbo].[XMLtest]
GO
/****** Object: XmlSchemaCollection [dbo].[XMLSchemaPiecePlateTruss] Script Date: 10/24/2011 11:01:46 ******/
CREATE XML SCHEMA COLLECTION [dbo].[XMLtest] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Book">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="PageNumber" type="xsd:integer" />
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
this xml schema is not bound to the table instead we do the work in the SP so that we can have multiple schemas in one table ie
CREATE PROCEDURE [DBO].[Save_TheXml]
@TheXML xml
AS
SET NOCOUNT ON
BEGIN
--Verify the xml is valid. In the real stored procedure there are types sent in so there are multiple xml schemas
Declare @TheTableXML Table (
[thexml] [xml](DOCUMENT [dbo].[XMLTest])
)
--Put data into Var table
INSERT INTO @TheTableXML
([thexml])
VALUES
(@TheXML)
--Commit data
INSERT INTO dbo.table
Values (@TheXML)
END
--endregion
GO
Now for the question. My machine the exe works and will upgrade/install cleanly. On others machines they are getting metadata locks because the create event is in the transaction scope. If we take that out we get passed all the errors. I believe the lock is due to the drop and create of the xml schema. Is the SP trying to validate the xml schema but is stuck in the transaction? I find it strange this type of deadlock would not happen on all machines. Is there a setting that potentially is causing this?
August 30, 2012 at 11:48 am
Well, the Isolation_Level could affect this.
Also, have you checked to make sure that they are all the same version of SQL server?
[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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply