Migrating Data to SQL Azure using Import Export Wizard

Question

I have both read and heard that it is possible to migrate data from a local db to a SQL Azure db using the Import/Export wizard of SQL Server 2008 R2. Well, I can't get it to work.

I have tried both user_name@server_Name as well as server_name\user_name. Both return a Login Timeout Expired error.

What is the correct ServerName format to connect to a SQL Azure server via the Import/Export wizard?

Thanks.

Answer 1

Hello, we haven't officially tested the import/export wizard. To migrate data to SQL Azure, I suggest you to follow the steps described in http://blogs.msdn.com/sateeshp/archive/2010/01/01/seven-benefits-for-using-the-sql-server-management-studio-ssms-for-managing-the-sql-azure-database.aspx. (refer to #4 Database Migration) That is, you need to generate scripts from your on-premises SQL Server, and then run the scripts against SQL Azure. Note the November CTP of SSMS 2008 R2 has a few bugs in this area, which will be fixed in the future. Sometimes this approach may not work in November CTP.

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Actually, I did A LOT of testing last night, and I can tell you exactly why it doesn't work. However I will have to post the answer a little later as I have all of my notes at home. Sorry for the teaser, but I will post full details this afternoon.

Answer 3

I was able to duplicate the issue here at the office.

So the trick to figuring this out was to understand how to accruately fill out the servername and username information in the Import/Export wizard dialog (on either the source or destination  page). What was interesting was that depending on the information I entered, I would get different error messages back, ranging from Login Timeout errors to invalid username format errors.

I was finally able to guess the correct format because I immediately received an error that was able to lead me to "step 2" in debugging this. The server name is the FQDN, such as fugqztxmtz.database.windows.net, and the username must be username@servername where server name is NOT the FQDN. For example, bob@fugqztxmtz. Or, fugqztxmtz\bob also works. This is also good to know for using BCP.

The next important step is how you select the database. If you click the dropdown arrow of the database list you will get one error, but if you type in the database name and click the NEXT button, you will get a different error, but SIMILAR in message. For example, clicking the database drop down returns "could not retrieve table list", while typing in the database name and clicking NEXT returns a more meaningful "cannot get teh supported data types from teh database connection 'provider=SQLNCLI10; data source=blahblah".

However, BOTH errors include "additional error information" which state the same thing, which is "The stored procedure required to complete this operation could not be found on the server".

Ah HA! Now we're getting somewhere. So I fired up SQL Profiler, pointing it to my LOCAL SQL instance. I then restarted the import/export wizard and pointed the source to a local database. When I clicked NEXT on the import/export wizard, I paused the trace and went hunting in the results.

Sure enough, the import/export wizard called a stored procedure called sp_provider_types_100_rowset.

exec [sys].sp_provider_types_100_rowset NULL,NULL

This proc simply returns a list of supported data types. If you execute this locally you'll get stuff back like:

TYPE_NAME       DATA_TYPE   COLUMN_SIZE
smallint            2                   5
int                   3                    10
real                 4                     7
float                5                     15
money            6                     19
smallmoney     6                    10
bit                   11                  1

This stored procedure does not exist in SQL Azure, and thus the import / export wizard does not work. We know from the documentation that not all data types are supported in SQL Azure, so I don't know if this is why.

I'm sure the import/export wizard will be supported at some point, but I at least wanted to update you on the findings.

Answer 4

Hey SQLRocks,

Yep, we are working on it. Can't commit to a timeframe, but it is definitly in the works. Thanks for the feedback.

-Dave

Answer 5

Thanks.

The data types returned by the stored procedure as shown above are all supported in SQL Azure, they being exact or approximae numerics.

I suppose then that the SP does not exist.

---

mysorian

Answer 6

Export/Import works, the wizard has a bug.

---

mysorian

Answer 7

Hi

What is the solution for to make Import/Export wizard working for you?

I just created an azure account and trying to export the data from my local database which is on SQL Server 2008 R2 and getting the following error when I am trying to export with import/export wizard using SQL Server Native Client 10.0 or OLE DB Provider for SQL Server as the destination.

 

===================================

Cannot get the supported data types from the database connection "Provider=SQLNCLI10;Data Source=XXXXXXXX.database.windows.net;User ID=[e-mail];Auto Translate=false;Initial Catalog=MyTestDb". (SQL Server Import and Export Wizard)

===================================

Unspecified error
The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. (Microsoft SQL Server Native Client 10.0)

------------------------------
Program Location:

   at System.Data.OleDb.OleDbConnectionInternal.ProcessResults(OleDbHResult hr)
   at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
   at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
   at System.Data.OleDb.OleDbMetaDataFactory.GetDataTypesTable(OleDbConnection connection)
   at System.Data.OleDb.OleDbMetaDataFactory.PrepareCollection(String collectionName, String[] restrictions, DbConnection connection)
   at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
   at System.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions)
   at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName, String[] restrictionValues)
   at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName)
   at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(DbConnection myDestConnection)
   at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)

 

Thanks in advance,

Raghuram.

Answer 8

Ten... Months... Later...

 

:S

Answer 9

The SQL Server Import and Export Wizard does work with SQL Azure - I've successfully used the version included in SQL Server 2008 R2 to move data from a local SQL Server to SQL Azure.

The trick to getting it to work is to select the ".Net Framework Data Provider for SqlServer" as the data source.  Selecting OLEDB will not work as OLEDB is not currently supported by SQL Azure.

Hope that helps.

Answer 10

The process has been documented in the TechNet Wiki article titled "SQL Azure Data Migration Using SQL Server Import and Export Wizard" at http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-data-migration-using-sql-server-import-and-export-wizard.aspx.

Answer 11

Thanks Mark, that did indeed do the trick.  To clarify, make sure to use ".Net Framework Data Provider for SqlServer" for *both* source *and* destination.

There were still some problems with the export, as the export doesn't seem to transfer Keys, for whatever reason.  I ended up getting it to work by using a schema compare in Visual Studio 2010 towards an empty database and generating a sql query that does all the magic (and erasing all the properties that SQL Azure doesn't support, sigh).  Kinda kludgy, but that seems to be the theme when dealing with Azure SQL.

I would still expect any sort of Export to "just work" without having any sort of intervention or handholding, but that's never been the case with any scenario involving Sql Server's *extraordinary* user experience and 1000s of dialog boxes offering you gracious messages of something gone wrong, but little service on how to fix it or (god forbid) a button or two offering a default action to fix the issue at hand.  I welcome with opened arms the few hours lost with each and every Sql Server experience digging into the bowels of Google (and forums like these) to find the remedy to a problem whose source is only hinted with a vague exception message --if I'm lucky. *cough cough*

(Yes, it simply is the worst.)

Anyways, diatribe aside, I finally got this to work without firing up an SSIS package, and learning yet another vein of black magic required to simply move a set of bytes from point a to point b in the most complicated manner possible that is only bound by the limits of human imagination. :P

(There, NOW I'm done)

Thanks again. ;)

Answer 12

Hey SQLRocks,

Yep, we are working on it. Can't commit to a timeframe, but it is definitly in the works. Thanks for the feedback.

-Dave

Hi dave - I wonder if you know why I cant use Sql server managment studio to see design table when I connected into azure DB