We are trying to do the Bulk importing XML to SQL 2000 and facing some
problem.
At first, we want to use OPENXML. We can use the bulk insert to import the
XML file to the text field in a table but cannot pass the text data to the
local variable as local variable does not support text and the XML is sure
more than 8000 characters.
So, we try to follow the instruction in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
However, we found the XML format is different from the one in the web site.
It is similar as below:
<TestingData>
<Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E" Field6="F"
Field7="G" />
</TestingData>
This format can be used by OPENXML but cannot use the Bulk Importing.
I have set the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Details" sql:relation="ImportTest" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Field1" type="xsd:string" />
<xsd:element name="Field2" type="xsd:string" />
<xsd:element name="Field3" type="xsd:string" />
<xsd:element name="Field4" type="xsd:string" />
<xsd:element name="Field5" type="xsd:string" />
<xsd:element name="Field6" type="xsd:string" />
<xsd:element name="Field7" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
After running the VBScript, we found the number of records is correct but
all fields become null.
Is there other method we can do?
Ivan
I know the problem. The problem is in the schema.
Ivan
"Ivan" <ivan@.microsoft.com> glsD:ejty019LHHA.140@.TK2MSFTNGP04.phx.gb l...
> We are trying to do the Bulk importing XML to SQL 2000 and facing some
> problem.
> At first, we want to use OPENXML. We can use the bulk insert to import the
> XML file to the text field in a table but cannot pass the text data to the
> local variable as local variable does not support text and the XML is sure
> more than 8000 characters.
> So, we try to follow the instruction in
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
> However, we found the XML format is different from the one in the web
> site. It is similar as below:
> <TestingData>
> <Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E"
> Field6="F" Field7="G" />
> </TestingData>
> This format can be used by OPENXML but cannot use the Bulk Importing.
> I have set the schema as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Details" sql:relation="ImportTest" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Field1" type="xsd:string" />
> <xsd:element name="Field2" type="xsd:string" />
> <xsd:element name="Field3" type="xsd:string" />
> <xsd:element name="Field4" type="xsd:string" />
> <xsd:element name="Field5" type="xsd:string" />
> <xsd:element name="Field6" type="xsd:string" />
> <xsd:element name="Field7" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> After running the VBScript, we found the number of records is correct but
> all fields become null.
> Is there other method we can do?
> Ivan
>
|||Hello,
The data you are trying to bulkload doesn't match the schema definition,
that's why you are getting NULLs.
The schema describes Field1, 2, 3... as elements but they are attributes in
the data file.So you have to change either the schema or the data to make it
work.
Hope this helps.
Regards,
Monica Frintu
"Ivan" wrote:
> I know the problem. The problem is in the schema.
> Ivan
> "Ivan" <ivan@.microsoft.com> ???g?ó?l￥ó·s?D:ejty019LHHA.140@.TK2MSFTNG P04.phx.gbl...
>
>
Showing posts with label facing. Show all posts
Showing posts with label facing. Show all posts
Friday, February 24, 2012
Bulk importing XML to SQL 2000
We are trying to do the Bulk importing XML to SQL 2000 and facing some
problem.
At first, we want to use OPENXML. We can use the bulk insert to import the
XML file to the text field in a table but cannot pass the text data to the
local variable as local variable does not support text and the XML is sure
more than 8000 characters.
So, we try to follow the instruction in
http://msdn.microsoft.com/library/d...>
oad_6bos.asp
However, we found the XML format is different from the one in the web site.
It is similar as below:
<TestingData>
<Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E" Field6="F"
Field7="G" />
</TestingData>
This format can be used by OPENXML but cannot use the Bulk Importing.
I have set the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Details" sql:relation="ImportTest" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Field1" type="xsd:string" />
<xsd:element name="Field2" type="xsd:string" />
<xsd:element name="Field3" type="xsd:string" />
<xsd:element name="Field4" type="xsd:string" />
<xsd:element name="Field5" type="xsd:string" />
<xsd:element name="Field6" type="xsd:string" />
<xsd:element name="Field7" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
After running the VBScript, we found the number of records is correct but
all fields become null.
Is there other method we can do?
IvanI know the problem. The problem is in the schema.
Ivan
"Ivan" <ivan@.microsoft.com> glsD:ejty019LHHA.140@.TK2MSFTNGP04.phx.gbl...rkred">
> We are trying to do the Bulk importing XML to SQL 2000 and facing some
> problem.
> At first, we want to use OPENXML. We can use the bulk insert to import the
> XML file to the text field in a table but cannot pass the text data to the
> local variable as local variable does not support text and the XML is sure
> more than 8000 characters.
> So, we try to follow the instruction in
> http://msdn.microsoft.com/library/d...
kload_6bos.asp
> However, we found the XML format is different from the one in the web
> site. It is similar as below:
> <TestingData>
> <Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E"
> Field6="F" Field7="G" />
> </TestingData>
> This format can be used by OPENXML but cannot use the Bulk Importing.
> I have set the schema as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Details" sql:relation="ImportTest" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Field1" type="xsd:string" />
> <xsd:element name="Field2" type="xsd:string" />
> <xsd:element name="Field3" type="xsd:string" />
> <xsd:element name="Field4" type="xsd:string" />
> <xsd:element name="Field5" type="xsd:string" />
> <xsd:element name="Field6" type="xsd:string" />
> <xsd:element name="Field7" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> After running the VBScript, we found the number of records is correct but
> all fields become null.
> Is there other method we can do?
> Ivan
>|||Hello,
The data you are trying to bulkload doesn't match the schema definition,
that's why you are getting NULLs.
The schema describes Field1, 2, 3... as elements but they are attributes in
the data file.So you have to change either the schema or the data to make it
work.
Hope this helps.
Regards,
Monica Frintu
"Ivan" wrote:
> I know the problem. The problem is in the schema.
> Ivan
> "Ivan" <ivan@.microsoft.com> ???g?ó?l￥ó·s?D:ejty019LHHA.140@.TK2MS
FTNGP04.phx.gbl...
>
>
problem.
At first, we want to use OPENXML. We can use the bulk insert to import the
XML file to the text field in a table but cannot pass the text data to the
local variable as local variable does not support text and the XML is sure
more than 8000 characters.
So, we try to follow the instruction in
http://msdn.microsoft.com/library/d...>
oad_6bos.asp
However, we found the XML format is different from the one in the web site.
It is similar as below:
<TestingData>
<Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E" Field6="F"
Field7="G" />
</TestingData>
This format can be used by OPENXML but cannot use the Bulk Importing.
I have set the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Details" sql:relation="ImportTest" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Field1" type="xsd:string" />
<xsd:element name="Field2" type="xsd:string" />
<xsd:element name="Field3" type="xsd:string" />
<xsd:element name="Field4" type="xsd:string" />
<xsd:element name="Field5" type="xsd:string" />
<xsd:element name="Field6" type="xsd:string" />
<xsd:element name="Field7" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
After running the VBScript, we found the number of records is correct but
all fields become null.
Is there other method we can do?
IvanI know the problem. The problem is in the schema.
Ivan
"Ivan" <ivan@.microsoft.com> glsD:ejty019LHHA.140@.TK2MSFTNGP04.phx.gbl...rkred">
> We are trying to do the Bulk importing XML to SQL 2000 and facing some
> problem.
> At first, we want to use OPENXML. We can use the bulk insert to import the
> XML file to the text field in a table but cannot pass the text data to the
> local variable as local variable does not support text and the XML is sure
> more than 8000 characters.
> So, we try to follow the instruction in
> http://msdn.microsoft.com/library/d...
kload_6bos.asp
> However, we found the XML format is different from the one in the web
> site. It is similar as below:
> <TestingData>
> <Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E"
> Field6="F" Field7="G" />
> </TestingData>
> This format can be used by OPENXML but cannot use the Bulk Importing.
> I have set the schema as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Details" sql:relation="ImportTest" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Field1" type="xsd:string" />
> <xsd:element name="Field2" type="xsd:string" />
> <xsd:element name="Field3" type="xsd:string" />
> <xsd:element name="Field4" type="xsd:string" />
> <xsd:element name="Field5" type="xsd:string" />
> <xsd:element name="Field6" type="xsd:string" />
> <xsd:element name="Field7" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> After running the VBScript, we found the number of records is correct but
> all fields become null.
> Is there other method we can do?
> Ivan
>|||Hello,
The data you are trying to bulkload doesn't match the schema definition,
that's why you are getting NULLs.
The schema describes Field1, 2, 3... as elements but they are attributes in
the data file.So you have to change either the schema or the data to make it
work.
Hope this helps.
Regards,
Monica Frintu
"Ivan" wrote:
> I know the problem. The problem is in the schema.
> Ivan
> "Ivan" <ivan@.microsoft.com> ???g?ó?l￥ó·s?D:ejty019LHHA.140@.TK2MS
FTNGP04.phx.gbl...
>
>
Sunday, February 19, 2012
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS database.
‘Archiving’ process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server.
"Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> ‘Archiving Database’ on the same server) and deleted from the OSS database.
> ‘Archiving’ process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.
|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS database.
‘Archiving’ process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server.
"Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> ‘Archiving Database’ on the same server) and deleted from the OSS database.
> ‘Archiving’ process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.
|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS databa
se.
‘Archiving’ process is taken care by a stored procedure. Also, please no
te
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. Aft
er
> 10 days these requests are supposed to be archived (to a different databas
e
> ‘Archiving Database’ on the same server) and deleted from the OSS data
base.
> ‘Archiving’ process is taken care by a stored procedure. Also, please
note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks ar
e
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk delet
es
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS databa
se.
‘Archiving’ process is taken care by a stored procedure. Also, please no
te
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. Aft
er
> 10 days these requests are supposed to be archived (to a different databas
e
> ‘Archiving Database’ on the same server) and deleted from the OSS data
base.
> ‘Archiving’ process is taken care by a stored procedure. Also, please
note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks ar
e
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk delet
es
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Subscribe to:
Posts (Atom)