May 24, 2011 at 9:27 am
Yesterday I imported a MS Access 2003 database into SQL Server 2008 Express. I did this from SQL Express, not from Access. There's 2 things, initially, which I don't understand and would like some help in understanding.
First, I noticed that MS Access Memo fields came across as NVARCHAR(MAX), which I guess makes sense. The MS Access database is a duplication of a couple of SQL Server databases (it doesn't have all of the tables, but it has many of them). However, even the MS Access Text fields came across as NVARCHAR(N) fields, and that's what surprised me. In the SQL Server databases, we've used VARCHAR(N) or VARCHAR(MAX), but not NVARCHAR(N) or NVARCHAR(MAX). I'm guessing I could either modify all of the NVARCHAR(*) fields to VARCHAR(*) fields instead. (Basically, I'd like to keep the table schemas between the regular SQL Server databases and the new SQL Express database in sync.) Unless there's something superior to using NVARCHAR(*).
Secondly, in checking the dependencies in my new SQL Express database, after importing everything, I was surprised to see that none of the relationships came across. Is it something I did (or failed to do), or is that the default behavior for importing a MS Access into a SQL Exress 2008 database?
Kindest Regards, Rod Connect with me on LinkedIn.
May 24, 2011 at 9:32 am
No idea on relationships, but nvarchar(max) and varchar(max) for Latin data are the same. Only if you are storing data requiring Unicode does this matter.
May 24, 2011 at 9:36 am
If you really want to transfer everything I'd look into access based upsizing wizards. I don't have any recommandations, but those will be built by and for people who do this 24 / 7 so it should be way more complete.
May 24, 2011 at 9:45 am
Ninja's_RGR'us (5/24/2011)
If you really want to transfer everything I'd look into access based upsizing wizards. I don't have any recommandations, but those will be built by and for people who do this 24 / 7 so it should be way more complete.
I went with the SQL Server Import Wizard, because I read somewhere (can't remember where) that it was better than MS Access's upsizing wizard. But maybe I should check into that.
Kindest Regards, Rod Connect with me on LinkedIn.
May 24, 2011 at 9:54 am
Quite possible, but I was thinking about totally 3rd party apps. I've once read about one and it looked really good (indexes, views, relations) were also included. Can't remember the name or find the link tho.
May 24, 2011 at 9:54 am
Steve Jones - SSC Editor (5/24/2011)
No idea on relationships, but nvarchar(max) and varchar(max) for Latin data are the same. Only if you are storing data requiring Unicode does this matter.
Steve, I just thought of something you mentioned here. At this point having non-Unicode has worked well for us (the data has been that way for 10+ years). In particular, we've never used anything like rich text, or in our applications a RichTextBox control to allow the user to do any sort of formatting like bold text, bulleted lists, etc. However, your response suggest, to me at least, that it may be possible, even in our non-Unicode text, to allow the user to use rich text. Am I understanding you correctly?
Kindest Regards, Rod Connect with me on LinkedIn.
May 24, 2011 at 9:58 am
Rich text, AFAIK, isn't unicode. It's markup inside the text that is interpreted by the editor. So if I make a set of bold text and a regular text like this:
====
This is a test of bold
This is a test
======
in Wordpad, save as .rtf, and then open in a plain text editor, I get this:
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Calibri;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\sa200\sl276\slmult1\lang9\b\f0\fs22 This is a test of bold\par
\b0 This is a test\par
\par
}
Rich text should work find in varchar(max). where you need Unicode is if you store Chinese, Arabic, etc., non-Western character sets.
May 24, 2011 at 10:19 am
Really?! Man, I'm amazed, about rich text and what we've stored in our varchar(max) fields. WOW. That's like an epithany, at least on a small scale. For all these years, we didn't allow the user to enter formatting, because we thought it required Unicode.
Kindest Regards, Rod Connect with me on LinkedIn.
May 24, 2011 at 11:13 am
Amazing amount of stuff in RTF. The Word files are XML storage, AFAIK, so you can even store those, but you need an interpreter to render them back.
The transformation to a rich format depends on the client, but the data for some formats is all regular Western text
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply