Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/925712

From BetaArchive Wiki
Knowledge Base


Article ID: 925712

Article Last Modified on 10/15/2007



APPLIES TO

  • Microsoft Office Excel 2007



SYMPTOMS

You use the Microsoft SQL Native Client driver or the Microsoft SQL Server driver to import external data in Microsoft Office Excel 2007 from a table that contains new data types from Microsoft SQL Server 2005. When you do this, not all columns are returned to the Excel spreadsheet.

This problem occurs when the SQL Server 2005 table contains a column data type such as "varbinary(max)."

CAUSE

This problem occurs because neither the SQL Native Client driver nor the SQL Server driver supports the new data types in SQL Server 2005 from Excel 2007.

WORKAROUND

To work around this problem, select a driver other than SQL Native Client or the SQL OLE DB provider if another driver is available.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the problem

  1. Create the following table in a SQL Server 2005 database.

    CREATE Table Test1(
    col1 xml, 
    col2 varchar(max), 
    col3 varbinary(max), 
    col4 nvarchar(max))
  2. Insert the following row into the table that you created in step 1.

    INSERT INTO Test1 values('<b/>', 'abcdefg', 0x1234567890, N'ABCDEFG')
  3. In a new Excel 2007 spreadsheet, click the Data tab, click From Other Sources, and then click From Microsoft Query.
  4. Click <New Data Source>, and then click OK.
  5. Type myconnection1 in the What name do you want to give your data source text box, and then select SQL Native Client from the driver list.
  6. Click Connect, type the name of the SQL Server 2005 instance in which you created the table in step 1, and then enter the appropriate credentials.
  7. Click Options. In the Database list, select the database in which you created the table in step 1, and then click OK.
  8. Select Test1 in the table list, and then click OK.
  9. Click OK in the Choose Data Source dialog box.
  10. Add the Test1 table to the Columns in your query list, and then click Next.
  11. Click Next on the Query Wizard – Filter Data page, click Next on the Query Wizard – Sort Order page, and then click Finish.
  12. Click OK in the Import Data dialog box.

    Notice that the column that is named "col3" does not appear in the spreadsheet.



Additional query words: XL2007 Excel2007

Keywords: kbtshoot kbprb kbexpertisebeginner KB925712