Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » Excel Pivot Table shows zeros from a SQL View...

12 posts, Page 1 of 2

1 2 »»

Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server

Rate Topic

Display Mode

Topic Options

Author

Message

JesseDW

Posted Tuesday, August 21, 2012 5:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 12:22 PM
Points: 8, Visits: 53

I have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without installing the Oracle Client on my users' machines. So I created a SQL Server 2008 R2 view that queries the Oracle view through a Linked Server. I can run the view in Mgt. Studio. It looks good....I can see the text fields as well as the numeric fields. When I connect to this SQL Server view with the Pivot Table, the numbers disappear.....all zeros! The text fields all look good. I tried to connect with the regular SQL Server Native driver as well as with ODBC and Microsoft Query with the same result. If I connect the Pivot Table directly to Oracle, there is no issue. For whatever reason, this scenario is not yielding results in a Google search. I thought I would check here to see if anyone has seen this before.

Thank you for your help!

Post #1348134

PaulB-TheOneAndOnly

Posted Wednesday, August 22, 2012 7:23 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 2,982, Visits: 4,397

JesseDW (8/21/2012)

...If I connect the Pivot Table directly to Oracle, there is no issue.

...which means it is not an Oracle side issue.

This issue may be better addressed on an Excel forum - I would also consider opening a case with Microsoft.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1348384

JesseDW

Posted Wednesday, August 22, 2012 8:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 12:22 PM
Points: 8, Visits: 53

It is probably something that Excel should handle. It seems like a UniCode issue, but the source data-type is just Number. I thought there was also a possiblity that the problem could be in my SQL view code or linked-server settings. I included the SQL view code below. I spelled out the fields instead of using *, but it still has the problem.

CREATE VIEW [dbo].[vwIMPACT_FEE_DATA]
AS

SELECT [Applicant]
,[Phone]
,[Address Line 1]
,[Address Line 2]
,[City]
,[State]
,[Zip Code]
,[Premise Address]
......
,[Meter Size]
,[Water Flow]
,[Water Supply]
,[Water System Dev]
,[WW Treatment]
,[WW Collection]
,[Service Line Total]
FROM HANSEN..SAWS.VW_IMPACT_FEE_DATA

GO

Post #1348435

PaulB-TheOneAndOnly

Posted Wednesday, August 22, 2012 8:58 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 2,982, Visits: 4,397

Okay - we already know it is not an Oracle issue and also that it is not a linked server issue; we know this 'cause you stated that you can query the Oracle view from SQL Server, is that correct?

What happens if you query the SQL Server view on SQL Server? do you see the data?

If the answer is NO then the issue is with the view - SQL Server issue.

If the answer is YES then the issue is with Excel

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1348475

JesseDW

Posted Wednesday, August 22, 2012 9:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 12:22 PM
Points: 8, Visits: 53

I can query the SQL view in Mgt. Studio and see all of the numbers.

Excel Pivot Tables work for views that are not using linked servers pointing to Oracle so....
It is either...

1. An Excel bug when connecting to this type of view--most likely
2. Excel requires a certain setup on the view and/or linked server in order to work properly

Does that make sense?

Post #1348511

PaulB-TheOneAndOnly

Posted Wednesday, August 22, 2012 11:21 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 2,982, Visits: 4,397

JesseDW (8/22/2012)

Excel shouldn't even know the SQL Server view is a view pointing to an Oracle view via a linked server -- I agree the issue is most likely around the Excel piece. If after a reasonable amount of time solution still refuses to work I would open a case with Microsoft.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1348590

JesseDW

Posted Tuesday, August 28, 2012 8:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 12:22 PM
Points: 8, Visits: 53

I thought I would share the answer, although I am still left with questions.

Someone on another forum suggested the following to get more insight into what is happening:

select * into your_table from [dbo].[vwIMPACT_FEE_DATA]

The table it created consisted of all nvarchar fields--no number fields.

I also tried drilling into the pivot table to see the raw data. All of the number fields were left justified--an Excel indication of a text field.

It appeared that SQL Server is unable to detect the Oracle data types from an Oracle view through the linked server. I added some casting to the SQL Server view to allow the Pivot table to see the data as Numbers:

, CAST([Water Flow] AS Numeric) AS "Water Flow"

I question why I would have to do this. Why can't SQL Server detect the data types in the Oracle view through the Linked Server?

Post #1351013

Jo Pattyn

Posted Tuesday, August 28, 2012 2:41 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:46 PM
Points: 1,329, Visits: 4,303

Check: linked server provider should be OracleOleDB instead of MS provider for Oracle

Post #1351263

JesseDW

Posted Tuesday, August 28, 2012 3:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 12:22 PM
Points: 8, Visits: 53

Thank you, Jo!

The driver/Provider for my linked server is "Oracle Provider for OLE DB".

Post #1351279

Jo Pattyn

Posted Tuesday, August 28, 2012 3:54 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:46 PM
Points: 1,329, Visits: 4,303

Someone on another forum suggested the following to get more insight into what is happening:
select * into your_table from [dbo].[vwIMPACT_FEE_DATA]

Can you retrieve definition of the datasource(s) on the oracle side on which vwIMPACT_FEE_DATA is built upon. Oracle mentions USER_TAB_COLUMNS for retrieving column definitions.
As datatypes differ between relational databases, mssql server may have to create it own (nvarchar...)

Number as Text oddity in Excel from SSIS

Post #1351310

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions