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>
<!--
<SchemeID />
<NominalValue>5000</NominalValue>
<CashFlowType>
<Name>Fixed</Name>
<LastUpdated>2005-06-30</LastUpdated>
</CashFlowType>
-->
</SchemeCashFlowNominal>
</Items>
</SchemeCashFlowNominalList>
</Scheme>
</SchemaList>
<?xml version="1.0"?>
<!--
Look up ID in Schema table for the given InterLinkID. Insert this ID
into the SchemaID column of the
SchemeLDIFund table and get a new ID back. Insert this new ID as the
SchemaLDIFundID into the
SchemeLDIFundValue table.
-->
<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:annotation>
<xsd:appinfo>
<sql:relationship name="NominalToCashFlow"
parent="SchemeCashFlowNominal" parent-key="CashFlowTypeID"
child="CashFlowType" child-key="ID" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
-->
<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:element name="SchemeID" type="xsd:string" minOccurs="0"
sql:field="SchemeID" />
<xsd:element name="NominalValue"
type="xsd:string" minOccurs="1" />-->
<!--
<xsd:element name="CashFlowType"
minOccurs="0" maxOccurs="1" sql:relationship="NominalToCashFlow">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Name"
type="xsd:string" minOccurs="1" />
<xsd:element name="LastUpdated"
type="xsd:string" minOccurs="1" sql:field="LastUpdated" />
</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: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>
<!--
<SchemeID />
<NominalValue>5000</NominalValue>
<CashFlowType>
<Name>Fixed</Name>
<LastUpdated>2005-06-30</LastUpdated>
</CashFlowType>
-->
</SchemeCashFlowNominal>
</Items>
</SchemeCashFlowNominalList>
</Scheme>
</SchemaList>
<?xml version="1.0"?>
<!--
Look up ID in Schema table for the given InterLinkID. Insert this ID
into the SchemaID column of the
SchemeLDIFund table and get a new ID back. Insert this new ID as the
SchemaLDIFundID into the
SchemeLDIFundValue table.
-->
<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:annotation>
<xsd:appinfo>
<sql:relationship name="NominalToCashFlow"
parent="SchemeCashFlowNominal" parent-key="CashFlowTypeID"
child="CashFlowType" child-key="ID" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
-->
<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:element name="SchemeID" type="xsd:string" minOccurs="0"
sql:field="SchemeID" />
<xsd:element name="NominalValue"
type="xsd:string" minOccurs="1" />-->
<!--
<xsd:element name="CashFlowType"
minOccurs="0" maxOccurs="1" sql:relationship="NominalToCashFlow">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Name"
type="xsd:string" minOccurs="1" />
<xsd:element name="LastUpdated"
type="xsd:string" minOccurs="1" sql:field="LastUpdated" />
</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:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Bulk Load - net enough memory error- HELP!

I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
database) . When I change the connection to point to another machine (simila
r
hardware configuration) I get
[There is insufficient system memory to run this query.]
The script is running on my machine connecting to a MSDE database on another
machine.
The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
still generates the error. I think it is a configuration issue because if I
change the connection to another development machine it works with the 140
meg file.Solution - I checked the maximum memory that the instance was set to. It was
5 meg, I increased it to 500 meg and the bulkload ran like a champ. it's a
good thing that you can use enterprise manager to manage MSDE databases....
.
"xke" wrote:

> I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
> database) . When I change the connection to point to another machine (simi
lar
> hardware configuration) I get
> [There is insufficient system memory to run this query.]
> The script is running on my machine connecting to a MSDE database on anoth
er
> machine.
> The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
> still generates the error. I think it is a configuration issue because if
I
> change the connection to another development machine it works with the 140
> meg file.
>sql

Bulk Load - net enough memory error- HELP!

I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
database) . When I change the connection to point to another machine (similar
hardware configuration) I get
[There is insufficient system memory to run this query.]
The script is running on my machine connecting to a MSDE database on another
machine.
The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
still generates the error. I think it is a configuration issue because if I
change the connection to another development machine it works with the 140
meg file.
Solution - I checked the maximum memory that the instance was set to. It was
5 meg, I increased it to 500 meg and the bulkload ran like a champ. it's a
good thing that you can use enterprise manager to manage MSDE databases.....
"xke" wrote:

