.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

View Complete Post

More Related Resource Links

How to query for id's of the fields with List Data Retrival Services Query() web service

Since i marked a post as answer in my previous topic, it seems that people dont visit the topic anymore as they see it as "answered" while it actually is only partially answered. So i guess i have to ask again in a different topic.    I need to get data from Sharepoint Server (3.0 i belive) for my Integration Services project. For that i use Sharepoint's webservices. I've read tutorials and downloaded sample programs to talk to WSS web services. I realise i have two ways to get items in a list: Use Query() or GetListItems(). Problem is, GetListItems() has a mandatory "Row limit" field and i might have to import ALOT of fields, so i thought Query() is better for me.  But, when using GetListItems(), the WSS returns the ID of the fields (for example, for a field of type user, something like "395;Ivanov Ivan Ivanovich" is returned) but when using Query() for the same list, WSS returns only the field value ("Ivanov Ivan Ivanovich") without the ID. I need it to return the ID as well. How can i set it up, on my side or server side, so i would receive the ID's, not only the values of the fields that are actually linking to something by ID when i use Query() web service?     

Analysis Service Process Dimension Cause : OLE DB error: OLE DB or ODBC error: ORA-00942: table or v

Dear Gurus, I'd face a "STRANGE" problem. I'm using SQL2008 connect to Oracle Data Source (OracleOldDB.1) When I process a snow-flake dimension . There is a ORA-00942 problem. But if I past the SQL Statement to a Oracle Tool . It worked without any problem. Does any body ever face this "STRANGE" problem ? And I'd try to remove this table from data-source-view and re-join it . Still same problem !!  Wilson

Analysis Services Using Oracle as a Data Source Data Types mismatch System.Int32 and System.Int64

Hi everyone, I searched for this issue but there is only 4 questions about it in this forum from 2007 I hope that someone can help me. Im usign SASS 2008 using a Oracle as a datasource.  I already have my fact tables linked with dimensions, lets say, FACTID System.Int64 with DIMENSIONID System.Int64 (My dimensions are replaced by named querys and work perfectly) I add a new table to my DSV and the NEWDIMENSIONID looks like System.Int64, but at the moment I replace the table with a named query (I have to use Oracle quering) the NEWDIMENSIONID changes to System.Int32 .  After that I cant relate the fact table with the dimension table. Actions: Looked for the columns that relate from Oracle... both are NUMBER(5). Looked for the differences between my other tables that are already related as System.Int64 and there is not a single difference between the ones that works and the one who doesnt. Solutions Ive tried and didnt work: 1. Cast the column as SQL query didnt work it send an error.  Looks kind of obvious because we are quering Oracle... but I had to try.  CAST( X AS BIGINT) 2. Cast the column as Oracle query . TO_NUMBER 3. Do it inside the server using the Analysis Services Enterprise insted of my Analysis Services Developer. Same issue.   Any ideas? suggestions?   Please? Thank you very much ;) Sincerly Adriana L. PS. At the begging of

Ms sql query for this type of table structure

Hi This is my Table structure Field1       Filed2        001          A001          B001          C 001          D002          A003          B 003          C I need MS SQL query I need to show Field1 which has B and Not in DI mean 003 will shown coz it has B still D not comes... and 001 ,002 will not shown Coz 001 has B and D and 002 will not shown coz still it doesnt have B..Need Query... 

Merge replication - table with geography data type



I have a problem with geography data type replication. Any change that is done on a Geography column breaks the replication - the error message is "The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber." I double checked compatibility settings that is suggested here: http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/d8e31621-5d43-48cd-bdd3-c02b8a24625c. But both publisher and subscriber are 100RTM.

I checked synchronization logs and found a strange message - attempt to convert varchar to geography. I don't understand from where the varchar data type comes from. Tables at subscriber and publisher has the same format - geometry stored in geography data type.

2010-09-20 18:36:22.499 The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.
2010-09-20 18:36:22.845 Category:NULL
Source:  Merge Replication Provider
Number:  -2147199398
Message: The

How to submit data from repeating table to an SAP web service


Hi ,

                 I am new to infopath. I have a porblem here while submitting data from a repeating table to an SAP web service to insert data into the fields in the web service. Can any one suggest me how to submit data from a repeating table to SAP using an SAP web service. I need to use a web service which is created by SAP group. My job is to map the fields in table and in the web service. SAP web service contains a table which can get the data from our repeating table. Any help would be greatly appreciated. If you are confused with my question please let me know.



Passing array or table data type


I need to pass many records with number of fields(i.e 5rows and 5 columns) in either array or table form to oracle for processing. Is it possible to pass array/table from asp.net pages? If no, any solutions for this? I have been thinking of passing it as string concatenated with delimiter and split it.  Seems like it is not appropriate as i have to pass 5 strings(5rows) Any better suggestion? Thanks in advance

Webmatrix.Data.Database.Query() method return type


I'm using the Webmatrix.Data.Database.Query method to return a result set in the following form

var myResult = db.Query("Select * from mytable");

How do you cast myResult to get the count of records returned? I'd like to do something like


View data type doesn't reflect table


I'm not sure if this is the place for this question, but here goes.

I am running SQL Server 2005, and I've encountered an issue with view data types.  I had a client who asked if they could allow for decimals in a field that was previously an integer.  This meant going and changing the column data type on a few tables from integer to float, and changing some parameters and variables within some stored procs/functions to floats.  Somewhere the value is still being rounded down, so I queried the information_schema.columns and found about 60 views in the system that reference that column and are still being considered an int, from the tables which were already converted to float.  I've found that if I simply script the views as alter and run it the column no longer is considered an int in the information schema.

