Re: [MS_AccessPros] Access Memo Field Became ntext in SQL Server

 

Tim

What did you use to upsize your Access tables? The one that comes with Access is not reliable.  I've found the SQL Server Migration Assistant to be the best tool ( http://www.microsoft.com/en-us/download/details.aspx?id=28763 ). If I remember right it will actually let you select the data type for each column. But most cases that is not necessary. 

When SQL changes a column's data type it has to re-create the table and then insert the original table's converted data into the new table. This is especially difficult once foreign keys are created.

And with SQL Server, you have to change a setting so you can change data types. In SSMS, Tools>Options>Designers>Table and Database Designers>Uncheck Prevent saving changes that require re-creation of table.

Once you uncheck that box you should be able to change the ntext to nvarchar(max). But a warning! Access cannot use the SQL Server Native Client driver for the ODBC connection. If you do use it nvarchar(max) fields will be truncated in all kinds of weird ways. You will have to use the old SQL Server driver. Hopefully, that bug will get fixed eventually.

Regards,

Bill Mosca

---

From: timdbui@gmail.com
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, February 22, 2014 5:47:55 PM
Subject: [MS_AccessPros] Access Memo Field Became ntext in SQL Server

 

Hi, would any of you help me to solve this issue:

I put my back end on SQL Server 2012 and use Access 2010 as front end. I have no problem migrating the backend up to SQL Server. However, when I use Task, Export to send a table back down to Access, I have errors. The message was SQL Server no longer support ntext and it wants me to use nvarchar(Max). 

So when I try to change the fields in SQL server from ntext to nvarchar(max), it would not allow me to save 

To make things easier in the future so I don't have this issue, is there a way to make SQL Server to accept Access memo field as nvarchar(max)?  I notice that SQL server have no problem accepting the Access text field. If my field have 50 characters, then it becomes nvarchar(50) automatically in SQL Server.

Thanks so much in advance for your help!

Tim Bui

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (5)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___