Problem reciving xml element

I hvae a problem receiving some xml data from my sql 2000 database and I
cant figure out why.The information is correct in the database and receives
all type of elements except one type.(Even dough this is declare the same).
Hope someone can help me with this problem.
Code:
Here is the xml beeing stored in the database:
<Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
<Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
<Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81" CheckListeID="10">
<ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
ChecklisteElementID="10" />
<ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
ChecklisteElementID="10" />
</Checkliste>
<Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
<ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
<ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
</Liste>
</Gruppe></Soknadsskjema>
This is the xsd schema (stillingsannonse6.xsd)
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="GruppeRef" parent="Soknadsskjemaer" parent-key="ID"
child="Grupper" child-key="SoknadsIDRef" />
<sql:relationship name="GruppeListe" parent="Grupper" parent-key="GruppeID"
child="Lister" child-key="ListeID" />
<sql:relationship name="ListeElementRef" parent="Lister"
parent-key="ElementerID" child="ListeElementer"
child-key="ElementID" />
<sql:relationship name="ChecklisteRef" parent="Grupper"
parent-key="GruppeID" child="Checklister"
child-key="CheckListeID" />
<sql:relationship name="ChecklisteItem" parent="Checklister"
parent-key="CheckID" child="ChecklisteElementer"
child-key="ChecklisteElementID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="Gruppe" sql:relation="Grupper"
sql:relationship="GruppeRef" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence minOccurs="1" maxOccurs="unbounded">
<xsd:element name="Liste" sql:relation="Lister"
sql:relationship="GruppeListe" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ListeElement" sql:relation="ListeElementer"
sql:relationship="ListeElementRef"
minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
<xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
<xsd:element name="Checkliste" sql:relation="Checklister"
sql:relationship="ChecklisteRef" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
sql:relationship="ChecklisteItem"
minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
<xsd:attribute name="DefaultMerket" type="xsd:string" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
<xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
<xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
<xsd:attribute name="Gruppenavn" type="xsd:string" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ID" type="xsd:long" use="required" />
<xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
<xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
It is recieved with this code:
Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
Dim leser As System.Xml.XmlReader
cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
XML = New System.Xml.XmlDataDocument
cmd.SchemaPath = XMLPath
cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
leser = cmd.ExecuteXmlReader
leser.MoveToContent()
XML.Load(leser)
The database look like this:
CREATE TABLE Soknadsskjemaer
(
ID bigint NOT NULL,
UtdanningCV char(5),
ErfaringCV char(5),
CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
insert into Soknadsskjemaer values(0,null,null)
CREATE TABLE SoknadsFiler
(
ID bigint IDENTITY(1,1) NOT NULL,
FilerID bigint NOT NULL,
ExtensionsID bigint NOT NULL unique,
Navn char(60) not null,
Tekst char(150)not null,
MaxSize Int,
CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
REFERENCES Soknadsskjemaer (ID)
)
insert into SoknadsFiler values(0,0,'Standard navn','Standard Tekst',null)
CREATE TABLE Extensions
(
ID bigint IDENTITY(1,1) NOT NULL,
ExtensionID bigint NOT NULL,
Type char(4)not null,
CONSTRAINT Extensions_pk PRIMARY KEY (ID),
CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
REFERENCES SoknadsFiler (ExtensionsID)
)
insert into Extensions values (0,'jpeg')
CREATE TABLE Grupper
(
ID bigint IDENTITY(1,1) NOT NULL,
GruppeID bigint NOT NULL unique,
SoknadsIDRef bigint NOT NULL,
Gruppenavn char(40) not null,
CONSTRAINT Grupper_pk PRIMARY KEY (ID),
CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
REFERENCES Soknadsskjemaer (ID)
)
insert into Grupper values (0,0,'Standardgruppe')
CREATE TABLE Lister
(
ID bigint IDENTITY(1,1) NOT NULL,
ListeID bigint NOT NULL,
ElementerID bigint NOT NULL unique,
Navn char(30) not null,
Tekst char(50) not null,
CONSTRAINT Lister_pk PRIMARY KEY (ID),
CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
REFERENCES Grupper (GruppeID)
)
insert into Lister values (0,0,'StandardNavn','StandardFelt')
CREATE TABLE ListeElementer
(
ID bigint IDENTITY(1,1) NOT NULL,
ElementID bigint NOT NULL,
Navn char(30)not null,
Tekst char(30)not null,
CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
REFERENCES Lister (ElementerID)
)
insert into ListeElementer values (0,'StandardNavn','StandardFelt')
CREATE TABLE Checklister
(
ID bigint IDENTITY(1,1) NOT NULL,
CheckListeID bigint NOT NULL unique,
CheckID bigint NOT NULL,
Navn char(30) not null,
Tekst char(50) not null,
CONSTRAINT Checklister_pk PRIMARY KEY (ID),
CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
REFERENCES Grupper (GruppeID)
)
insert into Checklister values (0,0,'StandardNavn','StandardFelt')
CREATE TABLE ChecklisteElementer
(
ID bigint IDENTITY(1,1) NOT NULL,
ChecklisteElementID bigint NOT NULL,
Navn char(30) not null,
Tekst char(30) not null,
DefaultMerket char(5) NOT NULL,
CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
REFERENCES Checklister (CheckListeID)
)
insert into ChecklisteElementer values
(0,'StandardNavn','StandardFelt','false')
The values is correct in the databae, but when i receive it it looks like
this:
The xml that is returned.
<Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
<Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
<Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
<ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
<ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
</Liste>
</Gruppe>
</Soknadsskjema>
As you can se the Checkliste element is missing, apparently for no reason.
Can someone pleas help me, i have been struggeled with this for a week now.
Marius Pettersen
..Net developer
MP Productions
It looks like the child-key on the ChecklisteRef relationship is wrong. The
child-key should be CheckID, the Foreign Key to the Gruppe table.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"marius" <marius@.discussions.microsoft.com> wrote in message
news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> I hvae a problem receiving some xml data from my sql 2000 database and I
> cant figure out why.The information is correct in the database and
receives
> all type of elements except one type.(Even dough this is declare the
same).
> Hope someone can help me with this problem.
>
> Code:
> Here is the xml beeing stored in the database:
> <Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
> <Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
> <Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81"
CheckListeID="10">
> <ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
> ChecklisteElementID="10" />
> <ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
> ChecklisteElementID="10" />
> </Checkliste>
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe></Soknadsskjema>
> This is the xsd schema (stillingsannonse6.xsd)
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="GruppeRef" parent="Soknadsskjemaer"
parent-key="ID"
> child="Grupper" child-key="SoknadsIDRef" />
> <sql:relationship name="GruppeListe" parent="Grupper"
parent-key="GruppeID"
> child="Lister" child-key="ListeID" />
> <sql:relationship name="ListeElementRef" parent="Lister"
> parent-key="ElementerID" child="ListeElementer"
> child-key="ElementID" />
> <sql:relationship name="ChecklisteRef" parent="Grupper"
> parent-key="GruppeID" child="Checklister"
> child-key="CheckListeID" />
> <sql:relationship name="ChecklisteItem" parent="Checklister"
> parent-key="CheckID" child="ChecklisteElementer"
> child-key="ChecklisteElementID" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element name="Gruppe" sql:relation="Grupper"
> sql:relationship="GruppeRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence minOccurs="1" maxOccurs="unbounded">
> <xsd:element name="Liste" sql:relation="Lister"
> sql:relationship="GruppeListe" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ListeElement" sql:relation="ListeElementer"
> sql:relationship="ListeElementRef"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="Checkliste" sql:relation="Checklister"
> sql:relationship="ChecklisteRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
> sql:relationship="ChecklisteItem"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="DefaultMerket" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
> <xsd:attribute name="Gruppenavn" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="ID" type="xsd:long" use="required" />
> <xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
> <xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> It is recieved with this code:
> Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
> Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
> Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
> Dim leser As System.Xml.XmlReader
> cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
> XML = New System.Xml.XmlDataDocument
> cmd.SchemaPath = XMLPath
> cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
> cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
> leser = cmd.ExecuteXmlReader
> leser.MoveToContent()
> XML.Load(leser)
> The database look like this:
> CREATE TABLE Soknadsskjemaer
> (
> ID bigint NOT NULL,
> UtdanningCV char(5),
> ErfaringCV char(5),
> CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
> insert into Soknadsskjemaer values(0,null,null)
> CREATE TABLE SoknadsFiler
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> FilerID bigint NOT NULL,
> ExtensionsID bigint NOT NULL unique,
> Navn char(60) not null,
> Tekst char(150)not null,
> MaxSize Int,
> CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
> CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into SoknadsFiler values(0,0,'Standard navn','Standard
Tekst',null)
> CREATE TABLE Extensions
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ExtensionID bigint NOT NULL,
> Type char(4)not null,
> CONSTRAINT Extensions_pk PRIMARY KEY (ID),
> CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
> REFERENCES SoknadsFiler (ExtensionsID)
> )
> insert into Extensions values (0,'jpeg')
> CREATE TABLE Grupper
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> GruppeID bigint NOT NULL unique,
> SoknadsIDRef bigint NOT NULL,
> Gruppenavn char(40) not null,
> CONSTRAINT Grupper_pk PRIMARY KEY (ID),
> CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into Grupper values (0,0,'Standardgruppe')
> CREATE TABLE Lister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ListeID bigint NOT NULL,
> ElementerID bigint NOT NULL unique,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Lister_pk PRIMARY KEY (ID),
> CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Lister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ListeElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ElementID bigint NOT NULL,
> Navn char(30)not null,
> Tekst char(30)not null,
> CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
> REFERENCES Lister (ElementerID)
> )
> insert into ListeElementer values (0,'StandardNavn','StandardFelt')
> CREATE TABLE Checklister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> CheckListeID bigint NOT NULL unique,
> CheckID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Checklister_pk PRIMARY KEY (ID),
> CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Checklister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ChecklisteElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ChecklisteElementID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(30) not null,
> DefaultMerket char(5) NOT NULL,
> CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
> REFERENCES Checklister (CheckListeID)
> )
> insert into ChecklisteElementer values
> (0,'StandardNavn','StandardFelt','false')
>
> The values is correct in the databae, but when i receive it it looks like
> this:
> The xml that is returned.
> <Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
> <Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe>
> </Soknadsskjema>
> As you can se the Checkliste element is missing, apparently for no reason.
> Can someone pleas help me, i have been struggeled with this for a week
now.
> --
> Marius Pettersen
> .Net developer
> MP Productions
>
|||Oh, my god, thank you.
I can't belive i missed that.
I have litteraly been watching, comparing and testing for days, and haven't
found it.
Again Thank You!
Marius Pettersen
"Bertan ARI [MSFT]" wrote:

> It looks like the child-key on the ChecklisteRef relationship is wrong. The
> child-key should be CheckID, the Foreign Key to the Gruppe table.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "marius" <marius@.discussions.microsoft.com> wrote in message
> news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> receives
> same).
> CheckListeID="10">
> parent-key="ID"
> parent-key="GruppeID"
> Tekst',null)
> now.
>
>

Problem reciving xml element

I hvae a problem receiving some xml data from my sql 2000 database and I
cant figure out why.The information is correct in the database and receives
all type of elements except one type.(Even dough this is declare the same).
Hope someone can help me with this problem.
Code:
Here is the xml beeing stored in the database:
<Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
<Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
<Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81" CheckListeID="10">
<ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
ChecklisteElementID="10" />
<ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
ChecklisteElementID="10" />
</Checkliste>
<Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
<ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
<ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
</Liste>
</Gruppe></Soknadsskjema>
This is the xsd schema (stillingsannonse6.xsd)
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="GruppeRef" parent="Soknadsskjemaer" parent-key="ID"
child="Grupper" child-key="SoknadsIDRef" />
<sql:relationship name="GruppeListe" parent="Grupper" parent-key="GruppeID"
child="Lister" child-key="ListeID" />
<sql:relationship name="ListeElementRef" parent="Lister"
parent-key="ElementerID" child="ListeElementer"
child-key="ElementID" />
<sql:relationship name="ChecklisteRef" parent="Grupper"
parent-key="GruppeID" child="Checklister"
child-key="CheckListeID" />
<sql:relationship name="ChecklisteItem" parent="Checklister"
parent-key="CheckID" child="ChecklisteElementer"
child-key="ChecklisteElementID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="Gruppe" sql:relation="Grupper"
sql:relationship="GruppeRef" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence minOccurs="1" maxOccurs="unbounded">
<xsd:element name="Liste" sql:relation="Lister"
sql:relationship="GruppeListe" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ListeElement" sql:relation="ListeElementer"
sql:relationship="ListeElementRef"
minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
<xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
<xsd:element name="Checkliste" sql:relation="Checklister"
sql:relationship="ChecklisteRef" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
sql:relationship="ChecklisteItem"
minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
<xsd:attribute name="DefaultMerket" type="xsd:string" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="Navn" type="xsd:string" use="required" />
<xsd:attribute name="Tekst" type="xsd:string" use="required" />
<xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
<xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
<xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
<xsd:attribute name="Gruppenavn" type="xsd:string" />
<xsd:attribute name="ID" type="xsd:unsignedLong" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ID" type="xsd:long" use="required" />
<xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
<xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
It is recieved with this code:
Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
Dim leser As System.Xml.XmlReader
cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
XML = New System.Xml.XmlDataDocument
cmd.SchemaPath = XMLPath
cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
leser = cmd.ExecuteXmlReader
leser.MoveToContent()
XML.Load(leser)
The database look like this:
CREATE TABLE Soknadsskjemaer
(
ID bigint NOT NULL,
UtdanningCV char(5),
ErfaringCV char(5),
CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
insert into Soknadsskjemaer values(0,null,null)
CREATE TABLE SoknadsFiler
(
ID bigint IDENTITY(1,1) NOT NULL,
FilerID bigint NOT NULL,
ExtensionsID bigint NOT NULL unique,
Navn char(60) not null,
Tekst char(150)not null,
MaxSize Int,
CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
REFERENCES Soknadsskjemaer (ID)
)
insert into SoknadsFiler values(0,0,'Standard navn','Standard Tekst',null)
CREATE TABLE Extensions
(
ID bigint IDENTITY(1,1) NOT NULL,
ExtensionID bigint NOT NULL,
Type char(4)not null,
CONSTRAINT Extensions_pk PRIMARY KEY (ID),
CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
REFERENCES SoknadsFiler (ExtensionsID)
)
insert into Extensions values (0,'jpeg')
CREATE TABLE Grupper
(
ID bigint IDENTITY(1,1) NOT NULL,
GruppeID bigint NOT NULL unique,
SoknadsIDRef bigint NOT NULL,
Gruppenavn char(40) not null,
CONSTRAINT Grupper_pk PRIMARY KEY (ID),
CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
REFERENCES Soknadsskjemaer (ID)
)
insert into Grupper values (0,0,'Standardgruppe')
CREATE TABLE Lister
(
ID bigint IDENTITY(1,1) NOT NULL,
ListeID bigint NOT NULL,
ElementerID bigint NOT NULL unique,
Navn char(30) not null,
Tekst char(50) not null,
CONSTRAINT Lister_pk PRIMARY KEY (ID),
CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
REFERENCES Grupper (GruppeID)
)
insert into Lister values (0,0,'StandardNavn','StandardFelt')
CREATE TABLE ListeElementer
(
ID bigint IDENTITY(1,1) NOT NULL,
ElementID bigint NOT NULL,
Navn char(30)not null,
Tekst char(30)not null,
CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
REFERENCES Lister (ElementerID)
)
insert into ListeElementer values (0,'StandardNavn','StandardFelt')
CREATE TABLE Checklister
(
ID bigint IDENTITY(1,1) NOT NULL,
CheckListeID bigint NOT NULL unique,
CheckID bigint NOT NULL,
Navn char(30) not null,
Tekst char(50) not null,
CONSTRAINT Checklister_pk PRIMARY KEY (ID),
CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
REFERENCES Grupper (GruppeID)
)
insert into Checklister values (0,0,'StandardNavn','StandardFelt')
CREATE TABLE ChecklisteElementer
(
ID bigint IDENTITY(1,1) NOT NULL,
ChecklisteElementID bigint NOT NULL,
Navn char(30) not null,
Tekst char(30) not null,
DefaultMerket char(5) NOT NULL,
CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
REFERENCES Checklister (CheckListeID)
)
insert into ChecklisteElementer values
(0,'StandardNavn','StandardFelt','false'
)
The values is correct in the databae, but when i receive it it looks like
this:
The xml that is returned.
<Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
<Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
<Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
<ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
<ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
</Liste>
</Gruppe>
</Soknadsskjema>
As you can se the Checkliste element is missing, apparently for no reason.
Can someone pleas help me, i have been struggeled with this for a w now.
Marius Pettersen
.Net developer
MP ProductionsIt looks like the child-key on the ChecklisteRef relationship is wrong. The
child-key should be CheckID, the Foreign Key to the Gruppe table.
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"marius" <marius@.discussions.microsoft.com> wrote in message
news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> I hvae a problem receiving some xml data from my sql 2000 database and I
> cant figure out why.The information is correct in the database and
receives
> all type of elements except one type.(Even dough this is declare the
same).
> Hope someone can help me with this problem.
>
> Code:
> Here is the xml beeing stored in the database:
> <Soknadsskjema UtdanningCV="True" ErfaringCV="True" ID="112" xmlns="">
> <Gruppe Gruppenavn="Personalia" SoknadsIDRef="112" GruppeID="81">
> <Checkliste Navn="EgenCheck1" Tekst="boxer" CheckID="81"
CheckListeID="10">
> <ChecklisteElement Navn="EgenCheck1_1" Tekst="box1" DefaultMerket="False"
> ChecklisteElementID="10" />
> <ChecklisteElement Navn="EgenCheck1_2" Tekst="box2" DefaultMerket="False"
> ChecklisteElementID="10" />
> </Checkliste>
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe></Soknadsskjema>
> This is the xsd schema (stillingsannonse6.xsd)
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="GruppeRef" parent="Soknadsskjemaer"
parent-key="ID"
> child="Grupper" child-key="SoknadsIDRef" />
> <sql:relationship name="GruppeListe" parent="Grupper"
parent-key="GruppeID"
> child="Lister" child-key="ListeID" />
> <sql:relationship name="ListeElementRef" parent="Lister"
> parent-key="ElementerID" child="ListeElementer"
> child-key="ElementID" />
> <sql:relationship name="ChecklisteRef" parent="Grupper"
> parent-key="GruppeID" child="Checklister"
> child-key="CheckListeID" />
> <sql:relationship name="ChecklisteItem" parent="Checklister"
> parent-key="CheckID" child="ChecklisteElementer"
> child-key="ChecklisteElementID" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="Soknadsskjema" sql:relation="Soknadsskjemaer">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element name="Gruppe" sql:relation="Grupper"
> sql:relationship="GruppeRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence minOccurs="1" maxOccurs="unbounded">
> <xsd:element name="Liste" sql:relation="Lister"
> sql:relationship="GruppeListe" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ListeElement" sql:relation="ListeElementer"
> sql:relationship="ListeElementRef"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ElementerID" type="xsd:unsignedLong" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="Checkliste" sql:relation="Checklister"
> sql:relationship="ChecklisteRef" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ChecklisteElement" sql:relation="ChecklisteElementer"
> sql:relationship="ChecklisteItem"
> minOccurs="1" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="ChecklisteElementID" type="xsd:unsignedLong"/>
> <xsd:attribute name="DefaultMerket" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="Navn" type="xsd:string" use="required" />
> <xsd:attribute name="Tekst" type="xsd:string" use="required" />
> <xsd:attribute name="CheckListeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="CheckID" type="xsd:unsignedLong"/>
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="GruppeID" type="xsd:unsignedLong"/>
> <xsd:attribute name="SoknadsIDRef" type="xsd:unsignedInt" />
> <xsd:attribute name="Gruppenavn" type="xsd:string" />
> <xsd:attribute name="ID" type="xsd:unsignedLong" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="ID" type="xsd:long" use="required" />
> <xsd:attribute name="UtdanningCV" type="xsd:string" use="required" />
> <xsd:attribute name="ErfaringCV" type="xsd:string" use="required" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> It is recieved with this code:
> Dim XMLPath As String = savepath + "\" + "Soknadsskjema6.xsd"
> Dim XML As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument
> Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand
> Dim leser As System.Xml.XmlReader
> cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(dbstreng2)
> XML = New System.Xml.XmlDataDocument
> cmd.SchemaPath = XMLPath
> cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.XPath
> cmd.CommandText = "Soknadsskjema[@.ID=" & soknadsID & "]"
> leser = cmd.ExecuteXmlReader
> leser.MoveToContent()
> XML.Load(leser)
> The database look like this:
> CREATE TABLE Soknadsskjemaer
> (
> ID bigint NOT NULL,
> UtdanningCV char(5),
> ErfaringCV char(5),
> CONSTRAINT Soknadsskjemaer_pk PRIMARY KEY (ID))
> insert into Soknadsskjemaer values(0,null,null)
> CREATE TABLE SoknadsFiler
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> FilerID bigint NOT NULL,
> ExtensionsID bigint NOT NULL unique,
> Navn char(60) not null,
> Tekst char(150)not null,
> MaxSize Int,
> CONSTRAINT SoknadsFiler_pk PRIMARY KEY (ID),
> CONSTRAINT SoknadsFiler_fk FOREIGN KEY (FilerID)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into SoknadsFiler values(0,0,'Standard navn','Standard
Tekst',null)
> CREATE TABLE Extensions
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ExtensionID bigint NOT NULL,
> Type char(4)not null,
> CONSTRAINT Extensions_pk PRIMARY KEY (ID),
> CONSTRAINT Extensions_fk FOREIGN KEY (ExtensionID)
> REFERENCES SoknadsFiler (ExtensionsID)
> )
> insert into Extensions values (0,'jpeg')
> CREATE TABLE Grupper
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> GruppeID bigint NOT NULL unique,
> SoknadsIDRef bigint NOT NULL,
> Gruppenavn char(40) not null,
> CONSTRAINT Grupper_pk PRIMARY KEY (ID),
> CONSTRAINT Grupper_fk FOREIGN KEY (SoknadsIDRef)
> REFERENCES Soknadsskjemaer (ID)
> )
> insert into Grupper values (0,0,'Standardgruppe')
> CREATE TABLE Lister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ListeID bigint NOT NULL,
> ElementerID bigint NOT NULL unique,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Lister_pk PRIMARY KEY (ID),
> CONSTRAINT Lister_fk FOREIGN KEY (ListeID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Lister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ListeElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ElementID bigint NOT NULL,
> Navn char(30)not null,
> Tekst char(30)not null,
> CONSTRAINT ListeElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ListeElementer_fk FOREIGN KEY (ElementID)
> REFERENCES Lister (ElementerID)
> )
> insert into ListeElementer values (0,'StandardNavn','StandardFelt')
> CREATE TABLE Checklister
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> CheckListeID bigint NOT NULL unique,
> CheckID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(50) not null,
> CONSTRAINT Checklister_pk PRIMARY KEY (ID),
> CONSTRAINT Checklister_fk FOREIGN KEY (CheckID)
> REFERENCES Grupper (GruppeID)
> )
> insert into Checklister values (0,0,'StandardNavn','StandardFelt')
> CREATE TABLE ChecklisteElementer
> (
> ID bigint IDENTITY(1,1) NOT NULL,
> ChecklisteElementID bigint NOT NULL,
> Navn char(30) not null,
> Tekst char(30) not null,
> DefaultMerket char(5) NOT NULL,
> CONSTRAINT ChecklisteElementer_pk PRIMARY KEY (ID),
> CONSTRAINT ChecklisteElementer_fk FOREIGN KEY (ChecklisteElementID)
> REFERENCES Checklister (CheckListeID)
> )
> insert into ChecklisteElementer values
> (0,'StandardNavn','StandardFelt','false'
)
>
> The values is correct in the databae, but when i receive it it looks like
> this:
> The xml that is returned.
> <Soknadsskjema ID="112" UtdanningCV="True " ErfaringCV="True ">
> <Gruppe GruppeID="81" SoknadsIDRef="112" Gruppenavn="Personalia" ID="98">
> <Liste Navn="EgenListe1" Tekst="Listen" ListeID="81" ElementerID="13">
> <ListeElement Navn="EgenListe1_1" Tekst="Liste1" ElementID="13" />
> <ListeElement Navn="EgenListe1_2" Tekst="Liste2" ElementID="13" />
> </Liste>
> </Gruppe>
> </Soknadsskjema>
> As you can se the Checkliste element is missing, apparently for no reason.
> Can someone pleas help me, i have been struggeled with this for a w
now.
> --
> Marius Pettersen
> .Net developer
> MP Productions
>|||Oh, my god, thank you.
I can't belive i missed that.
I have litteraly been watching, comparing and testing for days, and haven't
found it.
Again Thank You!
Marius Pettersen
"Bertan ARI [MSFT]" wrote:

> It looks like the child-key on the ChecklisteRef relationship is wrong. Th
e
> child-key should be CheckID, the Foreign Key to the Gruppe table.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "marius" <marius@.discussions.microsoft.com> wrote in message
> news:AF46E927-B3CD-48CC-823F-00C36B7BE00D@.microsoft.com...
> receives
> same).
> CheckListeID="10">
> parent-key="ID"
> parent-key="GruppeID"
> Tekst',null)
> now.
>
>sql

Problem passing parameter into remote stored proc

I'm having a problem passing a parameter value into a stored procedure
that I am running on a remote (linked) server, and am receiving a DTC
error because of it.
I have a stored procedure that brings in a variable (@.CustID int). I
later pass that parameter to another stored procedure. The code looks
like this...
EXEC LinkedServer.dbname.dbo.spname @.CustID
When I run that, I get this error...
Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
However, if I hard-code the parameter, it works:
EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
I can even do this:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
But if I accept the variable as an input parameter to my stored
procedure, I get the error listed above.
Any ideas?
Thanks in advance for your help...
Zev Steinhardtzev_steinhardt
what happen if you assign the parameter to a variable?
DECLARE @.var int
SET @.var = @.CustID
EXEC LinkedServer.dbname.dbo.spname @.var
...
AMB
"zev_steinhardt" wrote:

> I'm having a problem passing a parameter value into a stored procedure
> that I am running on a remote (linked) server, and am receiving a DTC
> error because of it.
> I have a stored procedure that brings in a variable (@.CustID int). I
> later pass that parameter to another stored procedure. The code looks
> like this...
> EXEC LinkedServer.dbname.dbo.spname @.CustID
> When I run that, I get this error...
> Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> However, if I hard-code the parameter, it works:
> EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
> I can even do this:
> DECLARE @.var int
> SET @.var = 1234
> EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
> But if I accept the variable as an input parameter to my stored
> procedure, I get the error listed above.
> Any ideas?
> Thanks in advance for your help...
> Zev Steinhardt
>|||Thanks for the reply, Alejandro.
I tried that. It didn't work.
I even tried to trick it into thinking that it's another variable
altogether. I put the variable into a temp table, declared a new
variable, populated it with the value from the temp table and passed it
in. That didn't work either.
Zev Steinhardt|||zev_steinhardt,
Are you executing the remote sp inside a transaction?
AMB
"zev_steinhardt" wrote:

> Thanks for the reply, Alejandro.
> I tried that. It didn't work.
> I even tried to trick it into thinking that it's another variable
> altogether. I put the variable into a temp table, declared a new
> variable, populated it with the value from the temp table and passed it
> in. That didn't work either.
> Zev Steinhardt
>|||Yes. The remote sp is within a transaction.
Zev|||zev_steinhardt,
you are using a distributed one, correct?
begin distributed transaction
exec ...
AMB
"zev_steinhardt" wrote:

> Yes. The remote sp is within a transaction.
> Zev
>|||Alejandro...
Yes, it is a distributed transaction... and I have XACT_ABORT on
Zev|||zev_steinhardt,
When you execute the remote sp using:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
then you are not executing it using a distributed transaction, that is why
you do not get the error.
See if this helps.
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
AMB
"zev_steinhardt" wrote:

> Alejandro...
> Yes, it is a distributed transaction... and I have XACT_ABORT on
> Zev
>