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
Create the following table in a SQL Server 2005 database.
CREATE Table Test1( col1 xml, col2 varchar(max), col3 varbinary(max), col4 nvarchar(max))
Insert the following row into the table that you created in step 1.
INSERT INTO Test1 values('<b/>', 'abcdefg', 0x1234567890, N'ABCDEFG')- In a new Excel 2007 spreadsheet, click the Data tab, click From Other Sources, and then click From Microsoft Query.
- Click <New Data Source>, and then click OK.
- 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.
- 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.
- Click Options. In the Database list, select the database in which you created the table in step 1, and then click OK.
- Select Test1 in the table list, and then click OK.
- Click OK in the Choose Data Source dialog box.
- Add the Test1 table to the Columns in your query list, and then click Next.
- Click Next on the Query Wizard – Filter Data page, click Next on the Query Wizard – Sort Order page, and then click Finish.
- 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