My question is this: Does this seem to be my problem, that when the view is referenced the value is changed to an int? Or does the view simply reflect the table's data type and the information schema is incorrect?  If the former, is there a faster way to update all these views without going one by one and scripting them? Thanks in advance.

web service data type interoperability


Is there a good example that shows how to develop web services to not return a generic object or dataset but rather return more basic datatypes so that it has better interoperability with non-.NET consumers? So lets say I want to return a list of customers but want a list with basic datatypes such as strings, int, etc. rather than a dataset. Any examples on this?


is it possible to get customer level analysis from service level fact table?


I have a service fact table

select 1 as serviceid,1 as [week],1 as gain,0 as loss,1 as onsupply UNION
select 1, 2, 0, 0, 1 UNION
select 2, 2, 1, 0, 1 UNION
select 3, 2, 1, 0, 1 UNION
select 1, 3, 0, 1, 1 UNION
select 2, 3, 0, 0, 1 UNION
select 3, 3, 0, 0, 1 UNION
select 2, 4, 0, 1, 1 UNION
select 3, 4, 0, 0, 1

each service has a related customer

select 1 as supplyid , 1 as customerid UNION
select 2,1 UNION
select 3,2


I have created a cube which allows me to product the following services analysis as an output (a service is on supply for a week it was live at any point during the week)

select 1 as week, 1 as gain, 0 as loss, 1 as supply UNION
select 2, 2, 0, 3 UNION
select 3, 0, 1, 3 UNION
select 4, 0, 1, 2 UNION
select 5, 0, 0, 1


However I would like to do a similar thing at customer level to get the following out put

select 1 as week, 1 as gain, 0 as loss, 1 as onsupply UNION
select 2, 0, 0, 1 UNION
select 3, 0, 0, 2 UNION
select 4, 0, 1, 2 UNION
select 5, 0, 0, 1

A customer is gained if a supply is created for a new customer (doesnt have any existing services). A customer is lossed if a service is a loss and the customer has no other active services. A customer is 'on supply' if he has a service active at any point in the reporting week.

Is it possible

MSSCrawlURL table is filled ErrorID: 560 Error in the Site Data Web Service; Any ideas why?


I'm performing fullcrawl and I was examining results in table: MSSCrawlURL.  I found that table is filled ErrorID: 560 - Error in the Site Data Web Service. Does anyone have any ideas what maybe causing this issue?

Here is the SQL statement I ran:

FROM [WSS_Search_CC-CLINTRA54].[dbo].[MSSCrawlURL] where ErrorID != 0 and DisplayURL like '%sharepoint.ccf.org%'
Group by ErrorID

Totals ErrorID
5        3
2        4
37753 560


Crawl Error - Error in the Site Data Web Service. (Exception of type 'System.OutOfMemoryException'


I have a Sharepoint site that includes a WIki library.  Recently searches have stopped returning any hits from the Wiki pages.  (Search hits from documents and discussions are correctly returned.)  When I check the crawl log from Shared Services Admin site, I see the following error every time a crawl is performed:



Error in the Site Data Web Service. (Exception of type 'System.OutOfMemoryException' was thrown.)


The URL above is part of the Wiki library.  When I look at the raw Sharepoint logs, I see the following entries every time a crawl is performed:


11/26/2007 05:01:50.93  mssdmn.exe (0x1F98)                      0x1368 Search Server Common           MS Search Indexing             7hp2 Monitorable EnumerateListFolder fail. error 2147755542, strWebUrl http://cao.tfn.com/Caopedia, strListName {e9acf8e9-f23f-4afd-b8e9-6eaf6b0101db}, strFolder  
11/26/2007 05:01:50.93  mssdmn.exe (0x1F98)                      0x1368 Search Server Com

Accessing Oracle Table data and updating using SharePoint 2010 List



We are trying to access Oracle database table through SharePoint 2010 List. The functionality required is, the user should be able to read the data from the Oracle table in to the SharePoint 2010 List and should also be allowed to update the list and in turn the data should be updated back in to the oracle table.

Over and above any changes made to the oracle table by users, should be updated in the sharepoint 2010 list. Which means the users require a two synchronisation from SharePoint 2010 List to Oracle table and vice versa.

let us know if anyone has worked on this before.


copy data (table) SQL server to Oracle


I want to copy or transfer data SQL server to Oracle 10 G. any body can help me.


Update Query ? Incorrect use of the xml data type method 'modify'. A non-mutator method is expecte


I have a column of ntext da


ta type and NOT XML. It stores all xml data. I need to update xml node in the records. It throws an error saying "Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context."

 begin transaction
declare @Cps_Id int;
set @Cps_Id = 236;
declare @Cps_Message nvarchar(1024);
set @Cps_Message = 'updating cpsia message with smoking';

update table_name
set column_name = CONVERT(xml,column_name).modify('replace value of (/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]/CpsiaMessage/text())[1] with sql:variable("@Cps_Message")')
WHERE Convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]')=1


Sample XML:

<strong><root> <ProductInformation> <Name> Truck with Battery Charger</Name> <Description>Fr.</Description> <CPSIA> <CpsiaDetails> <Item> <CpsiaId>456</CpsiaId> <CpsiaMessage>waring</CpsiaMessage> </Item> <Item> <CpsiaId>236</CpsiaId> <CpsiaMessage>to health</CpsiaMessage> </Item&

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend