Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: extract from ntext

    I'm working on a one time conversion thingie to import some data. I received a table in ms access that I fed into my trusty old sqlserver 2000. During the import, a memo column got converted to a nvarchar column. I'm assuming the entire contents of that column got imported properly.

    I need to extract some information from that column, but I'm not exactly sure at this point how I'm going to do that.

    However, I thought I'd first take a look inside, so, I wrote this loop that I thought would show me whatever is inside, but it only shows part of what I select and the datalength function doesn't seem to work nicely either.

    Code:
    select @@textsize -- returns 4096
    
    declare @ptrval varbinary(16)
          , @maxlen int
          , @offset int
          , @size   int
    
    select @ptrval = TEXTPTR(memo) 
         , @maxlen = datalength(memo)
     from table99
    where id = 1
    
    select @maxlen -- returns 18906
    
    set @maxlen = 9453 -- overwrite the @maxlen because the original will throw an error
    set @offset = 0
    set @size = 8000
    
    while @offset < @maxlen
    begin
      if @maxlen < @offset + @size
      begin
        set @size = @maxlen - @offset
      end
      
      select @offset, @size, @maxlen
      READTEXT table99.memo @ptrval @offset @size;
      set @offset = @offset + @size
    end
    What am I doing wrong?

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    first issue fixed by: Query options -> text results -> maximum characters

    the other issue of datalength function not returning the proper length troubles me. anyone?

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    Hello i get you good news ,write these methods below pls
    The following table displays the names of the Access data type names as they appear in the Access UI, and their default SQL Server data type mappings.

    Access

    SQL Server

    Text

    nvarchar

    Memo

    nvarchar(max)

    Number:


    Byte

    tinyint

    Integer

    smallint

    Long Integer

    int

    Single

    real

    Double

    float

    Replication ID (guid)

    uniqueidentifier

    Decimal

    float

    Date/Time

    datetime

    Currency

    money

    AutoNumber

    int

    Yes/No (Boolean)

    bit

    OLE Object

    varbinary(max)

    To research the best SQL Server data type mappings for your application, see Data Types (Database Engine) Data Types (Database Engine) Data Types (Transact-SQL) Data Types (Transact-SQL) in SQL Server Books Online.

    Working with incompatible data types
    If your tables have replication columns (Replication ID), you should remove them before migrating your data. After your data has been migrated to SQL Server, there are a variety of replication and synchronization technologies to choose from. For more information, see SQL Server Replication SQL Server Replication.

    The Attachment data type and multi-valued fields are not supported in SQL Server. Multi-valued fields are for compatibility with SharePoint, and the Attachment data type lets you store binary files in an Access database. Instead of attachments, you might want to consider FILESTREAM storage. For more information, see FILESTREAM Storage in SQL Server 2008 FILESTREAM Storage in SQL Server 2008.

    Working with SQL Server datetime data ranges
    The Access Date/Time data type supports a range of values from 1/1/100 through 12/31/9999 whereas the SQL Server datetime data type is limited in range to values from 1/1/1753 through 12/31/9999. SSMA will flag values that fall outside of the SQL Server datetime range in an assessment report. Note that SSMA can convert Access datetime to SQL Server datetime2, which was introduced in SQL Server 2008. However, the new date and time data types are not supported in Access if you use Access as a front end after converting your data. For more information, see Using Date and Time Data Using Date and Time Data in SQL Server Books Online.

    Note Access and SQL Server store datetime values differently. If your application contains custom logic based on assumptions about how datetime values are stored as numbers, that logic will fail after your data has been migrated to SQL Server.

    Dealing with Unicode (nchar, nvarchar, ntext)
    Depending on the needs of your application, you may want to consider revising the default mapping for Access Text data from nvarchar to varchar. Varchar takes one byte of storage, but is limited to 265 characters. The Unicode encoding of nvarchar uses 2 bytes to encode each character, and is capable of handling non-European alphabets. If your application does not require extended character sets, nvarchar may be overkill. For more information, see Using Unicode Data Using Unicode Data and Working with Unicode Data Working with Unicode Data in SQL Server Books Online.
    Access to SQL Server Migration: Understanding Data Type Conversions - SQL Server Migration Assistant (SSMA) Team&#39;s Blog - Site Home - MSDN Blogs

Posting Permissions

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