Import Truncation Error - Please help
Craig
I am at my wit's end. All I'm trying to do is import data into a SQL table.
No matter what data type I try (varchar, nvarchar(max), ntext) it's no use.
I get the same truncation errors and the import halts.
I've tried importing from Excel, text, Access. No use. Am I to assume that
if you have a field that just happens (God forbid) to be over 255 characters,
you're up the creek?
I've tried creating my own table and importing into it - nothing.
I've searched this site and I can't get find any solution. Somebody please
help.
Thank you.
--
Craig
Craig
Don't mind me replying to myself...
It's a little odd that SQL 2008 doesn't recognize Access 2007 databases. I
had to export to a text file, import into a 2003 mdb, then import into SQL.
SQL didn't like the actual delimited text file, Access 2007, or Excel 2007.
I was also told that you can set the field length on the fly as you import.
Any ideas on this?
Thanks for any and all help.
--
Craig
Linchi Shea
import/export feature for quite some time, and it generally works fine.
Although it may not handle the true CSV format well, it doesn't just
arbitrarily truncate data if your data stay within the bound.
It sounds like it doesn't work for you at all. If that's the case, there
must be something else going on.
Linchi
Craig
I get the following errors when importing a .txt/.xls file:
**********************************************************
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
conversion for column "ToNEW" returned status value 4 and status text "Text
was truncated or one or more characters had no match in the target code
page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "output column "ToNEW" (22)" failed
because truncation occurred, and the truncation row disposition on "output
column "ToNEW" (22)" specifies failure on truncation. A truncation error
occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file
"C:\Documents and Settings\[redacted]\Desktop\[redacted]\tblResults1.txt" on
data row 17.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "Source - tblResults1_txt" (1) returned
error code 0xC0202092. The component returned a failure code when the
pipeline engine called PrimeOutput(). The meaning of the failure code is
defined by the component, but the error is fatal and the pipeline stopped
executing. There may be error messages posted before this with more
information about the failure.
(SQL Server Import and Export Wizard)
**********************************************************
Sorry for the length - I just copied the error message report.
Now, this happens when I create a table and then import into it, as well.
--
Craig
Linchi Shea
separators and the row terminators are properly in place, and try to import
the text data into a table using BCP, just o make sure the fundamental pieces
are there and working? Then, we can move on to test import wizrad.
Linchi
Craig
(incorrect syntax):
BCP xxxxxxx.dbo.tblresults in C:\Documents and
Settings\xxxxxxxx\Desktop\LawHI\test1.txt -T -c
Erland Sommarskog
> Haven't used BCP. I must be doing something wrong, this doesn't work
> (incorrect syntax):
>
> BCP xxxxxxx.dbo.tblresults in C:\Documents and
> Settings\xxxxxxxx\Desktop\LawHI\test1.txt -T -c
path on the command line.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Linchi Shea
Linchi
Craig
I also tried BULK INSERT and received the error code 5 (access is denied).
I can access these drives through explorer. Any ideas?
Thanks.
--
Craig
Erland Sommarskog
> I encountered errors with bcp (server connection not available).
Did you specify the server correctly? You do this with the -S option.
If you leave it out, it will try connect to the local default instance.
> I also tried BULK INSERT and received the error code 5 (access is denied).
>
> I can access these drives through explorer. Any ideas?
It could be that the service account for SQL Server does not have access
to these disks. What machine is SQL Server running on, and which is the
service account? It usually best to have a regular user account, and
not LocalSystem or somesuch.
chrisb
Craig wrote:
Import Truncation Error - Please help
11-Sep-09
Hello. I am using SQL 2008.
I am at my wit is end. All I am trying to do is import data into a SQL table.
No matter what data type I try (varchar, nvarchar(max), ntext) it is no use.
I get the same truncation errors and the import halts.
I have tried importing from Excel, text, Access. No use. Am I to assume that
if you have a field that just happens (God forbid) to be over 255 characters,
you are up the creek?
I have tried creating my own table and importing into it - nothing.
I have searched this site and I cannot get find any solution. Somebody please
help.
Thank you.
--
Craig
Previous Posts In This Thread:
On Friday, September 11, 2009 1:15 AM
Craig wrote:
Import Truncation Error - Please help
Hello. I am using SQL 2008.
I am at my wit is end. All I am trying to do is import data into a SQL table.
No matter what data type I try (varchar, nvarchar(max), ntext) it is no use.
I get the same truncation errors and the import halts.
I have tried importing from Excel, text, Access. No use. Am I to assume that
if you have a field that just happens (God forbid) to be over 255 characters,
you are up the creek?
I have tried creating my own table and importing into it - nothing.
I have searched this site and I cannot get find any solution. Somebody please
help.
Thank you.
--
Craig
On Friday, September 11, 2009 3:10 AM
Craig wrote:
sUPDATE:Don't mind me replying to myself...
sUPDATE:
Don't mind me replying to myself...
it is a little odd that SQL 2008 does not recognize Access 2007 databases. I
had to export to a text file, import into a 2003 mdb, then import into SQL.
SQL did not like the actual delimited text file, Access 2007, or Excel 2007.
I was also told that you can set the field length on the fly as you import.
Any ideas on this?
Thanks for any and all help.
--
Craig
"Craig" wrote:
On Friday, September 11, 2009 12:52 PM
Linchi Shea wrote:
It would help if you could be a bit more specific.
It would help if you could be a bit more specific. I have been using the
import/export feature for quite some time, and it generally works fine.
Although it may not handle the true CSV format well, it does not just
arbitrarily truncate data if your data stay within the bound.
It sounds like it does not work for you at all. If that is the case, there
must be something else going on.
Linchi
"Craig" wrote:
On Friday, September 11, 2009 1:50 PM
Craig wrote:
--
Craig
"Linchi Shea" wrote:
On Friday, September 11, 2009 3:03 PM
Linchi Shea wrote:
Can you save your text file in the ASCII format, make sure the
Can you save your text file in the ASCII format, make sure the column
separators and the row terminators are properly in place, and try to import
the text data into a table using BCP, just o make sure the fundamental pieces
are there and working? Then, we can move on to test import wizrad.
Linchi
"Craig" wrote:
On Friday, September 11, 2009 8:21 PM
Craig wrote:
Haven't used BCP.
Haven't used BCP. I must be doing something wrong, this does not work
(incorrect syntax):
BCP xxxxxxx.dbo.tblresults in C:\Documents and
Settings\xxxxxxxx\Desktop\LawHI\test1.txt -T -c
--
Craig
"Linchi Shea" wrote:
On Sunday, September 13, 2009 11:37 PM
Linchi Shea wrote:
Oh, how much I love embedded space in a file path!
Oh, how much I love embedded space in a file path! :-(
Linchi
"Erland Sommarskog" wrote:
On Monday, September 14, 2009 3:17 PM
Craig wrote:
I encountered errors with bcp (server connection not available).
I encountered errors with bcp (server connection not available).
I also tried BULK INSERT and received the error code 5 (access is denied).
I can access these drives through explorer. Any ideas?
Thanks.
--
Craig
"Linchi Shea" wrote:
On Monday, October 26, 2009 8:09 PM
Erland Sommarskog wrote:
Craig (Cr...@discussions.microsoft.
Craig (Cr...@discussions.microsoft.com) writes:
You must put the file path in quotes, as always when specifying a file
path on the command line.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
On Monday, October 26, 2009 8:09 PM
Erland Sommarskog wrote:
Craig (Cr...@discussions.microsoft.
Craig (Cr...@discussions.microsoft.com) writes:
Did you specify the server correctly? You do this with the -S option.
If you leave it out, it will try connect to the local default instance.
It could be that the service account for SQL Server does not have access
to these disks. What machine is SQL Server running on, and which is the
service account? It usually best to have a regular user account, and
not LocalSystem or somesuch.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server - Insert Record And Get New ID
http://www.eggheadcafe.com/tutorials/aspnet/4b5c0707-d70a-4e8a-ba7c-aa271ab7302a/sql-server--insert-recor.aspx