.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

VPD - Oracle versus Ms SQL 2008

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server


We are currently reviewing our achitecture which is currently under Oracle 10G and thinking about Ms SQL 2008.    One of the concept that we would like to keep is Oracle's Virtual Private Databases (VPD) which allows us to make a select/update/... in function of a context.  


An example of it is if I have a web interface for 10 000 corporations and I want them to see only their information, I create a virtual database where on each table I have a corp_id included.      If one of these corporation logs into my web site, they will only be able to see their data even if the programmer forgot where corp_id = x  Oracle do it for them.


Is there something similar in Ms SQL 2008 ?





View Complete Post

More Related Resource Links

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

SQL server 2008 to oracle replication

Hi, I am trying to replicate data from a sqlserver to an oracle 11 subscriber(push subscription). I recently moved to SQL server 2008 from 2005. The replication in 2005 worked ok, and now in 2008 - it does not. The problem: It tries to make some fields as an interval data type in Oracle. For example, nvarchar(3) are turned into interval and numeric(38,8) fields are turned into nvarchar2(40). Anyone has any idea why is this happening and how can I change it?

Transforing the Data from Oracle to SQL Server 2008

Hi all I have to create a SSIS package which can Transfer the Data from Oracle to SQL Server 2008. Every time when packages run it will suppose to create tables in SQL Server Database. I guess it is possible with export and Import, but the privies tables suppose be deleting before or after dumping.   Suggest me best method to do this.     Thanks in advance     SNIVAS

Server 2008 R2 64 bit, SSRS 2008 64 bit with Report Builder 3.0 issues with Oracle OLE Driver

Folks, ran out of idea on this issues. Setup as shown at the Title. I can connect via VS 2010  but report builder cannot run properly when setup with connection string. I use a 32 bit Oracle Driver and VS 2010 works well with it, however, Report Builder on SSRS report a bad image issues while attempting to use the same driver. What have been tried: 1-Already attempted to try to download Oracle ODP drivers 64 bit, but current driver 11g and minor version above 11g does not install, installation failed. So far all chats indicated that Server 2008 R2 does not have a Oracle 64 bit driver provided y Oracle. Any help is appreciated ....    

SSRS 2008 with Oracle

Hey all, Quick question. I'm getting ready to configure a SSRS 2008 instance and most of the data I'll be accessing is in an Oracle DW. The SQL Server instance is running on a Win2k3 server. Do I need to install Oracle client on this is server? Are there any other considerations for configuring ssrs with Oracle? Thanks!

Microsoft ole provider for oracle is not available in my VS 2008 list of providers when setting up C

Hi, Trying to set up a connection manager using Microsoft OLE Provider for Oracle, but it is not in the list of Providers. I do have Oracle Provider for OLE DB. But I need the former. I tried installing MDAC 2.8 but I get a message saying that it cannot be installed because it is part of the OS already. So, how do I get Microsoft OLE Provider for Oracle as an available provider? Thanks!--ACG

Selecting ODP.NET (provider for Oracle) in Reporting Services 2008 R2 x64, Report Builder 3.0, Busin

We have a requirement to connect Reporting Services 2008 R2 x64 + Report Builder 3.0 to Oracle database and also to build a web application which connects to Oracle. I installed ODAC (Oracle Data Access Components) which contains ODP.NET (Oracle Data Provider for .NET) and Instant Client (required by the provider). Now the problem is how to make Report Designer, Reporting Services and Report Builder 3.0 to add ODP.NET as an Oracle provider in the drop down list contained by the Data Source Wizard? I found out that Reporting Services and Report Builder work based on Data Extensions so I added Oracle.DataAccess as extension to RsReportServer.config and RsReportDesigner.config as shown in: http://www.bigresource.com/Tracker/Track-ms_sql-aIXqXMEE/. By changing RsReportDesigner the ODP.NET appeared in the list for Report Designer (Business Intelligence Studio) but it couldn't be used because an error was thrown as soon as Test Connection was pressed stating that the provider is not registered with the application. With these changes ODP.NET does not appear in the list of providers for Reporting Services and Report Builder 3.0. For now I managed to connect to Oracle through .NET Provider for Oracle (provided by default) but this seems to be deprecated and in Report Builder 3.0 I can't select tables to be added to the query, I'm prompted only with a blank textbox and one excla

2008 SSRS (R2) Oracle Client issue



We have installed SSRS 2008 R2 on 64 bit windows 2003 server, and the 64 bit oracle client to connect to oracle databases.  The tnsping, and SQL plus are working from the server.  But when we trying to access the same database from report manager using new Datasource, we will get the below error.

"Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed."

We have installed only the 64 bit oracle client, and the Oracle folders are given with proper permissions to access it for SQL serving account which is used to run SSRS.

Any help greatly apprciated.  Thank you.

Sync Oracle and SQL Server 2008


Does anyone know of a way that I could sync a few contact info views from an OracleDB to SQL Server without spending thousands on syncing software?

Best Regards

Replicate data from Oracle 10g to SQL 2008?



I need to replicate about 20 tables from Oracle 10g to SQL 2008 nightly.  I wonder if anyone knows or have done
this kind of task before or know what web site showing steps by steps setting it up.  I hear that you can use SSIS
to setup the task but never try it before.  I try to google it but nothing really showing how to set them up.
Any helps would greatly appreciate.



VisualStudio 2008 64bit in windows 2008 r2 64 bit oracle does not connect to databas



I am currently using visualstudio 2008 with 3.5 framework sp1 release.

