I'm trying to create a stored procecudure using the openquery command to an linked ODBC server.
Everytime I try to save the procedure I get "error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."
I've checked everywhere and set these options on, yet it still doesn't work.
Any ideas?
It's a simple procedure and when I type the sql into query analyzer it works.
HELP?
Here is the procedure.
===============================
CREATE PROCEDURE [dbo].[stpDKLink] AS
select * from openquery(dk, 'select * from pub.invoice')
GO
===============================Yes, SQLServer error message is a bit misleading in this case. Try this out:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[stpDKLink] AS
select * from openquery(dk, 'select * from pub.invoice')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql
Showing posts with label save. Show all posts
Showing posts with label save. Show all posts
Wednesday, March 21, 2012
error 7405
I have created a stored procedure wich queries a linked ORACLE database. When
I try to save the procedure I get the following error
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
help.
Are there any suggestions?
Jim Capehart
The connection creating the stored procedure needs to have
the settings on. So you'd want to create the stored
procedure using something along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 17 Dec 2004 08:11:02 -0800, Jim Capehart
<JimCapehart@.discussions.microsoft.com> wrote:
>I have created a stored procedure wich queries a linked ORACLE database. When
>I try to save the procedure I get the following error
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
>set for the connection. This ensures consistent query semantics. Enable these
>options and then reissue your query.
> I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
>help.
>Are there any suggestions?
>Jim Capehart
|||Guys,
This doesn't necessarily work. If your not happ y with adding these
settings to the the server as a whole then try using the ISQL window to
create the procedure.
Be sure to make a copy of the oprginal (just in case) but this will
allow the SET command to intigated at the top of he procedure.
Hope this helps.
JohnT-A
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message654588.html
I try to save the procedure I get the following error
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
help.
Are there any suggestions?
Jim Capehart
The connection creating the stored procedure needs to have
the settings on. So you'd want to create the stored
procedure using something along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 17 Dec 2004 08:11:02 -0800, Jim Capehart
<JimCapehart@.discussions.microsoft.com> wrote:
>I have created a stored procedure wich queries a linked ORACLE database. When
>I try to save the procedure I get the following error
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
>set for the connection. This ensures consistent query semantics. Enable these
>options and then reissue your query.
> I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
>help.
>Are there any suggestions?
>Jim Capehart
|||Guys,
This doesn't necessarily work. If your not happ y with adding these
settings to the the server as a whole then try using the ISQL window to
create the procedure.
Be sure to make a copy of the oprginal (just in case) but this will
allow the SET command to intigated at the top of he procedure.
Hope this helps.
JohnT-A
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message654588.html
Monday, March 19, 2012
Error 6913 error when I save to a typed xml field.
I am getting an 6913 error when I save to a typed xml field.
I have a table called Is.Lead which has the following collums
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlAHG typed Xml data type xml(CONTENT
dbo.AmericanHomeguideSchemaCollection)
I also have a sproc called Is_uspProcessLeads that among other things,
determines if a an xml file is well formed, and if it is valid. I do
this by using a sql update statement and if there is an error, an entry
is made into a log table. The update statements look like the
following...
UPDATE[Is.Lead]
SETIsLead_XmlWellFormed= IsLead_XmlRaw,
IsLead_LeadStatus_Id= @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
UPDATE[Is.Lead]
SETIsLead_XmlAHG= IsLead_XmlRaw -- IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
When I get to the second update statement, I get the following error...
Error Number: 6913
Error Message: XML Validation: Declaration not found for element
'Leads'. Location: /*:Leads[1]
Below is the script on how I created my XSD...
Use Integration
go
CREATE XML SCHEMA COLLECTION
[dbo].[AmericanHomeguideSchemaCollection] AS
N'<?xml version="1.0"?>
<xsd:schema
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
xmlns
="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Leads">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" name="Source" type="xsd:string" />
<xsd:element minOccurs="0" name="Community_Name"
type="xsd:string" />
<xsd:element minOccurs="0" name="SubdivisionID"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Community_State" />
<xsd:element minOccurs="0" name="Community_Region"
type="xsd:string" />
<xsd:element minOccurs="0" name="Community_City"
type="xsd:string" />
<xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="Street" type="xsd:string" />
<xsd:element minOccurs="0" name="City" type="xsd:string" />
<xsd:element minOccurs="0" name="State" type="xsd:string" />
<xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Phone" type="xsd:string" />
<xsd:element minOccurs="0" name="Email" type="xsd:string" />
<xsd:element minOccurs="0" name="Best_Time_To_Call"
type="xsd:string" />
<xsd:element minOccurs="0" name="Month_Planned_Relocation"
type="xsd:string" />
<xsd:element minOccurs="0" name="Year_Planned_Relocation"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Comments" type="xsd:string" />
<xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
After I create my schema collection, I verify it by running these two
select statements.
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
sys.xml_schema_collections
xml_collection_idschema_idprincipal_idNamecreate_datemodify_date
14NULLSys2005-09-02 22:30:10.8402005-09-02 22:30:11.013
655631NULLAmericanHomeguideSchemaCollection2005-12-19
10:23:29.2432005-12-19 10:23:29.243
sys.xml_schema_namespaces
xml_collection_idNamexml_namespace_id
1http://www.w3.org/2001/XMLSchema
1
1http://schemas.microsoft.com/sqlserver/2004/sqltypes
2
1http://www.w3.org/XML/1998/namespace
3
65563http://www.ryland.com/Integration/Ex...ricanHomeguide
1
Below is some sample data that I am using for testing...
<?xml version="1.0" encoding="windows-1250"?>
<Leads>
<Lead>
<Source>NHG</Source>
<Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
<SubdivisionID>34621</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Atlanta</Community_City>
<Last_Name> FakeLastName </Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxxxx</Street>
<City>college park</City>
<State>ga</State>
<Zip>30349</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
<Year_Planned_Relocation>2006</Year_Planned_Relocation>
<Comments>NA</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
<Lead>
<Source>NHG</Source>
<Community_Name>Cottage Cove-Ryland Homes</Community_Name>
<SubdivisionID>34605</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Dallas</Community_City>
<Last_Name>FakeLastName</Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxx</Street>
<City>rome</City>
<State>ga</State>
<Zip>30165</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
<Year_Planned_Relocation>2007</Year_Planned_Relocation>
<Comments>No Comments Offered.</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
</Leads>
Your schema declares that the data has a target namespace:
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
But the instance data that you insert does not use the namespace. Since the
namespace is used to bind the names to the schema, the schema validator
cannot find a schema for the names without namespace and raises the
validation error 6913.
You have two options:
Either not use a targetNamespace on your schema or add namespaces to your
XML documents.
Best regards
Michael
"Freddie Tripples" <JohnMarsing@.gmail.com> wrote in message
news:1135022743.002785.162690@.g49g2000cwa.googlegr oups.com...
>I am getting an 6913 error when I save to a typed xml field.
> I have a table called Is.Lead which has the following collums
> IsLead_Id Primary Key, int
> IsLead_XmlRaw varchar(max)
> IsLead_XmlWellFormed untyped Xml data type
> IsLead_XmlAHG typed Xml data type xml(CONTENT
> dbo.AmericanHomeguideSchemaCollection)
> I also have a sproc called Is_uspProcessLeads that among other things,
> determines if a an xml file is well formed, and if it is valid. I do
> this by using a sql update statement and if there is an error, an entry
> is made into a log table. The update statements look like the
> following...
> UPDATE [Is.Lead]
> SET IsLead_XmlWellFormed = IsLead_XmlRaw,
> IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
>
> UPDATE [Is.Lead]
> SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
> When I get to the second update statement, I get the following error...
> Error Number: 6913
> Error Message: XML Validation: Declaration not found for element
> 'Leads'. Location: /*:Leads[1]
> Below is the script on how I created my XSD...
> Use Integration
> go
> CREATE XML SCHEMA COLLECTION
> [dbo].[AmericanHomeguideSchemaCollection] AS
> N'<?xml version="1.0"?>
> <xsd:schema
> targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> xmlns
> ="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> elementFormDefault="qualified"
> attributeFormDefault="unqualified"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
> <xsd:element name="Leads">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" name="Source" type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_Name"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="SubdivisionID"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Community_State" />
> <xsd:element minOccurs="0" name="Community_Region"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_City"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="Street" type="xsd:string" />
> <xsd:element minOccurs="0" name="City" type="xsd:string" />
> <xsd:element minOccurs="0" name="State" type="xsd:string" />
> <xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Phone" type="xsd:string" />
> <xsd:element minOccurs="0" name="Email" type="xsd:string" />
> <xsd:element minOccurs="0" name="Best_Time_To_Call"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Month_Planned_Relocation"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Year_Planned_Relocation"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Comments" type="xsd:string" />
> <xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>' ;
> GO
> After I create my schema collection, I verify it by running these two
> select statements.
> select * from sys.xml_schema_collections
> select * from sys.xml_schema_namespaces
>
> sys.xml_schema_collections
> xml_collection_id schema_id principal_id Name create_date modify_date
> 1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
> 65563 1 NULL AmericanHomeguideSchemaCollection 2005-12-19
> 10:23:29.243 2005-12-19 10:23:29.243
>
> sys.xml_schema_namespaces
> xml_collection_id Name xml_namespace_id
> 1 http://www.w3.org/2001/XMLSchema
> 1
> 1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
> 2
> 1 http://www.w3.org/XML/1998/namespace
> 3
> 65563 http://www.ryland.com/Integration/Ex...ricanHomeguide
> 1
>
> Below is some sample data that I am using for testing...
> <?xml version="1.0" encoding="windows-1250"?>
> <Leads>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
> <SubdivisionID>34621</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Atlanta</Community_City>
> <Last_Name> FakeLastName </Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxxxx</Street>
> <City>college park</City>
> <State>ga</State>
> <Zip>30349</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
> <Year_Planned_Relocation>2006</Year_Planned_Relocation>
> <Comments>NA</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Cottage Cove-Ryland Homes</Community_Name>
> <SubdivisionID>34605</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Dallas</Community_City>
> <Last_Name>FakeLastName</Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxx</Street>
> <City>rome</City>
> <State>ga</State>
> <Zip>30165</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
> <Year_Planned_Relocation>2007</Year_Planned_Relocation>
> <Comments>No Comments Offered.</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> </Leads>
>
|||Thanks
*** Sent via Developersdex http://www.codecomments.com ***
I have a table called Is.Lead which has the following collums
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlAHG typed Xml data type xml(CONTENT
dbo.AmericanHomeguideSchemaCollection)
I also have a sproc called Is_uspProcessLeads that among other things,
determines if a an xml file is well formed, and if it is valid. I do
this by using a sql update statement and if there is an error, an entry
is made into a log table. The update statements look like the
following...
UPDATE[Is.Lead]
SETIsLead_XmlWellFormed= IsLead_XmlRaw,
IsLead_LeadStatus_Id= @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
UPDATE[Is.Lead]
SETIsLead_XmlAHG= IsLead_XmlRaw -- IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
When I get to the second update statement, I get the following error...
Error Number: 6913
Error Message: XML Validation: Declaration not found for element
'Leads'. Location: /*:Leads[1]
Below is the script on how I created my XSD...
Use Integration
go
CREATE XML SCHEMA COLLECTION
[dbo].[AmericanHomeguideSchemaCollection] AS
N'<?xml version="1.0"?>
<xsd:schema
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
xmlns
="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Leads">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" name="Source" type="xsd:string" />
<xsd:element minOccurs="0" name="Community_Name"
type="xsd:string" />
<xsd:element minOccurs="0" name="SubdivisionID"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Community_State" />
<xsd:element minOccurs="0" name="Community_Region"
type="xsd:string" />
<xsd:element minOccurs="0" name="Community_City"
type="xsd:string" />
<xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="Street" type="xsd:string" />
<xsd:element minOccurs="0" name="City" type="xsd:string" />
<xsd:element minOccurs="0" name="State" type="xsd:string" />
<xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Phone" type="xsd:string" />
<xsd:element minOccurs="0" name="Email" type="xsd:string" />
<xsd:element minOccurs="0" name="Best_Time_To_Call"
type="xsd:string" />
<xsd:element minOccurs="0" name="Month_Planned_Relocation"
type="xsd:string" />
<xsd:element minOccurs="0" name="Year_Planned_Relocation"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Comments" type="xsd:string" />
<xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
After I create my schema collection, I verify it by running these two
select statements.
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
sys.xml_schema_collections
xml_collection_idschema_idprincipal_idNamecreate_datemodify_date
14NULLSys2005-09-02 22:30:10.8402005-09-02 22:30:11.013
655631NULLAmericanHomeguideSchemaCollection2005-12-19
10:23:29.2432005-12-19 10:23:29.243
sys.xml_schema_namespaces
xml_collection_idNamexml_namespace_id
1http://www.w3.org/2001/XMLSchema
1
1http://schemas.microsoft.com/sqlserver/2004/sqltypes
2
1http://www.w3.org/XML/1998/namespace
3
65563http://www.ryland.com/Integration/Ex...ricanHomeguide
1
Below is some sample data that I am using for testing...
<?xml version="1.0" encoding="windows-1250"?>
<Leads>
<Lead>
<Source>NHG</Source>
<Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
<SubdivisionID>34621</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Atlanta</Community_City>
<Last_Name> FakeLastName </Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxxxx</Street>
<City>college park</City>
<State>ga</State>
<Zip>30349</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
<Year_Planned_Relocation>2006</Year_Planned_Relocation>
<Comments>NA</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
<Lead>
<Source>NHG</Source>
<Community_Name>Cottage Cove-Ryland Homes</Community_Name>
<SubdivisionID>34605</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Dallas</Community_City>
<Last_Name>FakeLastName</Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxx</Street>
<City>rome</City>
<State>ga</State>
<Zip>30165</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
<Year_Planned_Relocation>2007</Year_Planned_Relocation>
<Comments>No Comments Offered.</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
</Leads>
Your schema declares that the data has a target namespace:
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
But the instance data that you insert does not use the namespace. Since the
namespace is used to bind the names to the schema, the schema validator
cannot find a schema for the names without namespace and raises the
validation error 6913.
You have two options:
Either not use a targetNamespace on your schema or add namespaces to your
XML documents.
Best regards
Michael
"Freddie Tripples" <JohnMarsing@.gmail.com> wrote in message
news:1135022743.002785.162690@.g49g2000cwa.googlegr oups.com...
>I am getting an 6913 error when I save to a typed xml field.
> I have a table called Is.Lead which has the following collums
> IsLead_Id Primary Key, int
> IsLead_XmlRaw varchar(max)
> IsLead_XmlWellFormed untyped Xml data type
> IsLead_XmlAHG typed Xml data type xml(CONTENT
> dbo.AmericanHomeguideSchemaCollection)
> I also have a sproc called Is_uspProcessLeads that among other things,
> determines if a an xml file is well formed, and if it is valid. I do
> this by using a sql update statement and if there is an error, an entry
> is made into a log table. The update statements look like the
> following...
> UPDATE [Is.Lead]
> SET IsLead_XmlWellFormed = IsLead_XmlRaw,
> IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
>
> UPDATE [Is.Lead]
> SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
> When I get to the second update statement, I get the following error...
> Error Number: 6913
> Error Message: XML Validation: Declaration not found for element
> 'Leads'. Location: /*:Leads[1]
> Below is the script on how I created my XSD...
> Use Integration
> go
> CREATE XML SCHEMA COLLECTION
> [dbo].[AmericanHomeguideSchemaCollection] AS
> N'<?xml version="1.0"?>
> <xsd:schema
> targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> xmlns
> ="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> elementFormDefault="qualified"
> attributeFormDefault="unqualified"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
> <xsd:element name="Leads">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" name="Source" type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_Name"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="SubdivisionID"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Community_State" />
> <xsd:element minOccurs="0" name="Community_Region"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_City"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="Street" type="xsd:string" />
> <xsd:element minOccurs="0" name="City" type="xsd:string" />
> <xsd:element minOccurs="0" name="State" type="xsd:string" />
> <xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Phone" type="xsd:string" />
> <xsd:element minOccurs="0" name="Email" type="xsd:string" />
> <xsd:element minOccurs="0" name="Best_Time_To_Call"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Month_Planned_Relocation"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Year_Planned_Relocation"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Comments" type="xsd:string" />
> <xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>' ;
> GO
> After I create my schema collection, I verify it by running these two
> select statements.
> select * from sys.xml_schema_collections
> select * from sys.xml_schema_namespaces
>
> sys.xml_schema_collections
> xml_collection_id schema_id principal_id Name create_date modify_date
> 1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
> 65563 1 NULL AmericanHomeguideSchemaCollection 2005-12-19
> 10:23:29.243 2005-12-19 10:23:29.243
>
> sys.xml_schema_namespaces
> xml_collection_id Name xml_namespace_id
> 1 http://www.w3.org/2001/XMLSchema
> 1
> 1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
> 2
> 1 http://www.w3.org/XML/1998/namespace
> 3
> 65563 http://www.ryland.com/Integration/Ex...ricanHomeguide
> 1
>
> Below is some sample data that I am using for testing...
> <?xml version="1.0" encoding="windows-1250"?>
> <Leads>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
> <SubdivisionID>34621</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Atlanta</Community_City>
> <Last_Name> FakeLastName </Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxxxx</Street>
> <City>college park</City>
> <State>ga</State>
> <Zip>30349</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
> <Year_Planned_Relocation>2006</Year_Planned_Relocation>
> <Comments>NA</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Cottage Cove-Ryland Homes</Community_Name>
> <SubdivisionID>34605</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Dallas</Community_City>
> <Last_Name>FakeLastName</Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxx</Street>
> <City>rome</City>
> <State>ga</State>
> <Zip>30165</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
> <Year_Planned_Relocation>2007</Year_Planned_Relocation>
> <Comments>No Comments Offered.</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> </Leads>
>
|||Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Error 6913 error when I save to a typed xml field.
I am getting an 6913 error when I save to a typed xml field.
I have a table called Is.Lead which has the following collums
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlAHG typed Xml data type xml(CONTENT
dbo.AmericanHomeguideSchemaCollection)
I also have a sproc called Is_uspProcessLeads that among other things,
determines if a an xml file is well formed, and if it is valid. I do
this by using a sql update statement and if there is an error, an entry
is made into a log table. The update statements look like the
following...
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
When I get to the second update statement, I get the following error...
Error Number: 6913
Error Message: XML Validation: Declaration not found for element
'Leads'. Location: /*:Leads[1]
Below is the script on how I created my XSD...
Use Integration
go
CREATE XML SCHEMA COLLECTION
[dbo].[AmericanHomeguideSchemaCollection] AS
N'<?xml version="1.0"?>
<xsd:schema
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguid
e"
xmlns
="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Leads">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" name="Source" type="xsd:string" />
<xsd:element minOccurs="0" name="Community_Name"
type="xsd:string" />
<xsd:element minOccurs="0" name="SubdivisionID"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Community_State" />
<xsd:element minOccurs="0" name="Community_Region"
type="xsd:string" />
<xsd:element minOccurs="0" name="Community_City"
type="xsd:string" />
<xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="Street" type="xsd:string" />
<xsd:element minOccurs="0" name="City" type="xsd:string" />
<xsd:element minOccurs="0" name="State" type="xsd:string" />
<xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Phone" type="xsd:string" />
<xsd:element minOccurs="0" name="Email" type="xsd:string" />
<xsd:element minOccurs="0" name="Best_Time_To_Call"
type="xsd:string" />
<xsd:element minOccurs="0" name="Month_Planned_Relocation"
type="xsd:string" />
<xsd:element minOccurs="0" name="Year_Planned_Relocation"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Comments" type="xsd:string" />
<xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
After I create my schema collection, I verify it by running these two
select statements.
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
sys.xml_schema_collections
xml_collection_id schema_id principal_id
Name create_date modify_date
1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
65563 1 NULL AmericanHomeguideSchemaColl
ection 2005-12-19
10:23:29.243 2005-12-19 10:23:29.243
sys.xml_schema_namespaces
xml_collection_id Name xml_namespace_id
1 http://www.w3.org/2001/XMLSchema
1
1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
2
1 http://www.w3.org/XML/1998/namespace
3
65563 http://www.ryland.com/Integration/E...ericanHomeguide
1
Below is some sample data that I am using for testing...
<?xml version="1.0" encoding="windows-1250"?>
<Leads>
<Lead>
<Source>NHG</Source>
<Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
<SubdivisionID>34621</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Atlanta</Community_City>
<Last_Name> FakeLastName </Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxxxx</Street>
<City>college park</City>
<State>ga</State>
<Zip>30349</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
<Year_Planned_Relocation>2006</Year_Planned_Relocation>
<Comments>NA</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
<Lead>
<Source>NHG</Source>
<Community_Name>Cottage Cove-Ryland Homes</Community_Name>
<SubdivisionID>34605</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Dallas</Community_City>
<Last_Name>FakeLastName</Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxx</Street>
<City>rome</City>
<State>ga</State>
<Zip>30165</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
<Year_Planned_Relocation>2007</Year_Planned_Relocation>
<Comments>No Comments Offered.</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
</Leads>Your schema declares that the data has a target namespace:
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguid
e"
But the instance data that you insert does not use the namespace. Since the
namespace is used to bind the names to the schema, the schema validator
cannot find a schema for the names without namespace and raises the
validation error 6913.
You have two options:
Either not use a targetNamespace on your schema or add namespaces to your
XML documents.
Best regards
Michael
"Freddie Tripples" <JohnMarsing@.gmail.com> wrote in message
news:1135022743.002785.162690@.g49g2000cwa.googlegroups.com...
>I am getting an 6913 error when I save to a typed xml field.
> I have a table called Is.Lead which has the following collums
> IsLead_Id Primary Key, int
> IsLead_XmlRaw varchar(max)
> IsLead_XmlWellFormed untyped Xml data type
> IsLead_XmlAHG typed Xml data type xml(CONTENT
> dbo.AmericanHomeguideSchemaCollection)
> I also have a sproc called Is_uspProcessLeads that among other things,
> determines if a an xml file is well formed, and if it is valid. I do
> this by using a sql update statement and if there is an error, an entry
> is made into a log table. The update statements look like the
> following...
> UPDATE [Is.Lead]
> SET IsLead_XmlWellFormed = IsLead_XmlRaw,
> IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
>
> UPDATE [Is.Lead]
> SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
> When I get to the second update statement, I get the following error...
> Error Number: 6913
> Error Message: XML Validation: Declaration not found for element
> 'Leads'. Location: /*:Leads[1]
> Below is the script on how I created my XSD...
> Use Integration
> go
> CREATE XML SCHEMA COLLECTION
> [dbo].[AmericanHomeguideSchemaCollection] AS
> N'<?xml version="1.0"?>
> <xsd:schema
> targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomegu
ide"
> xmlns
> ="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> elementFormDefault="qualified"
> attributeFormDefault="unqualified"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
> <xsd:element name="Leads">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" name="Source" type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_Name"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="SubdivisionID"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Community_State" />
> <xsd:element minOccurs="0" name="Community_Region"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_City"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="Street" type="xsd:string" />
> <xsd:element minOccurs="0" name="City" type="xsd:string" />
> <xsd:element minOccurs="0" name="State" type="xsd:string" />
> <xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Phone" type="xsd:string" />
> <xsd:element minOccurs="0" name="Email" type="xsd:string" />
> <xsd:element minOccurs="0" name="Best_Time_To_Call"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Month_Planned_Relocation"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Year_Planned_Relocation"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Comments" type="xsd:string" />
> <xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>' ;
> GO
> After I create my schema collection, I verify it by running these two
> select statements.
> select * from sys.xml_schema_collections
> select * from sys.xml_schema_namespaces
>
> sys.xml_schema_collections
> xml_collection_id schema_id principal_id Name create_date modify_date
> 1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
> 65563 1 NULL AmericanHomeguideSchemaCollection 2005-12-19
> 10:23:29.243 2005-12-19 10:23:29.243
>
> sys.xml_schema_namespaces
> xml_collection_id Name xml_namespace_id
> 1 http://www.w3.org/2001/XMLSchema
> 1
> 1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
> 2
> 1 http://www.w3.org/XML/1998/namespace
> 3
> 65563 http://www.ryland.com/Integration/E...ericanHomeguide
> 1
>
> Below is some sample data that I am using for testing...
> <?xml version="1.0" encoding="windows-1250"?>
> <Leads>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
> <SubdivisionID>34621</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Atlanta</Community_City>
> <Last_Name> FakeLastName </Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxxxx</Street>
> <City>college park</City>
> <State>ga</State>
> <Zip>30349</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
> <Year_Planned_Relocation>2006</Year_Planned_Relocation>
> <Comments>NA</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Cottage Cove-Ryland Homes</Community_Name>
> <SubdivisionID>34605</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Dallas</Community_City>
> <Last_Name>FakeLastName</Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxx</Street>
> <City>rome</City>
> <State>ga</State>
> <Zip>30165</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
> <Year_Planned_Relocation>2007</Year_Planned_Relocation>
> <Comments>No Comments Offered.</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> </Leads>
>|||Thanks
*** Sent via Developersdex http://www.examnotes.net ***
I have a table called Is.Lead which has the following collums
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlAHG typed Xml data type xml(CONTENT
dbo.AmericanHomeguideSchemaCollection)
I also have a sproc called Is_uspProcessLeads that among other things,
determines if a an xml file is well formed, and if it is valid. I do
this by using a sql update statement and if there is an error, an entry
is made into a log table. The update statements look like the
following...
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
When I get to the second update statement, I get the following error...
Error Number: 6913
Error Message: XML Validation: Declaration not found for element
'Leads'. Location: /*:Leads[1]
Below is the script on how I created my XSD...
Use Integration
go
CREATE XML SCHEMA COLLECTION
[dbo].[AmericanHomeguideSchemaCollection] AS
N'<?xml version="1.0"?>
<xsd:schema
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguid
e"
xmlns
="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Leads">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" name="Source" type="xsd:string" />
<xsd:element minOccurs="0" name="Community_Name"
type="xsd:string" />
<xsd:element minOccurs="0" name="SubdivisionID"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Community_State" />
<xsd:element minOccurs="0" name="Community_Region"
type="xsd:string" />
<xsd:element minOccurs="0" name="Community_City"
type="xsd:string" />
<xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="Street" type="xsd:string" />
<xsd:element minOccurs="0" name="City" type="xsd:string" />
<xsd:element minOccurs="0" name="State" type="xsd:string" />
<xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Phone" type="xsd:string" />
<xsd:element minOccurs="0" name="Email" type="xsd:string" />
<xsd:element minOccurs="0" name="Best_Time_To_Call"
type="xsd:string" />
<xsd:element minOccurs="0" name="Month_Planned_Relocation"
type="xsd:string" />
<xsd:element minOccurs="0" name="Year_Planned_Relocation"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Comments" type="xsd:string" />
<xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
After I create my schema collection, I verify it by running these two
select statements.
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
sys.xml_schema_collections
xml_collection_id schema_id principal_id
Name create_date modify_date
1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
65563 1 NULL AmericanHomeguideSchemaColl
ection 2005-12-19
10:23:29.243 2005-12-19 10:23:29.243
sys.xml_schema_namespaces
xml_collection_id Name xml_namespace_id
1 http://www.w3.org/2001/XMLSchema
1
1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
2
1 http://www.w3.org/XML/1998/namespace
3
65563 http://www.ryland.com/Integration/E...ericanHomeguide
1
Below is some sample data that I am using for testing...
<?xml version="1.0" encoding="windows-1250"?>
<Leads>
<Lead>
<Source>NHG</Source>
<Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
<SubdivisionID>34621</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Atlanta</Community_City>
<Last_Name> FakeLastName </Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxxxx</Street>
<City>college park</City>
<State>ga</State>
<Zip>30349</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
<Year_Planned_Relocation>2006</Year_Planned_Relocation>
<Comments>NA</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
<Lead>
<Source>NHG</Source>
<Community_Name>Cottage Cove-Ryland Homes</Community_Name>
<SubdivisionID>34605</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Dallas</Community_City>
<Last_Name>FakeLastName</Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxx</Street>
<City>rome</City>
<State>ga</State>
<Zip>30165</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
<Year_Planned_Relocation>2007</Year_Planned_Relocation>
<Comments>No Comments Offered.</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
</Leads>Your schema declares that the data has a target namespace:
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguid
e"
But the instance data that you insert does not use the namespace. Since the
namespace is used to bind the names to the schema, the schema validator
cannot find a schema for the names without namespace and raises the
validation error 6913.
You have two options:
Either not use a targetNamespace on your schema or add namespaces to your
XML documents.
Best regards
Michael
"Freddie Tripples" <JohnMarsing@.gmail.com> wrote in message
news:1135022743.002785.162690@.g49g2000cwa.googlegroups.com...
>I am getting an 6913 error when I save to a typed xml field.
> I have a table called Is.Lead which has the following collums
> IsLead_Id Primary Key, int
> IsLead_XmlRaw varchar(max)
> IsLead_XmlWellFormed untyped Xml data type
> IsLead_XmlAHG typed Xml data type xml(CONTENT
> dbo.AmericanHomeguideSchemaCollection)
> I also have a sproc called Is_uspProcessLeads that among other things,
> determines if a an xml file is well formed, and if it is valid. I do
> this by using a sql update statement and if there is an error, an entry
> is made into a log table. The update statements look like the
> following...
> UPDATE [Is.Lead]
> SET IsLead_XmlWellFormed = IsLead_XmlRaw,
> IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
>
> UPDATE [Is.Lead]
> SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
> When I get to the second update statement, I get the following error...
> Error Number: 6913
> Error Message: XML Validation: Declaration not found for element
> 'Leads'. Location: /*:Leads[1]
> Below is the script on how I created my XSD...
> Use Integration
> go
> CREATE XML SCHEMA COLLECTION
> [dbo].[AmericanHomeguideSchemaCollection] AS
> N'<?xml version="1.0"?>
> <xsd:schema
> targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomegu
ide"
> xmlns
> ="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> elementFormDefault="qualified"
> attributeFormDefault="unqualified"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
> <xsd:element name="Leads">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" name="Source" type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_Name"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="SubdivisionID"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Community_State" />
> <xsd:element minOccurs="0" name="Community_Region"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_City"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="Street" type="xsd:string" />
> <xsd:element minOccurs="0" name="City" type="xsd:string" />
> <xsd:element minOccurs="0" name="State" type="xsd:string" />
> <xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Phone" type="xsd:string" />
> <xsd:element minOccurs="0" name="Email" type="xsd:string" />
> <xsd:element minOccurs="0" name="Best_Time_To_Call"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Month_Planned_Relocation"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Year_Planned_Relocation"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Comments" type="xsd:string" />
> <xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>' ;
> GO
> After I create my schema collection, I verify it by running these two
> select statements.
> select * from sys.xml_schema_collections
> select * from sys.xml_schema_namespaces
>
> sys.xml_schema_collections
> xml_collection_id schema_id principal_id Name create_date modify_date
> 1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
> 65563 1 NULL AmericanHomeguideSchemaCollection 2005-12-19
> 10:23:29.243 2005-12-19 10:23:29.243
>
> sys.xml_schema_namespaces
> xml_collection_id Name xml_namespace_id
> 1 http://www.w3.org/2001/XMLSchema
> 1
> 1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
> 2
> 1 http://www.w3.org/XML/1998/namespace
> 3
> 65563 http://www.ryland.com/Integration/E...ericanHomeguide
> 1
>
> Below is some sample data that I am using for testing...
> <?xml version="1.0" encoding="windows-1250"?>
> <Leads>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
> <SubdivisionID>34621</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Atlanta</Community_City>
> <Last_Name> FakeLastName </Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxxxx</Street>
> <City>college park</City>
> <State>ga</State>
> <Zip>30349</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
> <Year_Planned_Relocation>2006</Year_Planned_Relocation>
> <Comments>NA</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Cottage Cove-Ryland Homes</Community_Name>
> <SubdivisionID>34605</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Dallas</Community_City>
> <Last_Name>FakeLastName</Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxx</Street>
> <City>rome</City>
> <State>ga</State>
> <Zip>30165</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
> <Year_Planned_Relocation>2007</Year_Planned_Relocation>
> <Comments>No Comments Offered.</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> </Leads>
>|||Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Error 627:Cannot use SAVE TRANSACTION within a distributed transac
Information:
SQL Server 2000 Enterprise Edition
Server Pack 3a
Server Edition is Windows 2000 Advanced Server
Replication Type is Transactional
I am trying to drop a publication. I am getting the error:
Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
This error also is generated when trying to drop the individual subscription.
Can anyone provide insight as to what could be causing this error?
Thank you in advance.
Robert M.Check the "remote proc trans" value from sp_configure. If it's 1, you need
to change it to 0.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> Information:
> SQL Server 2000 Enterprise Edition
> Server Pack 3a
> Server Edition is Windows 2000 Advanced Server
> Replication Type is Transactional
> I am trying to drop a publication. I am getting the error:
> Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> This error also is generated when trying to drop the individual
subscription.
> Can anyone provide insight as to what could be causing this error?
> Thank you in advance.
> Robert M.
>|||I am assuming that the 'remote proc trans' setting needs to be set to zero on
the publisher (i have separate servers for distributor/publisher/subscriber).
Thanks...Robert M.
"Richard Ding" wrote:
> Check the "remote proc trans" value from sp_configure. If it's 1, you need
> to change it to 0.
>
> "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > Information:
> > SQL Server 2000 Enterprise Edition
> > Server Pack 3a
> > Server Edition is Windows 2000 Advanced Server
> > Replication Type is Transactional
> >
> > I am trying to drop a publication. I am getting the error:
> > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> >
> > This error also is generated when trying to drop the individual
> subscription.
> >
> > Can anyone provide insight as to what could be causing this error?
> >
> > Thank you in advance.
> >
> > Robert M.
> >
> >
>
>|||Actually, I believe it should be set to 0 on the subscriber. No need to
touch the values on publisher or distributor.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:4A56EC3F-0537-441C-990E-0C7D43BD972C@.microsoft.com...
> I am assuming that the 'remote proc trans' setting needs to be set to zero
on
> the publisher (i have separate servers for
distributor/publisher/subscriber).
> Thanks...Robert M.
> "Richard Ding" wrote:
> > Check the "remote proc trans" value from sp_configure. If it's 1, you
need
> > to change it to 0.
> >
> >
> > "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> > news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > > Information:
> > > SQL Server 2000 Enterprise Edition
> > > Server Pack 3a
> > > Server Edition is Windows 2000 Advanced Server
> > > Replication Type is Transactional
> > >
> > > I am trying to drop a publication. I am getting the error:
> > > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> > >
> > > This error also is generated when trying to drop the individual
> > subscription.
> > >
> > > Can anyone provide insight as to what could be causing this error?
> > >
> > > Thank you in advance.
> > >
> > > Robert M.
> > >
> > >
> >
> >
> >
SQL Server 2000 Enterprise Edition
Server Pack 3a
Server Edition is Windows 2000 Advanced Server
Replication Type is Transactional
I am trying to drop a publication. I am getting the error:
Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
This error also is generated when trying to drop the individual subscription.
Can anyone provide insight as to what could be causing this error?
Thank you in advance.
Robert M.Check the "remote proc trans" value from sp_configure. If it's 1, you need
to change it to 0.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> Information:
> SQL Server 2000 Enterprise Edition
> Server Pack 3a
> Server Edition is Windows 2000 Advanced Server
> Replication Type is Transactional
> I am trying to drop a publication. I am getting the error:
> Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> This error also is generated when trying to drop the individual
subscription.
> Can anyone provide insight as to what could be causing this error?
> Thank you in advance.
> Robert M.
>|||I am assuming that the 'remote proc trans' setting needs to be set to zero on
the publisher (i have separate servers for distributor/publisher/subscriber).
Thanks...Robert M.
"Richard Ding" wrote:
> Check the "remote proc trans" value from sp_configure. If it's 1, you need
> to change it to 0.
>
> "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > Information:
> > SQL Server 2000 Enterprise Edition
> > Server Pack 3a
> > Server Edition is Windows 2000 Advanced Server
> > Replication Type is Transactional
> >
> > I am trying to drop a publication. I am getting the error:
> > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> >
> > This error also is generated when trying to drop the individual
> subscription.
> >
> > Can anyone provide insight as to what could be causing this error?
> >
> > Thank you in advance.
> >
> > Robert M.
> >
> >
>
>|||Actually, I believe it should be set to 0 on the subscriber. No need to
touch the values on publisher or distributor.
"Robert M." <RobertM@.discussions.microsoft.com> wrote in message
news:4A56EC3F-0537-441C-990E-0C7D43BD972C@.microsoft.com...
> I am assuming that the 'remote proc trans' setting needs to be set to zero
on
> the publisher (i have separate servers for
distributor/publisher/subscriber).
> Thanks...Robert M.
> "Richard Ding" wrote:
> > Check the "remote proc trans" value from sp_configure. If it's 1, you
need
> > to change it to 0.
> >
> >
> > "Robert M." <RobertM@.discussions.microsoft.com> wrote in message
> > news:28E165FE-67B2-44F1-98DC-1F2CB3EA0B7E@.microsoft.com...
> > > Information:
> > > SQL Server 2000 Enterprise Edition
> > > Server Pack 3a
> > > Server Edition is Windows 2000 Advanced Server
> > > Replication Type is Transactional
> > >
> > > I am trying to drop a publication. I am getting the error:
> > > Error 627:Cannot use SAVE TRANSACTION within a distributed transaction
> > >
> > > This error also is generated when trying to drop the individual
> > subscription.
> > >
> > > Can anyone provide insight as to what could be causing this error?
> > >
> > > Thank you in advance.
> > >
> > > Robert M.
> > >
> > >
> >
> >
> >
Subscribe to:
Posts (Atom)