SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
JesseDW
JesseDW
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 56
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!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5353 Visits: 4639
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.
JesseDW
JesseDW
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 56
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5353 Visits: 4639
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.
JesseDW
JesseDW
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 56
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?
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5353 Visits: 4639
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.
JesseDW
JesseDW
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 56
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?
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2742 Visits: 9899
Check: linked server provider should be OracleOleDB instead of MS provider for Oracle
JesseDW
JesseDW
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 56
Thank you, Jo!

The driver/Provider for my linked server is "Oracle Provider for OLE DB".
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2742 Visits: 9899
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search