locked
SSMA 5.2 Sybase ASE -> MS SQL Server 2012 freezes during data migration RRS feed

  • Question

  • I try to migrate a database from Sybase ASE 12.5.3 (32 bit) to MS SQL Server 2012 using SSMA 5.2.

    Migrating the schema works fine. However, when I start to migrate the data, the output window of SSMA freezes containing the text "Preparing table <First table in selection>".

    When I look what is happening at the Sybase server, I just see one connection doing nothing:
      SELECT  status, cmd, cpu, physical_io, memusage, blocked
      FROM    sysprocesses
      WHERE  SPID <> @@SPID
      AND      suid <> 0

      cmd                            cpu  physical_io memusage blocked
      ---                               ---  -----------   --------      -------
      AWAITING COMMAND   8     0               2              0

    When I use the profile to look what is happening on MS SQL, just 2 simple statements are issued.

    What am I doing wrong? Or is it the version of the ASE Server to old for this version of the SSMA tool?

    The exact version numbers are:
    ASE: Adaptive Server Enterprise/12.5.3/EBF 12461 ESD#2/P/NT (IX86)/OS 4.0/ase1253/1904/32-bit/OPT/Tue Mar 22 17:23:51 2005
    MS SQL: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
     Feb 10 2012 19:39:15
     Copyright (c) Microsoft Corporation
     Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    SSMA: SQL Server Migration Assistant for Sybase (32 bit) v.5.2.1260

    Wednesday, July 11, 2012 9:38 AM

Answers

  • OK, case closed. Just use the ADO.net provider and everything works fine.
    • Marked as answer by Chris Sijtsma Friday, July 27, 2012 10:59 AM
    Friday, July 27, 2012 10:59 AM

All replies

  • Hi Chris,

    What account are you using to login to Sybase database? Maybe you have to login to Sybase as the owner of the database. For more details, you can take a look at this thread with the similar issue.

    You also can see the following articles for your reference to check the steps of migrating data from Sybase ASE to SQL Server:


    Best Regards, Ray Chen

    Thursday, July 12, 2012 6:22 AM
  • Hi Ray,

    Thank you for your reply. I already read the thread you are referring to. I logged in on the Sybase server as 'sa', but the 'sa' was the database owner. I also logged in on the MS SQL Server as 'sa'. However, on the MS SQL Server, my integrated login was the database owner.

    I tried login in on both servers as the dbo, but that didn't help.

    I created a now login on both servers, DBOwner, and created the database on both servers with DBOwner as the dbo.
    Then I used the DBOwner login in the SSMA tool. It still doesn't work.

    Thanks again for your reply, but in my case, this does not seem to do the trick.

    Thursday, July 12, 2012 9:46 AM
  • I can also add some more information with regard to the processes that are running.

    On the Sybase server I use the following statement to scan the active processes:
      SELECT spid, DB_Name(dbid) AS DBName, status, cmd, blocked
      FROM   master.dbo.sysprocesses
      WHERE suid = (SELECT suid FROM master.dbo.syslogins WHERE name = 'DBOwner')

    On the MS SQL Server I use the statement below:
      SELECT spid, DB_NAME(dbid) AS DBName, status, cmd, blocked, waittype, program_name
      FROM   master.dbo.sysprocesses
      WHERE loginame = 'DBOwner'

    After starting the SSMA tool, I see one process in both servers. The process on the Sybase server has TempDB as active database. The process on the MS SQL Server has the database with the highest database_id as the active database. After migrating the schema, the MS SQL Server process has the intended database as the active database. On the Sybase server, TempDB still is the active database. When I start the data migration, the same process shows up on the Sybase site and on the MS SQL Server site. Although you have to login again for the data migration, new connections do not show up. When I also include the login_time column and comment out the where clause, I can see that the singe SSMA process is the last one that connected to the server. The second login, used for the data migration, does not show up.

    Thursday, July 12, 2012 10:16 AM
  • Chris,

    Couple of months back, I migrated Sybase ASE 15 to SQL Server 2012 (64bit) using SSMA without any problem.

    Could you please try to migrate using SSMA (64 bit), and see if that make some difference.


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 12, 2012 10:30 AM
  • Hi Manish,

    Thank you for your reply.

    I tried SSMA (64 bit), but it cannot connect to a Sybase (32 bit) server. ASE 12.5.3 (the Sybase version our application is using) is only available as a 32 bit server for Windows. This version of ASE was available as a 64 bit server for Unix, but we use the Windows platform. You will get the error below if you try to connect to the 32 bit ASE 12.5.3 with SSMA (64 bit).

    Unable to find specified provider.
    Error occurred while establishing connection to Sybase server. You might have 32-bit connectivity components installed on the machine, in order for SSMA to make use of those connectivity libraries, you need to run the 32 bit version of SSMA application whose shortcut can be found under the Programs menu.

    Thursday, July 12, 2012 10:53 AM
  • I do not know if it is viewed as bad behaviour, but I just also posted this problem as feedback on

    https://connect.microsoft.com/SQLServer/feedback/details/753430/ssma-5-2-32-bit-hangs-when-migrating-data-from-ase-12-5-3-to-ms-sql-2012

    Just tell me if I shouldn't have done this.

    Thursday, July 12, 2012 11:39 AM
  • OK, case closed. Just use the ADO.net provider and everything works fine.
    • Marked as answer by Chris Sijtsma Friday, July 27, 2012 10:59 AM
    Friday, July 27, 2012 10:59 AM