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?
View Complete Post
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.
- 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
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 10.2.0.3 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
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.
I have create a subscription (pull, merge replication) on SQL Server 2008 R2 Express Edition to a publication on SQL Server 2008 R2 Enterprise Edition. I have seen that the subscription has been enabled for Windows Sychronization Manager as I can see in
its property dialog. However, I cannot see it in the Sync Center of Windows 7 on the Subscriber.
Does Sync Center support in this scenario? From
another thread, it seems we need to use Windows Task Manager. If so, how can I set it up?
Thanks in advance.
Currently there are two developers working on a VB.NET desktop application that accesses a number of SQL Server databases. We have recently added SQL Server script files (for stored procedures, tables, agent jobs, views and functions) to SourceSafe
(VSS). This finally gives us a way to store sql source code with change tracking, centralized shared access, etc. - a vast improvement over the previous method of manually trying to keep track of all the code components of our database development
We have a "solution" defined in SQL Server that has three projects: right now we have one project for each of three databases that we are accessing. The projects were created as "SQL Server Scripts" so they have Connections, Queries and Miscellaneous
folders. The Queries folders currently contain stored procedures used in each of the three databases. In all we have about 200 stored procedures.
However, we have not yet figured out a bullet proof way to keep the source in VSS in sync with the code that SQl Server is actually storing and executing. If I open a stored procedure for edit by double-clicking the file shown in the Solution frame, it gives
me the stored procedure in VSS. If I edit it and then click on EXECUTE to actually ALTER the procedure in SQL Server and then check-in the
We created a publication on SQL Server 2008 R2 (Dev. Edition). Then we tried to create a pull-merge subscription on the client which should create an entry in Synchronization Center (Win 7) as well under Windows Explorer –
Synchronization (Win XP).
This scenario works if there is only our instance of SQL Server installed on the client.
If there is another instance (default or another named instance) installed before we create our instance with subscription there is following strange situation:
Subscription is created properly within Management Studio, property for showing in sync center set to enabled.
No entries created in Synchronization Center or Win Explorer Synch.
I am running SQL Server 2008 Express R2 64-bit on Windows 7 x64, and I've created a subscription to a database publication on a Windows Server 2008 with SQL Server 2008 R2 standard. The subscription created fine on the Windows 7 machine in SQL Server Management,
but it does not display in the Sync Center. I have set the subscription to "Use Windows Synchronization Manager" and recreated the subscription several times, but it never shows up.
I've searched for hours and I cannot find any answers to this. Has anyone else run into this problem? Is there a step I'm missing?
Hello, I am trying to migrate from a "SQL Server 2005" to a "SQL Server 2008 R2" on 64 bits. In my old SQL 2005, I have a link to a Oracle Server via MSDAORA "Microsoft
OLE DB Provider for Oracle", but this option does not come in the new SQL Server 2008. I tried with the Oracle's provider, but does not work, and in this forums I read that I should use the "Microsoft OLE DB Provider for Oracle", How can I get "Microsoft OLE
DB Provider for Oracle" for a "SQL Server 2008 R2" on 64 bits?
I have 2 boxes running SQL Server 2008 and using linked Oracle OLEDB that behaves different for same query.
On 1st machine, using win 2003 , SQL Server 2008 SP1 on a 64-bit server I I created a linked server to Oracle database using Oracle OLEDB provider. When I submit query to a Oracle database using Oracle OLEBD using WHERE clause, this WHERE clause
is sent to Oracle, then it uses an index to resolve query and send result already filttered to SQL Server.
I have a 2nd box running Windows 2008 R2, SQL 2008 SP2 on a 64-bit server and queries are working fine, but when I filter some column of a linked server table using WHERE clause, SQL Server sends to Oracle SELECT without where clause, Oracle performs a table
scan on that table and then sends results to SQL Server, that applies filter to the result.
The problem is that some Oracle tables are huge and sending a SELECT without where clause causes table scans on Oracle, what should be avoided.
I already tried to change OLEDB properties and also tried to change linked server properties, but none of them solved the problem.
Does anyone knows what I should change on 2nd server to make it works like 1st server?