> I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
> database) . When I change the connection to point to another machine (similar
> hardware configuration) I get
> [There is insufficient system memory to run this query.]
> The script is running on my machine connecting to a MSDE database on another
> machine.
> The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
> still generates the error. I think it is a configuration issue because if I
> change the connection to another development machine it works with the 140
> meg file.
>

BULK INSERT using Exisiting Folder

I am getting the following error when trying to point a Bulk Insert Connection Manager to an existing folder (rather than a file). Anyone else having this problem or know what i am doing
wrong? I find it interesting that is says file (below) when i told the connection manager to point to the folder. Thanks in advance.

"Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "c:\Folder\" could not be opened. Operating system error code 5(Access is denied.).".
Task failed: Bulk Insert Task"

James

Bulk Insert Task uses FILE connections that point to a file wich contains data that you want to be loaded using BULK INSERT statement.

Is there a reason you want to make the file connection point to a folder instead of a file?

Thanks,
Ovidiu

Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).
have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>
|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it
>

Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it[vbcol=seagreen]
>

Building Indexes

I have a couple of questions on indexes if someone can explain or point me to
a good resource that explains these type of questions in detail. We have a
database that has no more than 200,000 records, but some of our lookups take
up to 5 minutes or more. I believe the problem to be with the indexes (or
lack of).
1. Should every table have an index?
2. If a table is used as a join table for two other tables and only has
about 100 records, should it have an index, or do the other tables having
indexes the best solution.
3. If I have a SQL statement that has a where clause on three (or any other
number) different fields, should I build an index on each item separately, or
all three items together in one index?
If someone can please help out these questions, that would be great. Also,
if you can recommend a book (or website) that explains basic questions like
these in detail, that would be even better.
Thank you in advance for your help.
> 1. Should every table have an index?
Yes, at least a clustered one.

> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
Create an index for every foreign key constraint. SQL Server create a unique
clustered (if you do not specify other) for primary key constraint, but no
index is created for foreign key constraints. In other words, you should have
an index, in each table, by the columns participating in the join.

> 3. If I have a SQL statement that has a where clause on three (or any other
> number) different fields, should I build an index on each item separately, or
> all three items together in one index?
You can start creating an index by each column (single column), sql server
is smart enough to use then when optimizing a query. Sometime you can improve
the performance using a covering index (an index where the columns in the
key, cover all columns being referenced by the statement).
Here is a good place to read about indexing and I also strongly recommend
the book "Inside SQL Server 2000" written by Kalen Delaney.
http://www.sql-server-performance.co...erformance.asp
Read all topics about indexes.
AMB
"Mike Collins" wrote:

> I have a couple of questions on indexes if someone can explain or point me to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups take
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any other
> number) different fields, should I build an index on each item separately, or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions like
> these in detail, that would be even better.
> Thank you in advance for your help.
|||1. It is not really necessary, but I make it a point to have a clustered
index on every table. This makes for easier maintenance of the table
structure, especially when the table is huge.
2. Generally every table has a primary key, and the primary key needs to be
supported by a clustered or nonclustered index. Having an index is never a
bad thing from a query performance point of view. But you have to see how
the index is affecting the writes to the table.
3. You don't necessarily have to build indexes on all 3 columns, as long as
the column you are indexing on is very selective. Also make sure the first
column in a composite index is the selective column. Some times you do have
situations where composite indexes are useful, where all the information
required by a query can be obtained directly from the index - these are
called covered indexes.
For general advice, check out sql-server-performance.com. Have a look at SQL
Server performance related books at:
http://vyaskn.tripod.com/sqlbooks.htm#perf
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> I have a couple of questions on indexes if someone can explain or point me
to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups
take
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any
other
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions
like
> these in detail, that would be even better.
> Thank you in advance for your help.
|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index to perform
a query or it can select more than 1 index also. Informix selects only 1 index by
analyzing statistics.
|||Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:

> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> to
> take
> other
> or
> like
>
>
|||Mike,

> What is a composite index and what does it mean to make the first column the
> selective column. An example would be great. Thanks again for answering.
A composite index is one with multiple columns participating in the index
key. SQL Server stores statistics about the distribution of the values in the
key and in a multicolumn key, the statistics is different that the one from a
single column key index. This paragragh is from the article I am attaching.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/de...asp?frame=true
*****
The multicolumn statistics for one set of columns consists of one histogram
for the first column in the statistics definition, one density value for the
first column, and an all density value for each prefix combination of columns
(including the first column alone). Each set of statistics (a histogram and
two or more density values) is stored in one row of SYSINDEXES together with
the timestamp of the last statistics update, the number of rows in the sample
used to produce the statistical information, the number of steps in the
histogram, and the average length of the key. The number of rows value
(rowcnt column) is maintained only for index number 0 or 1 (heap or clustered
index) and it is replicated across all indexes on the table. Similarly, the
dpages is maintained for each table and index. The statistical information is
empty for tables without any rows at the time of statistics collection.
*****
If the selectivity oh the most left column in the key is low, then sql
server will not perform an "index seek" operation in the index in question.
AMB
"Mike Collins" wrote:
[vbcol=seagreen]
> Thanks for the reply. I have a question on number 3 though.
> What is a composite index and what does it mean to make the first column the
> selective column. An example would be great. Thanks again for answering.
> "Narayana Vyas Kondreddi" wrote:
|||A composite index is an index that consists of more than one column. For
example, if you have an index on EmpID and Surname, it would make more sense
to have the index as "EmpID, Surname", than "Surname, EmpID" - because EmpID
is more selective and has unique values.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:A568ACC8-D7FC-4A87-A31A-B7C2CAB37FC2@.microsoft.com...
Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:

> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL[vbcol=seagreen]
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
me[vbcol=seagreen]
> to
a[vbcol=seagreen]
> take
(or[vbcol=seagreen]
having[vbcol=seagreen]
> other
separately,[vbcol=seagreen]
> or
Also,
> like
>
>
|||SQL Server 2000 can use multiple indexes for a single table, in the same
query. See "Designing an Index" in SQL Server Books Online for more
information.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:3lpsruF13v248U1@.individual.net...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index
to perform
a query or it can select more than 1 index also. Informix selects only 1
index by
analyzing statistics.

Building Indexes

I have a couple of questions on indexes if someone can explain or point me t
o
a good resource that explains these type of questions in detail. We have a
database that has no more than 200,000 records, but some of our lookups take
up to 5 minutes or more. I believe the problem to be with the indexes (or
lack of).
1. Should every table have an index?
2. If a table is used as a join table for two other tables and only has
about 100 records, should it have an index, or do the other tables having
indexes the best solution.
3. If I have a SQL statement that has a where clause on three (or any other
number) different fields, should I build an index on each item separately, o
r
all three items together in one index?
If someone can please help out these questions, that would be great. Also,
if you can recommend a book (or website) that explains basic questions like
these in detail, that would be even better.
Thank you in advance for your help.> 1. Should every table have an index?
Yes, at least a clustered one.

> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
Create an index for every foreign key constraint. SQL Server create a unique
clustered (if you do not specify other) for primary key constraint, but no
index is created for foreign key constraints. In other words, you should hav
e
an index, in each table, by the columns participating in the join.

> 3. If I have a SQL statement that has a where clause on three (or any othe
r
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
You can start creating an index by each column (single column), sql server
is smart enough to use then when optimizing a query. Sometime you can improv
e
the performance using a covering index (an index where the columns in the
key, cover all columns being referenced by the statement).
Here is a good place to read about indexing and I also strongly recommend
the book "Inside SQL Server 2000" written by Kalen Delaney.
http://www.sql-server-performance.c...performance.asp
Read all topics about indexes.
AMB
"Mike Collins" wrote:

> I have a couple of questions on indexes if someone can explain or point me
to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups ta
ke
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any othe
r
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions lik
e
> these in detail, that would be even better.
> Thank you in advance for your help.|||1. It is not really necessary, but I make it a point to have a clustered
index on every table. This makes for easier maintenance of the table
structure, especially when the table is huge.
2. Generally every table has a primary key, and the primary key needs to be
supported by a clustered or nonclustered index. Having an index is never a
bad thing from a query performance point of view. But you have to see how
the index is affecting the writes to the table.
3. You don't necessarily have to build indexes on all 3 columns, as long as
the column you are indexing on is very selective. Also make sure the first
column in a composite index is the selective column. Some times you do have
situations where composite indexes are useful, where all the information
required by a query can be obtained directly from the index - these are
called covered indexes.
For general advice, check out sql-server-performance.com. Have a look at SQL
Server performance related books at:
http://vyaskn.tripod.com/sqlbooks.htm#perf
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> I have a couple of questions on indexes if someone can explain or point me
to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups
take
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any
other
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions
like
> these in detail, that would be even better.
> Thank you in advance for your help.|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to b
e
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long a
s
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do hav
e
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at S
QL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index
to perform
a query or it can select more than 1 index also. Informix selects only 1 ind
ex by
analyzing statistics.|||Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:

> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to b
e
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long a
s
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do hav
e
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at S
QL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> to
> take
> other
> or
> like
>
>|||Mike,

> What is a composite index and what does it mean to make the first column t
he
> selective column. An example would be great. Thanks again for answering.
A composite index is one with multiple columns participating in the index
key. SQL Server stores statistics about the distribution of the values in th
e
key and in a multicolumn key, the statistics is different that the one from
a
single column key index. This paragragh is from the article I am attaching.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/d...asp?frame=true
*****
The multicolumn statistics for one set of columns consists of one histogram
for the first column in the statistics definition, one density value for the
first column, and an all density value for each prefix combination of column
s
(including the first column alone). Each set of statistics (a histogram and
two or more density values) is stored in one row of SYSINDEXES together with
the timestamp of the last statistics update, the number of rows in the sampl
e
used to produce the statistical information, the number of steps in the
histogram, and the average length of the key. The number of rows value
(rowcnt column) is maintained only for index number 0 or 1 (heap or clustere
d
index) and it is replicated across all indexes on the table. Similarly, the
dpages is maintained for each table and index. The statistical information i
s
empty for tables without any rows at the time of statistics collection.
*****
If the selectivity oh the most left column in the key is low, then sql
server will not perform an "index seek" operation in the index in question.
AMB
"Mike Collins" wrote:
[vbcol=seagreen]
> Thanks for the reply. I have a question on number 3 though.
> What is a composite index and what does it mean to make the first column t
he
> selective column. An example would be great. Thanks again for answering.
> "Narayana Vyas Kondreddi" wrote:
>|||A composite index is an index that consists of more than one column. For
example, if you have an index on EmpID and Surname, it would make more sense
to have the index as "EmpID, Surname", than "Surname, EmpID" - because EmpID
is more selective and has unique values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:A568ACC8-D7FC-4A87-A31A-B7C2CAB37FC2@.microsoft.com...
Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:

> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
me[vbcol=seagreen]
> to
a[vbcol=seagreen]
> take
(or[vbcol=seagreen]
having[vbcol=seagreen]
> other
separately,[vbcol=seagreen]
> or
Also,[vbcol=seagreen]
> like
>
>|||SQL Server 2000 can use multiple indexes for a single table, in the same
query. See "Designing an Index" in SQL Server Books Online for more
information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:3lpsruF13v248U1@.individual.net...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index
to perform
a query or it can select more than 1 index also. Informix selects only 1
index by
analyzing statistics.

Building Indexes

I have a couple of questions on indexes if someone can explain or point me to
a good resource that explains these type of questions in detail. We have a
database that has no more than 200,000 records, but some of our lookups take
up to 5 minutes or more. I believe the problem to be with the indexes (or
lack of).
1. Should every table have an index?
2. If a table is used as a join table for two other tables and only has
about 100 records, should it have an index, or do the other tables having
indexes the best solution.
3. If I have a SQL statement that has a where clause on three (or any other
number) different fields, should I build an index on each item separately, or
all three items together in one index?
If someone can please help out these questions, that would be great. Also,
if you can recommend a book (or website) that explains basic questions like
these in detail, that would be even better.
Thank you in advance for your help.> 1. Should every table have an index?
Yes, at least a clustered one.
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
Create an index for every foreign key constraint. SQL Server create a unique
clustered (if you do not specify other) for primary key constraint, but no
index is created for foreign key constraints. In other words, you should have
an index, in each table, by the columns participating in the join.
> 3. If I have a SQL statement that has a where clause on three (or any other
> number) different fields, should I build an index on each item separately, or
> all three items together in one index?
You can start creating an index by each column (single column), sql server
is smart enough to use then when optimizing a query. Sometime you can improve
the performance using a covering index (an index where the columns in the
key, cover all columns being referenced by the statement).
Here is a good place to read about indexing and I also strongly recommend
the book "Inside SQL Server 2000" written by Kalen Delaney.
http://www.sql-server-performance.com/tips_performance.asp
Read all topics about indexes.
AMB
"Mike Collins" wrote:
> I have a couple of questions on indexes if someone can explain or point me to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups take
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any other
> number) different fields, should I build an index on each item separately, or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions like
> these in detail, that would be even better.
> Thank you in advance for your help.|||1. It is not really necessary, but I make it a point to have a clustered
index on every table. This makes for easier maintenance of the table
structure, especially when the table is huge.
2. Generally every table has a primary key, and the primary key needs to be
supported by a clustered or nonclustered index. Having an index is never a
bad thing from a query performance point of view. But you have to see how
the index is affecting the writes to the table.
3. You don't necessarily have to build indexes on all 3 columns, as long as
the column you are indexing on is very selective. Also make sure the first
column in a composite index is the selective column. Some times you do have
situations where composite indexes are useful, where all the information
required by a query can be obtained directly from the index - these are
called covered indexes.
For general advice, check out sql-server-performance.com. Have a look at SQL
Server performance related books at:
http://vyaskn.tripod.com/sqlbooks.htm#perf
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> I have a couple of questions on indexes if someone can explain or point me
to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups
take
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any
other
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions
like
> these in detail, that would be even better.
> Thank you in advance for your help.|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index to perform
a query or it can select more than 1 index also. Informix selects only 1 index by
analyzing statistics.|||Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> > I have a couple of questions on indexes if someone can explain or point me
> to
> > a good resource that explains these type of questions in detail. We have a
> > database that has no more than 200,000 records, but some of our lookups
> take
> > up to 5 minutes or more. I believe the problem to be with the indexes (or
> > lack of).
> >
> > 1. Should every table have an index?
> > 2. If a table is used as a join table for two other tables and only has
> > about 100 records, should it have an index, or do the other tables having
> > indexes the best solution.
> > 3. If I have a SQL statement that has a where clause on three (or any
> other
> > number) different fields, should I build an index on each item separately,
> or
> > all three items together in one index?
> >
> > If someone can please help out these questions, that would be great. Also,
> > if you can recommend a book (or website) that explains basic questions
> like
> > these in detail, that would be even better.
> >
> > Thank you in advance for your help.
>
>|||Mike,
> What is a composite index and what does it mean to make the first column the
> selective column. An example would be great. Thanks again for answering.
A composite index is one with multiple columns participating in the index
key. SQL Server stores statistics about the distribution of the values in the
key and in a multicolumn key, the statistics is different that the one from a
single column key index. This paragragh is from the article I am attaching.
Statistics Used by the Query Optimizer in Microsoft SQL Server 200
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
*****
The multicolumn statistics for one set of columns consists of one histogram
for the first column in the statistics definition, one density value for the
first column, and an all density value for each prefix combination of columns
(including the first column alone). Each set of statistics (a histogram and
two or more density values) is stored in one row of SYSINDEXES together with
the timestamp of the last statistics update, the number of rows in the sample
used to produce the statistical information, the number of steps in the
histogram, and the average length of the key. The number of rows value
(rowcnt column) is maintained only for index number 0 or 1 (heap or clustered
index) and it is replicated across all indexes on the table. Similarly, the
dpages is maintained for each table and index. The statistical information is
empty for tables without any rows at the time of statistics collection.
*****
If the selectivity oh the most left column in the key is low, then sql
server will not perform an "index seek" operation in the index in question.
AMB
"Mike Collins" wrote:
> Thanks for the reply. I have a question on number 3 though.
> What is a composite index and what does it mean to make the first column the
> selective column. An example would be great. Thanks again for answering.
> "Narayana Vyas Kondreddi" wrote:
> > 1. It is not really necessary, but I make it a point to have a clustered
> > index on every table. This makes for easier maintenance of the table
> > structure, especially when the table is huge.
> >
> > 2. Generally every table has a primary key, and the primary key needs to be
> > supported by a clustered or nonclustered index. Having an index is never a
> > bad thing from a query performance point of view. But you have to see how
> > the index is affecting the writes to the table.
> >
> > 3. You don't necessarily have to build indexes on all 3 columns, as long as
> > the column you are indexing on is very selective. Also make sure the first
> > column in a composite index is the selective column. Some times you do have
> > situations where composite indexes are useful, where all the information
> > required by a query can be obtained directly from the index - these are
> > called covered indexes.
> >
> > For general advice, check out sql-server-performance.com. Have a look at SQL
> > Server performance related books at:
> > http://vyaskn.tripod.com/sqlbooks.htm#perf
> > --
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> > news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> > > I have a couple of questions on indexes if someone can explain or point me
> > to
> > > a good resource that explains these type of questions in detail. We have a
> > > database that has no more than 200,000 records, but some of our lookups
> > take
> > > up to 5 minutes or more. I believe the problem to be with the indexes (or
> > > lack of).
> > >
> > > 1. Should every table have an index?
> > > 2. If a table is used as a join table for two other tables and only has
> > > about 100 records, should it have an index, or do the other tables having
> > > indexes the best solution.
> > > 3. If I have a SQL statement that has a where clause on three (or any
> > other
> > > number) different fields, should I build an index on each item separately,
> > or
> > > all three items together in one index?
> > >
> > > If someone can please help out these questions, that would be great. Also,
> > > if you can recommend a book (or website) that explains basic questions
> > like
> > > these in detail, that would be even better.
> > >
> > > Thank you in advance for your help.
> >
> >
> >|||A composite index is an index that consists of more than one column. For
example, if you have an index on EmpID and Surname, it would make more sense
to have the index as "EmpID, Surname", than "Surname, EmpID" - because EmpID
is more selective and has unique values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:A568ACC8-D7FC-4A87-A31A-B7C2CAB37FC2@.microsoft.com...
Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> > I have a couple of questions on indexes if someone can explain or point
me
> to
> > a good resource that explains these type of questions in detail. We have
a
> > database that has no more than 200,000 records, but some of our lookups
> take
> > up to 5 minutes or more. I believe the problem to be with the indexes
(or
> > lack of).
> >
> > 1. Should every table have an index?
> > 2. If a table is used as a join table for two other tables and only has
> > about 100 records, should it have an index, or do the other tables
having
> > indexes the best solution.
> > 3. If I have a SQL statement that has a where clause on three (or any
> other
> > number) different fields, should I build an index on each item
separately,
> or
> > all three items together in one index?
> >
> > If someone can please help out these questions, that would be great.
Also,
> > if you can recommend a book (or website) that explains basic questions
> like
> > these in detail, that would be even better.
> >
> > Thank you in advance for your help.
>
>|||SQL Server 2000 can use multiple indexes for a single table, in the same
query. See "Designing an Index" in SQL Server Books Online for more
information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:3lpsruF13v248U1@.individual.net...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index
to perform
a query or it can select more than 1 index also. Informix selects only 1
index by
analyzing statistics.