Does the following topics help? BOL (SQL Server 2000 or 2005) has lot of examples including format files, statements to use etc.
http://msdn2.microsoft.com/en-us/library/ms189989.aspx
http://msdn2.microsoft.com/en-us/library/aa337544.aspx
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
http://msdn2.microsoft.com/en-us/library/ms178129.aspx
http://msdn2.microsoft.com/en-us/library/ms189848.aspx
http://msdn2.microsoft.com/en-us/library/ms190625.aspx
sqlI first noticed that the try/catch did not seem to work when the text file did not exist and I tried to execute this block of code from a stored proc.
Then, when I tried to compile the proc using a file that did not exist at the time of compilation, I can't even get it to compile.
If I compile, then remove the file, the try/catch does not work.
TRUNCATE TABLE LoadDaily;
BEGIN TRY
BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
BATCHSIZE = 100,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0A',
FIRSTROW = 2
);
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
Instead of saying "compile" I mean that I can't use either ALTER PROCEDURE or CREATE PROCEDURE when the text file does not exist.
Fortunately, using EXEC works and the try/catch works as directed.
This will be run from a service so I don't think I will have any permissions issues.
I tested in SQL 2000 and did not have any problems editing the SP using EM with a non-existant text file.
It does not work if the format file is missing either.
TRUNCATE TABLE LoadDaily;
BEGIN TRY
DECLARE @.sql nvarchar(300), @.Fname nvarchar(300)
SET @.Fname = 'c:\_temp\daily.txt'
SET @.sql = 'BULK INSERT LoadDaily FROM ''' + @.Fname + ''''
+ ' WITH '
+ ' ( '
+ ' BATCHSIZE = 100, '
+ ' FIELDTERMINATOR = ''|'', '
+ ' ROWTERMINATOR = ''0x0A'', '
+ ' FIRSTROW = 2'
+ ' );'
EXEC (@.sql)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
|||This is a known limitation. TRY...CATCH cannot catch all errors. Some of the errors that happen at compile time can be caught only in the outer TRY..CATCH. This is what you kind of achieved by using dynamic SQL for the BULK INSERT. Please see the TRY...CATCH topic in BOL for more details.|||Sorry, this is a level 16 error so it should be caught by definition (BOL says the range is 10 to 20).I think I'm more concerned about the regression error. I may be doing something wrong because the environment has changed but I found no way to create the Stored Procedure if the the text file does not exist. I went back and tested it in SQL 2000 and it worked fine.
Since there is now "run as" functionality with EXECUTE, it won't be a big deal but the issue should be addressed.
The new OPENROWSET has the same problem for the text file and the format file.|||Not all errors of a particular severity can be caught. In your examples, these errors are raised at compile time so there is no code execution involved at all for the TRY...CATCH to work. OPENROWSET(BULK) and BULK INSERT pretty much uses the same infrastructure so both will error out if the data file/format file is invalid or missing.|||
OK, let's forget the Try/Catch issue for now.
Just see if you can compile the test proc below in SQL 2005 when daily.txt does NOT exist.
I just found an interesting side effect: Make sure that LoadDaily is a valid table. If LoadDaily is invalid, the code WILL compile.
Now that's really weird.
CREATE PROCEDURE test
AS
BEGIN
BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
END
GO
Deferred name resolution really DOES explain why it worked in 2000.
It seems to be partially broken now:
If the table is VALID but the text file is NOT VALID then the proc will NOT compile.
I can't believe that this was the intention. This is simply an external text file so it should not affect compilation.
|||Unfortunately, this is a side-effect of BULK INSERT using the OPENROWSET(BULK) infrastructure internally. OPENROWSET(BULK) validates parameters at compile-time itself and hence the error. This behavior is same as other OPENROWSET commands. For example, if you try OPENROWSET with Jet provider and missing/invalid mdb file you will get same behavior in SQL Server 2000/2005. The reality is that most people will use dynamic SQL since the filenames cannot be parameterized directly so they will not hit the issue.sqlCan anyone suggest a bulk insert solution in a mirroring environment?
Basically, I'd like to import a text or excel file using SSIS.
Do all the "Flat File" and "Excel" data flow sources use bulk inserts?
Just wondering if I would need to use a "staging" table or something like that since bulk inserts won't be logged when dbs are mirrored. I'm assuming I'd be able to import data using the dataflow sources into a sql destination "staging" table, then do a "INSERT INTO [production] SELECT * FROM [staging]". I'm assuming this will be logged (but slow).
Any help would be appreciated.
BULK INSERT is fully logged in FULL mode, so you should not need to do the intermediate step.
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
|||Instead of converting your files to ANSI as suggested you could also fix your delimiter problem for Unicode files. The TERMINATOR should be '\t\0' instead of just '\t' for tabs and '\r\0\n\0' for the row delimiter if you are using standard CR+LF.
Regards,
Lars
sqlWe are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
|||Instead of converting your files to ANSI as suggested you could also fix your delimiter problem for Unicode files. The TERMINATOR should be '\t\0' instead of just '\t' for tabs and '\r\0\n\0' for the row delimiter if you are using standard CR+LF.
Regards,
Lars
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
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:
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.
