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

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]