Importing Access DB: error

Showing posts with label error. Show all posts

Wednesday, March 28, 2012

Importing unicode data from Oracle Database

Hi everybody,

i try to import data from an oracle database configured in unicode.
When i run the package i have an error message

Error at Data Flow Task [Source - ACCOUNT]: Column "ACCOUNT_CODE" cannot convert between unicode and non-unicode string data types.

how can i resolve this?

regards.Open up your package and set the data type of ACCOUNT_CODE field to be DT_WSTR.

-Jamie|||Thanks for your help.

I have change data type in advanced editor but now i have this error
the output column "ACCOUNT_CODE" on the error output has properties that do not match the properties of its corresponding data source column.
The data appears as string and i can not change the error output data type.

how can i resolve this ?

Thank you.|||Any luck here? I'm having the same problem. Unicode coming in, bound for a text file so I change the output column data type to string. Trouble is, the error output column data type remains Unicode text stream, and I can't change it.|||have you tried using the data conversion transformation component?|||

Indeed, using a Data Conversion component is the way to go. We do not perform implicit conversions.

We have had a number of requests for implicit conversions and we're certainly looking at those. However, it is important to bear in mind that the more SSIS does implicitly, the fewer opportunities you have to capture and respond to errors at a fine grain of detail. Of course, there will be some business cases where such fine-grained error handling is less important than others - Unicode Ansi conversion may well be one of them.

Donald

|||

Importing unicode from Oracle 10g to SQL2005. Source column is defined as VARCHAR2, destination as NVARCHAR.

I have tried a standard OLEDB input/output using the MS Oracle driver, and also with a data conversion component specifying the input and output columns as DT_WSTR. The data-flow runs with no errors, yet the data loaded into SQL is incorrect.

In Oracle, using the DUMP function to see the Unicode value of the column - value is 151. Inspecting the column in SQL Server after the import using the UNICODE function yields a value of 191.

Any other suggestions as to what I might try? Thanks

|||

Could you check what Locale ID and code pages are set on your source connecting to Oracle?

Thanks.

|||

LocaleID is English (United States)

Codepage is 1252.

Using SQL2005 SP2.

thanks

|||

OK. And do you know what code page your string in the Oracle database is supposed to be associated with? If it is not 1252 (ANSI - Latin I) you can expect conversion errors.

Thanks.

|||

The Oracle CharacterSet is WE8ISO8859P1.

I strongly suspect the issue is the MS Oracle OLEDB driver. The source columns can contain characters equivalent to the decimal values 150,151,152,153 - which are extended ASCII.

My workaround is to translate these characters to standard ASCII characters within the source query, then translate back after the SQL insert is completed.

Thanks for your assistance.

|||Hi Marc,

How do you translate extended ASCII characters to standard ASCII characters within the source query?

This data importation is given me a lot of headeaches !!! :-((

Your help will be much appreciated.

Thank you,

Nicole
|||Has anyone successfully gotten this to work? We started running into this issue last week when we started testing going from 9.2.0.8 to using 10.2.0.3 migrating to SQL server 2005.

Here is my character set for 9i:

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8iSO8859P1

10g

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF

Are these the settings everyone else is using? 10g defaults to the newer version.

Thanks for any help.

Importing unicode data from Oracle Database

Hi everybody,

i try to import data from an oracle database configured in unicode.
When i run the package i have an error message

Error at Data Flow Task [Source - ACCOUNT]: Column "ACCOUNT_CODE" cannot convert between unicode and non-unicode string data types.

how can i resolve this?

regards.Open up your package and set the data type of ACCOUNT_CODE field to be DT_WSTR.

-Jamie|||Thanks for your help.

how can i resolve this ?

Indeed, using a Data Conversion component is the way to go. We do not perform implicit conversions.

Donald

|||

Importing unicode from Oracle 10g to SQL2005. Source column is defined as VARCHAR2, destination as NVARCHAR.

In Oracle, using the DUMP function to see the Unicode value of the column - value is 151. Inspecting the column in SQL Server after the import using the UNICODE function yields a value of 191.

Any other suggestions as to what I might try? Thanks

|||

Could you check what Locale ID and code pages are set on your source connecting to Oracle?

Thanks.

|||

LocaleID is English (United States)

Codepage is 1252.

Using SQL2005 SP2.

thanks

|||

OK. And do you know what code page your string in the Oracle database is supposed to be associated with? If it is not 1252 (ANSI - Latin I) you can expect conversion errors.

Thanks.

|||

The Oracle CharacterSet is WE8ISO8859P1.

I strongly suspect the issue is the MS Oracle OLEDB driver. The source columns can contain characters equivalent to the decimal values 150,151,152,153 - which are extended ASCII.

My workaround is to translate these characters to standard ASCII characters within the source query, then translate back after the SQL insert is completed.

Thanks for your assistance.

|||Hi Marc,

How do you translate extended ASCII characters to standard ASCII characters within the source query?

This data importation is given me a lot of headeaches !!! :-((

Your help will be much appreciated.

Thank you,

Nicole
|||Has anyone successfully gotten this to work? We started running into this issue last week when we started testing going from 9.2.0.8 to using 10.2.0.3 migrating to SQL server 2005.

Here is my character set for 9i:

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8iSO8859P1

10g

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF

Are these the settings everyone else is using? 10g defaults to the newer version.

Thanks for any help.

Importing unicode data from Oracle Database

Hi everybody,

i try to import data from an oracle database configured in unicode.
When i run the package i have an error message

Error at Data Flow Task [Source - ACCOUNT]: Column "ACCOUNT_CODE" cannot convert between unicode and non-unicode string data types.

how can i resolve this?

regards.Open up your package and set the data type of ACCOUNT_CODE field to be DT_WSTR.

-Jamie|||Thanks for your help.

how can i resolve this ?

Indeed, using a Data Conversion component is the way to go. We do not perform implicit conversions.

Donald

|||

Importing unicode from Oracle 10g to SQL2005. Source column is defined as VARCHAR2, destination as NVARCHAR.

In Oracle, using the DUMP function to see the Unicode value of the column - value is 151. Inspecting the column in SQL Server after the import using the UNICODE function yields a value of 191.

Any other suggestions as to what I might try? Thanks

|||

Could you check what Locale ID and code pages are set on your source connecting to Oracle?

Thanks.

|||

LocaleID is English (United States)

Codepage is 1252.

Using SQL2005 SP2.

thanks

|||

OK. And do you know what code page your string in the Oracle database is supposed to be associated with? If it is not 1252 (ANSI - Latin I) you can expect conversion errors.

Thanks.

|||

The Oracle CharacterSet is WE8ISO8859P1.

I strongly suspect the issue is the MS Oracle OLEDB driver. The source columns can contain characters equivalent to the decimal values 150,151,152,153 - which are extended ASCII.

My workaround is to translate these characters to standard ASCII characters within the source query, then translate back after the SQL insert is completed.

Thanks for your assistance.

|||Hi Marc,

How do you translate extended ASCII characters to standard ASCII characters within the source query?

This data importation is given me a lot of headeaches !!! :-((

Your help will be much appreciated.

Thank you,

Nicole
|||Has anyone successfully gotten this to work? We started running into this issue last week when we started testing going from 9.2.0.8 to using 10.2.0.3 migrating to SQL server 2005.

Here is my character set for 9i:

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8iSO8859P1

10g

NLS_CHARACTER_SET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF

Are these the settings everyone else is using? 10g defaults to the newer version.

Thanks for any help.
sql

Importing text file fails

I try to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message

Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

The column 19 which reported a problem contains this information:
?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067

However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file.

The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing.

Please help someone!

BjrnWhat datatype you have on column 19 destination table?|||I use string [DT_STR] with a column width of 500

/Bjrn|||

Quote:

Originally Posted by gulllet

I use string [DT_STR] with a column width of 500

/Bjrn

...And I'm using the locale Swedish. Saving in Notepad or Excel is done in ANSI.

/Bjrn

Monday, March 26, 2012

Importing Tables from MS Access

I m trying to import Tables from MS Access and I m having this error

- Copying to [Manpower].[dbo].[MAINTABLE] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "PPExpiry" (157) on input "Destination Input" (113). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (113)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (113)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - MAINTABLE" (100) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

any hekp on this

Regards

How you're importing the tables from MS Access, is it DTS or import/export tool, any other tool?

Also check what kind of data or schema exists on the specified tables.

|||

I was import/export tool. I solved the problem. The Proble was with data type. There were some wrong entries in dates. after deleting those i was able to import the database.

Thanx

|||

kinda intrested how did u solve problem. i am trying to import access tables from harddrive into sql server which is my backend database.

i used

docmd.transferdatabse acimport,"microsoft Access",dbname,actable,Me.listtable,me.listtable

my tables are listed on listbox, call listtable.

i get error so i do not know what to do.

sql

Importing Tables from MS Access

I m trying to import Tables from MS Access and I m having this error

any hekp on this

Regards

How you're importing the tables from MS Access, is it DTS or import/export tool, any other tool?

Also check what kind of data or schema exists on the specified tables.

|||

I was import/export tool. I solved the problem. The Proble was with data type. There were some wrong entries in dates. after deleting those i was able to import the database.

Thanx

|||

kinda intrested how did u solve problem. i am trying to import access tables from harddrive into sql server which is my backend database.

i used

docmd.transferdatabse acimport,"microsoft Access",dbname,actable,Me.listtable,me.listtable

my tables are listed on listbox, call listtable.

i get error so i do not know what to do.

Wednesday, March 21, 2012

importing from access error

Let me describe my situation

I am running SQL Server 2005. I created all the tables/columns/keys I needed. I export the data to Access.

In Access, I add all the data for the tables. Then I use SSIS to import-append the data back in to SQL Server 2005.

I get this error:
-
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - tbl_name" (19) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
--

I can use the import tool (SSIS) to create new tables, but not append the data. Creating new tables makes all the tables to lose information about field lengths etc, so this is not an option

any help would be greatly appreciated.

Thanks!

Could you verify the metadata of your existing tables matches the tables you want to import. The error message hints a problem in that area.

Thanks.

|||yea..turns out the metadata was in error...but the access DB's metadata is the result of an export of the SQL Server DB.

Why doesn't Microsoft let you import what you export? just seems to make sense to me.
|||

It is because the data type spectrum is much richer for SQL Server than for Access. By transferring data from SQL Server to Access you are basically downgrading richness of your data and it makes the reverse process difficult without the user intervention.

Even for equally rich data type spectrums of heterogeneous data sources you will end up having to map more than one type to the single destination type. When going in the opposite direction you will have to pick one of possible types as a best match, and the best match will not always be the proper one.

HTH.

Monday, March 19, 2012

Importing Errors when using BCP

Alright, I've been getting an interesting error when using BCP between two databases on the same MSSQL Server 2000. I am bulk copying data from an older version of a database into a newer one, which includes new tables and columns. When bcp gets to a certain table, I get this error

SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

If anyone would happen to know exactly what SQL is executed by BCP or where to find such information, that would be great. None of the columns' data types have been changes in the newer version of the database.
-MikeYou migt try running profiler while using BCP but I don't think you will find much. The error seems to indicate a problem converting data.

Could you post the table structures, BCP command(s) and any BCP format files?|||DTS might give you more of a clue to where the actual error is.

I had a problem like this once that turned out to be typos in date fields that were before 1/1/1753 or whatever the magic "start date" in SQL server is (like '12/31/0999' instead of '12/31/1999').|||You should get a nice picture of the problem with the -o(filename) switch on BCP. The output file will just contain the rows that are causing the errors. I had a similar problem with "Right string truncation" errors in BCP, and the -o spotlighted the problem immediately.

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Error

Hi all,

I am facing problem on importing csv data file into table,

For Ex:

csv file which has 2 columns & 4 records are as follows

EmpNo EmpName

1 a
2 b
3 c
1 a

I created new table called T4, structure of T4 is

EmpNo Varchar(50) with Primary Key
EmpName Varchar(50) disabled Allow Nulls

Now I started importing csv data to T4 table, during importing it couldn't finish entire process. Throwing error, when I remove primary key with disable Allow Nulls for both constraints of a table T4. Importing data successfull.
My question is if I have above said structure for the table T4 (with primary Key), during importing instead of throwing error let it be import first 3 rows then throw error as primary key enabled could not able to import. Is this can be possibe, if so please suggest me to solve the problem.Can I use Bulk Insert or SQL Scripts to solve this problem.

Thanks in advance
Karna

I believe that I understand your current situation, but I do not think I understand what it is you are trying to do.

If you're trying to insert the three "good rows" and "fail" the one "bad" row without failing the package as a whole, you may be able to accomplish this by adding an error output to the destination to which you're writing these records. Then the "bad" records will be redirected to that portion of the data flow, where you can count them, log them, or ignore them if you want.

Is this what you're trying to accomplish? If so, please let us know if this technique gives you the solution you need. If not, please see if you can rephrase your goal, and I can try again.

|||Hi Mathew,

Thanks for your reply. I am executing wizard in SQL Server 2005. Actual problem is "I am preparing a job which can import CSV data file to the table. Table already has primary key & disabled allow null option. Here in CSV file it has duplicate records & empty rows. During importing of job is not successfully done, it is throwing error." My question is if the duplicate data lies in 1000th record, let job be success for until 999 record. Then let it can show error for 1000 record & logged .
Please suggest me to how to succeed the job.

Thanks in advance
Karna|||

I'm honestly not certain how I would go about doing that. For most situations where I'm using SSIS, the behavior you're trying to achieve would be inappropriate for my needs (I would either need the entire load to fail with no rows written to the destination, or else have error rows redirected to a log file or table) so I've never tried to set things up this way. I'm also pretty unfamiliar with the Input/Output Wizard, so that's two strikes against me.

With that said, the first thing I would try would be to update the destination component in the package (i do not know if this is exposed through the wizard - i assume it is not) to turn off fast load. This will slow things down quite a bit as each row will be inserted into the destination database one by one, but it might give you what you need.

Perhaps someone else has more experience with this problem than I...

|||Hi Mathew,

Actually I have to run a job for which it has to import csv data file to table. CSV has duplicate records. My job should not import duplicate records, it should append only actual records. If I set primary key & disabled allow null option. After this when I run the job, job is not even appending actual records (which is not duplicated), job is throwing error which is not importing any data from csv file.
Please suggest me to solve this issue.

Thanks in advance
Karna|||Hi Mathew,

Thanks in advance
Karna|||

Karna,

I think you best option use the error output in the destination component to redirect the errors. If you don't change the default value of the error configuration; then the package will fail as soon as the 1st duplicate hit the destination table. This is nothing diffrent from what Matthew has already suggested.

|||Hi,

Thanks for reply. My problem is I need to import CSV data file to one of the table in the database. During importing it shouldn't import null rows & same rows to the table. The method which should checks the data in the table before importing, if data exists it shouldn't import any data else it should. Please suggest me to solve this problem.

Thanks in advance
Karna|||

See this link for a number of different ways to see if a row exists:

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

Friday, March 9, 2012

Importing Data Into Sql Server Database

I am experiencing intermittent connection problems with the Northwind database and sql server express. The most frequent error message indicates that I am already logged in and will not allow another user.

The odd thing is that even when I am locked out I can access a Northwind database from another connection on Server Explorer.

I ran code to demonstrate simple binding for two text boxes, no problem. I added a project to show complex binding using the datagrid view tool, but could not get past selecting the database.

I tried the same simple binding project on another computer running a beta version of VS 2005 Pro and could not connect to Northwind on that computer also. (Two different Northwind databases, different computers)

A couple days ago I restored the database in Solutions Manager and that helped for a while, but I should not have to resort to that, I know.

How do I remove the user lock? I tried Solutions Manager Express with the first computer but could not access the database in the management module.

The exact text of the error would help. It sounds like you might be trying to access the data through both a User Instance and through the parent instance. This doesn't work since only one copy of SQL can access a database at a time.

Mike

|||

Mike-

I learned that whenever I had a problem with connecting to a database, it was because I was using the dataset from the prior project in the solution, in a new project.

Thanks for replying.

Ruel

P.S.

I don't know if I should start a new thread, but how do I pull data from an Excel spreadsheet into a sql server database table?

|||

Hi,

i would suggest to refer

http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

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

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

HTH

Hemantgiri S. Goswami

|||

Thanks Hemantgiri -

The links were helpful for understanding the process of pulling data files into sql server. However, I am still running into a roadblock. For instance, the following:

===================================================================

bulk insert

AAS Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

===================================================================

Where:

AAS = Database Name

Accounts = Table Name

Gives the following error message:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'Accounts'.

|||

Hi,

run it like,

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

bulk insert

AAS.ObjectOwner.Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

Hemantgiri S. Goswami

|||

Tried the following as you suggested

bulk insert AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

With the resulting error message:

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 1 (AccountNo).

Column 1 of table Accounts is nchar(15) - I raised it from 10 to 15 to see if that would take care of the truncation issue.

The format for column 1 of the xls file is general.

I have had a similar experience with an xml file.

I also tried the bcp utility to format a file for input but I get a message telling me that either the server does not exist or I do not have access.

Thanks for your assistance

Ruel

|||

Could not get to the server with the bcp utility so I tried bulk insert. I found that I had to use "firstrow=2" to get past error messages.

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

with

(

firstrow=2

)

However, the above returns the following message in SSMSE:

(0 row(s) affected)

|||

Hi,

Could ensure the length of the all field!!!!

Hemantgiri S. Goswami

Importing data from Oracle9i CLOB column to MS SQL Server text column

Hi everyone,

I encountered an error "Need to run the object to perform this operation

Code execution exception: EXCEPTION_ACCESS_VIOLATION

" When I try to import data from Oracle to MS SQL Server with Enterprise
Manager (version 8.0) using DTS Import/Export Wizard. There are 508 rows
in Oracle table and I did get first 42 rows imported to SQL Server.
Anyone knows what does the above error message mean and what causes the
rest of the row failed importing?

Thanks very much in advance!

Rene Z.

--
Posted via http://dbforums.com"yzhao12" <member46605@.dbforums.com> wrote in message
news:3547161.1067635623@.dbforums.com...
> Hi everyone,
>
> I encountered an error "Need to run the object to perform this operation
> Code execution exception: EXCEPTION_ACCESS_VIOLATION
> " When I try to import data from Oracle to MS SQL Server with Enterprise
> Manager (version 8.0) using DTS Import/Export Wizard. There are 508 rows
> in Oracle table and I did get first 42 rows imported to SQL Server.
> Anyone knows what does the above error message mean and what causes the
> rest of the row failed importing?
>
> Thanks very much in advance!
>
> Rene Z.
>
> --
> Posted via http://dbforums.com

This error is described here:

http://support.microsoft.com/?kbid=271889

However, since the KB article refers to running a package from VB, but
you're using the Import/Export wizard, I'm not sure if this will help. You
could try saving the package from the wizard, instead of executing it, then
using this workaround:

http://www.databasejournal.com/feat...e.php/1461391#5

If that doesn't help, then I suggest you post to
microsoft.public.sqlserver.dts - you may get a better answer there.

Simon

Sunday, February 19, 2012

Importing Access table

hi guys,
i have a problem here. i want to import a table from microsoft access, but i encountered an error. it says something like cannot convert into sqlserver format. I notice that the column in the access table that causes the error is actually an auto-number column with long integer. What i have to do to be able to import the table in?
thanxAssuming you want it to continue to be an autonumber/identity it has to be a a numeric field of some sort (so int, big int, numeric, not sure what else is allowed to be an identity).

You are most likely having a problem because the column is set to int and the number in your access db is more then a standard sql int. try changing it to big int and see what happens.|||u mean big int in access or sqlserver? i checked just now, access in the size field only has long integer or replication ID. What if i change it to replication ID? i'm new to sqlserver, i just know access.please help.
thanx|||Are you upsizing or importing ?|||importing...from an access table. coz i am migrating to sqlserver from access, my application can run using access.|||big int in sql server.|||ok then i'll try it later, thanx guys...|||Long integer in access is the same as int in sql. Both are 4 bytes. What are the other field definitions in that table ? Also, what is the definition of the sql server table ?|||you sure on that?? I understood that that used to be the case but it changed with sql server 2k.|||i'm not sure about the bytes of the data type, but in my table there are text data type in my access table. and after convert they will change to nvarchar in sqlserver. i know this because i successfully converted a different table.|||yeah i did not declare anything for the sqlserver table because i use the import wizard and just find the path of my database. then it run into this autonumber problem.|||okie, just had a look through the doco and an int (in sql) should be fine for a long (in access).|||Can you post the mdb with the same schema and a few records ?|||sorry, i can only post it on fri as it's christmas, so i'll let u guys know later ok!
thanx guys and have a merry christmas!

Importing Access 2002 into Reporting Services

Getting the following error when trying to import an Access 2002 database into Microsoft Reporting Services:
--The given path's format is not supported--
Please advise if you know what this is referring to.R U Using URL or local path ?|||Originally posted by Ben Mansouri
R U Using URL or local path ?

local path, should I use URL?

Wednesday, March 28, 2012

Importing unicode data from Oracle Database

Importing unicode data from Oracle Database

Importing unicode data from Oracle Database

Importing text file fails

Monday, March 26, 2012

Importing Tables from MS Access

Importing Tables from MS Access

Wednesday, March 21, 2012

importing from access error

Monday, March 19, 2012

Importing Errors when using BCP

Importing Error

Importing Error

Importing Error

Friday, March 9, 2012

Importing Data Into Sql Server Database

Importing data from Oracle9i CLOB column to MS SQL Server text column

Sunday, February 19, 2012

Importing Access table

Importing Access 2002 into Reporting Services

Importing Access DB

Blog Archive

About Me