August 21, 2012 at 5:02 pm
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!
August 22, 2012 at 7:23 am
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.August 22, 2012 at 8:17 am
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
August 22, 2012 at 8:58 am
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.August 22, 2012 at 9:27 am
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?
August 22, 2012 at 11:21 am
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.August 28, 2012 at 8:57 am
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?
August 28, 2012 at 2:41 pm
Check: linked server provider should be OracleOleDB instead of MS provider for Oracle
August 28, 2012 at 3:03 pm
Thank you, Jo!
The driver/Provider for my linked server is "Oracle Provider for OLE DB".
August 28, 2012 at 3:54 pm
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...)
August 28, 2012 at 4:42 pm
Below is the info you were looking for....a describe in PL/SQL developer did the job.
One thing to note that I successfully hooked up a pivot table directly to the Oracle view. I just can't install the client on my users' machines.
applicant, nvarchar2(194), y, , ,
phone, nvarchar2(30), y, , ,
address line 1, nvarchar2(40), y, , ,
address line 2, nvarchar2(40), y, , ,
city, nvarchar2(30), y, , ,
state, nvarchar2(30), y, , ,
zip code, nvarchar2(20), y, , ,
premise address, nvarchar2(112), y, , ,
lot number, nvarchar2(15), y, , ,
block number, nvarchar2(15), y, , ,
ncb/cb number, nvarchar2(10), y, , ,
application number, nvarchar2(25), y, , ,
application status, nvarchar2(10), y, , ,
utility service rep, nvarchar2(30), y, , ,
residence units, number, y, , ,
application category, varchar2(7), y, , ,
plat number, nvarchar2(10), y, , ,
icl/ocl, varchar2(3), y, , ,
receive month, varchar2(12), y, , ,
receive year, varchar2(7), y, , ,
receive date, date, y, , ,
land use, nvarchar2(10), y, , ,
single-family-resident, varchar2(1), y, , ,
multi-family-resident, varchar2(1), y, , ,
non-resident, varchar2(1), y, , ,
release month, varchar2(12), y, , ,
release year, varchar2(7), y, , ,
release date, date, y, , ,
application type, varchar2(5), y, , ,
service area, nvarchar2(10), y, , ,
pressure zone, nvarchar2(10), y, , ,
rate year, nvarchar2(10), y, , ,
edu label, varchar2(10), y, , ,
edus, number, y, , ,
service line numbers, varchar2(4000), y, , ,
ccma ww treatment, number, y, , ,
pro-rata, number, y, , ,
local benefit, number, y, , ,
meter install fee, number, y, , ,
misc fee, number, y, , ,
transaction adjustments, number, y, , ,
statement total, number, y, , ,
barcode, varchar2(30), y, , ,
meter size, nvarchar2(10), y, , ,
water flow, number, y, , ,
water supply, number, y, , ,
water system dev, number, y, , ,
ww treatment, number, y, , ,
ww collection, number, y, , ,
service line total, number, y, ,
August 29, 2012 at 1:21 pm
Workaround is your shared answer: proper sql server casting the oracle datatypes.
Could you check if NUMBER in oracle all have a scale, precision specified? It was troublesome (2000 era) when precision or scale was not specified.
SELECT owner,table_name,column_name, data_type,precision,scale from ALL_TAB_COLUMNS where table_name= 'MYSOURCE' *** table_name could be case sensitive
Should read something like "myowner","statement total",NUMBER,5,0 and hope it can be contained in sql server int,bigint, numeric...
It is also mentioned as a bug in sql2005 without sp3
You can use the free oracle sql developer tool to view oracle definitions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply