Bulk Loading: TempFilePath security question

I've got a program written that runs on one computer and performs bulk
updates to a SQL Server 2000 table on a different computer. I also
need transactional behavior, so I have to specify a value for
TempFilePath on my SQLXMLBulkLoad3Class object.
I do not have permissions to create folders, files, or interact in any
way with the computer hosting SQL server. This means that the value of
TempFilePath must be a UNC path that can be written to by the
application performing bulk loads, and (at a minimum) read from by the
computer hosting SQL server.
I'd like to make access to this UNC path as restricted as possible. I
am using SQL authentication, not Windows authentication.
How can I tell what ID will try from the SQL Server computer to read
from the TempFilePath?
How much permission will this ID need in the directory? Read only?
Read/write?If you do this with a connection using standard SQL login, then the thread
that is running the query will be using the account that the SQL Server
service runs under to access the file share.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
<isbat1@.yahoo.com> wrote in message
news:1128017182.904592.4100@.z14g2000cwz.googlegroups.com...
> I've got a program written that runs on one computer and performs bulk
> updates to a SQL Server 2000 table on a different computer. I also
> need transactional behavior, so I have to specify a value for
> TempFilePath on my SQLXMLBulkLoad3Class object.
> I do not have permissions to create folders, files, or interact in any
> way with the computer hosting SQL server. This means that the value of
> TempFilePath must be a UNC path that can be written to by the
> application performing bulk loads, and (at a minimum) read from by the
> computer hosting SQL server.
> I'd like to make access to this UNC path as restricted as possible. I
> am using SQL authentication, not Windows authentication.
> How can I tell what ID will try from the SQL Server computer to read
> from the TempFilePath?
> How much permission will this ID need in the directory? Read only?
> Read/write?
>

Bulk loading with OpenXML

I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GO
Change the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)
|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:

> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>
|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:

Bulk loading with OpenXML

I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GOChange the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:

> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:
>

bulk load: no error message, but not loading

this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" &
_
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.htmlill try attaching my schema
+---+
| Attachment filename: q.txt |
|Download attachment: http://www.mcse.ms/attachment.php?postid=3575113 |
+---+
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.html

bulk load: no error message, but not loading

hey guys, my xml bulk load creates no error message, but does not populate my tables.
I am very new to xml and sql server, so maybe this is a simple problem?
i have a root element i dont want to map, and then several tables of data in one document. I am unconcerned with any key constraints at this point.this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" & _
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************|||ill try attaching my schemasql

Bulk Load XML into SQL using Schema File ...

Hi ...

I am bulk loading XML data into SQL using

SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I

provide the xml data and a schema file (below).

When I use the attached schema file I get the following error:

No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.

I changed the table definition to allow nulls in the SensorId column

and the bulk load was successful. Notice on the records in the

MacgowanTestRWISRawSurface table … where are the records with the NULL

SensorId(s) in the XML data ? Is this is a side effect of my

self-join with the MacgowanTestRWISRawSurface table ?

Any comments?

Thanks,

Chris
[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]

RecordId DataSourceId

ProductInstanceId

SystemId RpuId SensorId ObsDateTime

-- --

-- --

1

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 0

NULL NULL

2

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 1

NULL NULL

3

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 2

NULL NULL

4

OH

5abbbc86-fb2c-4703-9589-b55f763ee150

200 3

NULL NULL

[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]

RecordId DataSourceId SystemId RpuId SensorId ObsDateTime

-- -- --

1

OH

200 0

0 2005-12-05 15:48:00.000

2

OH

200 0

1 2005-12-05 15:48:00.000

3

OH

200 0

NULL NULL

4

OH

200 1

0 2005-12-05 15:49:00.000

5

OH

200 1

NULL NULL

6

OH

200 2

0 2005-12-05 15:50:00.000

7

OH

200 2

1 2005-12-05 15:50:00.000

8

OH

200 2

NULL NULL

9

OH

200 3

0 2005-12-05 15:45:00.000

10

OH

200 3

1 2005-12-05 15:45:00.000

11

OH

200 3

NULL NULL
//////////////////////////////////////////////////////

// Schema File
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:annotation>

<xsd:appinfo>

<sql:relationship name="MacgowanTest_Atmospheric_Surface"

parent="MacgowanTestRWISRawAtmospheric"

parent-key="SystemId RpuId"

child="MacgowanTestRWISRawSurface"

child-key="SystemId RpuId" />

</xsd:appinfo>

</xsd:annotation>

<xsd:annotation>

<xsd:appinfo>

<sql:relationship name="MacgowanTest_Surface_Surface"

parent="MacgowanTestRWISRawSurface"

parent-key="SystemId RpuId"

child="MacgowanTestRWISRawSurface"

child-key="SystemId RpuId" />

</xsd:appinfo>

</xsd:annotation>

<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"

sql:relationship="MacgowanTest_Atmospheric_Surface">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"

sql:relationship="MacgowanTest_Surface_Surface" >

<xsd:complexType>

<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>

<xsd:attribute name="datetime" type="xsd:date"

sql:field="ObsDateTime" />

<xsd:attribute name="surfacecondition" type="xsd:string"

sql:field="SurfaceCondition"/>

<xsd:attribute name="surfacetemp" type="xsd:string"

sql:field="SurfaceTemperature"/>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>

<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>

</xsd:complexType>

</xsd:element>

</xsd:schema>

//////////////////////////////////////////////////////

// Xml Data

<?xml version="1.0"?>

<odot_rwis_site_info>

<site id="200000" number="1" sysid="200" rpuid="0"

name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">

<atmospheric datetime="12/05/2005 03:48:00 PM"

airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11"

