bulk load change in 2005?

I have a dts package that does some bulk load operations without a problem in 2000. When trying to convert them to SISS or trying to run the same dts package against the a copy of the database (in 2000 mode) running on a 2005 server I get Bulk load data conversion errors (truncation) for row xxxx....

Has the bulk load process changed in 2005?

My Sproc is using a bulk insert command with a fmt file..

Even when you are using SSIS for this; I think this question is better asked in the databse engine forum...|||

Daveko wrote:

Has the bulk load process changed in 2005?

Yes, there have been certain changes in the bulk load that may cause seemingly fine SQL Server 2000 scenarios to fail when they run against SQL Server 2005: the engine now enforces stricter data purity checks and additional physical consistency checks for the input data streams both in BCP and BULK INSERT/OPENROWSET.|||

Is there any way to relax those checks? I was under the assumption that running a database in 2000 mode would allow backward compatibility. I am under a huge time crunch now, and at this point my only contingency plan is to keep a 2000 server up for this specific database.

Any assistance would be appreciated..

|||Unfortunately, there is no 'officially' documented way of relaxing those checks. Please contact CSS - they might be able to assist you.|||

What is CSS?

I have cleaned up my data and got the import to run successfully.

|||CSS stands for Customer Service and Support. It used to be called PSS.

bulk load change in 2005?

I have a dts package that does some bulk load operations without a problem in 2000. When trying to convert them to SISS or trying to run the same dts package against the a copy of the database (in 2000 mode) running on a 2005 server I get Bulk load data conversion errors (truncation) for row xxxx....

Has the bulk load process changed in 2005?

My Sproc is using a bulk insert command with a fmt file..

Even when you are using SSIS for this; I think this question is better asked in the databse engine forum...|||

Daveko wrote:

Has the bulk load process changed in 2005?

Yes, there have been certain changes in the bulk load that may cause seemingly fine SQL Server 2000 scenarios to fail when they run against SQL Server 2005: the engine now enforces stricter data purity checks and additional physical consistency checks for the input data streams both in BCP and BULK INSERT/OPENROWSET.|||

Is there any way to relax those checks? I was under the assumption that running a database in 2000 mode would allow backward compatibility. I am under a huge time crunch now, and at this point my only contingency plan is to keep a 2000 server up for this specific database.

Any assistance would be appreciated..

|||Unfortunately, there is no 'officially' documented way of relaxing those checks. Please contact CSS - they might be able to assist you.|||

What is CSS?

I have cleaned up my data and got the import to run successfully.

|||CSS stands for Customer Service and Support. It used to be called PSS.

BULK INSERTing UNICODE data with format files

Sorry but could you please show me how I convert (specifically the
separators) this bcp format file to unicode so that it works (with a unicod
e
file). I have tried various methods without success.
8.0
2
1 SQLNCHAR 2 100 "," 1 RecordId
Latin1_General_CI_AS
2 SQLNCHAR 4 0 "\r\n" 2 TheText
Latin1_General_CI_AS
Thanks
Peter Doyle
"Erland Sommarskog" wrote:

> Nitin M (nitin@.nowhere.com) writes:
> Ah! Glad to hear that you where able to find it out yourself.
>
> The obvious idea would be to save the format file as Unicode, but that
> does not work; you only get a message about unknown version. I tried in
> SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
> be an XML format file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Peter Doyle (PeterDoyle@.discussions.microsoft.com) writes:
> Sorry but could you please show me how I convert (specifically the
> separators) this bcp format file to unicode so that it works (with a
> unicode file). I have tried various methods without success.
> 8.0
> 2
> 1 SQLNCHAR 2 100 "," 1 RecordId
> Latin1_General_CI_AS
> 2 SQLNCHAR 4 0 "\r\n" 2 TheText
> Latin1_General_CI_AS
The above won't work for several reasons. You have specified a
prefix length, but prefix lengths is only for binary formats.
The problem with impoting Unicode files, is that the format file itself
must be a 8-bit file. At least I seem to recall that BCP freaked out
when I tried with a Unicode file. (Hm, did I try BULK INSERT as well+)
Anyway, with the format indicated by your format file, you don't any
format file at all. For a table like:
CREATE TABLE test (a nvarchar(100) NOT NULL,
b nvarchar(100) NOT NULL)
And this data (saved as Unicode):
Detta r lite data,Och detta r mer data.
Sedan kommer det n mer data hr,Och nu kommer det n mer
Trams!, Det r vad det r!
This command line will do:
bcp tempdb..test in slask.bcp -w -t, -T
-w specifies that the file a Unicode file.
But in case that your real case calls for a format file, here is one
that works with the above table and data:
8.0
2
1 SQLNCHAR 0 0 ",\0" 1 a ""
2 SQLNCHAR 0 0 "\r\0\n\0" 2 b ""
The tricky part is the \0 that must come aftet the character it belongs
to, due to endianness.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BULK INSERT from a table

Hi there.

I have a table that contains comma delimited text, and I am trying to convert this into another table

eg my target table looks like

Produce|Price|QuantityPerPrice

and my input table contains strings such as

"apples","7.5","10"

"pears","10","8"

"oranges","8","6"

Does anyone have any ideas on how to do this? I am after a solution that does them all at once: I am currently using charindex() to find each column, one at a time, but given the speed of BULK INSERT I would much rather do it as a table. The one solution that I don't want to resort to is to export the table with delimited strings to a data file, then BULK INSERT it...

Cheers

Neil

After reading the problem I am very interested to see the solution as I cant think of one without moving data to file or using charindex.|||Bulk insert or openrowset(bulk) (in SQL Server 2005) requires file as a data source. So what you are trying is not possible. You could however optimize some of the string splitting and do just insert. If there are large number of rows then perform the insert in batches in a transaction to get better performance. But I am confused as to why you would store such inefficient format in the database table and then try to manipulate it relationally. Why can't you store the data directly into the target table?|||

Thanks for the reply.

We are taking inputs with unknown numbers of fields and unknown delimiters, and working with them. We do this by importing the data into a single field and then analysing it. This leads to single-field tables with delimited data in them.

I think we are going to have to export with bcp, create a format file, then re-import with the known delimiter and fieldnames.

Which raises another question: is there a command line parameter I can pass to bulk insert so that it will take a delimiter and use the first row as fieldnames, or do I need a format file?

Regards,

Neil

|||I am still confused as to why you would first import the data into single column values and then try to do the parsing operation on the server. This seems inefficient to me. You could easily design DTS/SSIS packages or write your own scripting code to import the files directly in the first place. There is no facility in any of the bulk API to consider the first row as column names. You will have to skip it using the FIRSTROW parameter in BULK INSERT or similar option in BCP. There are also options to specify delimiter. Use of format files depends on the complexity of your data format. For the simple, delimiter case you may not require it. See BOL for more details on the command syntax and the BCP utility.

BULK INSERT from a table

Hi there.

I have a table that contains comma delimited text, and I am trying to convert this into another table

eg my target table looks like

Produce|Price|QuantityPerPrice

and my input table contains strings such as

"apples","7.5","10"

"pears","10","8"

"oranges","8","6"

Does anyone have any ideas on how to do this? I am after a solution that does them all at once: I am currently using charindex() to find each column, one at a time, but given the speed of BULK INSERT I would much rather do it as a table. The one solution that I don't want to resort to is to export the table with delimited strings to a data file, then BULK INSERT it...

Cheers

Neil

After reading the problem I am very interested to see the solution as I cant think of one without moving data to file or using charindex.|||Bulk insert or openrowset(bulk) (in SQL Server 2005) requires file as a data source. So what you are trying is not possible. You could however optimize some of the string splitting and do just insert. If there are large number of rows then perform the insert in batches in a transaction to get better performance. But I am confused as to why you would store such inefficient format in the database table and then try to manipulate it relationally. Why can't you store the data directly into the target table?|||

Thanks for the reply.

We are taking inputs with unknown numbers of fields and unknown delimiters, and working with them. We do this by importing the data into a single field and then analysing it. This leads to single-field tables with delimited data in them.

I think we are going to have to export with bcp, create a format file, then re-import with the known delimiter and fieldnames.

Which raises another question: is there a command line parameter I can pass to bulk insert so that it will take a delimiter and use the first row as fieldnames, or do I need a format file?

Regards,

Neil

|||I am still confused as to why you would first import the data into single column values and then try to do the parsing operation on the server. This seems inefficient to me. You could easily design DTS/SSIS packages or write your own scripting code to import the files directly in the first place. There is no facility in any of the bulk API to consider the first row as column names. You will have to skip it using the FIRSTROW parameter in BULK INSERT or similar option in BCP. There are also options to specify delimiter. Use of format files depends on the complexity of your data format. For the simple, delimiter case you may not require it. See BOL for more details on the command syntax and the BCP utility.