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 problem - error connecting

I get the following error when trying to use the SQL Bulk Load object.

"Error connecting to the data source." The datasource is used right above this code to successfully read from the database. It is the last line that blows up.

Here is the problem code:

connStr = "provider=SQLNCLI;Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"

Dim objBL As New SQLXMLBULKLOADLib.SQLXMLBulkLoad

objBL.ConnectionString = connStr

objBL.BulkLoad = True

objBL.XMLFragment = True

objBL.KeepIdentity = False

objBL.ErrorLogFile = "C:\BulkLoadErrors.xml"

objBL.Execute(SchemaFile, datafile)

(SchemaFile and datafile are strings containing the full file name and path)

Use the connection string as follows :

ConnStr = "provider=sqloledb;data source=myserver;database=mydb;integrated security=SSPI;"

It should work.

Thanks.

Naras.

|||Thank you! That did it. Annoying part is the connection string I was using was taken straight from the MSDN examples.

Bulk inserting into table with computed columns

Using SS2K, I'm getting the following error while bulk inserting:

Column 'warranty_expiration_date' cannot be modified because it is a
computed column.

Here is my bulk insert statement:

BULK INSERT dbo.TestData
FROM 'TestData.dat'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='TestData.fmt')

The computed column is not referenced in the format file and the data file
does not contain the computed data.

Thankstperovic (tperovic@.compumation.com) writes:
> Using SS2K, I'm getting the following error while bulk inserting:
> Column 'warranty_expiration_date' cannot be modified because it is a
> computed column.
> Here is my bulk insert statement:
> BULK INSERT dbo.TestData
> FROM 'TestData.dat'
> WITH (CHECK_CONSTRAINTS,
> FIELDTERMINATOR='|',
> MAXERRORS = 1,
> FORMATFILE='TestData.fmt')
> The computed column is not referenced in the format file and the data file
> does not contain the computed data.

Could you provide more information, for instance a CREATE TABLE statement,
a sample data file and a sample format file that demonstrates the problem.

To wit, I created this table:

create table c (a int NOT NULL,
b as sqrt(a))

And I created this format file:

8.0
1
1 SQLCHAR 0 0 "\r\n" 1 dda_num ""

And used this data file:

12
4144
356

And this command:

bulk insert c FROM 'E:\temp\slask.bcp'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='E:\temp\slask.fmt')

And my load was successful.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Facing a deadline, I dropped the computed column and added it to a view.
Maybe later we can revisit this issue. Thanks.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948A483591F3Yazorman@.127.0.0.1...
> tperovic (tperovic@.compumation.com) writes:
> > Using SS2K, I'm getting the following error while bulk inserting:
> > Column 'warranty_expiration_date' cannot be modified because it is a
> > computed column.
> > Here is my bulk insert statement:
> > BULK INSERT dbo.TestData
> > FROM 'TestData.dat'
> > WITH (CHECK_CONSTRAINTS,
> > FIELDTERMINATOR='|',
> > MAXERRORS = 1,
> > FORMATFILE='TestData.fmt')
> > The computed column is not referenced in the format file and the data
file
> > does not contain the computed data.
> Could you provide more information, for instance a CREATE TABLE statement,
> a sample data file and a sample format file that demonstrates the problem.
> To wit, I created this table:
> create table c (a int NOT NULL,
> b as sqrt(a))
> And I created this format file:
> 8.0
> 1
> 1 SQLCHAR 0 0 "\r\n" 1 dda_num ""
> And used this data file:
> 12
> 4144
> 356
> And this command:
> bulk insert c FROM 'E:\temp\slask.bcp'
> WITH (CHECK_CONSTRAINTS,
> FIELDTERMINATOR='|',
> MAXERRORS = 1,
> FORMATFILE='E:\temp\slask.fmt')
> And my load was successful.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Bulk Insert.

Is there any way I can use the following command like that ?
BULK INSERT Northwind.dbo.[Order Details]
FROM (select CSVTextFile from tblCSV)
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Thanks in advance
The syntax of BULK INSERT is

quote:

---

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM
'data_file' }...
'data_file'
Is the full path of the data file that contains data to copy into the
specified table or view. BULK INSERT can copy data from a disk (including
network, floppy disk, hard disk, and so on).
data_file must specify a valid path from the server on which SQL Server is
running. If data_file is a remote file, specify the Universal Naming
Convention (UNC) name.

---

Please refer to Books Online for examples of how to use BULK INSERT
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Cristian Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET
"Rogers" <Rogers@.mailstuff.com> wrote in message
news:Oyx71bHuFHA.2624@.TK2MSFTNGP12.phx.gbl...
> Is there any way I can use the following command like that ?
> BULK INSERT Northwind.dbo.[Order Details]
> FROM (select CSVTextFile from tblCSV)
> WITH
> (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> Thanks in advance
>
sql

Bulk Insert XML with IDENTITY Column

Hi ...
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments are appreciated.
Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

///////////////////////////////////////////////////
// xml data
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

///////////////////////////////////////////////////
// xsc schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Hi ...

To allow SQL Server to set the IDENTITY column the KeepIdentity atribute needs to be set to false (KeepIdentity((bool)FALSE).

Thanks,

Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

Bulk Insert XML with IDENTITY Column

Hi ...
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments are appreciated.
Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

///////////////////////////////////////////////////
// xml data
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

///////////////////////////////////////////////////
// xsc schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GOHi ...
To allow SQL Server to set the IDENTITY column the KeepIdentity atribute needs to be set to false (KeepIdentity((bool)FALSE).
Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

BULK INSERT Vs BCP.exe

We have the following code written in the TSQL using the Bulk Insert command which works fine in SQL Server 2000 and 2005

Code Snippet

BULK INSERT MYTABLE FROM 'C:\TextDataFiles\MYTABLE.txt' WITH ( FORMATFILE = 'C:\MYTABLE.fmt' , MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\MYTABLE.bad')

Please note the MAXERRORS is set to 0. So, if a error occurs nothing is written to the table.

Now we are trying to convert this functionality using the bcp.exe (actually using the API like bcp_control in C++) with the following code

Code Snippet

bcp "MYTABLE" in "'C:\TextDataFiles\MYTABLE.txt' -m0 -f"'C:\MYTABLE.fmt' -e 'C:\MYTABLE.bad' -b500000 -SMYSERVER -Uuid -Ppwd -h"CHECK_CONSTRAINTS"

This code does not work the same way as the BULK INSERT code above. It default the max errors to 10 (as documented) and inserts row(s) before the first error. How can I have the same functionality, fail on first error, with the bcp?

Your help is appreciated.

Since it will still insert the rows that are processed before the first error occurs (then failing and halting the insertions) I would probably push the data to a temporary table, do a check to make sure all data was inserted properly, then move the data from the temp table to the production table. This way you won't get half of the data if the bcp fails.

|||That is the way it works because of the batch=500000. You will always get the "batch" inserted before the error occurs.

|||

Why is the behaviour different in the TSQL for BULK INSERT, we have the same value for the BATCH. Is it possible BATCH INSERT is started in a commitment boundary automatically?. I do not understand why there is a different in the behaviour between the 2 commands.I would like to keep the existing functionality, as it, if possible, with out making significant changes like inserting to temp table and then moving to the perminant tables.

|||The Bulk Insert command creates an "implicit" transaction around the command. So if there is an error, everything gets rolled back. bcp.exe does not.

If you remove the "-b500000" from the bcp.exe command, you will get all or nothing.
sql

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 SYNTAX

I am setting up a new database using a shopping cart SW, when I create the DB
using there script, I get the following error

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '('.

The line syntax is as follows.

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ','

)

GO

What is wrong?

hi,

ShaneShowers wrote:

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

you have the semicolon (;) in the wrong place... at the end of the first line after the file to be imported.. the semicolon indicates a statement termination so that the next statement just becomes

WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
)

which makes no sense.... remove it and place it at the end of the statement

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);

regards

Bulk Insert Related Tables - PKs

Hi all,
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:

> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Bulk Insert question

Hello,
I use the following statement to insert a string of 35 characters into a
table (I am planning to figure out a suitable .FMT file for parsing this int
o
the right column definitions, but that is after I figure out this current
problem described below)
BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
(FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
MyTestTable is currently defined as
CREATE TABLE MyTestTable (Col1 CHAR(35))
and some sample test data from MyTextFile.txt is as follows
ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
(length of the data is always 36 bytes and the length of the filename is
always 14 bytes)
(As you can tell, the DDL and data is what I am using for this test)
This BULK INSERT works fine, but what I need to do is to get the name of the
file (MyTextFile.txt in this case) appended to the end of the column.
So, the table definition would change to
CREATE TABLE MyTestTable (Col1 CHAR(49))
I am clueless about how to get the name of the file (which will vary at
runtime) into each row of the MyTestTable that gets affected by this BULK
INSERT.
(I will be inserting multiple files - different file names - to the same
table one after the other and would like to have the filename stored in a
separate column or appended to the column - either way. When I build the .FM
T
file, I will split this into the appropriate columns and will change the
table definition.
Any suggestions would be appreciated. Please let me know if any further
details are needed.
Thanks!you could probably write a TSQL block to read filename before inserting the
data with bulk insert. and then insert data, and update data with appending
the file name to recently inserted records.
hth,
avnrao
http://avnrao.blogspot.com
"Bob" wrote:

> Hello,
> I use the following statement to insert a string of 35 characters into
a
> table (I am planning to figure out a suitable .FMT file for parsing this i
nto
> the right column definitions, but that is after I figure out this current
> problem described below)
> BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
> (FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
> MyTestTable is currently defined as
> CREATE TABLE MyTestTable (Col1 CHAR(35))
> and some sample test data from MyTextFile.txt is as follows
> ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
> 123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
> (length of the data is always 36 bytes and the length of the filename is
> always 14 bytes)
> (As you can tell, the DDL and data is what I am using for this test)
> This BULK INSERT works fine, but what I need to do is to get the name of t
he
> file (MyTextFile.txt in this case) appended to the end of the column.
> So, the table definition would change to
> CREATE TABLE MyTestTable (Col1 CHAR(49))
> I am clueless about how to get the name of the file (which will vary at
> runtime) into each row of the MyTestTable that gets affected by this BULK
> INSERT.
> (I will be inserting multiple files - different file names - to the same
> table one after the other and would like to have the filename stored in a
> separate column or appended to the column - either way. When I build the .
FMT
> file, I will split this into the appropriate columns and will change the
> table definition.
> Any suggestions would be appreciated. Please let me know if any further
> details are needed.
> Thanks!
>|||Bob,
I'm currently doing a migration which has between 40 and 50 text files as
the datasource from a mainframe. Their names can change so I use a batch
file to handle this. eg in my batch file,
Call dir with simple header, full filename options to list the files
required into :\temp\filelist.txt
Your file list should be a single column with the complete filepath and
filename
Upload the list to the server using bcp
Note: bcp into a view which has only one column, the filename
If you need more details post back. Basicallly, even if it's a bit
old-fashioned, DOS already has the commands for working with files. There's
always DTS but I'm not a fan ... ; )
Damien
"Bob" wrote:

> Hello,
> I use the following statement to insert a string of 35 characters into
a
> table (I am planning to figure out a suitable .FMT file for parsing this i
nto
> the right column definitions, but that is after I figure out this current
> problem described below)
> BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
> (FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
> MyTestTable is currently defined as
> CREATE TABLE MyTestTable (Col1 CHAR(35))
> and some sample test data from MyTextFile.txt is as follows
> ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
> 123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
> (length of the data is always 36 bytes and the length of the filename is
> always 14 bytes)
> (As you can tell, the DDL and data is what I am using for this test)
> This BULK INSERT works fine, but what I need to do is to get the name of t
he
> file (MyTextFile.txt in this case) appended to the end of the column.
> So, the table definition would change to
> CREATE TABLE MyTestTable (Col1 CHAR(49))
> I am clueless about how to get the name of the file (which will vary at
> runtime) into each row of the MyTestTable that gets affected by this BULK
> INSERT.
> (I will be inserting multiple files - different file names - to the same
> table one after the other and would like to have the filename stored in a
> separate column or appended to the column - either way. When I build the .
FMT
> file, I will split this into the appropriate columns and will change the
> table definition.
> Any suggestions would be appreciated. Please let me know if any further
> details are needed.
> Thanks!
>|||avnrao, thank you for your quick response. I had considered this option too,
but the problem is that this load process is supposed to run once a month an
d
with all the 100 files combined, I will have totally about 600 million rows
to process - I guess I was hoping that BULK INSERT would allow us to process
the filename along with it - Any other means of achieving this (instead of
Bulk Insert) would be welcome too.
Thanks!
"avnrao" wrote:
> you could probably write a TSQL block to read filename before inserting th
e
> data with bulk insert. and then insert data, and update data with appendin
g
> the file name to recently inserted records.
> hth,
> avnrao
> http://avnrao.blogspot.com
> "Bob" wrote:
>|||Damien,
I'm ok with using DOS commands too - anything that can get this done
(without too much of a performance hit). :)
I do have the list of filenames available (and I can get it into a SQL table
too), but the problem is how to update the filenames onto the MyTestTable...
I guess I didn't quite understand the suggestion - bcp into the view (and
this view should be for the MyTestTable table?) - but how would I be able to
associate the multiple records from the file with the filename that I am
BCPing?
Sorry if I misunderstood you... but could you clarify a little on this?
Thanks again!
"Damien" wrote:
> Bob,
> I'm currently doing a migration which has between 40 and 50 text files as
> the datasource from a mainframe. Their names can change so I use a batch
> file to handle this. eg in my batch file,
> Call dir with simple header, full filename options to list the files
> required into :\temp\filelist.txt
> Your file list should be a single column with the complete filepath and
> filename
> Upload the list to the server using bcp
> Note: bcp into a view which has only one column, the filename
> If you need more details post back. Basicallly, even if it's a bit
> old-fashioned, DOS already has the commands for working with files. There
's
> always DTS but I'm not a fan ... ; )
>
> Damien
>
>
> "Bob" wrote:
>|||Ah,
well I cheated a little bit here. I used SQL to write the batch file for
me, and I use osql to fire off an ALTER TABLE to set the default for the
column.
So, from Query Analyser, write a query which selects your records, but
create a bcp string. This script will create a meaninful looking batch file
but obviously you can't bcp into temp tables:
DROP TABLE #import_files
CREATE TABLE #import_files ( file_id INT UNIQUE IDENTITY NOT NULL, file_name
VARCHAR(30) NOT NULL )
DROP TABLE #raw_data
CREATE TABLE #raw_data ( record_id INT UNIQUE IDENTITY NOT NULL, file_id INT
NOT NULL, record CHAR(36) )
ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT -1 FOR
file_id
GO
SET NOCOUNT ON
INSERT INTO #import_files ( file_name ) VALUES ( 'test1.txt' )
INSERT INTO #import_files ( file_name ) VALUES ( 'test2.txt' )
SET NOCOUNT OFF
GO
DROP TABLE #batch_file
CREATE TABLE #batch_file ( file_id INT, sort_id INT, command VARCHAR( 500 )
)
GO
--
SET NOCOUNT ON
-- Section header
INSERT INTO #batch_file
SELECT file_id, 10, 'REM bcp file ' + CAST( file_id AS VARCHAR ) + ' - ' +
file_name
FROM #import_files
-- Drop the default
INSERT INTO #batch_file
SELECT file_id, 20, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppassword
-q"ALTER TABLE #raw_data DROP CONSTRAINT def_raw_data__file_id'
FROM #import_files
-- Set the default
INSERT INTO #batch_file
SELECT file_id, 30, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppassword
-q"ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT ' +
CAST( file_id AS CHAR ) + ' FOR file_id"'
FROM #import_files
-- bcp the file
INSERT INTO #batch_file
SELECT file_id, 40, 'bcp -iyou get the idea.txt ; )'
FROM #import_files
-- Make a gap
INSERT INTO #batch_file
SELECT file_id, 50, ''
FROM #import_files
SELECT command
FROM #batch_file
ORDER BY file_id, sort_id
SET NOCOUNT OFF
Now, save the results as a batch file, remove the dashes from the top and
away you go. I actually use a similar structure in the migration, only it's
a bit more complex, plus it's wrapped in a stored procedure and paramterized
so it's nice and flexible.
If it seems like a lot of hard work, then perhaps this isn't the solution
for you, but it's worked for me!
Let me know how you get on.
Damien|||Bob,
If you are moving to SQL Server 2005, you might consider using
the BULK rowset provider. Existing (in SQL Server 2000) text
providers might also work, but may not be as fast.
DECLARE @.f nvarchar(200)
SET @.f = 'c:\test\values.txt'
INSERT INTO MyTestTable
SELECT colFromTextFile + @.f
SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;
Steve Kass
Drew University
Bob wrote:

>Hello,
> I use the following statement to insert a string of 35 characters into
a
>table (I am planning to figure out a suitable .FMT file for parsing this in
to
>the right column definitions, but that is after I figure out this current
>problem described below)
>BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
>(FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
>MyTestTable is currently defined as
>CREATE TABLE MyTestTable (Col1 CHAR(35))
>and some sample test data from MyTextFile.txt is as follows
>ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
>123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
>(length of the data is always 36 bytes and the length of the filename is
>always 14 bytes)
>(As you can tell, the DDL and data is what I am using for this test)
>This BULK INSERT works fine, but what I need to do is to get the name of th
e
>file (MyTextFile.txt in this case) appended to the end of the column.
>So, the table definition would change to
>CREATE TABLE MyTestTable (Col1 CHAR(49))
>I am clueless about how to get the name of the file (which will vary at
>runtime) into each row of the MyTestTable that gets affected by this BULK
>INSERT.
>(I will be inserting multiple files - different file names - to the same
>table one after the other and would like to have the filename stored in a
>separate column or appended to the column - either way. When I build the .F
MT
>file, I will split this into the appropriate columns and will change the
>table definition.
>Any suggestions would be appreciated. Please let me know if any further
>details are needed.
>Thanks!
>
>|||Thanks Damien - yes, this would work (now, I just have to get the DBA to
approve of building and dropping the constraint - hopefully, he will be ok
with it).
Thanks again.
"Damien" wrote:

> Ah,
> well I cheated a little bit here. I used SQL to write the batch file for
> me, and I use osql to fire off an ALTER TABLE to set the default for the
> column.
> So, from Query Analyser, write a query which selects your records, but
> create a bcp string. This script will create a meaninful looking batch fi
le
> but obviously you can't bcp into temp tables:
> DROP TABLE #import_files
> CREATE TABLE #import_files ( file_id INT UNIQUE IDENTITY NOT NULL, file_na
me
> VARCHAR(30) NOT NULL )
> DROP TABLE #raw_data
> CREATE TABLE #raw_data ( record_id INT UNIQUE IDENTITY NOT NULL, file_id I
NT
> NOT NULL, record CHAR(36) )
> ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT -1 FOR
> file_id
> GO
>
> SET NOCOUNT ON
> INSERT INTO #import_files ( file_name ) VALUES ( 'test1.txt' )
> INSERT INTO #import_files ( file_name ) VALUES ( 'test2.txt' )
> SET NOCOUNT OFF
> GO
> DROP TABLE #batch_file
> CREATE TABLE #batch_file ( file_id INT, sort_id INT, command VARCHAR( 500
) )
> GO
> --
> SET NOCOUNT ON
> -- Section header
> INSERT INTO #batch_file
> SELECT file_id, 10, 'REM bcp file ' + CAST( file_id AS VARCHAR ) + ' - ' +
> file_name
> FROM #import_files
>
> -- Drop the default
> INSERT INTO #batch_file
> SELECT file_id, 20, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppasswor
d
> -q"ALTER TABLE #raw_data DROP CONSTRAINT def_raw_data__file_id'
> FROM #import_files
> -- Set the default
> INSERT INTO #batch_file
> SELECT file_id, 30, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppasswor
d
> -q"ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT ' +
> CAST( file_id AS CHAR ) + ' FOR file_id"'
> FROM #import_files
> -- bcp the file
> INSERT INTO #batch_file
> SELECT file_id, 40, 'bcp -iyou get the idea.txt ; )'
> FROM #import_files
> -- Make a gap
> INSERT INTO #batch_file
> SELECT file_id, 50, ''
> FROM #import_files
>
> SELECT command
> FROM #batch_file
> ORDER BY file_id, sort_id
> SET NOCOUNT OFF
> Now, save the results as a batch file, remove the dashes from the top and
> away you go. I actually use a similar structure in the migration, only it
's
> a bit more complex, plus it's wrapped in a stored procedure and paramteriz
ed
> so it's nice and flexible.
> If it seems like a lot of hard work, then perhaps this isn't the solution
> for you, but it's worked for me!
> Let me know how you get on.
> Damien
>
>|||Steve,
Thanks for the update. At this time, the Co is not planning to move to
SQL Server 2005 (this project is expected to go live within a month), so I
guess I am stuck with 2000.
I haven't used text providers yet, so I don't fully understand the code. I
will go thru' BOL and assuming the performance drop isn't too much, I will
try to use this. Currently, I am able to push about 400 million rows into th
e
table (without the filename of course) in about an hour
Thanks again!
"Steve Kass" wrote:

> Bob,
> If you are moving to SQL Server 2005, you might consider using
> the BULK rowset provider. Existing (in SQL Server 2000) text
> providers might also work, but may not be as fast.
> DECLARE @.f nvarchar(200)
> SET @.f = 'c:\test\values.txt'
> INSERT INTO MyTestTable
> SELECT colFromTextFile + @.f
> SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
> FORMATFILE = 'c:\test\values.fmt') AS a;
> Steve Kass
> Drew University
> Bob wrote:
>
>

Bulk Insert problem

Hello,

I am trying to insert thousands of records from a CSV file to my SQL Server database.
I am using the following to do the operation

BULK INSERT
CrimeStats.dbo.[TestDB]
FROM
'c:\Test.csv'
WITH
(
CHECK_CONSTRAINTS, FIELDTERMINATOR = ',', KEEPIDENTITY, KEEPNULLS, TABLOCK ,
ROWTERMINATOR = '\n'
)

It works fine.

My problem is I want my table in SQL server to have a Primary Key which would automatically increment. SO with every bulk insert that I would be doing every month, the Primary Key would automatically increment itself. The data in the CSV file does not have the ID field. So If I use the above script to upload the same data from the CSV file into my SQL table with the Primary Key, the BULK INSERT does not work.

It prints out the following error.

"Bulk insert data conversion error (type mismatch) for row 1, column 1 (ID)."

Is there any way that I could do what I am trying to do? The primary key is quite important in this table.

Any help on this would be much appreciated.

Thank YouSounds like you need an identity column in your table. TheBulk Insert topic in Books Online indicates that you need to specify a format file if you want SQL Server to automatically insert that identity column for you.

Terri

Bulk Insert problem

Bulk insert report the following code after being
used for a period.
When this happens, we need to re-start the SQL Server
and then it will disappear for a while and then happen
again.
Any body have idea about how to work around this problem?
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
In Programming Xing has a problem where he gets this error
http://support.microsoft.com/default...&Product=sql2k
due to not being sysadmin.
The second time he tries to run a bulk insert he gets your error.
Do you have something using the wrong permissions for bulkinsert?
"Mike" wrote:

> Bulk insert report the following code after being
> used for a period.
> When this happens, we need to re-start the SQL Server
> and then it will disappear for a while and then happen
> again.
> Any body have idea about how to work around this problem?
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
>
|||This should have nothing about access since
my problem is that this BULK INSERT statement
works most time and then report the error.
After re-starting the SQL Server, then it works
again.
Looks like a bug of MS related to memory allocation
and instance creation.
Thanks!

>--Original Message--
>In Programming Xing has a problem where he gets this error
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;302621&Product=sql2k
>due to not being sysadmin.
>The second time he tries to run a bulk insert he gets
your error.
>Do you have something using the wrong permissions for
bulkinsert?[vbcol=seagreen]
>
>"Mike" wrote:
problem?[vbcol=seagreen]
DSO
>.
>

Bulk Insert Problem

I am trying to do a bulk insert with the following statement:
BULK INSERT dbo.Rand_By_Day from
'C:\Test\Randfinal.txt'
I get the following error:
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file "C:\Test\Randfinal.txt" could not be
opened. Operating system error code 3(The system cannot find the path
specified.).
Can anyone give me an idea why this error is happening? Any help in
advance is appreciated. Thanks.
Dave ChristmanBULK INSERT is running on the server. If "C:\Test\Randfinal.txt" is
not on the server it will not be found.
Roy Harvey
Beacon Falls, CT
On 29 Jun 2006 13:44:42 -0700, "hedgracer" <d.christman@.sbcglobal.net>
wrote:

>I am trying to do a bulk insert with the following statement:
>BULK INSERT dbo.Rand_By_Day from
>'C:\Test\Randfinal.txt'
>I get the following error:
>Msg 4861, Level 16, State 1, Line 3
>Cannot bulk load because the file "C:\Test\Randfinal.txt" could not be
>opened. Operating system error code 3(The system cannot find the path
>specified.).
>Can anyone give me an idea why this error is happening? Any help in
>advance is appreciated. Thanks.
>Dave Christman

Bulk Insert problem

Bulk insert report the following code after being
used for a period.
When this happens, we need to re-start the SQL Server
and then it will disappear for a while and then happen
again.
Any body have idea about how to work around this problem?
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].In programming Xing has a problem where he gets this error
http://support.microsoft.com/defaul...1&Product=sql2k
due to not being sysadmin.
The second time he tries to run a bulk insert he gets your error.
Do you have something using the wrong permissions for bulkinsert?
"Mike" wrote:

> Bulk insert report the following code after being
> used for a period.
> When this happens, we need to re-start the SQL Server
> and then it will disappear for a while and then happen
> again.
> Any body have idea about how to work around this problem?
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
>|||This should have nothing about access since
my problem is that this BULK INSERT statement
works most time and then report the error.
After re-starting the SQL Server, then it works
again.
Looks like a bug of MS related to memory allocation
and instance creation.
Thanks!

>--Original Message--
>In programming Xing has a problem where he gets this error
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;302621&Product=sql2k
>due to not being sysadmin.
>The second time he tries to run a bulk insert he gets
your error.
>Do you have something using the wrong permissions for
bulkinsert?
>
>"Mike" wrote:
>
problem?[vbcol=seagreen]
DSO[vbcol=seagreen]
>.
>

Bulk Insert of XML of two level of hierarchy

Hello,
I want to load following XML into one sql table .
Pls let me know, how can i load it..My XML file will be 60 MB so i want to
do BULK insert.
It is a two level of hierarchy. Project
can have multiple Activities.. so one project will have multiple Activities
rows. so in table i want to insert projid repeating for each activity
I want to insert like following. My sql table and xml is as following.
SQL TABLE
projid Aid Adesc
11142930 10000 General
11142930 10001 Accounting
XML
<?xml version="1.0" encoding="utf-8" ?>
- <ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
- <PROJECT>
<PID>11142930</PID>
- <ACTIVITIES>
- <ACT>
<AID>10000</AID>
<ADESC>General</ADESC>
</ACT>
</ACTIVITIES>
- <ACTIVITIES>
- <ACT>
<AID>10001</AID>
<ADESC>Accounting</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
</ROWSET>
Thanks,
mvpHi
Check out:
http://sqlxml.org/faqs.aspx?faq=24
http://sqlxml.org/faqs.aspx?faq=43
John
"mvp" wrote:

> Hello,
> I want to load following XML into one sql table .
> Pls let me know, how can i load it..My XML file will be 60 MB so i want to
> do BULK insert.
> It is a two level of hierarchy. Project
> can have multiple Activities.. so one project will have multiple Activitie
s
> rows. so in table i want to insert projid repeating for each activity
> I want to insert like following. My sql table and xml is as following.
> SQL TABLE
> projid Aid Adesc
> 11142930 10000 General
> 11142930 10001 Accounting
> XML
> <?xml version="1.0" encoding="utf-8" ?>
> - <ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> - <PROJECT>
> <PID>11142930</PID>
> - <ACTIVITIES>
> - <ACT>
> <AID>10000</AID>
> <ADESC>General</ADESC>
> </ACT>
> </ACTIVITIES>
> - <ACTIVITIES>
> - <ACT>
> <AID>10001</AID>
> <ADESC>Accounting</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> </ROWSET>
> Thanks,
> mvp
>

Bulk Insert not working in ASP

---------------------------

I have the following ASP code:

'load the conversion_dump_daily table
Response.Write("<br>Load Conversion_Dump_Daily data for " & theDate & ".")
Response.Write("<br> File Name: " & inputFile & ".")
Response.flush
cmd.CommandText = "AddConversionDumpDaily"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("InFile", adVarChar, , 1000, "D:\DataSources\ConversionBuilder\test.txt")
On Error Resume Next
cmd.Execute
cmd.Parameters.Delete("InFile")

..... asp error checking follows

---------------------------

The stored procedure is as follows:

CREATE PROCEDURE AddConversionDumpDaily @.InFile varchar(1000)
AS
DECLARE @.ErrorSave int
SET @.ErrorSave = 0
-- Create a transaction so that a rollback can be performed in case of an error.
BEGIN TRAN
-- Bulk insert Conversion data from csv file.
EXEC('BULK INSERT Conversion_Dump_Daily FROM ''' + @.InFile + ''' WITH (FORMATFILE=''D:\FormatFiles\ConversionBuilder.fmt '') ')

-- Check to see if the insert was successful
if @.@.error <> 0
begin
SET @.ErrorSave = @.@.error -- Store the error code in a variable.
rollback tran
goto endOfBatch
end
COMMIT TRAN
endOfBatch:
RETURN @.ErrorSave -- Return the error code to determine success or failure of the process.
GO

---------------------------

The stored procedure works fine from the Query Analyzer, but nothing is inserted from the ASP page. I don't get any errors... it is just that nothing appears!

Any ideas anyone?I am not sure if you left out the code, but where are you creating the command object and adding the connection information. Also, you will not receive an error when you are using "On Error Resume Next" before your execute statement. By removing this, you will see the problem.

Good luck.

bulk insert is drinving me mad!

I am probably a putz, but the following is got me stumped. I have a text file called file001.txt that looks like this:

1,2,3,4,5,6,7,8,9|1,2,3,4,5,6,7,8,9|

When I use the following bulk insert statement everything works and I have no problems at all:

BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '|'
)

But, if I try to do this it fails:

declare @.fdel varchar
declare @.rdel varchar
set @.fdel = ','
set @.rdel = '|'
BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = @.fdel,
ROWTERMINATOR = @.rdel
)

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. The statement has been terminated.

What the heck is happening? Please help!!!!Are you sure that BULK INSERT allows variables to be used for terminators ?

Try:
declare @.SqlStr varchar(8000)
declare @.fdel varchar
declare @.rdel varchar
set @.fdel = ','
set @.rdel = '|'
set @.SqlStr='
BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = '+@.fdel+',
ROWTERMINATOR = '+@.rdel+'
)
exec (SqlSr)

?|||Good morning kukuk,
That did not get it, but thanks. I farted around with it a little and got the following to work - and its closer to what I eventually want to accomplish anyway:

declare @.sql varchar(8000)
select @.sql = "BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = '" + (select fld_delimiter from datadef where description = 'BFH') + "',
ROWTERMINATOR = '" + (select row_delimiter from datadef where description = 'BFH') + "'
)"
exec(@.sql)

While your response wasnt an excat fix, it did allow me to break out of the endless cycle of MADNESS that totally blinded me to a simple solution last night.
-thanks again

Bulk insert in to a partitioned View?

Greetings once again my SQL friends,

I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :

Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation

How to solve this problem?

Hi,

Bulk insert operations are not supported for partitioned views. See for more details:

Exporting Data from or Importing Data to a View
http://msdn2.microsoft.com/en-us/ms187086.aspx

You can import Data within a Data Flow Task into a Partitioned View if you use an OLE DB Destination with the Data Access Mode option set to Table or View instead of "Table or View - fastload", which is the default and technically a bulk operation.

Please be aware that locally partitioned views are supported in SQL Server 2005 only for backward compatibility. See also:

Scenarios for Using Views
http://msdn2.microsoft.com/en-us/library/ms188250.aspx

I hope that helps,

Bertil