I have added MSDE merge modules in VS Installer as well as MDF and LDF file,
now what should I do, so that setup can automatically Attach the database
after installing MSDE client's Machine.
Best Regards,
Luqman
Hi Luqman,
see this thread here:
http://groups.google.de/group/micros...8f627e88171d02
HTH, Jens Suessmeyer.
Showing posts with label automatically. Show all posts
Showing posts with label automatically. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
How could I restore a backup automatically?
How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison
How could I restore a backup automatically?
How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison
How could I restore a backup automatically?
How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>
|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>
|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison
Monday, March 19, 2012
How can XML Bulk Load assign automatically a PK to a column?
Hi to everybody!
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David GrantThe table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.com...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?xml version="1.0" encoding="UTF-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant
>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<!-- ProductID IS THE PRIMARY KEY FOR THE
SpecialProductsTABLE -->
<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<!-- CategoryID IS THE PRIMARY KEY FOR THE
SpecialCategoriesTABLE -->
<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
" PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=Northwind;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant
>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David GrantThe table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.com...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?xml version="1.0" encoding="UTF-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant
>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<!-- ProductID IS THE PRIMARY KEY FOR THE
SpecialProductsTABLE -->
<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<!-- CategoryID IS THE PRIMARY KEY FOR THE
SpecialCategoriesTABLE -->
<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
" PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=Northwind;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant
>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
How can XML Bulk Load assign automatically a PK to a column?
Hi to everybody!
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David Grant
The table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.c om...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?xml version="1.0" encoding="UTF-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant
|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant
>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>
|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<!-- ProductID IS THE PRIMARY KEY FOR THE
SpecialProductsTABLE -->
<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<!-- CategoryID IS THE PRIMARY KEY FOR THE
SpecialCategoriesTABLE -->
<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
"PROVIDER=SQLOLEDB;SERVER=(local);DATABASE=Northwi nd;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant
>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David Grant
The table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.c om...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?xml version="1.0" encoding="UTF-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant
|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant
>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>
|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>
<!-- ProductID IS THE PRIMARY KEY FOR THE
SpecialProductsTABLE -->
<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<!-- CategoryID IS THE PRIMARY KEY FOR THE
SpecialCategoriesTABLE -->
<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
"PROVIDER=SQLOLEDB;SERVER=(local);DATABASE=Northwi nd;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant
>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
Friday, March 9, 2012
How can SQL Server DTC be stopped from automatically enlisting a remote server in a distributed
How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?
I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.
I am executing the following statement in Query analyzer.
INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@.RemoteQuery,
@.ParameterDefinition,
@.Paramter = 'somevalue'
@.RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
Anyone know the answer?
How can SQL Server DTC be stopped from automatically enlisting a remote server in a distributed
How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?
I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.
I am executing the following statement in Query analyzer.
INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@.RemoteQuery,
@.ParameterDefinition,
@.Paramter = 'somevalue'
@.RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
Anyone know the answer?
Wednesday, March 7, 2012
how can it automatically generating a ordered number
hi,
i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.
as asp.net is new for me, i would have some simple problems to ask.
1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me
2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.
thanks
Rgds, universe
1. I suggest you can have two columns: one used to record the inserted data for the row, another for row ID. So you can create a table as following:
create table tbl_test(id int identity(1,1),RecDate smalldatetime default getdate(), Description varchar(200))
create table tbl_test1(id uniqueidentifier default newid(),RecDate smalldatetime default getdate(), Description varchar(200))
insert into tbl_test(description) select 'This is a test row'
insert into tbl_test1(description) select 'This is a test row'
2. Sorry I'm not clear about this issue
Friday, February 24, 2012
How can I use getdate() to be an input parameter in a sproc?
I am trying to write a sproc that automatically uses the system date (i.e. -
getdate()) as an input parameter. Even though it displays it does not beha
ve like an input parameter.Casey
Do you need the entire date down the minutes and seconds, in other words, an
exact snapshot
of the date?
If not, you can just refer to GETDATE() right within your procedure and
bypass the
parameter part.
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.|||Hi,
Use the below sample,
alter proc test_proc2
as
begin
declare @.to_day smalldatetime
set @.to_day = getdate()
select @.to_day
end
Incase if it is a must to have date as input parameter then,
alter proc test_proc2 @.to_day datetime = '01/01/1900'
as
begin
set @.to_day = getdate()
select @.to_day
end
Thanks
Hari
MCDBA
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.
getdate()) as an input parameter. Even though it displays it does not beha
ve like an input parameter.Casey
Do you need the entire date down the minutes and seconds, in other words, an
exact snapshot
of the date?
If not, you can just refer to GETDATE() right within your procedure and
bypass the
parameter part.
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.|||Hi,
Use the below sample,
alter proc test_proc2
as
begin
declare @.to_day smalldatetime
set @.to_day = getdate()
select @.to_day
end
Incase if it is a must to have date as input parameter then,
alter proc test_proc2 @.to_day datetime = '01/01/1900'
as
begin
set @.to_day = getdate()
select @.to_day
end
Thanks
Hari
MCDBA
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.
Subscribe to:
Comments (Atom)