EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / Access Memo to SQL

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Access Memo to SQL

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

apletfx
02-26-2007 @ 11:42 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 607
Joined: Nov 2002

ok, I have to admit that I know very little about MSSQL. I rarely get the opportunity to work on a project that requires anything more than Access. However Now I have a project that will require it and I am a bit confuesd by all the field types. I started with an upsize of a Access Database, however I cannot for the life of me figure out the best data type for what in access ammounts to a memo field. I have some fields that are storing large amounts of text. It needs to hold text for news stories that are several pages long. Not knowing what I am doing here I am stabbing in the dark. I have done some research online but the solutions that I have tried have only made things worse. What should I use?

Thanks in advance.

kicker
02-26-2007 @ 12:47 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

To my knowledge the nText type is the same as the memo field in Access. So it would look like this:

Create Table myTbl (
myColumn nText Not Null
)


Craig
Clearcg.com

mquack
02-26-2007 @ 5:10 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

'ntext' is for double-byte characters, and 'text' is for single-byte chars.

Hope that helps. Smile

http://www.rachelqueensg.com

apletfx
02-27-2007 @ 12:32 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 607
Joined: Nov 2002

I know this sounds pretty ignorant, but what's the diff? Is double byte
unicode? and when would I know what version to use?

Mark Aplet
http://www.pixeljunkie.org

mquack
02-27-2007 @ 12:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

Yes, double-byte chars are unicode.  If you're going to store non-english characters (e.g. chinese, japanes, etc), then you want 'ntext'.  Also, if you don't really care about the size of your data, 'ntext' is the "safe" one to use.  If you know that you know that you know that only english characters are going to be used, then 'text' is what you want.  Also, if you're worried about db size, you'll probably want 'text'.  That's the main two things that come into play in that decision.

http://www.rachelqueensg.com

nmiller
02-27-2007 @ 2:07 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 773
Joined: Apr 2003

Also note that depending on how much text you are storing, you may not want to use the 'text' datatype.  The varchar type also stores text, but you can give it an upper limit (up to 8000 characters).  In Access you are limited to 255 characters.  If you want to store 1000 characters, use varchar(1000), not text.

Nathan Miller
NM Consulting

apletfx
02-27-2007 @ 9:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 607
Joined: Nov 2002

this is what I mean it gets confusing. when I read it only holds 8000
characters that is small. A story may have that many words or more. So I
would need 5 times that or more. Maybe I am just not understanding what
exactly you mean.  Confused Am I overcomplicating this? Or is there a
better way to do what I need?

On this same vein. when I try to view the records in the Enterprise
manager all I see is <long text> or something similar and not the actual
story. Is this a preference? If so where can I change it so that I can see the
data so that I can try adding info to it for testing.

nmiller
02-27-2007 @ 10:14 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 773
Joined: Apr 2003

it's showing <long text> because, well, the text is too long to display...

There is an option in query analyzer (sql 2000):

tools > options > Results tab
Maximum characters per column

Ent. manager isn't really meant to display the data that way.



Nathan Miller
NM Consulting


Website Designed and Developed by Pablo Varando.