Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar

Search for    in all of
view all search results right arrow
Support > Technical Documents > Document Types > Partner Certification Report > Microsoft Excel 2010 with Sybase IQ 15.4, Report 3...

Microsoft Excel 2010 with Sybase IQ 15.4, Report 381, May 2012

This report describes the certification of Microsoft Excel 2010 with Sybase IQ version 15.4 using ODBC connectivity.
RSS Feed



This section describes each of the products tested and how they were used during testing. For more information about any of the products, please refer to the product literature. This report documents only the platforms and products tested for certification. Other possible combinations of these products and platforms are not certified by Sybase. For more information about Sybase IQ , including known issues not encountered during this certification or those occurring on other platforms, see the Sybase Release Bulletin for the relevant product and platform.

  • Microsoft Office Excel 2010 (hereafter called "Excel"), which is part of Microsoft Office Enterprise 2010 suite of products, served as the reporting tool, querying the Sybase IQ database. Excel includes a feature called MS Query. MS Query and its wizard were used to select tables in Sybase IQ and build queries. The Excel version tested was a 32-bit application installed and run on a 64-bit Windows Server 2003 machine.
  • Sybase® IQ version 15.4 (hereafter called "IQ") served as a data source. Two 64-bit IQ server instances running on the same Sun SPARC Solaris 10 workstation were tested.
  • 32-bit Sybase IQ 15.4 version 12 Network Database Client ODBC Driver for Windows (hereafter called the "IQ ODBC driver") enabled ODBC connectivity between Excel and the IQ servers. The driver was installed with the Sybase IQ 15.4 Database Client for Windows, which includes the driver in 32-bit word size. Note that version 12 of the driver is the appropriate version to work with the 15.4 version of IQ. The 32-bit version of the driver was used because the version of Excel tested was a 32-bit application and the word-size of the driver must match that of the client application, not the target server.

*Click here for for Sybase trademark and registration information.

The following diagram illustrates the test environment. For detailed information about the environment, see the section Test Environment later in this report.

Back to Contents

Tests Performed

This section contains a detailed explanation of the tests performed.

  • Installed the Sybase database servers. IQ was installed on a 64-bit Sun SPARC Solaris 10 workstation. Note that the language packs required for Simplified Chinese language support were installed on the Sybase server host machine. See the section Test Environment for details about the software installed on the test machines.
  • Ran two instances of the IQ server: one with the sample demo databases and one created to hold supported data type test data. See Test Note 3 for information about the IQ demo database. The IQ demo and data type test databases were configured with the UTF-8 character set.
  • Installed Excel, a 32-bit application, accepting the default installation options, onto a Windows Server 2003 machine. See step 2 in the Setup and Configuration section for details on the Excel setup.
  • Installed Microsoft Windows Asian language packs to load the Simplified Chinese language onto the Excel host machine.
  • Installed the Sybase IQ 15.4 Database Client for Windows on the same machine with Excel. This software installed the 32-bit IQ ODBC driver used for connectivity between MS Excel and the remote Sybase databases.
  • Used the 32-bit Microsoft ODBC Administrator, installed with the Sybase IQ Database Client, to configure two system data source names (DSNs) for connectivity to the remote IQ server on Solaris. For information about why it is best to use the ODBC Administrator installed with the Sybase IQ Database client, see Test Note 4.
  • Tested the ODBC connections from Excel to the Sybase servers using the defined data sources. See Install Microsoft Excel and configure it for connectivity to Sybase IQ in the Setup and Configuration section for information.
  • Within Excel, used the Get External Data | From Other Sources | From Microsoft Query option to choose from a list of defined DSNs.
  • Used the Choose Data Source dialog box to select the ODBC DSN for the Sybase data sources.
  • Used the Query Wizard application within Excel to create queries, which included the following tasks: (See the section Feature Example for instructions to create queries.)
    • Selecting tables and columns from the Sybase DSNs for inclusion in queries. Note that the Query Wizard interface allows the selection of columns from a single table only. For information about an error that appears in MS Query Wizard when you attempt to choose columns from more than one table and for information about how to create queries with columns from more than one table, see Test Note 2.
    • Specifying to filter data in result sets.
    • Specifying the sort order for columns in result sets.
    • Specifying whether to return results to the Excel spreadsheet or MS Query.
    • Creating a Pivot table and group the results in that table.
  • Used the MS Query application and its menu options (instead of the wizard) to create queries and work with data, which included the following tasks:
    • Select columns for inclusion in a query. This was the method used for creating queries that include data from more than one source table (multiple-table JOINs). This involved manually creating links/relationships between tables (for example, by column name). See Test Note 2 for more information.
    • Filtered query results by specifying "criteria," which is another term for "filtering" in Excel.
    • Sorted results.
    • Returned only unique values.
    • Used the Go to option to move to a specific row in the result set.
    • Create reports based on VIEWs defined in the Sybase databases. See Test Note 1 for information about how to see other objects besides tables in databases.
    • Explicitly executed the resulting SQL. Note that making any changes to a query in MS Query did not automatically update the result set shown in the application. It was necessary to execute the SQL to see any changes.
    • Prohibited and allowed editing of the retrieved data.
  • Evaluated Excel, MS Query and the Sybase databases for the following using ODBC connectivity:
    • Error handling - database unavailable.
    • Multiple-table joins handling. See Test Note 2 for more information.
    • Retrieval of data from tables and views. Stored procedures are supported by IQ. However, there is not an option for selection on the Choose Data dialog box in the Excel Query Wizard and, therefore, they were assumed to be unsupported by Excel. See Test Note 1 for more information.
    • Minimum, large and null-able value handling, for a sampling of numeric, datetime and binary data types supported by IQ. See Test Note 6 for detailed data type information.
    • Display of Multi-byte data in a report in the form of Simplified Chinese characters.
    • Retrieval of user-defined data types.

Back to top of Tests Performed | Back to Contents

Setup and Configuration

The following information relates to the setup and configuration of the test environment.

Install and configure the Sybase IQ 15.4 server

  1. Install IQ 15.4 onto a 64-bit Sun SPARC Solaris 10 workstation, accepting all default installation options except the target installation directory.
  2. Run the provided script to create the 15.4 version iqdemo sample database and then run the sample database and server. See the Sybase IQ 15.4 New Features Guide for information about the demo database. As stated previously, the test environment setup also included the creation and operation of a second IQ database configured with the UTF-8 character set and populated with supported data type data, including CLOB and Simplified Chinese data.
  3. Load the Solaris language packs required to support the Simplified Chinese data used for multi-byte data testing. Refer to the Sun Solaris documentation for this information.

Install and configure Microsoft Excel client and configure it for ODBC connectivity to Sybase IQ

In the test environment, the Sybase IQ 15.4 Database Client for Windows was installed on the same 64-bit Windows Server 2003 machine where Excel was installed. The IQ 15.4 Database Client includes the IQ 32-bit ODBC driver, which is required to enable connectivity between Excel and Sybase IQ.

  1. Install the Sybase IQ 15.4 Database Client for Windows on the same machine where you will install Excel.
  2. Open the 32-bit ODBC Administrator available from the Start | Programs | Sybase | Sybase IQ 15.4 menu to configure ODBC data sources. Note that using the version of the ODBC Administrator available on this menu ensures you correctly load the 32-bit Sybase drivers. See Test Note 4 for more information about loading drivers.
  3. Configure data source names (DSNs) for IQ. For information about configuring DSNs, see the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SQL Anywhere 12.0.1. Also note that the demo database for IQ 15.4 was configured with the UTF-8 character set. Thus, to connect to the demo database, you may need to specify "utf8" in the Character Set field of the Advanced tab of the driver configuration dialog box.
In the test environment, Excel was installed on the same 64-bit Windows Server 2003 machine with the Sybase IQ 32-bit database client. Note that even though the operating system was 64-bit, the Sybase ODBC driver tested and Excel were 32-bit applications.
  1. Install Microsoft Office Excel from the Microsoft Office 2010 suite installation, accepting all the default installation options. After installation, the Microsoft Office menu will be an option on the Start | Programs menu and Microsoft Office Excel 2010 will be available on the Microsoft Office menu.
  2. Launch the Excel application from the Start menu. Excel usually opens with three new blank worksheets. If it does not, choose File | New. A dialog box opens displaying the available templates to choose from. Double-click on the Blank workbook template and a new workbook opens displaying three blank worksheets.
  3. Locate the Excel application menu bar. Note that the menus are actually tabs that display different options. Excel opens the blank worksheets with the Home menu open by default. The Home menu/tab options include font, alignment, text style and number formatting options, as well as Excel cell (insert, update and delete) and data editing (AutoSum, Fill, Clear, Sort & Filter and Find & Select) features.

  1. Locate the Data tab and click it to display it. Notice that the menu options change. The Data menu provides the options for data management, which include accessing data from external data sources, such as the Sybase databases.

  1. In the Data menu options, locate the Get External Data options on the left and select drop-down menu for From Other Sources.

  1. Select the From Microsoft Query (bottom) option. MS Query is a feature provided within Excel for creating queries. This is the option to use for Sybase IQ ODBC data sources. The Choose Data Source dialog box opens with the Databases tab in focus. The ODBC data sources (DSNs) defined in the ODBC Administrator will be listed here. For example, if you created an ODBC DSN as described in the section Install and configure the Sybase database client software, you will see that DSN listed.

  1. Select the ODBC DSN you defined for the target Sybase database. You are now ready to retrieve ODBC data into Excel. Notice the option Use the Query Wizards to create/edit queries is selected by default. When you click OK, the Query Wizard will walk you through how to create a query using MS Query and how to retrieve the query result set into MS Query or an Excel spreadsheet. Leave the Query Wizard open and proceed to the next section for an example of how to create a query and retrieve data into Excel.