We want to migrate our applications to the new server everything as a 64 bit. which is having the following configuration 

windows 2008 server  R2 64 bit with Oracle as backend.

We donot have any 32 bit oracle client tools installed.I am able to connect to the database by using a console/windows/class library applications

When I try to simply connect to the database(open and close the connection) using either a website or a webapplication.I am getting the following different types of exceptions

Oracle client and networking components were not found. These components
are supplied by Oracle Corporation and are part of the Oracle version
7.3.3 or later client software installation.

Provider is unable to function until these components are installed.
Attempt to load Oracle client libraries threw BadImageFormatException.
This problem will occur when running in 64 bit mode with the 32 bit
Oracle client components installed.

Sql Server 2008 (64bit) on Windows Server 2008 with a connection to Oracle 10g (64bit)



I know there are similar questions posed but none that provided a solution to my particular problem.

I am upgrading a SSIS package from Sql Server 2005 (32bit) to Sql Server 2008 (64bit) on Windows Server 2008 R2. It worked on the Sql Server 2005 machine. This package connects to an Oracle 10g (64bit) database.

The Oracle 11g client and networking tools are installed (by the Oracle DBA). I tested my connection on the sql server 2008 machine using SqlPlus and doing a tnsping. I can also create a Windows ODBC datasource connection to the Oracle 10g database.

The package executes properly on my local computer XP (32bit) running bids 2008.

The package fails on the server in SSIS. I need to be able to schedule the package in a job so it must run in SSIS.

I have also tired to create a connection manager to the Windows ODBC oracle connection without success. It is blank and does not allow me to select or refresh to view the Windows ODBC datasources.

Any assistance would be greatly appreciated. I have seen other forums where installing the Oracle 11g ODAC and Oracle Developer Tools for Visual Studio might assist in creating the package. But I need assistance in running the package from SSIS and not BIDS.






Error deploying SSAS 2008 cube connected to oracle client


I've build a cube in BIDS 2008 on Windows 7 machine connecting to oracle data source. I've 32-bit oracle client on my machine. I'm able to create the data source, data source view, cube successfully. I can also explore the data in data source view. But when I try to deploy the cube on my SSAS 2008 server, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Attempt to load Oracle client libraries threw BadImageFormatException.  This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.;An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B).


Errors in the high-level relational engine. A connection could not be made
to the data source with the DataSourceID of '', Name of ''.

Errors in the OLAP storage engine: An error occurred while the dimension,
with the ID of '', Name of '' was being processed. <

Clustered SQL 2008 R2 x64, Oracle linked server works on one node, fails on the other


Juicy bits:

- SQL Server 2008 R2 x64

- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

- Active/passive cluster

- Oracle client 11gR1

- "Allow Inprocess" provider option is enabled


I'm stumped.  Our development, QA and UAT environments are all running the same versions of Windows, SQL, and Oracle client.  Linked server between SQL and Oracle works just fine.


Production is clustered.  The linked server works just fine on one node of the cluster, but if we failover to the other node, attempts to use the linked server in any way, OPENQUERY, GUI, etc..., cause the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message "ORA-12634: Memory allocation failed".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".


What am I missing?  I've reinstalled the Oracle client on the defective node, verified that the SQL Server service account has full rights to the Oracle client dir

Error on mapping oracle varchar2 to sql server 2008 using import facility, defaults to type 129 in m



I am trying to use the import wizard to import an oracle 10g table into an sql server 2008 (10.0.2531) database, it comes up with the error 'data types of source columns were not mapped correctly to those available on the destination provider'. When I go into 'edit mappings' it shows all the varchar2 oracle columns mapping to type 129 ? all the other columns have normal mapping types. If i go through and change 129 to varchar the import will work ok.

I understand that in c:\Program files\microsoft sql server\100\dts\mappingfiles there is a series of files that map datatypes to/from different sources, there are 3 files

oracleclienttomssql , oracleclienttomssql10 , oracleclienttossis10 which have a varchar2 to nvarchar mapping

and 2 other files  oracletomssql ,  oracletomssql10 which map varchar2 to varchar and one other file, oracletossis10 which maps varchar2 to dt_str

I running the import on a server with an Oracle client installed with sql server 2008 (10.0.2531).

I am not sure which mapping file is being used ? and whichever one is being used it should provide a mapping rather than type 129 (whatever that is).

I would like to get this mapping resolved as I have many tables to import and over time the structure of the Oracle tables will change so I want to use import as the easiest optio

datatype tranformation issue in oracle to sql 2008 migration using SSIS


Hello friends

I am doing oracle to SQL2008 R2 migration and i want to use SSIS.

Oracle has mainly three datatypes DATE, VARCHAR2 and number. 

I tried without using the transformation it worked but the datatypes like date did not changed to datetime in sql server, even the number datatype remain same.

secondly while doing very first time i got very weired name of the table on sql side. 

Instead of going to dbo schema it shows the name like tom\myname.A_RETURNS.

So how can i resolve both the datatype and naming issue?

Thanks in advance..


Microsoft OLE DB Provider for Oracle 64-Bit, SQL Server 2008: Does it exist?



Does anyone know if the "Microsoft OLE DB Provider for Oracle" in 64-Bit Windows exists in SQL Server 2008 64-bit?  It only existed in 32-bit SQL 2005,  which was a big problem.    Needed for Linked Server creation.  We do not want to use OPENQUERY calls with the native Oracle 10G driver,  too many coding changes.


Thank You

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