build sql script: 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>

Thursday, March 29, 2012

Bulk Upload : General operational error

No comments:

Post a Comment

build sql script

Blog Archive

About Me