Back to top of Setup and Configuration | Back to Contents

Feature Example

This section describes how to create a query using the Excel MS Query Wizard and data from the Sybase IQ demo database. Specifically, this example shows how to retrieve the ID, last name (Surname), department ID, City and State from the Employees table in the Sybase demo database. It filters the data by a particular state and orders them alphabetically by the city.

  1. Before proceeding, you will need to create an ODBC data source for the IQ demo database, if you have not done so already.
  2. Begin with Excel Query Wizard open to the MS Query Choose Data Source panel as shown in Step 6 of the previous section. The instructions in the previous section describe how to display this dialog box.
  3. Select the DSN for the Sybase demo database and click OK. A message may appear indicating Excel is connecting to the target database. When it does, the Query Wizard Choose Columns window displays.
  4. In the Available tables and columns pane, scroll down until you see the Employees table and expand it to display its list of columns.
  5. Select each of following columns and then the right-facing arrow to move the columns to the Columns in your query pane: EmployeeID, Surname, DepartmentID, City and State. Click Next. The Query Wizard - Filter Data panel opens.

  1. In the Column to filter pane, select State. Notice under Only includes rows where section that State is listed and the first row of drop-down menus is now available.
  2. Specify you want to include only states beginning with the letter "M" by selecting begins with from the drop-down menu on the left, typing the letter M in the menu on the right, and then clicking Next. The Query Wizard - Sort Order window displays.

Filter Data

  1. Display the drop-down menu next to the Sort by field and select the State column. Leave the default Ascending option selected and click Next. The Query Wizard - Finish window displays.

  1. Leave the default option, within the Return Data to Microsoft Office Excel selected and click Finish. The Query Wizard closes and an Import Data dialog box opens prompting you to specify how you want to view the data in your Excel workbook.

  1. Accept the default Import Data options, which are to view the data as a Table and to put the data in the Existing worksheet, and click OK. The Import Data dialog box closes and you may see a message saying the data is being retrieved from the DSN you specified in the top row of the Excel worksheet. Afterwards, the result set of the query created as specified within the wizard opens in the Excel worksheet with the Design menu/tab in focus. Notice that only employees in Michigan (MI) and Maine (ME), the states beginning with the letter "M", are listed alphabetically by city. Also notice the format of the query results, which returned by default in Excel in the blue Table Style with the header row and banded rows Table Style Options.

  1. Save the query results in the Excel workbook by clicking the save (diskette) icon.
  2. You can perform additional manipulations on the query results in the Excel workbook. While the Query Wizard provides options to filter and sort data, the Excel application menus provide other options, such as AutoSum (basic aggregate functions), Financial, Logical, Text, Date and Time, Lookup & Reference, Math and Trig and more on the Formulas menu. There are also Sort, Filter, Group, Subtotal, Remove Duplicates, Data Validation, Consolidation and What-If Analysis on the Data tab.
  3. You also have the option to return the query results to MS Query. To do so, you select View Data or Edit Query in Microsoft Query on the Finish panel instead of Return Data to Microsoft Office Excel.

This completes the basic Microsoft Excel feature example.

Back to top of Feature Example | Back to Contents

Test Notes

