Compartilhar via

SSMA for Oracle - Error: No columns were returned from the database for table 'Foo'

  • Artigo

 

Error:   SQL Server Migration Assistant for Oracle fails to load columns with error "Loading columns for table or view 'Table_Name_1' failed."

 

Solution:   Connect to the Oracle instance using the OLE DB provider instead of the default .NET Oracle Client provider.

 

On a recent client project, we were tasked with upgrading a very old Oracle 8.0.5 database to SQL Server 2008 R2.  Using SQL Server Migration Assistant for Oracle (found here), we were able to connect to the Oracle instance and view the existing schema.  At this point, everything looked like it was running as expected.  However, we hit an issue when expanding any table to view the columns and also hit the same set of errors when trying to convert the schema.

The errors appeared in the following format:

Oracle Server Object Collector error: table : Table_Name_1
ORA-01405: fetched column value is NULL
Loading columns for table or view 'Table_Name_1' failed.
Oracle Server Object Collector error: table : Table_Name_1
No columns were returned from the database for table 'Table_Name_1'.

 

Searching online, we noticed a few people were asking for assistance with the same set of errors, but no one had seemed to solve the problem.  After contacting one of the original developers of the SSMA tool set, the simple solution is to change the default connection (data access provider) for the connection to the Oracle database.  The default connection uses the .NET Oracle Client Provider.  Disconnecting from the Oracle instance and reconnecting using the OLE DB provider solved the problem for us.  For more information on the connection and provider, take a look at the documentation here.

 

Hope it helps,
Sam Lester (MSFT)

Comments

  • Anonymous
    July 18, 2013
    Sam, shouldn't a bug filed against .NET Oracle Client Provider instead of pointing to a different provider?

  • Anonymous
    October 25, 2013
    Hello Sam, Did you updated the Technet msdn question you posted ,IIRW you posted same question but unfortunately did not got the answer now you have found one ,It would be good if you close that question by posting this blog link as answer Please ignore if already done