Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 5:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 9:56 AM
Points: 8, Visits: 55
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
Posted Wednesday, August 22, 2012 7:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Wednesday, August 22, 2012 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 9:56 AM
Points: 8, Visits: 55
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
Posted Wednesday, August 22, 2012 8:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Wednesday, August 22, 2012 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 9:56 AM
Points: 8, Visits: 55
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
Posted Wednesday, August 22, 2012 11:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
JesseDW (8/22/2012)
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?


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
Posted Tuesday, August 28, 2012 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 9:56 AM
Points: 8, Visits: 55
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
Posted Tuesday, August 28, 2012 2:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 1,386, Visits: 6,243
Check: linked server provider should be OracleOleDB instead of MS provider for Oracle
Post #1351263
Posted Tuesday, August 28, 2012 3:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 9:56 AM
Points: 8, Visits: 55
Thank you, Jo!

The driver/Provider for my linked server is "Oracle Provider for OLE DB".
Post #1351279
Posted Tuesday, August 28, 2012 3:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 1,386, Visits: 6,243
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 »

Add to briefcase 12»»

Permissions Expand / Collapse