Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server
Jul 30, 2007
Hi,
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.
Please let me know.
Thanks
View 15 Replies
ADVERTISEMENT
Oct 20, 2006
Hi All,
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
Help!!!
View 8 Replies
View Related
Aug 19, 2007
Hi,
I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.
But after Importation it is showing Junk values. Please advise me what step I should take next.
Regards
Azeem
View 5 Replies
View Related
Sep 11, 2015
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
View 7 Replies
View Related
Jan 26, 2007
Hi all
I have a big problem with creating a linked server to ORACLE database!
- I have installed ORACLE Client 10g on my server (Windows Server 2003 64 bit)
- With ORACLE Tools I am able to connect to the ORACLE Server
- When I try to connect in SQL Server I get the following error:
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"MISPROD.WORLD".
I have tried I guess all combinations of getting data out of ORACLE ... :-(
For example:
- SELECT * FROM OPENQUERY([MISPROD.WORLD], 'SELECT * from ORACLE_TABLE')
- SELECT * FROM [MISPROD.WORLD]...ORACLE_TABLE
I allways get the same error and I dont know what to do ...
Would by very very happy for any comment.
Best regards
Frank Uray
View 1 Replies
View Related
Jun 9, 2006
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
View 4 Replies
View Related
Jun 8, 2006
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.
View 3 Replies
View Related
Mar 14, 2007
Hello all.
I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.
We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:
=====================
OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".
Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".
=====================
A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.
Thanks,
Adam
View 3 Replies
View Related
Mar 5, 2007
Hi,
I created a linked server as follows:
EXEC sp_addlinkedserver 'OracleLinkedServer', 'Oracle', 'MSDAORA', 'fcstage'
EXEC sp_addlinkedsrvlogin 'OracleLinkedServer', false, 'SA', 'fc_stage', 'password'
Now I try firing a simple select statement
SELECT FINANCIAL_TRX_INFO_ID FROM
[OracleLinkedServer]..[FC_STAGE].[WFS_FINANCIAL_TRX_INFO]
WHERE SFS_BUSINESS_SEGMENT IS NOT NULL
But I get the following error:
OLE DB provider "MSDAORA" for linked server "OracleLinkedServer" returned message "ORA-01426: numeric overflow
".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OracleLinkedServer".
This seems to be a generic error statement. Can anyone tell me where am I going wrong.
Thanks.
View 1 Replies
View Related
Sep 15, 2011
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
View 4 Replies
View Related
Aug 19, 2015
I wanna to create a linked server which connect to oracle. Â Here are my steps:
1.Install Oracel drivers Oracle Data Access Components.
2.Configure the Oracle Ole DB provider to run inside the SQL Server process and configure it to accept parameters. exec
master.dbo.sp_MSset_oledb_prop
'ORAOLEDB.Oracle',
N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop
'ORAOLEDB.Oracle',
N'DynamicParameters', 1Â
3.Create linked server with scripts:
exec sp_addlinkedserver N'MyOracle1', 'Oracle', 'ORAOLEDB.Oracle', N'//10.154.14.235', N'FetchSize=2000', ''
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'root', @rmtpassword='Sqlexp!23' Â Â
4. After create successful and testing the connection ,I got the error below.(My windows firewall is turn off)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle".OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA". (Microsoft SQL Server, Error: 7303)
View 4 Replies
View Related
Nov 5, 2007
I'm really new to the whole database deal (as well as VB.net) - specifically with the capabilities surrounding VB and SQL Server 2005. My question is open to any recommendations...
What I have is an application that a user uses to create 'new' products. They are presented a form to enter the information regarding the product they wish to create. They enter the details of the product and also locate an image that represents the product, too. Currently, this application saves the product information (including binary image data) into a SQL Server 2005 Express Edition database. This application and database reside on a client pc. What I need to do is to be able save updated and newly created product data into a file of some sort. That file will make its way to a memory stick (USB) and then be transported to a 'field' machine. Quite simply, what is the best way to do this? Are there walk-throughs on this sort of thing? The target database is also SQL Server 2005. I thought I'd post this question on here to get the best design ideas... Any help would be greatly appreciated.
~javasource
View 1 Replies
View Related
Oct 26, 2006
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
View 1 Replies
View Related
Jan 11, 2007
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
View 3 Replies
View Related
Oct 4, 2007
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
View 8 Replies
View Related
Oct 12, 2006
I need to import csv data into a SQL Server 2005 database using SQL Server Management Studio Express. I can't find any menu options for accomplishing this. Is this a limitation of the Express edition, or am I missing something when I try to find this feature?Thanks for any help provided.
View 7 Replies
View Related
Nov 27, 2007
HiI've got an Ingres database of some 200 tables which I need to importevery night into SQL Server 2005 for use by Reporting Services. Mostof the tables will come across unchanged (a few need massaging tohandle time intervals correctly), but the Import Wizard only seems towant to import one table (or more accurately query) at a time. I seemto remember the old 2000 Import Wizard handled multiple tables - isthere any way of processing multiple tables in 2005, or must I resignmyself to writing 200 import packages in SSIS.Chloe CrowderThe British Library
View 4 Replies
View Related
Sep 10, 2007
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
View 4 Replies
View Related
May 27, 2008
Hello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
View 1 Replies
View Related
Jun 11, 2007
Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.
View 1 Replies
View Related
Sep 3, 2007
Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Insert into Pamphlet
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:Pamphlet.xls;HDR=YES', 'SELECT * FROM [Sheet3$]')
But it is giving the following error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error.
Please help me.
View 1 Replies
View Related
Jun 6, 2007
Just installed SQL Server 2005 Eval version and Management Studio does not display any Import/Export functions to load data into tables of an existing database. I thought that this feature was turned off only in Management Studio Express.
View 1 Replies
View Related
Apr 4, 2006
I have a simple Integration Services project and the problem is that decimal fields are importing as real (I'm loosing the digits behind the decimal point).
The project contains a data flow task importing a flat file (.csv) to an SQL Server destination. My .csv file has two decimal type fields. Example:
Field 1: 12345.67
Field 2: .123456
My database table that I'm importing to has two fields. The only way that I can get this data to import is to define the fields as "float" in both the text file and database table. I want the database table fields to be defined as decimal or numeric, not float:
Field 1: decimal(7,2)
Field 2: decimal(6,6)
When all fields are defined as decimal (in both the flat file and database file), I get the following results:
Field 1: 12345.00
Field 2: .000000
How does one import decimal data from a flat file (.csv)?
Thank you in advance!
View 1 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Oct 12, 2006
Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?
So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.
I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.
When I run the task it does execute with no errors, however the table remains empty. It looks like I am failing to read the actual data.
Do you have any suggestions? I am willing to go around this approach with stored procs/com/you name it €“ just make it work!
Thanks a lot for your help!
LoveDanger
View 6 Replies
View Related
Nov 28, 2007
hello there! i have a problem importing data from a remote connection using Sybase ASE ODBC driver(the sybase odbc driver is the only way we can access the database from the remote connection, we've already tried this in crystal reports). in the sql server import export wizard i chose the .net framework data provider for odbc. then below the wizard requires the following info: the connection string, DSN and driver. i specified the correct driver and dsn. in the connection string i specified the user id and the server name but it still produces error. the error says:
the operation could not be completed.
additional information:
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
i need your help guys... thnx
View 2 Replies
View Related
Apr 28, 2008
Hi All,
We are extracting the data from ORACLE databse into SQL Server 2005 database. Both are hosted on different servers.
For this we have created linked server on SQL Server and we using the OPENQUERY to extract the data. Its taking too much time to extract the data.
Is there any other alternate thing for OPENQUERY clause.
Please help me on this.
Thanks in advance.
Thanks,
Ramesh.
View 3 Replies
View Related
Feb 20, 2008
HI,
I need some help regarding the data access.
I needs to access some data from tables which are in oracle and load it into sql server tables.
Please let me know the process for this.
Thanks in advance.
View 2 Replies
View Related
Nov 19, 2007
Hi,
When i try to use the Microsoft OLE-DB Drive for oracle in import/Export Wizard, it gives me message that Oracle networking driver is missing. Contact Oracle for this information.
Any idea to further move on this. I am wondering, why SQL Server 2005 does not have that driver.
Thanks,
View 1 Replies
View Related
Dec 14, 2007
Hi,
I'm new to SSIS.
I want to migrate data from Oracle Database to SQL Server 2005 by using SSIS Package. Client wants to migrate just data not schema. Schema is already there for SQL Server, generated through script.
I tried to migrate the data by using import-export wizard in SSIS. But, its asking for creating new tables when migrating data.
I want to migrate data in existing tables, without dropping already existed tables or creating new tables.
any suggestions ?
Regards,
Khurram
View 7 Replies
View Related
Mar 3, 2007
When running a linked server to Oracle using the MSDAORA driver using in-process option checked, I'm getting the following error:
"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
This then causes the instance to crash.
In the Event Log, it shows:
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
When using the out-of-process, it shows the column names in the query results window, but doesn't show any data then immediately in the message window it shows:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSDAORA" for linked server "smtest" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDAORA" for linked server "smtest".
We're using:
SQL Server 2005 SP1 with hot fixes (9.0.2153) 32-bit on 64-bit o/s Windows 2003 R2 Standard x64 Edition Sevice Pack 1
We use lots of linked servers to Oracle on other servers, and can't find anything different on this one.
Appreciate any thoughts, insights!
thanks,
Steve
View 4 Replies
View Related
Feb 26, 2008
Ok, so these are the facts:
My company wants to implement a BI, the warehousing company we used sold us panorama on sql server. Our database is on oracle.
They were proposing this method:
a) Link oracle server, copy oracle tables to sql database and then build cubes from sql database. The operation will be made only once a day at night.
I don't like it so make it like this:
b) Build cubes directly from linked oracle server. As it is not good to build directly from tables and views (because they could change between processing dimensions), I used materialized views (or snapshots) and some views on slow modifying tables.
Problem:
Oracle snapshots must be refreshed and that is done in oracle with a procedure.
My question is:
How can I run an oracle procedure directly from sql server? I want to create a job that will refresh the oracle snapshots and process analysis database after that.
I know I can use OPENQUERY to return queries from oracle but I want to execute a script.
Solutions?
Another way was by doing it in a command line.
I asume there is an special job feature that can run programs.
PS. posted in another section too
View 1 Replies
View Related
Nov 15, 2006
I have a oracle database that creates a table (to store call records) everyday and places data into it. How can i use Interation services to transfer the newly created tables to an SQL server database (SQL server 2005).
View 4 Replies
View Related
