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.
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.
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=XXXXXXXX@XXXXXXXXXX.database.windows.net;Auto Translate=false;Initial Catalog=MyTestDb". (SQL Server Import and Export Wizard)
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)
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 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. ;)
Proposed As Answer bydaniel310Saturday, April 30, 2011 8:21 PM