MySql to Sql Server migration questions

Question

I did a succesful migration from MySql to Sql Server using the migration tool.

Unfortunately for some reason it labels the tables database.DBO.tablename instead of just database.tablename

I have never used Sql Server so perhaps this is just the way they name their tables.

When I do:

SELECT TOP 1000 [rid]
      ,[filename]
      ,[qcname]
      ,[compound]
      ,[response]
      ,[isid]
      ,[isidresp]
      ,[finalconc]
      ,[rowid]
  FROM [test].[calibration]

it does not work

But, when I do:

SELECT TOP 1000 [rid]
      ,[filename]
      ,[qcname]
      ,[compound]
      ,[response]
      ,[isid]
      ,[isidresp]
      ,[finalconc]
      ,[rowid]
  FROM [test].[dbo].[calibration]

it works.

Does anyone know why it prefixes with DBO?

Answer 1

SqlServer uses an owner name when it references tables. In this case, dbo is the owner.

MySQL doesn't use owner for table names, which is why you didn't see those names before.

Answer 2

SQL Server has something called schemas, in this case the default schema is dbo but it could be anything you wanted. Schemas are used to logically group objects. So you can create a Employee schema and have all the Employee tables, views, procs and functions in there, this then also enables you to give certain users only access to certain schemas

Answer 3

dbo is the standard database owner for anything you create (tables, stored procedures, etc,..), hence the migration tool automatically prefixing everything with it.

When you access something in Sql Server, such as a table called calibration, the following are functionally equivalent:

• calibration
• dbo.calibration
• database_name.dbo.calibration
• server_name.database_name.dbo.calibration

MySql doesn't, as far as I remember (we migrated a solution from MySql to SqlServer about 12 months ago using custom scripts executed by nant) support database owner's when referencing objects, hence you're probably not familiar with four part (server_name.database_name.owner_name.object_name) references.

Basically, if you want to specify the database you're accessing, you also need to specify the "owner" of the object. i.e, the following are functionally identical:

USE [master]
GO
SELECT * FROM [mydatabase].[dbo].[calibration]



USE [mydatabase]
GO
SELECT * FROM [calibration]

Answer 4

Hi

Tell me your migration tool you have used, and let me know the version of from and to databases.

Regards
Eugene

Answer 5

You do have an issue here with the default schema, if it's set to 'dbo' for the user you logged in as you don't need to specify it. See http://msdn.microsoft.com/en-us/library/ms176060.aspx