This section contains information gained during testing, which may be helpful.

  1. Test Note 1: Configuring MS Query Wizard to list other objects stored in Sybase databases
  2. Test Note 2: Selecting columns from more than one table in MS Query results in error
  3. Test Note 3: IQ 15.4 Sample iqdemo Database Schema
  4. Test Note 4: Use the ODBC Administrator installed with the Sybase database client software
  5. Test Note 5: Formatting Data in Excel
  6. Test Note 6: Data Types tested and related notes

Test Note 1: Configuring the MS Query Wizard to list other objects stored in Sybase databases

By default, the MS Query Wizard only shows the list of tables on the Choose Columns panel for the specified owner in the ODBC DSN you selected on the Choose Data panel. To see other objects such as system tables and views, click the Options button on the Choose Columns panel. When the Table Options dialog box opens, select the options you want and click OK. Notice that you can filter by the objects' creator (owner). Also, notice that stored procedures are not an option here. Thus, they were assumed not to be supported and were not tested.

Test Note 2: Selecting columns from more than one table in MS Query Wizard results in error

When you choose columns from more than one table in MS Query Wizard, you will see the following error:

Workaround: To build queries with JOINs, you will have to do so manually (without the Wizard) using the menu options as follows:

  1. In the MS Query Wizard, select the ODBC DSN for the Sybase database as before.
  2. When the Choose Columns panel opens, click Cancel. It will display a message asking you if you want to continue editing the query in Microsoft Query.
  3. Click Yes and the Add Tables dialog box will open.
  4. Select all of the tables from which you want to include columns in the query and click Add. You will see them added to the top of the screen. Notice that the Microsoft Query menu options are grayed out while the Add Tables box is open.

  1. Click Close to save the added tables and dismiss the Add Tables dialog box.
  2. After you close the Add Tables dialog box, the Microsoft Query menu options become available again. Locate the Table menu, display it and select Joins from it. The Joins dialog box opens.

  1. Build the joins by selecting the columns on which you want to join the tables, setting them equal to the other desired columns and clicking Add for each one.
  2. Repeat this until you have created all the joins you want and click Close to close the Joins dialog box. You will see lines between the "joined" columns and tables.

  1. Finish building the Query using the MS Query menu options. For example, to select the columns to include in the query, from the Menu Tool Bar select Records, and then Add Column. The Insert Column dialog box opens.

  1. Select the columns you want from the drop-down list for the Field and click Insert. You have the option to give the column a different name here. As a side note, notice the Total field. It provides the option to apply basic aggregate functions (total, count and sum) to a columns.
  2. When you are finished adding the columns, click Close to dismiss the dialog box.
  3. Continue building query using the MS Query menus. Refer to the Excel on-line Help system for more information.

Test Note 3: IQ 15.4 Sample iqdemo Database Schema

With Sybase IQ 15.4, you have the option to install the 15.4 iqdemo sample database. However, it is not automatically created on UNIX and Linux. Scripts to create the sample database are included. For the certification tests, the 15.4 database was created and tested.

Test Note 4: Use the ODBC Administrator installed with the Sybase database client software

In some tests, Sybase experienced problems loading the correct IQ ODBC driver on the 64-bit Microsoft Windows operating systems. To avoid this problem, Sybase recommends using the 32-bit ODBC Administrator installed with the Sybase IQ 15.4 Database Client software. The Administrator is available with the Sybase IQ 15.4 options on the Start | Programs menu. For the 32-bit version of Excel, be sure to use the 32-bit version.

Test Note 5: Formatting Data in Excel

To format the display of data in an Excel worksheet, right-click in the worksheet and select Format Cells from the pop-up menu. The Format Cells dialog box will open. In this dialog box, you can format all types of data. For example, you can specify the alignment and font of text data, the border and fill of cells, and the display of numeric and date data. For example, for decimal data, you can specify the number decimal places to display as shown below.

Test Note 6: Data type test notes

This section contains two tables of data type information: the first lists the ANSI C/C++ numeric and string types supported by Microsoft Excel and the second lists the data types supported by IQ 15. The Excel type information is available on the following Microsoft web page:, which was the source of the information in the following table. Information about Excel types not tested is also available on this website.

Types Microsoft Excel-Supported Types
  • 8-byte double
  • short (signed short int) - for boolean and integer values
  • unsigned short (unsigned short int)
  • int (signed long int)
  • (signed) char - null terminated byte strings of up to 255 characters
  • unsigned char - length-counted byte strings of up to 255 characters
  • unsigned short - Unicode strings of up to 32,767 characters, which can be null-terminated or length-counted.

Sybase data types tested

