SQLServer To mySQL Migration

I'm trying to move a database/schema from an old SQLServer 2000 to mySQL 5+ using Workbench on mySQL.  Workbench goes through connecting to source and check target DBMS connection then bombs in the retrieve schema list from source.  Looking at the log i see at the bottom
"SystemError: ProgrammingError("('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'sys.sp_databases'. (2812) (SQLExecDirectW)")"): error calling Python module function DbMssqlRE.getCatalogNames
ERROR: Retrieve schema list from source: ProgrammingError("('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'sys.sp_databases'. (2812) (SQLExecDirectW)")"): error calling Python module function DbMssqlRE.getCatalogNames
Failed
"
Is there a way in workbench I can avoid any catalog issues and just get the tables over?
xoxomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xoxomosAuthor Commented:
Connected
Check target DBMS connection done
Retrieve schema list from source....
- Checking connection...
- Fetching catalog names...
Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.2 CE\modules\db_mssql_grt.py", line 186, in getCatalogNames
    return [ row[0] for row in execute_query(connection, query) ]
  File "C:\Program Files\MySQL\MySQL Workbench 6.2 CE\modules\db_mssql_grt.py", line 62, in execute_query
    return get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Could not find stored procedure 'sys.sp_databases'. (2812) (SQLExecDirectW)")

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.2 CE\workbench\wizard_progress_page_widget.py", line 192, in thread_work
    self.func()
  File "C:\Program Files\MySQL\MySQL Workbench 6.2 CE\modules\migration_source_selection.py", line 456, in task_fetch_schemata
    self.main.plan.migrationSource.doFetchSchemaNames(only_these_catalogs)
  File "C:\Program Files\MySQL\MySQL Workbench 6.2 CE\modules\migration.py", line 241, in doFetchSchemaNames
    catalog_names = self.getCatalogNames()
  File "C:\Program Files\MySQL\MySQL Workbench 6.2 CE\modules\migration.py", line 205, in getCatalogNames
    return self._rev_eng_module.getCatalogNames(self.connection)
SystemError: ProgrammingError("('42000', "[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Could not find stored procedure 'sys.sp_databases'. (2812) (SQLExecDirectW)")"): error calling Python module function DbMssqlRE.getCatalogNames
ERROR: Retrieve schema list from source: ProgrammingError("('42000', "[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Could not find stored procedure 'sys.sp_databases'. (2812) (SQLExecDirectW)")"): error calling Python module function DbMssqlRE.getCatalogNames
Failed
0
pcelbaCommented:
The question is whether the sys.sp_databases exists on the SQL 2000. I am not sure. I would say the "sys" schema was not used on SQL 2000.

And does the Workbench ensure SQL 2000 compatibility? Migration documentation says it is compatible to SQL 2000 so the SP could exist but who knows...

Can you check SP existence in SQL Management Studio?

If the SP exists then you should check its visibility for the migration user name.
0
xoxomosAuthor Commented:
I do find sp_databases under stored procedures on that 2000 instance.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
Yes, this procedure should be there but sys.sp_databases is something else. You may try to create procedure  sys.sp_databases which calls  sp_databases as the only command.
0
xoxomosAuthor Commented:
The problem with that is the database i'm trying to migrate from is a production database so putting new procedures, no matter how trivial, is forbidden. :-(
0
pcelbaCommented:
I don't see any reason why you should test this migration on the production data which are changing/growing continuously.

Create the backup and restore it to another server/database where you can play with all the data and code without restrictions. It will allow to test the future migration of the production database. I suppose you are planning some server downtime when the final migration happens. It will also allow to do some pre-migration steps.
0
xoxomosAuthor Commented:
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'sys.sp_databases'. (2812) (SQLExecDirectW)")

I've copied the sqlserver 2000 over to sqqlserver 2008.  I can see sys.sp_databases in object explorer, but mysql workbench still cannot.
0
xoxomosAuthor Commented:
Right.  I did a backup from the production server then restored to my desktop sqlserver 2008 express and tried migration there.
This is where i'm seeing sys.sp_databases in object explorer.
0
xoxomosAuthor Commented:
From the python program:
    [NOTE] From MSDN: [A catalog] is equivalent to a databases in SQL Server.
    """
    query = 'exec sys.sp_databases'
    return [ row[0] for row in execute_query(connection, query) ]
Apparently for some reason workbench cannot find the stored procedures. :-(
0
pcelbaCommented:
The work around: Create table containing info equal to the sys.sp_databases in Management Studio:

CREATE TABLE MyDatabases (dbname varchar(128), dbsize int, remarks varchar(254))
insert into MyDatabases EXEC sys.sp_databases  -- use whatever stored procedure you have available

You may even insert databases manually to your new table.

Update the Python source code - instead of the
query = 'exec sys.sp_databases'
simply write
query = 'SELECT * FROM MyDatabases'

and that's it. (Don't forget to delete the compiled Python module - my guess .PYC extension)

BTW, what username uses the Python program to connect SQL Server? Do you use same user to connect in SQL Management Studio?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
Thanks.  That looks like something i want to try.  In the meantime backup from SQLServer 2000 and restore on 2008 Express, then migration from 2008 Express works fine.  Still can't understand how migration tool allows me to see all the tables on the 2000 version initially, but when it come to actually copying to mySQL, lacks some permission.  Don't know anything about SQLServer but it would seem that the same catalog that sees the databases initially would see the tables also, but maybe that is a different permission.
0
xoxomosAuthor Commented:
Actually will have to wait until conversion is done and application is in production running on mySQL first since i can't make those kind of changes on production system as it is not really broken, we are just looking to change os.
0
xoxomosAuthor Commented:
In Management Studio I log on either as xoxomos (remoteinstance) or root (local instance).
On SQLServer it's sa.
0
pcelbaCommented:
It does not seem the Python program logs in as sa.

Thanks for the points!
1
xoxomosAuthor Commented:
I was thinking it had something to do with authentication or permissions however
Python does access the server and the database server in order to list the databases.  I've assumed, perhaps incorrectly, that if it had permission to read the names of the different databases it could  read the tables.   Maybe that is another question :-)
0
pcelbaCommented:
All these conversions should run under user having sufficient rights otherwise it is a nightmare...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.