Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" 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)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!
Hi
It looks like you text has some data that can not be imported within the
text field. You may want to look how far the import got and then look at the
data on the row that failed.
You can import directly from the excel file without converting the data.
John
"tootsuite@.gmail.com" wrote:
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>
|||Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegr oups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>
|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
> between the two. If it is a VARCHAR column, what is its specified size? Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegr oups.com...
|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:[vbcol=seagreen]
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at the
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
|||Hi
I assume you are doing this manually and not wanting to automate it?
John
"tootsuite@.gmail.com" wrote:
> I know you can import xls directly however if you've ever had your
> "text" converted to float in the process sometimes it's just easier to
> go with the txt format so you don't have to map fields
> John Bell wrote:
>
|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:[vbcol=seagreen]
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
>
Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts
Friday, March 23, 2012
Import fails on varchar field
Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" 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)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Hi
It looks like you text has some data that can not be imported within the
text field. You may want to look how far the import got and then look at the
data on the row that failed.
You can import directly from the excel file without converting the data.
John
"tootsuite@.gmail.com" wrote:
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternat
e
> between the two. If it is a VARCHAR column, what is its specified size?
Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:[vbcol=seagreen]
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at t
he
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
>|||Hi
I assume you are doing this manually and not wanting to automate it?
John
"tootsuite@.gmail.com" wrote:
> I know you can import xls directly however if you've ever had your
> "text" converted to float in the process sometimes it's just easier to
> go with the txt format so you don't have to map fields
> John Bell wrote:
>|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:[vbcol=seagreen]
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
>|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
>
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" 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)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Hi
It looks like you text has some data that can not be imported within the
text field. You may want to look how far the import got and then look at the
data on the row that failed.
You can import directly from the excel file without converting the data.
John
"tootsuite@.gmail.com" wrote:
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternat
e
> between the two. If it is a VARCHAR column, what is its specified size?
Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:[vbcol=seagreen]
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at t
he
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
>|||Hi
I assume you are doing this manually and not wanting to automate it?
John
"tootsuite@.gmail.com" wrote:
> I know you can import xls directly however if you've ever had your
> "text" converted to float in the process sometimes it's just easier to
> go with the txt format so you don't have to map fields
> John Bell wrote:
>|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:[vbcol=seagreen]
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
>|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
>
Import fails on varchar field
Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" 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)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
> between the two. If it is a VARCHAR column, what is its specified size? Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" 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)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at the
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" 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)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >
> >|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
> > I know you can import xls directly however if you've ever had your
> > "text" converted to float in the process sometimes it's just easier to
> > go with the txt format so you don't have to map fields
> >
> > John Bell wrote:
> > > Hi
> > >
> > > It looks like you text has some data that can not be imported within the
> > > text field. You may want to look how far the import got and then look at the
> > > data on the row that failed.
> > >
> > > You can import directly from the excel file without converting the data.
> > >
> > > John
> > >
> > >
> > >
> > > "tootsuite@.gmail.com" wrote:
> > >
> > > > Hello,
> > > >
> > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > file into SQL Server 2005. This file is a commercial marketing
> > > > information database that contains about 76,000 rows. I converted the
> > > > xls files provided into text files before the import.
> > > >
> > > > However, I keep getting this error when trying to import:
> > > >
> > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > conversion for column "CBSAName" 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)
> > > >
> > > > I looked at the text file and noticed that strings are delmited by
> > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > However the error persists.
> > > >
> > > > I'm not sure why it's complaining about the field, as it is just a
> > > > simple text field delimited by single quotes.
> > > >
> > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > >
> > > > Thanks!
> > > >
> > > >
> >
> >|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
> > Hi
> >
> > I assume you are doing this manually and not wanting to automate it?
> >
> > John
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > I know you can import xls directly however if you've ever had your
> > > "text" converted to float in the process sometimes it's just easier to
> > > go with the txt format so you don't have to map fields
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > It looks like you text has some data that can not be imported within the
> > > > text field. You may want to look how far the import got and then look at the
> > > > data on the row that failed.
> > > >
> > > > You can import directly from the excel file without converting the data.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "tootsuite@.gmail.com" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > > file into SQL Server 2005. This file is a commercial marketing
> > > > > information database that contains about 76,000 rows. I converted the
> > > > > xls files provided into text files before the import.
> > > > >
> > > > > However, I keep getting this error when trying to import:
> > > > >
> > > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > > conversion for column "CBSAName" 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)
> > > > >
> > > > > I looked at the text file and noticed that strings are delmited by
> > > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > > However the error persists.
> > > > >
> > > > > I'm not sure why it's complaining about the field, as it is just a
> > > > > simple text field delimited by single quotes.
> > > > >
> > > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > > >
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
>
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" 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)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" 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)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
> between the two. If it is a VARCHAR column, what is its specified size? Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" 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)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at the
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" 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)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >
> >|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
> > I know you can import xls directly however if you've ever had your
> > "text" converted to float in the process sometimes it's just easier to
> > go with the txt format so you don't have to map fields
> >
> > John Bell wrote:
> > > Hi
> > >
> > > It looks like you text has some data that can not be imported within the
> > > text field. You may want to look how far the import got and then look at the
> > > data on the row that failed.
> > >
> > > You can import directly from the excel file without converting the data.
> > >
> > > John
> > >
> > >
> > >
> > > "tootsuite@.gmail.com" wrote:
> > >
> > > > Hello,
> > > >
> > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > file into SQL Server 2005. This file is a commercial marketing
> > > > information database that contains about 76,000 rows. I converted the
> > > > xls files provided into text files before the import.
> > > >
> > > > However, I keep getting this error when trying to import:
> > > >
> > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > conversion for column "CBSAName" 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)
> > > >
> > > > I looked at the text file and noticed that strings are delmited by
> > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > However the error persists.
> > > >
> > > > I'm not sure why it's complaining about the field, as it is just a
> > > > simple text field delimited by single quotes.
> > > >
> > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > >
> > > > Thanks!
> > > >
> > > >
> >
> >|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
> > Hi
> >
> > I assume you are doing this manually and not wanting to automate it?
> >
> > John
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > I know you can import xls directly however if you've ever had your
> > > "text" converted to float in the process sometimes it's just easier to
> > > go with the txt format so you don't have to map fields
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > It looks like you text has some data that can not be imported within the
> > > > text field. You may want to look how far the import got and then look at the
> > > > data on the row that failed.
> > > >
> > > > You can import directly from the excel file without converting the data.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "tootsuite@.gmail.com" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > > file into SQL Server 2005. This file is a commercial marketing
> > > > > information database that contains about 76,000 rows. I converted the
> > > > > xls files provided into text files before the import.
> > > > >
> > > > > However, I keep getting this error when trying to import:
> > > > >
> > > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > > conversion for column "CBSAName" 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)
> > > > >
> > > > > I looked at the text file and noticed that strings are delmited by
> > > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > > However the error persists.
> > > > >
> > > > > I'm not sure why it's complaining about the field, as it is just a
> > > > > simple text field delimited by single quotes.
> > > > >
> > > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > > >
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
>
Import fails on text field - help
Hi,
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work?
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "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: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)
Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work?
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "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: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work?
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "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: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)
Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work?
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "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: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>
Import fails on text field - help
Hi,
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work'
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "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: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work'
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "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: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>sql
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work'
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "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: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work'
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "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: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>sql
Import fails on text field - help
Hi,
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work'
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "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: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work'
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "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: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work'
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "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: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work'
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "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: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. 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.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>
Friday, February 24, 2012
Import a COM+ application That depends on SQLNCLI on 32 bit XP Fails.
We have a 32 bit COM+ application that depends on SQLNCLI.DLL for BCP functionality. We are upgrading to the SQL Server Native client and moving away from ODBCBCP.dll.
Anyone running 32 bit XP attempting to import the COM+ application gets the following message: The DLL could not be loaded. Check to make sure all required application runtime files and other dependent DLLs are available in the component DLL's directory or the system path.
If I revert the DLL back to using ODBCBCP.dll the application will import and run fine. Here is the interesting part. If I import the application with the ODBCBCP.dll then rebuild the DLL so it links against SQLNCLI.DLL the app will run fine. It has no problem finding and loading SQLNCLI.DLL along with all of it's dependencies.
Anyone running 64 bit XP can import the COM+ application when the DLL is linked against SQLNCLI.DLL.
Does anyone have any insight into this issue. It is causing our developers alot of headache. If sample code is needed I will be glad to provide it, but only at request.
Thanks for your time,
Mike
To use the bcp APIs you have to link with a .lib that has the bcp API entry points because they are not accessed through the driver manager like mainstream ODBC APIs. With the MDAC 'SQL Server' driver you have to link with odbcbcp.lib and the bcp APIs are in a seperate odbcbcp.dll. With 'SQL Native Client' you have to link with sqlncli.lib - for SQL Native Client we put everything into a single dll - APIs, bcp and netlibs. If an app is linked with odbcbcp.lib but loads sqlsrv32.dll (or links with sqlncli.lib and loads sqlsrv32.dll) then you get an error when you load the app.
This is covered in Books Online in the topic which deals with upgrading applicatsion from MDAC to SQL Native Client(http://msdn2.microsoft.com/en-us/library/ms131035.aspx), which says:
"SQL Native client is not compatible with odbcbcp.dll. Applications which use both ODBC and bcp APIs must be rebuilt to link with sqlncli.lib in order to use SQL Native Client."
Applications which use only the 'standard' ODBC calls only link with the driver manager (odbc32.dll) so don't have a dependency on the driver dll (or bcp dll).
|||I was aware of that and made sure that our entire app only linked with sqlncli.lib. I changed that DLL to be delay loaded and our problem went away and the app runs fine.
Subscribe to:
Posts (Atom)