A sampling of following Sybase IQ 15.4 supported data types were tested. Large values (where maximum values are theoretical and restricted by hardware) and minimum values as well as null and not null values were tested. The following types were tested despite the limited set of data types supported by Excel. All types were handled as expected unless noted otherwise.See Test Note 5 for information about formatting data, such as decimal values.

Types Sybase IQ 15.4 ODBC

Exact Numeric Integer Types


  • tinyint, smallint, int - Excel returned data as expected.
  • unsigned int, bigint and unsigned bigint - Not supported. Excel displayed bigint and unsigned bigint values in scientific notation by default. Using Excel to format the data as a number with zero decimal places resulted in the display of a rounded-up value. For example, the bigint value 9223372036854775807 to 9223372036854780000.

Exact Numeric Decimal Types

  • decimal(10,4), (8,8) and smallmoney - Excel returned data as expected.
  • money, dec(19,4) and (31,0) - By default Excel displayed values in scientific notation. Using Excel to format the data as a number with the decimal places as defined for the column and forcing all digits to display resulted in the display of a rounded-up value.

Approximate Numeric Float Types

float, real and double - Excel returned data as expected.
Character Types
  • char(1), (254), varchar(10), (254) and long varchar(300) - Excel returned data as expected.
  • CLOB - See LOB below
Date/Time Types
  • date, time, smalldatetime - Excel returned data as expected.
  • datetime and timestamp - as stored in IQ include month, date, year, hours, minutes, seconds and milliseconds. Excel displays the time portion of the data with hour, minutes and seconds only. Milliseconds are dropped
Binary Types bin, varbinary - Not supported in Excel. Note that initial attempts to return binary data failed. However, with IQ database option ASE_BINARY_DISPLAY='ON' enabled through a separate Interactive SQL session, Excel returned data as expected.
Bit Array Types Not supported in IQ.
Large Object Types
  • CLOB (character large object and long varchar) types were tested in the form of character strings having a length of 300 bytes - Excel returned as expected.
Multi-byte Data Tested in the form of Simplified Chinese data inserted into a varchar column in a UTF8 database. Note that the Microsoft Asian Language packs were installed on the Excel host machine and the appropriate language packs were installed on the Solaris IQ host machine - Excel returned data as expected.
User-defined Types Based on char and numeric identity types - Excel returned data as expected.

Back to top of Test Notes | Back to Contents

Issues and Incompatibilities

No issues were found during testing for which there are not workarounds. Any such issues are documented with the workarounds in the Test Notes section.

Back to Contents

Test Environment

This section provides details about the test environment.

Microsoft Excel and Sybase Database Client Machine

Hardware or Software Description or Version


Intel(R) Xeon(R) CPU E7320, 2.13 GHz processor. 32-bit VMWare virtual machine.
Memory (RAM)

4 GB

Disk 20 GB
Operating system Microsoft Windows Server 2003 Enterprise Edition SP2, 64-bit


TCP/IP Ethernet E1000

Reporting Tool

Microsoft Office Excel 2010 Trial Version 14.0.6021.1000 (32-bit) SP1. Part of Microsoft Office Professional Trial 14.0.6023.1000

Database Client software

Sybase IQ 15.4 Network Database Client for Windows, which includes the 32-bit Sybase IQ ODBC driver version


Sybase IQ Machine

Hardware or Software Description or Version
Processors Four 1.8 GHZ Ultra SPARC IV (64-bit)
Memory 32 GB RAM
Disk Space 1 TB
Network hardware and software Ethernet, TCP/IP
Operating System 64-bit Sun Solaris 10 with language packs installed

Analytics database

64-bit Sybase IQ 15.4 Enterprise Edition

Back to top of Test Environment | Back to Contents

Other Sources of Information

Sybase e-shop

Sybase provides product and technical documentation online at, including the certification reports written by the Sybase Interoperability Services Group. You may also order paper versions of product manuals through Sybase's online store,

Sybase Partner Program

For more information about the Sybase Partner Program, please visit the Sybase Partner Program web site,

Back to Contents

Vendor Contact Information

Vendor Name: Microsoft Corp.

Address: Headquarters: Redmond, WA USA


Back to Contents


Related Links

Last Revised: May 23, 2012
Product: Sybase IQ
Technical Topics: Data Marts, Data Mining, Data Analysis, Database Admin, Data Management, Accelerated Decision Making
Business or Technical: Technical
Content Id: 1098468
Infotype: Partner Certification Report

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics