build sql script: fix

Showing posts with label fix. Show all posts

Thursday, March 29, 2012

Bulk Upload : General operational error

Hi all. Have checked around and tried to fix this, but no joy as of yet.
I have got to the point where i have actually flipped most of the
values in the Bulk Upload component and even commented out all of the
data that is to be interted (and even set schemagen to true).
This is actually the simplest of all the things i have worked on
recently and i think it is to do with the setup of the tables/relations
(done by someone else).
[I include the Sql generates for the related tables at the bottom of
this post]
I am inserting into table B, which has a column which stores the ID of
a key in table A (it is not a key in table B because i can apparently
be NULL sometimes).
Additionally i insert a new lookup record into table C and use the
inverse attribute to get the newly inserted ID and plug this into table
B.
It is ridicously simple. Before I was getting an exception that it
couldn't drop some temporary table, but after i removed all the data
from the Xml file, i guess Bulk Upload just runs through the rest of
the process, but determines there is no data and terminates - it seems
that at that termination point things go wrong.
The message i get is below (although i doubt it will be of much use):
<?xml version="1.0"?><Result
State="FAILED"><Error><HResult>0x8000FFFF</HResult><Description>General
operational
error</Description><Source><![CDATA[]]></Source><Type>FATAL</Type></Erro
r></Result>
I finish this contract tomorrow, so any help before then would be much
appreciated. Who ever said leave the easiest to last !?
steven :: http://stevenR2.com
========================================
=============================
========================================
=============================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_SchemeAssetValueBAMLDIFundRun_CashFl
owType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeAssetValueBAMLDIFundRun] DROP CONSTRAINT
FK_SchemeAssetValueBAMLDIFundRun_CashFlo
wType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_SchemeCashFlowNominal_CashFlowType]'
) and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeCashFlowNominal] DROP CONSTRAINT
FK_SchemeCashFlowNominal_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_SchemeFinalSummaryRun_CashFlowType]'
) and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeFinalSummaryRun] DROP CONSTRAINT
FK_SchemeFinalSummaryRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_ClientSchemeLBPDetail_CashFlowType]'
) and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLBPDetail] DROP CONSTRAINT
FK_ClientSchemeLBPDetail_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_SchemeLDIFundRevaluationBucketRun_Ca
shFlowType]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLDIFundRevaluationBucketRun] DROP CONSTRAINT
FK_SchemeLDIFundRevaluationBucketRun_Cas
hFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_BAMLDIFund_CashFlowType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLDIFundValue] DROP CONSTRAINT
FK_BAMLDIFund_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_SchemePostRebalanceSummaryRun_CashFl
owType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemePostRebalanceSummaryRun] DROP CONSTRAINT
FK_SchemePostRebalanceSummaryRun_CashFlo
wType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemePostRegearRun_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemePostRegearRun] DROP CONSTRAINT
FK_SchemePostRegearRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientScheme_SchemeStatus]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Scheme] DROP CONSTRAINT FK_ClientScheme_SchemeStatus
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientScheme_SchemeType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Scheme] DROP CONSTRAINT FK_ClientScheme_SchemeType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Audit_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Audit] DROP CONSTRAINT FK_Audit_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientScheme_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Scheme] DROP CONSTRAINT FK_ClientScheme_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeRun_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeRun] DROP CONSTRAINT FK_SchemeRun_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SystemUserSchemeRun_SystemUser]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SystemUserSchemeRun] DROP CONSTRAINT
FK_SystemUserSchemeRun_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_YieldModel_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[YieldModel] DROP CONSTRAINT FK_YieldModel_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeBucket_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeBucket] DROP CONSTRAINT FK_SchemeBucket_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeCashflow_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeCashflow] DROP CONSTRAINT
FK_SchemeCashflow_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeCashFlowNominal_Scheme]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeCashFlowNominal] DROP CONSTRAINT
FK_SchemeCashFlowNominal_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientSchemeLBP_ClientScheme]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLBP] DROP CONSTRAINT
FK_ClientSchemeLBP_ClientScheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeLDIFund_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLDIFund] DROP CONSTRAINT
FK_SchemeLDIFund_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeNonLDIFund_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeNonLDIFund] DROP CONSTRAINT
FK_SchemeNonLDIFund_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ThirdPartyAsset_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ThirdPartyAsset] DROP CONSTRAINT
FK_ThirdPartyAsset_Scheme
GO
/****** Object: Table [dbo].[SchemeCashFlowNominal] Script Date:
29/12/2005 13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SchemeCashFlowNominal]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[SchemeCashFlowNominal]
GO
/****** Object: Table [dbo].[Scheme] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Scheme]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Scheme]
GO
/****** Object: Table [dbo].[CashFlowType] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CashFlowType]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[CashFlowType]
GO
/****** Object: Table [dbo].[SchemeStatus] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SchemeStatus]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[SchemeStatus]
GO
/****** Object: Table [dbo].[SchemeType] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SchemeType]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[SchemeType]
GO
/****** Object: Table [dbo].[SystemUser] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SystemUser]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[SystemUser]
GO
/****** Object: Table [dbo].[CashFlowType] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[CashFlowType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchemeStatus] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[SchemeStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchemeType] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[SchemeType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SystemUser] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[SystemUser] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Scheme] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[Scheme] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ReferenceID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SchemeName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InterLinkID] [int] NULL ,
[SchemeTypeID] [int] NOT NULL ,
[SchemeStatusID] [int] NOT NULL ,
[MinTransactionAmount] [numeric](18, 10) NULL ,
[SystemUserID] [int] NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchemeCashFlowNominal] Script Date:
29/12/2005 13:30:25 ******/
CREATE TABLE [dbo].[SchemeCashFlowNominal] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CashFlowTypeID] [int] NOT NULL ,
[SchemeID] [int] NOT NULL ,
[NominalValue] [numeric](18, 10) NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CashFlowType] WITH NOCHECK ADD
CONSTRAINT [PK_CashFlowType] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeStatus] WITH NOCHECK ADD
CONSTRAINT [PK_PensionSchemeStatus] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeType] WITH NOCHECK ADD
CONSTRAINT [PK_PensionSchemeType] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SystemUser] WITH NOCHECK ADD
CONSTRAINT [PK_SystemUser] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Scheme] WITH NOCHECK ADD
CONSTRAINT [PK_ClientScheme] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeCashFlowNominal] WITH NOCHECK ADD
CONSTRAINT [PK_SchemeCashFlowNominal] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeStatus] ADD
CONSTRAINT [DF_SchemeStatus_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[SchemeType] ADD
CONSTRAINT [DF_SchemeType_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[SystemUser] ADD
CONSTRAINT [DF_SystemUser_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[Scheme] ADD
CONSTRAINT [DF_Scheme_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[Scheme] ADD
CONSTRAINT [FK_ClientScheme_SchemeStatus] FOREIGN KEY
(
[SchemeStatusID]
) REFERENCES [dbo].[SchemeStatus] (
[ID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_ClientScheme_SchemeType] FOREIGN KEY
(
[SchemeTypeID]
) REFERENCES [dbo].[SchemeType] (
[ID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_ClientScheme_SystemUser] FOREIGN KEY
(
[SystemUserID]
) REFERENCES [dbo].[SystemUser] (
[ID]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[SchemeCashFlowNominal] ADD
CONSTRAINT [FK_SchemeCashFlowNominal_CashFlowType] FOREIGN KEY
(
[CashFlowTypeID]
) REFERENCES [dbo].[CashFlowType] (
[ID]
),
CONSTRAINT [FK_SchemeCashFlowNominal_Scheme] FOREIGN KEY
(
[SchemeID]
) REFERENCES [dbo].[Scheme] (
[ID]
)
GOFWIW - here is the xml and schema. I have reduced this to just one
table and it still fails so maybe i am missing something in the schema.
Xml and schema below.
<?xml version="1.0" encoding="utf-8"?>
<SchemaList xmlns="http://tempuri.org/SchemeNominal.xsd">
<Scheme>
<InterLinkID>1</InterLinkID>
<SchemeCashFlowNominalList>
<Items>
<SchemeCashFlowNominal>

</SchemeCashFlowNominal>
</Items>
</SchemeCashFlowNominalList>
</Scheme>
</SchemaList>
<?xml version="1.0"?>

<xsd:schema
xmlns="http://tempuri.org/SchemeNominal.xsd"
targetNamespace="http://tempuri.org/SchemeNominal.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
elementFormDefault="qualified">

<xsd:element name="SchemaList" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element ref="Scheme" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Scheme" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="InterLinkID" type="xsd:string" minOccurs="1"
/>
<xsd:element name="SchemeCashFlowNominalList"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Items" minOccurs="0"
maxOccurs="unbounded" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="SchemeCashFlowNominal"
sql:relation="SchemeCachFlowNominal2">
<xsd:complexType>
<xsd:sequence>


</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Bulk Upload : General operational error

Hi all. Have checked around and tried to fix this, but no joy as of yet.
I have got to the point where i have actually flipped most of the
values in the Bulk Upload component and even commented out all of the
data that is to be interted (and even set schemagen to true).
This is actually the simplest of all the things i have worked on
recently and i think it is to do with the setup of the tables/relations
(done by someone else).
[I include the Sql generates for the related tables at the bottom of
this post]
I am inserting into table B, which has a column which stores the ID of
a key in table A (it is not a key in table B because i can apparently
be NULL sometimes).
Additionally i insert a new lookup record into table C and use the
inverse attribute to get the newly inserted ID and plug this into table
B.
It is ridicously simple. Before I was getting an exception that it
couldn't drop some temporary table, but after i removed all the data
from the Xml file, i guess Bulk Upload just runs through the rest of
the process, but determines there is no data and terminates - it seems
that at that termination point things go wrong.
The message i get is below (although i doubt it will be of much use):
<?xml version="1.0"?><Result
State="FAILED"><Error><HResult>0x8000FFFF</HResult><Description>General
operational
error</Description><Source><![CDATA[]]></Source><Type>FATAL</Type></Erro
r></Result>
I finish this contract tomorrow, so any help before then would be much
appreciated. Who ever said leave the easiest to last !?
steven :: http://stevenR2.com
================================================== ===================
================================================== ===================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeAssetValueBAMLDIFundRun_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeAssetValueBAMLDIFundRun] DROP CONSTRAINT
FK_SchemeAssetValueBAMLDIFundRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeCashFlowNominal_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeCashFlowNominal] DROP CONSTRAINT
FK_SchemeCashFlowNominal_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeFinalSummaryRun_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeFinalSummaryRun] DROP CONSTRAINT
FK_SchemeFinalSummaryRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientSchemeLBPDetail_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLBPDetail] DROP CONSTRAINT
FK_ClientSchemeLBPDetail_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeLDIFundRevaluationBucketRun_CashFlowTyp e]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLDIFundRevaluationBucketRun] DROP CONSTRAINT
FK_SchemeLDIFundRevaluationBucketRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_BAMLDIFund_CashFlowType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLDIFundValue] DROP CONSTRAINT
FK_BAMLDIFund_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemePostRebalanceSummaryRun_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemePostRebalanceSummaryRun] DROP CONSTRAINT
FK_SchemePostRebalanceSummaryRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemePostRegearRun_CashFlowType]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemePostRegearRun] DROP CONSTRAINT
FK_SchemePostRegearRun_CashFlowType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientScheme_SchemeStatus]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Scheme] DROP CONSTRAINT FK_ClientScheme_SchemeStatus
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientScheme_SchemeType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Scheme] DROP CONSTRAINT FK_ClientScheme_SchemeType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Audit_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Audit] DROP CONSTRAINT FK_Audit_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientScheme_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Scheme] DROP CONSTRAINT FK_ClientScheme_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeRun_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeRun] DROP CONSTRAINT FK_SchemeRun_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SystemUserSchemeRun_SystemUser]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SystemUserSchemeRun] DROP CONSTRAINT
FK_SystemUserSchemeRun_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_YieldModel_SystemUser]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[YieldModel] DROP CONSTRAINT FK_YieldModel_SystemUser
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeBucket_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeBucket] DROP CONSTRAINT FK_SchemeBucket_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeCashflow_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeCashflow] DROP CONSTRAINT
FK_SchemeCashflow_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeCashFlowNominal_Scheme]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeCashFlowNominal] DROP CONSTRAINT
FK_SchemeCashFlowNominal_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ClientSchemeLBP_ClientScheme]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLBP] DROP CONSTRAINT
FK_ClientSchemeLBP_ClientScheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeLDIFund_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeLDIFund] DROP CONSTRAINT
FK_SchemeLDIFund_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SchemeNonLDIFund_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SchemeNonLDIFund] DROP CONSTRAINT
FK_SchemeNonLDIFund_Scheme
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ThirdPartyAsset_Scheme]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ThirdPartyAsset] DROP CONSTRAINT
FK_ThirdPartyAsset_Scheme
GO
/****** Object: Table [dbo].[SchemeCashFlowNominal] Script Date:
29/12/2005 13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SchemeCashFlowNominal]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[SchemeCashFlowNominal]
GO
/****** Object: Table [dbo].[Scheme] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Scheme]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Scheme]
GO
/****** Object: Table [dbo].[CashFlowType] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CashFlowType]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[CashFlowType]
GO
/****** Object: Table [dbo].[SchemeStatus] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SchemeStatus]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[SchemeStatus]
GO
/****** Object: Table [dbo].[SchemeType] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SchemeType]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[SchemeType]
GO
/****** Object: Table [dbo].[SystemUser] Script Date: 29/12/2005
13:30:23 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SystemUser]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[SystemUser]
GO
/****** Object: Table [dbo].[CashFlowType] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[CashFlowType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchemeStatus] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[SchemeStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchemeType] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[SchemeType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SystemUser] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[SystemUser] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Scheme] Script Date: 29/12/2005
13:30:25 ******/
CREATE TABLE [dbo].[Scheme] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ReferenceID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SchemeName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InterLinkID] [int] NULL ,
[SchemeTypeID] [int] NOT NULL ,
[SchemeStatusID] [int] NOT NULL ,
[MinTransactionAmount] [numeric](18, 10) NULL ,
[SystemUserID] [int] NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[MarkedForDeletion] [bit] NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchemeCashFlowNominal] Script Date:
29/12/2005 13:30:25 ******/
CREATE TABLE [dbo].[SchemeCashFlowNominal] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CashFlowTypeID] [int] NOT NULL ,
[SchemeID] [int] NOT NULL ,
[NominalValue] [numeric](18, 10) NOT NULL ,
[RowTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CashFlowType] WITH NOCHECK ADD
CONSTRAINT [PK_CashFlowType] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeStatus] WITH NOCHECK ADD
CONSTRAINT [PK_PensionSchemeStatus] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeType] WITH NOCHECK ADD
CONSTRAINT [PK_PensionSchemeType] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SystemUser] WITH NOCHECK ADD
CONSTRAINT [PK_SystemUser] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Scheme] WITH NOCHECK ADD
CONSTRAINT [PK_ClientScheme] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeCashFlowNominal] WITH NOCHECK ADD
CONSTRAINT [PK_SchemeCashFlowNominal] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SchemeStatus] ADD
CONSTRAINT [DF_SchemeStatus_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[SchemeType] ADD
CONSTRAINT [DF_SchemeType_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[SystemUser] ADD
CONSTRAINT [DF_SystemUser_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[Scheme] ADD
CONSTRAINT [DF_Scheme_MarkedForDeletion] DEFAULT (0) FOR
[MarkedForDeletion]
GO
ALTER TABLE [dbo].[Scheme] ADD
CONSTRAINT [FK_ClientScheme_SchemeStatus] FOREIGN KEY
(
[SchemeStatusID]
) REFERENCES [dbo].[SchemeStatus] (
[ID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_ClientScheme_SchemeType] FOREIGN KEY
(
[SchemeTypeID]
) REFERENCES [dbo].[SchemeType] (
[ID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_ClientScheme_SystemUser] FOREIGN KEY
(
[SystemUserID]
) REFERENCES [dbo].[SystemUser] (
[ID]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[SchemeCashFlowNominal] ADD
CONSTRAINT [FK_SchemeCashFlowNominal_CashFlowType] FOREIGN KEY
(
[CashFlowTypeID]
) REFERENCES [dbo].[CashFlowType] (
[ID]
),
CONSTRAINT [FK_SchemeCashFlowNominal_Scheme] FOREIGN KEY
(
[SchemeID]
) REFERENCES [dbo].[Scheme] (
[ID]
)
GO
FWIW - here is the xml and schema. I have reduced this to just one
table and it still fails so maybe i am missing something in the schema.
Xml and schema below.
<?xml version="1.0" encoding="utf-8"?>
<SchemaList xmlns="http://tempuri.org/SchemeNominal.xsd">
<Scheme>
<InterLinkID>1</InterLinkID>
<SchemeCashFlowNominalList>
<Items>
<SchemeCashFlowNominal>

</SchemeCashFlowNominal>
</Items>
</SchemeCashFlowNominalList>
</Scheme>
</SchemaList>
<?xml version="1.0"?>

<xsd:schema
xmlns="http://tempuri.org/SchemeNominal.xsd"
targetNamespace="http://tempuri.org/SchemeNominal.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
elementFormDefault="qualified">

<xsd:element name="SchemaList" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element ref="Scheme" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Scheme" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="InterLinkID" type="xsd:string" minOccurs="1"
/>
<xsd:element name="SchemeCashFlowNominalList"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Items" minOccurs="0"
maxOccurs="unbounded" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="SchemeCashFlowNominal"
sql:relation="SchemeCachFlowNominal2">
<xsd:complexType>
<xsd:sequence>


</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Wednesday, March 7, 2012

Bulk Insert Data in Millions - Lock Issue

Hi all,
Hope there is a quick fix for this:

I am inserting data from one table to another on the same DB. The
insert is pretty simple as in:

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable...

This inserts about 4 millions rows in one go. And since I had the
'cannot obtain lock resources' problem, several methods were suggested
by some web sites:
1) one to split the insert into smaller chunks (I have no idea how I
can spit a insert to insert only n records at a time..)

2)to use waitfor - which I did but did not fix the error.

3)use bulk insert (in t-sql) - I dont know how to do this?

As I see I am simply trying to move data from one table to another
(ofcourse lots of data) in SQL Server 2000 and I dont see one simple
solution to the locking problem.

any ideas on how best I can do this will save my day!

thanks all.Hi,

Adding in smaller chunks doesn't solve the problem. Adding small chunk is
the same as adding big chunk. Size doesn't matter.

Looks like you are out of option.

How about adding the records one by one in the middle of the night instead?

"adi" <Adityanad@.gmail.com> wrote in message
news:1129063850.247250.54770@.g49g2000cwa.googlegro ups.com...
> Hi all,
> Hope there is a quick fix for this:
>
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)
>
> 2)to use waitfor - which I did but did not fix the error.
>
> 3)use bulk insert (in t-sql) - I dont know how to do this?
>
> As I see I am simply trying to move data from one table to another
> (ofcourse lots of data) in SQL Server 2000 and I dont see one simple
> solution to the locking problem.
>
> any ideas on how best I can do this will save my day!
>
> thanks all.|||adi (Adityanad@.gmail.com) writes:
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)

There are a couple of techniques. But for best performance, you need
a clustered index in the table. This does not have to be unqiue though.
Say that you have a date column in the table that has a decent distribution.
Create a clustered index on tbat column:

CREATE CLUSTERED INDEX ON temptable(datecol)

Then you can do:

DECLARE @.this_date datetime,
@.next_date datetim
SELECT @.this_date = MIN(datecol) FROM temptable
WHILE @.this_date <= (SELECT datecol FROM temptable)
BEGIN
SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
INSERT dataable (...)
SELECT ...
FROM temptable
WHERE datecol >= @.this_date
AND daetcol < @.next_date
SELECT @.this_date = @.next_date
END

It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.

If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.

You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)

> 2)to use waitfor - which I did but did not fix the error.

I will have to admit that I don't see the point with WAITFOR here.

> 3)use bulk insert (in t-sql) - I dont know how to do this?

This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.

Then again, your also try:

INSERT datatable (...) WITH (TABLOCKX)
SELECT ...

I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tom (nospam@.yahoo.com) writes:
> Adding in smaller chunks doesn't solve the problem. Adding small chunk is
> the same as adding big chunk. Size doesn't matter.

Huh? Care to elaborate?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no guarantee that the record in a smaller chunk will not be locked
by another user.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ED17409A33Yazorman@.127.0.0.1...
> Tom (nospam@.yahoo.com) writes:
>> Adding in smaller chunks doesn't solve the problem. Adding small chunk
>> is
>> the same as adding big chunk. Size doesn't matter.
> Huh? Care to elaborate?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tom (nospam@.yahoo.com) writes:
> There is no guarantee that the record in a smaller chunk will not be
> locked by another user.

I did not really recognize the error messages that Adi got, but it seemed
to me that the problem was to get locks on the table he is inserting to,
because of resource constraints, not because he was locked by another
user.

And even in a blocking scenario, smaller batches help as it reduces the
risk for exposure for blocking.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Just a thought, its too early :). What would happen if he used NOLOCK
option? He doesnt really need locks around the place does he?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ECF2B98CD3FYazorman@.127.0.0.1...
> adi (Adityanad@.gmail.com) writes:
>> I am inserting data from one table to another on the same DB. The
>> insert is pretty simple as in:
>>
>> insert into datatable(field1, field2, field3)
>> select a1, a2, a3 from temptable...
>>
>>
>> This inserts about 4 millions rows in one go. And since I had the
>> 'cannot obtain lock resources' problem, several methods were suggested
>> by some web sites:
>> 1) one to split the insert into smaller chunks (I have no idea how I
>> can spit a insert to insert only n records at a time..)
> There are a couple of techniques. But for best performance, you need
> a clustered index in the table. This does not have to be unqiue though.
> Say that you have a date column in the table that has a decent
> distribution.
> Create a clustered index on tbat column:
> CREATE CLUSTERED INDEX ON temptable(datecol)
> Then you can do:
> DECLARE @.this_date datetime,
> @.next_date datetim
> SELECT @.this_date = MIN(datecol) FROM temptable
> WHILE @.this_date <= (SELECT datecol FROM temptable)
> BEGIN
> SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
> INSERT dataable (...)
> SELECT ...
> FROM temptable
> WHERE datecol >= @.this_date
> AND daetcol < @.next_date
> SELECT @.this_date = @.next_date
> END
> It's important that the index is clustered to minimize seek times.
> In the example I used month, but this can be changed as you see fit.
> If you don't have a datetime column, maybe there is some id column or
> similar. But you could even use a value like max temperature, as long
> there is a decent disitribution.
> You can also use SET ROWCOUNT to TOP to batch, but it's messier to
> keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
> with could have poor performance.)
>> 2)to use waitfor - which I did but did not fix the error.
> I will have to admit that I don't see the point with WAITFOR here.
>> 3)use bulk insert (in t-sql) - I dont know how to do this?
> This requires you to first unload the temptable to file with BCP, and
> then load it to the target table with BCP or BULK INSERT. I skip
> examples, as this would be a last resort for me.
> Then again, your also try:
> INSERT datatable (...) WITH (TABLOCKX)
> SELECT ...
> I have never tried it, but I expect it to lead to a single table lock
> in the table, which which address the locking issue.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Just a thought, its too early :). What would happen if he used NOLOCK
> option? He doesnt really need locks around the place does he?

If the problem is on the table he is reading from yes. But I suspect that
it's on the table he is inserting to.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Having used this approach I still want to improve its performance.
My DBA says that splitting the insert statement into smaller chunks
will be very helpful - that is insert n number of records at a time.
so if I have 1 million rows to insert, he says its good to insert 50k
at a time.

Can someone help me on how to split the below insert to do that?

insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable.|||adi (Adityanad@.gmail.com) writes:
> Having used this approach I still want to improve its performance.
> My DBA says that splitting the insert statement into smaller chunks
> will be very helpful - that is insert n number of records at a time.
> so if I have 1 million rows to insert, he says its good to insert 50k
> at a time.
> Can someone help me on how to split the below insert to do that?
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable.

Which approach?

I did suggest a method for batching earlier in the thread. For your con-
venience I repost this here:

........................

CREATE CLUSTERED INDEX ON temptable(datecol)

Then you can do:

It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.

If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.

You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)

> 2)to use waitfor - which I did but did not fix the error.

I will have to admit that I don't see the point with WAITFOR here.

> 3)use bulk insert (in t-sql) - I dont know how to do this?

This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.

Then again, your also try:

INSERT datatable (...) WITH (TABLOCKX)
SELECT ...

I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 29, 2012

Bulk Upload : General operational error

Bulk Upload : General operational error

Wednesday, March 7, 2012

Bulk Insert Data in Millions - Lock Issue

build sql script

Blog Archive

About Me