Import problem with varchar(max) field

I'm trying to import some Assessor data from a text file into a table and the field for Legal Description (column 2 in the source text file) in the table is of data type varchar(max) because some of the data goes over the 8K size. I get an error on the first row of importing that refers to column 2 (see 'Initial errors' below). I read the related post and changed the size of input column 2 to 8000 and got this error. Finally I set the size of the of input column 2 to 4000 and it ran. So I'm thinking there is a limit on the size of varchar data that can be imported. Just want to clarify what that limit is and how I might go about importing this data.

Thanks, John

Error with input column 2 set to size of 8000:

Setting Destination Connection (Error)

Messages

Error 0xc0204016: DTS.Pipeline: The "output column "Column 2" (388)" has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)

Initial errors:

Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" 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: The "output column "Column 2" (18)" failed because truncation occurred, and the truncation row disposition on "output column "Column 2" (18)" 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: An error occurred while processing file "\\Scux00\assrdumps\SQLServerDB\exportsql.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - exportsql_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.
(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)

There is a limit on the size of unicode character strings at 4000. Non-unicode character strings have a limit of 8000. Ensure that you don't have type conversions between varchar and nvarchar.|||

I'm trying the SQL Server Import and Export Wizard. I've set the DataType to most of the fields as 'string[DT_STR]' and the Unicode box is unchecked, but when I look at column mappings after clicking 'Edit Mappings', all the columns say they're type nvarchar on the screen and it doesn't appear to be something I can change. If I highlight a column on that screen it shows the setting that I set in the Advanced screen for the text file, ie .. Column 2 string[DT_STR](4000). Since I'm getting the limit of 4k it must be because of this. I'm not sure how to modify it. I'll save the package as a file and open it up in BID Studio ... john

Import only yhe changed data

I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table.
THX
InonIt would be bit difficult to import without a time field, which is key to compile the data.

If your task is about changed data then why not consider replication.|||Sure, but you need a staging environment...

1. bcp (I can't abvide DTS unless the data is in Excel or Access, even then...) in to a stage table
2. Write 3 sql statements to compare new data with old...determine, based on keys, which data was added, which data was deleted, and which data was updated..

3. Then INSERT, DELETE and UPDATE those sets of data...

Got a link somewhere...

Hold on...|||Here's the code:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28305|||You can use the BINARY_CHECKSUM function to determine whether data was changed.sql

Import of Data from Access DB

Hello,

I'm trying to import some data from a table in an Access Database into my SQL Server.

It's one field I'm having a problem with and I don't know how to get round it.

Basically the field in Access is called "DueToStart" and is a "Date/Time" type field.
It shows values as "09:00:00" etc.

When I try to import this table into SQL, I get the following message, and I don't know how to get round it.

"Error as destination for row number 1. Errors encountered so far in this task: 1.
Insert Error, column 3('DueToStart', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification."

Does the access column contain only time?

|||Yes is does|||Is there a target table in the SQL Server or is it created automatically when you import the table? The Import Export Wizard in SQL Server automatically creates a table when the table does not exist when importing. Either the table destination exists or is created by the import wizard, check that the definition of the column in the SQL Server is datatime and not smalldatetime.|||

It seems that the column contains only time information and no date imformation. As SQL Server knows only combined data types (e. g. DT_DBTIMESTAMP), I wonder what date it should insert. Maybe 1/1/1753 but maybe that is the problem.

What about a viewer for the output of the data source?

|||Alas_gr - The table already exists in the new SQL DB. The format is smalldatetime and this is what it needs to be.

Anonymous - What do you mean by "viewer for the output data source" ? Sorry but I'm brand new to this and trying to find my way blindly !
|||

I think the problem occurs because the value in your Access table that is either too small or too large for the SQL data type. So try to switch it to datetime, and do the import, and see if it works.

As for the viewer, it can be useful only if you are using Visual Studio BI to design a package. If so, in the data flow designer, if you right click on the connector between the source (access) and the destination (sql server) there is a choice (Data Viewers). So next time you run the package you can see what data you are importing.

|||

This thread deals with an similar issue. In short the date time column from access had to be placed in a SSIS string variable and then casted to date/time.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2071251&SiteID=1

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

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.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:
>

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:
>

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!
> > > > >
> > > > >
> > >
> > >
>

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)
>

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

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)
>

import excel file into dropdownlist then export to sql server 2005

i am handling a project where user can choose the excel file and the field in the excel file to export into sql server 2005. which mean there will be dropdownlist where the user can choose the field and so on. anyone know how to do this?I'm a bit uncertain how this question is Asp.Net related. Please clarify how it is.|||which mean i will need to import the excel file into a dataset and then bind the dataset to the drop down list and at last import it to the sql server 2005. it can be in vb.net or asp.net

Import Data problem

