Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Unanswered: Access to SQL Rows Limitation?

    Just downloaded from Microsoft the SQL Server Migration Assistant for Access, and it worked great without any apparent problems. I was attempting to migrate a complex Access dB with many tables into SQL 2005.

    Using the SQL Server Migration Tool (SQL Metadata Explorer), and opening the largest table, it had the correct amount of rows in it. (520,378 rows)

    Upon browsing my newly formed tables with MS SQL Server 2005 Management Studio Express, the same table had exacly 50,000 (yes, fifty thousand) rows in the properties dialog.

    The original access table had 520,378 rows.

    Why is there such a large limitation with Management Studio Express? Is it because it's free and limited in function??

    Any help appreciated very much!

    Desperate in Sunnyvale, CA USA
    Last edited by dlong241950; 06-06-08 at 01:28.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I doubt that this is a result of a limitation in your version of SQL Server. More likely, there was data corruption or some error that occured during the upgrade. Data is frequently copied in batches of 50,000 so I would guess that the first batch uploaded correctly while the second one failed for some reason.
    Check through your logs for any errors or warnings that occurred.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Access automation is not very reliable, and the results of its usage are unpredictable at best. I had to write my own "upsizing" wizard, which is much more robust and guarantees the creation of every table. In almost every Access-to-SQL conversion the main cause of failures I encountered were related to out-of-range values for specific data types (primarily datetime). Access upsizer chokes on it and fails to even create a table, while my script actually builds the table without loading the data, and prefixes the name with FAILED_TO_CONVERT_<table_name>.

    Here's the script:
    Code:
    -- The script assumes the location and name of MS Access database that holds user data.
    -- It creates user database, and populates it with the contents of MS Access database.
    -- Name and location of MS Access database need to be changed to reflect its actual
    -- location in your environment.
    use master
    go
    set nocount on
    --if db_id('<SQLDB,sysname,FileMaker>') is not null begin
    --   exec ('alter database <SQLDB,sysname,FileMaker> set single_user with rollback immediate;')
    --   exec ('alter database <SQLDB,sysname,FileMaker> set restricted_user with rollback immediate;')
    --   drop database <SQLDB,sysname,FileMaker>
    --end
    go
    if exists (select * from sys.sysservers where srvname = N'<MSAccess_LinkedServer,sysname,FileMaker>')
       exec master.dbo.sp_dropserver @server = N'<MSAccess_LinkedServer,sysname,FileMaker>', @droplogins = 'droplogins'
    exec sp_addlinkedserver N'<MSAccess_LinkedServer,sysname,FileMaker>', 'Access', 'Microsoft.Jet.OLEDB.4.0', N'<MSAccessDB,nvarchar(4000),C:\SQL\SomeoneElse\FileMaker.mdb>'
    exec sp_addlinkedsrvlogin N'<MSAccess_LinkedServer,sysname,FileMaker>', false, 'sa', 'Admin', NULL
    go
    if db_id('<SQLDB,sysname,FileMaker>') is null
       exec ('create database <SQLDB,sysname,FileMaker>')
    go
    use <SQLDB,sysname,FileMaker>
    go
    set nocount on
    declare @name sysname, @cmd varchar(8000), @error int, @rows int, @errMessage varchar(8000)
    create table #t (c sysname null, s sysname null, n sysname not null, t sysname not null, r sysname null)
    create table #c (c int not null)
    insert #t exec sp_tables_ex '<MSAccess_LinkedServer,sysname,FileMaker>'
    set @rows = @@rowcount
    raiserror ('Total number of objects: %d', 0, 1, @rows) with nowait
    set @rows = (select count(*) from #t where t = N'TABLE')
    raiserror ('Tables to process: %d', 0, 1, @rows) with nowait
    declare c cursor local for
       select n from #t where t = N'TABLE' order by n
    open c
    fetch next from c into @name
    while @@fetch_status = 0 begin
       set @cmd = 'if object_id(''dbo.' + @name + ''') is not null drop table dbo.[' + @name + ']'
       exec (@cmd)
       set @cmd = 'select * into dbo.[' + @name + '] from <MSAccess_LinkedServer,sysname,FileMaker>...[' + @name + ']'
       --print @cmd
       begin try
          exec (@cmd)
          set @rows = @@rowcount
          raiserror ('Imported %d rows into %s', 0, 1, @rows, @name)
       end try
       begin catch
          select @error = @@error, @errMessage = error_message()
          raiserror ('Failed to import %s! Error: %d - %s', 0, 1, @name, @error, @errMessage) with nowait
          raiserror ('Attempting to count records for %s...', 0, 1, @name) with nowait
          set @cmd = 'select c=count(1) from <MSAccess_LinkedServer,sysname,FileMaker>...[' + @name + ']'
          truncate table #c
          begin try
             insert #c exec (@cmd)
             select @rows = c from #c
          end try
          begin catch
             set @error = @@error
             raiserror ('Failed to create structure for %s! Error: %d', 0, 1, @name, @error) with nowait
          end catch
          raiserror ('Attempting to create a structure for %s...', 0, 1, @name) with nowait
          set @cmd = 'if object_id(''dbo.FAILED_TO_CONVERT__' + @name + ''') is not null drop table dbo.[FAILED_TO_CONVERT__' + @name + ']'
          exec (@cmd)
          set @cmd = 'select * into dbo.[FAILED_TO_CONVERT__' + @name + '] from <MSAccess_LinkedServer,sysname,FileMaker>...[' + @name + '] where 1=2'
          begin try
             exec (@cmd)
             raiserror ('Table structure for %s has been created.  There are %d rows that need to be imported manually!', 0, 1, @name, @rows) with nowait
          end try
          begin catch
             set @error = @@error
             raiserror ('Failed to create structure for %s! Error: %d', 0, 1, @name, @error) with nowait
             raiserror ('There are %d rows that need to be imported manually!', 0, 1, @rows) with nowait
          end catch
       end catch
       fetch next from c into @name
    end
    close c
    deallocate c
    go
    drop table #t, #c
    go
    (edited): Forgot to mention, that the script is a template, so before running it you need to press Ctrl+Shift+M and specify the values in the right column.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    no comments? didn't work for you? still looking at it?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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