Geek Answers Handbook

MySql-Sql Server Migration Issues

I successfully migrated from MySql to Sql Server using the migration tool.

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

I have never used Sql Server, so maybe this is how they call their tables.

When I do this:

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

he does not work

But, when I do this:

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

it works.

Does anyone know why it is prefixed with DBO?

+4
source share
5 answers

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

When you access something on an 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, as far as I remember, did not change (we migrated the solution from MySql to SqlServer about 12 months ago using user scripts executed by nant) by the owner of the support database when referencing objects, so you are probably not familiar with the four parts ( server_name.database_name.owner_name.object_name ).

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

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

SqlServer uses the name of the owner when it refers to tables. In this case, dbo is the owner.

MySQL does not use an owner for table names, so you have not seen these names before.

+3
source

SQL Server has something called schemas, in which case the default schema is dbo, but that could be all you wanted. Schemes are used for logical grouping of objects. This way you can create an Employee schema and have all the Employee tables, views, procs and functions there, it also allows you to grant specific users access to only certain schemas

+3
source

Let me know your migration tool that you used and let me know the version from and from the databases.

respectfully
Evgeny

+1
source

You have a problem with the default schema if the user with whom you are logged in is set to 'dbo' because you do not need to specify it. See http://msdn.microsoft.com/en-us/library/ms176060.aspx

+1
source

More articles:


All Articles