Import data from Access into SQL server

Question

I am trying to copy a Microsoft Access table called Forms to SQL server 2005 database table called Forms, using the Tasks >> Import Data facility. However, I am getting the following error message:

Error 0xc0208265: Data Flow Task 1: Failed to retrieve long data for column "formNotes". (SQL Server Import and Export Wizard)

The ‘formNotes’ column data type is ‘Memo’ in Access and I tried different type format (Text / ntext / varchar(max) / nvarchar(max)) for the server side formNotes column. Unfortunately I am still getting the same error message and so couldn’t import data. Any help will be highly appreciated. Thanks in advance.

Aaron Kempf · Accepted Answer · 2011-03-17 00:57:06Z

2

I right click import into an Access Data Project. Honestly, it's simpler for most situations.

SSIS is an INCREDIBLY complex beast, and that's what you're asking us to do-

answered Mar 17, 2011 at 0:57

Aaron Kempf

5882 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David-W-Fenton Over a year ago

Right click on what? The question has the data table in Access/Jet/ACE and the questioner wants it in SQL Server. So far as I can tell, this has nothing to do with an ADP, unless after importing into SQL Server the questioner wants to start from scratch building a new Access application.

Aaron Kempf Over a year ago

in Access Data Project, you can right-click LINK to any datasource... Access Database-- just like you can with jet

David-W-Fenton Over a year ago

But how do you get to an ADP? It's not relevant to the question.

Patrick Honorez Over a year ago

+1for the ADP idea. I only recently switched to ADPs for simple jobs, and it really saves time. (e.g. copy/paste a table)

Aaron Kempf · Accepted Answer · 2011-03-17 18:36:35Z

1

I've moved a terabyte of attachments into Varbinary(max) fields in the past week using linked servers.. it works like a charm, I sometimes need to do it Row-by-row for throughput reasons (stage the keys, and then write one row at a time.. usually with ~10 threads - Vb console apps).

I reccomend just building a linked server in SQL Server that points to the Access Database. Linked Servers in SQL Server can do almost anything that linked tables can do in Access.

I call remote sprocs using SQL Server Express edition against other SQL Server databases without any limitations.

Linked Servers are awesome dude, they quickly get complex from a security standpoint.. but if you know enough about Active Directory to do 'trust for delegation' and SetSpn then you can do some pretty impressive double-hop scenarios using linked server.

answered Mar 17, 2011 at 18:36

Aaron Kempf

5882 silver badges11 bronze badges

4 Comments

David-W-Fenton Over a year ago

It's good to hear that an Access linked server in SQL Server works so well. But it seems like an awful lot of working setting one up for a one-time import, which seems to me to be what's involved here. Is it relatively simple to do?

Aaron Kempf Over a year ago

adding a linked server in SQL Server is SIMPLE. Just because you don't know how to do it, doesn't mean that this is the wrong answer.

David-W-Fenton Over a year ago

Aaron, where does it say I think this is a wrong answer? Most of my SQL Server work has been with 2000, and in that version, linked servers are hidden I forget where (did use it a long time ago). It's not something I use often (not having any need for it), so I don't claim expertise I don't have. But you really do need to read more carefully -- my comment was supportive of your answer.

Aaron Kempf Over a year ago

Create a new ADP, right click LINK.. it's quite straight forward to setup.

DiningPhilanderer · Accepted Answer · 2011-03-16 17:10:25Z

0

I just ported some data from Access to SQLServer.

Use DatabaseTools --> SQL Server.

It will ask you all the pertinent questions about authentication and which tables you wish to move over. It will also ask you if you want to put the data into an existing database or create a new one...

answered Mar 16, 2011 at 17:10

DiningPhilanderer

2,7673 gold badges25 silver badges29 bronze badges

Comments

David-W-Fenton · Accepted Answer · 2011-03-17 03:29:30Z

I'm not sure what the best solution is for your situation, as it's not something I ever do (export an Access table to SQL Server). If I'm upsizing to SQL Server, I'm doing the whole database, and for that I use the SQL Server Migration Assistant for Access, which is quite full-featured and does a darned good job of upsizing (much better than the Access upsizing wizards). There are some potential gotchas, but the SSMA allows to preview the conversion and make changes to eliminate problems.

Something you might try is to create an ODBC DSN for your target SQL Server database, and then within Access, you can use FILE | EXPORT to export a table to the DSN. It will choose the data types for you, according to what it thinks are the best.

But the best approach entirely depends on what you're doing all of this for and whether it's a one-time thing or something that needs to be scripted. I just thought I'd throw these observations into the mix.

Joost Hectors · Accepted Answer · 2013-12-20 14:38:39Z

0

(It's an old question, but no answer was accepted...)

I had the same error when importing database into a MSSQL2012 database. It was an Access database in Access 2003 file format. Saving the database in Access 2007 format and then import data into SQL Server fixed the problem for me.

answered Dec 20, 2013 at 14:38

Joost Hectors

213 bronze badges

Collectives™ on Stack Overflow

Import data from Access into SQL server

5 Answers 5

4 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related