I need to import data from VFP into SQL ,
Now, I found many many fields's length is different.
I need to compare one field by one field length, otherwise AS I process
Insert A ... select ... from B.
There is always error about the Truncated error .
however, I don't know which field is wrong, Any tools or any method can let
me know which field is wrong ?
thanks
Unfortunately, you cannot find out the exact column that is producing the
error message, as the message doesn't include the column name. I'm afraid
you'll have to do a manual compare:
http://vyaskn.tripod.com/programming_faq.htm#q1
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OdBQXbDNFHA.2136@.TK2MSFTNGP14.phx.gbl...
I need to import data from VFP into SQL ,
Now, I found many many fields's length is different.
I need to compare one field by one field length, otherwise AS I process
Insert A ... select ... from B.
There is always error about the Truncated error .
however, I don't know which field is wrong, Any tools or any method can let
me know which field is wrong ?
thanks

Import Data from Text file

I am importing data from a text file. The file is simple, only 9 fields.
Each field has quotation marks around the data. During the import it
creates a table with varchar fields and length of 8000!!
The only problem with this is that I merge this data and other inforation to
a permenant table with normal field sizes. With this I start getting errors
that the data will be truncated.
Second problem. The code below:
--
Declare @.TableName nvarchar(10)
Declare @.SQLCmd nvarchar(1000)
set @.TableName = 'RS201'
Set @.SQLCmd = '
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select '+ Char(39)+ @.TableName + char(39) + ' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(PatronID,Char(34),Null),
replace(Printer,Char(34),Null),
replace(DocumentType,Char(34),Null)
From ' + @.TableName
print @.Sqlcmd
--
The code produces:
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select 'RS201' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(PatronID,Char(34),Null),
replace(Printer,Char(34),Null),
replace(DocumentType,Char(34),Null)
From RS201
This works with the Print statement.
However when used with the Execute statement, I get:
---
Server: Msg 203, Level 16, State 2, Line 31
The name '
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select 'RS201' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(P...
---
It truncates the SQL string.
Any Ideas!
ArthurI have the solution.
Apparently the contructed SQL command is to long for the execute to handle
with one variable. The on-line books said to break it up into 2 variables
and concatenate the 2 command strings. Thus, Execute (@.Sqlcmd1 + @.SQLCmd2).
This works! So I think that the 8000 byte field size is affecting this.
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:OaWJoJ$lDHA.2424@.TK2MSFTNGP10.phx.gbl...
> I am importing data from a text file. The file is simple, only 9 fields.
> Each field has quotation marks around the data. During the import it
> creates a table with varchar fields and length of 8000!!
> The only problem with this is that I merge this data and other inforation
to
> a permenant table with normal field sizes. With this I start getting
errors
> that the data will be truncated.
> Second problem. The code below:
> --
> Declare @.TableName nvarchar(10)
> Declare @.SQLCmd nvarchar(1000)
> set @.TableName = 'RS201'
> Set @.SQLCmd = '
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select '+ Char(39)+ @.TableName + char(39) + ' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(PatronID,Char(34),Null),
> replace(Printer,Char(34),Null),
> replace(DocumentType,Char(34),Null)
> From ' + @.TableName
> print @.Sqlcmd
> --
> The code produces:
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select 'RS201' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(PatronID,Char(34),Null),
> replace(Printer,Char(34),Null),
> replace(DocumentType,Char(34),Null)
> From RS201
> This works with the Print statement.
> However when used with the Execute statement, I get:
> ---
> Server: Msg 203, Level 16, State 2, Line 31
> The name '
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select 'RS201' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(P...
> ---
> It truncates the SQL string.
>
> Any Ideas!
> Arthur
>

Import Data from Excel problem

I got a table which got over 50 fields from an excel files.
Now I can import them into a temp.dbf file . I found that the field type
defined as nvarchar 255.
I use SP to write a insert these data to another real table. (some field is
char20, . 50. 20...etc)
The problem is As I run the insert statment .I will got [String or binary
data would be truncated] <-- error
I don't want to modify the structure of temp.dbf.
Any method that I can ignore That error and import the data ?
Thanks
you can put 'trims' around the fields in your select
look up ltrim and rtrim in BOL
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:u43g$QvLFHA.732@.TK2MSFTNGP12.phx.gbl...
>I got a table which got over 50 fields from an excel files.
> Now I can import them into a temp.dbf file . I found that the field type
> defined as nvarchar 255.
> I use SP to write a insert these data to another real table. (some field
> is char20, . 50. 20...etc)
> The problem is As I run the insert statment .I will got [String or binary
> data would be truncated] <-- error
> I don't want to modify the structure of temp.dbf.
> Any method that I can ignore That error and import the data ?
> Thanks
>

import data and get unique id

I am trying to import a text file and have sql give it a unique id number but it keeps saying that the id field cannot have null values. What do I need to do to get this to work?So the table you are importing to does it have a column defined with IDENTITY property? What are you using to import DTS, BCP, BULK INSERT?|||I am using DTS and the table does have a identity field.|||Well that is what you will have to do first. Create your table with a column having the property of IDENTITY.

Example:CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

See Books online for more info.|||I did that but I still get an error saying that column cannot contain a null value when I import the data for the other column out of a text file (csv).|||I can offer a different perspective,
You just have to disable the 'enable identity insert' checkbox when you are doing your import.|||That worked perfectly, Thank you so much.

Import CSV file into SQL TABLE

Hi,
i try to import a csv file with tabulation field separator and linefeed as
rowfield.
I've write the following "stored procedure" but it don't import nothing.
My csv file have 41 colums and n rows; the colums is separated by "tab" and
some fields are blank.
Thank you for help!
Gianluca
CREATE PROCEDURE Import
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @.SQL varchar(200)
TRUNCATE TABLE table_csv
SET @.SQL = "BULK INSERT table_csv
FROM 'G:\file_csv.dat'
WITH
( FIELDTERMINATOR = '\t' ,
ROWTERMINATOR = '\n' ,
FIRSTROW = 2
)"
--Step 2: Execute BULK INSERT statement
EXEC (@.SQL)
GOHi,
Try executing the BULK INSERT from Query Analyzer or Query Window (SQL
2005). This will through the exact error.
THanks
Hari
SQL Server MVP
"Gianluca Floris" <gianluca.flo_NOSPAM_ris@.tiscali.it> wrote in message
news:45055ce9$0$998$5fc30a8@.news.tiscali.it...
> Hi,
> i try to import a csv file with tabulation field separator and linefeed as
> rowfield.
> I've write the following "stored procedure" but it don't import nothing.
> My csv file have 41 colums and n rows; the colums is separated by "tab"
> and some fields are blank.
> Thank you for help!
> Gianluca
> --
> CREATE PROCEDURE Import
> AS
> --Step 1: Build Valid BULK INSERT Statement
> DECLARE @.SQL varchar(200)
> TRUNCATE TABLE table_csv
> SET @.SQL = "BULK INSERT table_csv
> FROM 'G:\file_csv.dat'
> WITH
> ( FIELDTERMINATOR = '\t' ,
> ROWTERMINATOR = '\n' ,
> FIRSTROW = 2
> )"
> --Step 2: Execute BULK INSERT statement
> EXEC (@.SQL)
> GO
>
> --
>
>

Import CSV file into SQL TABLE

Hi,
i try to import a csv file with tabulation field separator and linefeed as
rowfield.
I've write the following "stored procedure" but it don't import nothing.
My csv file have 41 colums and n rows; the colums is separated by "tab" and
some fields are blank.
Thank you for help!
Gianluca
--
CREATE PROCEDURE Import
AS
--Step 1: Build Valid BULK INSERT Statement
DECLARE @.SQL varchar(200)
TRUNCATE TABLE table_csv
SET @.SQL = "BULK INSERT table_csv
FROM 'G:\file_csv.dat'
WITH
( FIELDTERMINATOR = '\t' ,
ROWTERMINATOR = '\n' ,
FIRSTROW = 2
)"
--Step 2: Execute BULK INSERT statement
EXEC (@.SQL)
GO
--Hi,
Try executing the BULK INSERT from Query Analyzer or Query Window (SQL
2005). This will through the exact error.
THanks
Hari
SQL Server MVP
"Gianluca Floris" <gianluca.flo_NOSPAM_ris@.tiscali.it> wrote in message
news:45055ce9$0$998$5fc30a8@.news.tiscali.it...
> Hi,
> i try to import a csv file with tabulation field separator and linefeed as
> rowfield.
> I've write the following "stored procedure" but it don't import nothing.
> My csv file have 41 colums and n rows; the colums is separated by "tab"
> and some fields are blank.
> Thank you for help!
> Gianluca
> --
> CREATE PROCEDURE Import
> AS
> --Step 1: Build Valid BULK INSERT Statement
> DECLARE @.SQL varchar(200)
> TRUNCATE TABLE table_csv
> SET @.SQL = "BULK INSERT table_csv
> FROM 'G:\file_csv.dat'
> WITH
> ( FIELDTERMINATOR = '\t' ,
> ROWTERMINATOR = '\n' ,
> FIRSTROW = 2
> )"
> --Step 2: Execute BULK INSERT statement
> EXEC (@.SQL)
> GO
>
> --
>
>

Import Access table to SQL Server

I have an Access 2002 database that I am importing into SQL Server. I have t
rouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message)
. Any ideas.
thans,
StanYou can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:

>I have an Access 2002 database that I am importing into SQL Server. I have
trouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message
). Any ideas.
>thans,
>Stan

Import Access table to SQL Server

I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
thans,
Stan
You can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:

>I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
>thans,
>Stan