windspeedgust="19" winddirectionavg="265" winddirectiongust="295"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="1900" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="450" waterlevel="0">

<traffic

datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82"

volume="21" sftemp="1900" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-60" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

<site id="200001" number="2" sysid="200" rpuid="1"

name="2-Hardin County Garage" longitude="-83.6148588"

latitude="40.6305358">

<atmospheric datetime="12/05/2005 03:49:00 PM"

airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9"

windspeedgust="22" winddirectionavg="303" winddirectiongust="299"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-410" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="300" waterlevel="0" />

</sensors>

</site>

<site id="200002" number="3" sysid="200" rpuid="2"

name="3-US24 @. Indiana Line" longitude="-84.769597"

latitude="41.17308">

<atmospheric datetime="12/05/2005 03:50:00 PM"

airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9"

windspeedgust="19" winddirectionavg="279" winddirectiongust="313"

pressure="65535" precipitationintensity="Light"

precipitationtype="Snow" precipitationrate="115"

precipitationaccumulation="-1" visibility="2000" />

<sensors>

<surface id="0" datetime="12/05/2005

03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="-100" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="160" waterlevel="0">

<traffic

datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89"

volume="16" sftemp="-100" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-130" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

<site id="200003" number="4" sysid="200" rpuid="3"

name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">

<atmospheric datetime="12/05/2005 03:45:00 PM"

airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6"

windspeedgust="20" winddirectionavg="216" winddirectiongust="275"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="100" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="300" waterlevel="0">

<traffic

datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108"

volume="11" sftemp="100" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-310" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

</odot_rwis_site_info>

It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.

Bulk Load XML into SQL using Schema File ...

Hi ...
I am bulk loading XML data into SQL using SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column and the bulk load was successful. Notice on the records in the MacgowanTestRWISRawSurface table … where are the records with the NULL SensorId(s) in the XML data ? Is this is a side effect of my self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris

[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]
RecordId DataSourceId ProductInstanceId SystemId RpuId SensorId ObsDateTime
-- -- -- --
1 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 0 NULL NULL
2 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 1 NULL NULL
3 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 2 NULL NULL
4 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 3 NULL NULL
[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]
RecordId DataSourceId SystemId RpuId SensorId ObsDateTime
-- -- --
1 OH 200 0 0 2005-12-05 15:48:00.000
2 OH 200 0 1 2005-12-05 15:48:00.000
3 OH 200 0 NULL NULL
4 OH 200 1 0 2005-12-05 15:49:00.000
5 OH 200 1 NULL NULL
6 OH 200 2 0 2005-12-05 15:50:00.000
7 OH 200 2 1 2005-12-05 15:50:00.000
8 OH 200 2 NULL NULL
9 OH 200 3 0 2005-12-05 15:45:00.000
10 OH 200 3 1 2005-12-05 15:45:00.000
11 OH 200 3 NULL NULL

//////////////////////////////////////////////////////
// Schema File

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

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Atmospheric_Surface"
parent="MacgowanTestRWISRawAtmospheric"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Surface_Surface"
parent="MacgowanTestRWISRawSurface"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>

<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Atmospheric_Surface">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Surface_Surface" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime" />
<xsd:attribute name="surfacecondition" type="xsd:string" sql:field="SurfaceCondition"/>
<xsd:attribute name="surfacetemp" type="xsd:string" sql:field="SurfaceTemperature"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>

<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

//////////////////////////////////////////////////////
// Xml Data

<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200001" number="2" sysid="200" rpuid="1" name="2-Hardin County Garage" longitude="-83.6148588" latitude="40.6305358">
<atmospheric datetime="12/05/2005 03:49:00 PM" airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9" windspeedgust="22" winddirectionavg="303" winddirectiongust="299" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-410" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0" />
</sensors>
</site>
<site id="200002" number="3" sysid="200" rpuid="2" name="3-US24 @. Indiana Line" longitude="-84.769597" latitude="41.17308">
<atmospheric datetime="12/05/2005 03:50:00 PM" airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9" windspeedgust="19" winddirectionavg="279" winddirectiongust="313" pressure="65535" precipitationintensity="Light" precipitationtype="Snow" precipitationrate="115" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="-100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="160" waterlevel="0">
<traffic datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89" volume="16" sftemp="-100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-130" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200003" number="4" sysid="200" rpuid="3" name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">
<atmospheric datetime="12/05/2005 03:45:00 PM" airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6" windspeedgust="20" winddirectionavg="216" winddirectiongust="275" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0">
<traffic datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108" volume="11" sftemp="100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-310" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.

Bulk Insert Statement

Hi All

I have a text file (sample below) i am bulk loading it into a staging table, the problem i am getting is the data is being loaded and scrambling the rows and I need the data to be imported exactly the same row by row

040207,"1007","","3319506/"
031207,"1509",">","US78016"
031207,"1509",">","AA004388"
031207,"1509",">","COMD88"
031207,"1509",">","US78016"
031207,"1509",">","AA001601"
031207,"1509",">","COMD88"
031207,"1510",">","US78016"
031207,"1510",">","AA004337"
031207,"1510",">","COMD88"
031307,"1138",">","US78016"
031307,"1139",">","AA004293"
031307,"1139",">","COMD81"

set nocount on
bulk insert data_load_stage.dbo.
from 'C:\load\CM07.txt'
with ( fieldterminator = ',')

missing a switch i think

thanks in advance

rich

Richie,

You'll need to add a clustered index to your staging table and then add the ORDER hint to your BULK INSERT statement (both matching the ordering of the input file).

From BULK INSERT in BOL:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. Bulk load operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.

sql

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?
Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:
> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John