Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Unanswered: Access Memo field to SQL Server Text field

    Hi,

    I'm importing an Access database to SQL Server 2000.
    The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).

    I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.

    Is this some sort of an encoding problem that arose during database import?
    I would appreciate any pointers.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If the data is visible within SQL Server, but not from your ASP page, then the problem is with your ASP page and the way it is pulling the text data.
    Do you really need this to be a text field? The varchar datatype will handle up to 8000 bytes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    51
    That's a good point - I might be able to get away with varchar. It's something I'll look into, but I'm still curious as to why the data behaves this way (I ran into similar problems in the past while trying to import Access data to SQL server).

    My ASP page is functioning properly. I use a simple "SELECT *" statement to pull the data and display it on the page. With an Access DSN it encounters no problems and shows all the data as intended. When I import the data into SQL Server and swap the DSN to point to the newly-created database (while not modifying the ASP page itself in any way) the text fields display no content.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A text field in SQL Server is not stored within the record. The record actually only stores a pointer to the location of the actual data. This may be why your page is not handling it properly, but I don't know the work-around. You could try posting your question on the ASP forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    51
    Interesting... I was aware that Text fields are stored differently and separately from the regular data in the database, but I didn't think it would affect the way they're displayed through the recordset...

    Maybe there's something that can be done on the ASP side - I'll ask some ASP guys.

    Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by nbozic
    Interesting... I was aware that Text fields are stored differently and separately from the regular data in the database, but I didn't think it would affect the way they're displayed through the recordset...
    I wouldn't have thought so either, but it is my best guess.
    What happens in your ASP code if you enumerate the column names rather than using "select *" (which is a bad practice anyway)?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by nbozic
    I use a simple "SELECT *" statement to pull the data and display it on the page.


    ahhhhhhhhhhhhhhhhhhhhhhhhhhh

    http://weblogs.sqlteam.com/brettk/ar...4/22/1272.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Posts
    51
    I try to do it properly with larger systems and database, but, yeah, for me it's laziness that makes me use "Select *"...

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by nbozic
    I try to do it properly with larger systems and database, but, yeah, for me it's laziness that makes me use "Select *"...
    Hey! No one's lazier than me.....

    SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
    WHERE TABLE_NAME = 'xxx'
    ORDER BY ORDINAL_POSITION

    SELECT * can buy you a boatload of trouble...so it's amatter how you want to spend your laziness...you have no choice when you have exploding code
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Mar 2004
    Posts
    51
    Quote Originally Posted by blindman
    I wouldn't have thought so either, but it is my best guess.
    What happens in your ASP code if you enumerate the column names rather than using "select *" (which is a bad practice anyway)?
    Well, that's an interesting question, mostly because it gives me some sort of a deja vu feeling - as if I encountered something like this several years ago...
    It's possible that I did something like this in the past and fixed the problem that way.
    I'll check into this on Thursday when I'm back at work.

    Thanks.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are off UNTIL Thursday?

    Okay, but this Thursday is Turkey Day for us Yanks, so you'll have to depend upon the Pootle Flumps and Cannucks of the world to help you out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2004
    Posts
    51
    Quote Originally Posted by Brett Kaiser
    Hey! No one's lazier than me.....

    SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
    WHERE TABLE_NAME = 'xxx'
    ORDER BY ORDINAL_POSITION

    SELECT * can buy you a boatload of trouble...so it's amatter how you want to spend your laziness...you have no choice when you have exploding code
    That's an interesting way to structure a query - I've never done it this way before.

    Dare to dream... Sorry, you think that you're the laziness champ, but you're not. Your laziness doesn't compare to mine - not even close.

  13. #13
    Join Date
    Mar 2004
    Posts
    51
    Quote Originally Posted by blindman
    You are off UNTIL Thursday?

    Okay, but this Thursday is Turkey Day for us Yanks, so you'll have to depend upon the Pootle Flumps and Cannucks of the world to help you out.
    Yes, it sounds a bit odd. I'm actually at work as we speak, but I also have another part time job as a contractor with my previous employer. Thanksgiving will be a good time to come in and get a bunch of work out of the way.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See but here's the rub, my laziness is born out of economy of effort..the smarter you work, the less you have to

    some laziness makes your life more simple, other types make your life much more difficult, you have to decide
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Mar 2004
    Posts
    51
    Quote Originally Posted by Brett Kaiser
    See but here's the rub, my laziness is born out of economy of effort..the smarter you work, the less you have to

    some laziness makes your life more simple, other types make your life much more difficult, you have to decide
    Agree. Mine usually gets me in trouble somewhere down the line.

    ... but it still feels good to be the "Champ"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •