CODEWITHSUNDEEP
sqlms-access
Shariful
Shariful

Reputation: 41

Import data from Access into SQL server

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.

Upvotes: 1

Views: 6479

Answers (5)

Joost Hectors
Joost Hectors

Reputation: 21

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

Upvotes: 0

Aaron Kempf
Aaron Kempf

Reputation: 588

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.

Upvotes: 1

David-W-Fenton
David-W-Fenton

Reputation: 23067

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.

Upvotes: 0

Aaron Kempf
Aaron Kempf

Reputation: 588

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-

Upvotes: 2

DiningPhilanderer
DiningPhilanderer

Reputation: 2767

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

Upvotes: 0

Related Questions