Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Importing Data From Oracle 8i/9i To SQL Server 2005 Using SQL Server Import And Export Wizard (AKA DTS Wizard)
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 Replies !
View Related
Importing Data From Oracle 10g To SQL Server
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 Replies !
View Related
SQL Server + Oracle Data Linked In One Query
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 Replies !
View Related
SQL Server + Oracle Data Linked In One Query
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 Replies !
View Related
SQL 2005 Express && Oracle 10g Linked Server
Hi all, I am trying to access an Oracle DB from SQL server. My environment: - Windows 2000 - SQL 2005 Express - Oracle 10g & Client & Oracle Provider for OLD DB 10.2... - MDAC 2.8 I linked Oracle from within SQL Management studio. When I try to access Oracle tables I get the error "Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server......" I test the connection to Oracle via OraOLEDB.Oracle.1 using a UDL file and the connection is successful. *** I posted the same thread to Oracle forum but I didn't get any reply *** Any ideas what is wrong ??
View Replies !
View Related
Problem While Fetching Data From Oracle Linked Server
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 Replies !
View Related
Problems With Linked Server To ORACLE (on SQL 2005 64bit)
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 Replies !
View Related
Oracle Linked Server Connection Error After Upgrading To SQL 2005
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 Replies !
View Related
Exporting/Importing Database Data (Sql Server 2005 To Sql Server 2005)
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 Replies !
View Related
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
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 Replies !
View Related
Problems Reading Data From Linked Server To Excel In SQL Server 2005
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 Replies !
View Related
Memo Data Type Import Error While Importing Data From Access File Into SQl Server 2005
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 Replies !
View Related
Error Query Data Through Linked Server , SQL Server 2005
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 Replies !
View Related
Importing Data Into SQL Server 2005 Via ODBC
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 Replies !
View Related
Importing Data From Excel 2003 To Sql Server 2005
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 Replies !
View Related
Importing Excel Data Into SQL Server 2005 With A Twist!
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 Replies !
View Related
Importing Decimal Data Types Into SQL Server 2005
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 Replies !
View Related
Importing Data In MS SQL Server 2005 Using Odbc Drivers From A Remote Connection
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 Replies !
View Related
Importing Online XML File Into SQL Server 2005 Tables – No Data Transferred
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 Replies !
View Related
Data Migration From Oracle 10g To SQL Server 2005
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 Replies !
View Related
Move Oracle Data To SQL Server 2005
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 Replies !
View Related
Extracting The Oracle Data Into SQL Server 2005.
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 Replies !
View Related
Data Transfer Between Progress-SQL Server 2005-Oracle
This will be a long post, but it is a complicated problem. Situation: We have 3 databases involved. Progress is the production database. SQL Server is the data warehouse. Oracle houses the combined financial data between Progress (via the data warehouse) and our other Oracle databases. Problem: 1. Data is imported successfully into SQL Server from Progress using a SQL script written by an INFOR technician Example: IF object_id('insite..wi_icseg') IS NOT NULL DROP TABLE insite..wi_icseg; SELECT * INTO insite..wi_icseg FROM OPENQUERY(TRAIN, 'SELECT a.* FROM PUB.icseg) 2. The column names are created in lower case, like the Progress table. 3. The table is recreated every day. 4. The data in this table gets exported to Oracle from SQL Server 2005 every day. 5. Oracle maintains the case of the column names. 6. Oracle defaults to uppercase in all table and column names, therefore SQL references must use quotes or it is not found. 7. This creates a giant problem with existing scripts and programming efforts due to the inconsistency. Avenues tried so far: 1. Created the SQL Server table with column names in caps. Truncated table instead of dropping table. This works great. 2. The INSERT INTO command does not seem to work with OPENQUERY. The documentation says it should work, but I get a syntax error at or near OPENQUERY. I have been unable to find any examples of these two commands put together. I need the statement that will allow me to insert data from an outside source into an existing SQL Server 2005 table. The truncate works fine. A SELECT * INTO does not work because the table already exists. Any advice would be helpful. I will clarify anything that may be fuzzy. Our go live date for the entire project is August 1st and we need this resolved before we switch systems. Thanks so much in advance for any help.
View Replies !
View Related
Execute Script To Linked Oracle Server From Sql Server
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 Replies !
View Related
Linked Server To Oracle Causes SQL Server Instance To Crash
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 Replies !
View Related
Linked Server Error Between SQL Server And Oracle
I have used Linked Servers to hook up to an Oracle 9i database in SQLServer 2000. I can see all the Tables and Views when I look in thelinked server section. I can also run the following SQL statement :Select *from Orcldb..SYSTEM.CrossTabSaveSitesand get a return result. The problem I realize is that some of thetables are going to be quite larger and I can't wait for SQL Server todownload all the data and parse it itself, so I tried to use apassthrough query like so :SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROMSYSTEM.CrossTabSaveSites')Unfortunately this gives me the error message :Server: Msg 7321, Level 16, State 2, Line 1An error occurred while preparing a query for execution against OLE DBprovider 'MSDAORA'.[OLE/DB provider returned message: ORA-00942: table or view does notexist]There is also the issue of speed. It takes about 15 seconds to comeback to me to tell me that the table/view doesn't exist, but sometimesit comes back immediately. Is this something with the userpermissions in Oracle? (Don't really know much about Oracle) It justseems like a very long time to wait just to let me know that the itemdoesn't exist.Any help would be greatly appreciated,-Jevon Thurlow
View Replies !
View Related
Linked Server From SQL-Server 2000 To Oracle 8.1.7
We have a linked server, which worked fine, but we have just changedthe collation sequence, in SQL-Server, fromSQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.Now trying to use the Linked server, we get the following message:"Error 7399: OLE DB Provider 'MSDAORA' reported an error. The providerdid not give any information about the error. OLE DB error trace [OLEDB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:The provider did not give any information about the error]."Ha anyone seen this before, because I am not sure why changing thecollation sequence, would affect the Linked Server
View Replies !
View Related
Importing Excel Using Linked Server And Passthrough Query
What: I am trying to import data from spreadsheets to SQL Server. Where: Windows Vista, SQL Server 2005 Express, Office 2007. How: Using linked servers following KB306397. In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default: Linked server: XLSX Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider Product name: XLSX Data source: D:XLSX.xlsx Provider string: Excel 12.0 The linked server was created ok. I then followed KB321686 and ran this: select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]') and got this: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX". But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]') the error is: Msg 7357, Level 16, State 2, Line 1 Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object. It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS. From the same KB article, I also tried: select * from XLSX...[Sheet1$] but got this: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX". Help help. Thanks.
View Replies !
View Related
Linked Server To Oracle
Hi,I have an Oracle (8.1) & a SQL Server 2000 database withProduction data. There are situations when I need data from both thedatabases. My first choice was to link Oracle to SQL and run DTSovernight. But this would have a 1 day latency not to mention the timeit would take.1. Has any one tried real time access via Linked server to Oracle?How good is the performance?2. The Oracle db is fairly big, so I'm kinda not in favor ofcopying the whole thing over into SQL overnight. Is there an easierway to just get only the changed records from Oracle?3. Is there a better solution to this?4. Lastly, if I use OPENROWSET or OPENQUERY, can I dynamicallychange the SQL that you pass it? e.g. can the query accept aparameter?Thanks in advanceSudhesh
View Replies !
View Related
Linked Server To Oracle
Hello,I have a linked server to oracle 7.1 from SQL 2000. when I try toexecute simple select statement which returns one row of data usingopenquery is not fetching the data. After 30 minutes SQL Queryanalyser is hanging. This is happening on the production server and itis working from last 6 months. I have tried by deleting and recreatingthe linked server, but no use. Please helpThanks,Regards,Pardhasaradhy
View Replies !
View Related
Linked Server (Oracle 9i)
Hello all,Having problems connecting to an Oracle 9i database from withinSQL/Server 2000 using the Security/Linked Servers feature.Server1 (SQL/Server)-----------Windows Server 2003, Standard editionMS SQL/Server 2000Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management toolsMicrosoft ODBC for OracleOracle OLEDBMDAC 2.8 RTMServer2 (Oracle)-----------Windows 2000 - Advanced ServerOracle 9i database (v9.2.0.1.0)Two nodes clustered using Microsoft cluster manager. (Nodes areDATABASE01 & DATABASE02 - Cluster is WMCLUSTER)When I try to connect to the linked server in Enterprise Manager I getthe following error messages.Error 7399 OLE DB provider 'MSDAORA' reported an error. Authenticationfailed.OLE DB error trace [OLE/DB Provider 'MSDAORA'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].From within Query analyzer I get a slightly different messagereporting that the username/password are incorrect.dbcc traceon(7399)select * from TURLIVE..SONICA.INV_LOCServer: Msg 7399, Level 16, State 1, Line 3OLE DB provider 'MSDAORA' reported an error. Authentication failed.[OLE/DB provider returned message: ORA-01017: invalidusername/password; logon denied]OLE DB error trace [OLE/DB Provider 'MSDAORA'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].I know the username/password combination is correct and I can usethese from with Oracle enterprise Manager with sucess.TURLIVE is the name I've given the linked server, SONICA is the nameof the schema on the Oracle database and INV_LOC is a valid table.TURLIVE is also the name of the database instance on Server2.Steps taken so farInstall Oracle client tools (Enterprise Manager, Net manager etc) onServer1.Setup an entry in TNSNAMES.ORA to the cluster that has the Oracledatabase. e.g.TURLIVE =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = TURLIVE)))This works fine, I can connect via Oracle Enterprise manager and I canTNSPING WMCLUSTER, DATABASE01 & DATABASE02.Configured an ODBC source to TURLIVE.On Server1 I've configured the linked server using the following SQL.sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'(password blanked)I then rebooted Server1The properties of the new linked server are:Product name = OracleData Source = TURLIVEProvider String = blankI've modifed the registry on Server1 as instructed by a Microsoft KBarticle.HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI OracleXaLib = "oracleclient8.dll"OracleSqlLib = "orasql8.dll"OracleOciLib = "oci.dll"Still no luck. Can anyone please point out he bleeding obvious? :-)Thanks in advanceAs an aside, has anyone ever configured a linked server to an OracleRdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?Are there any HOWTO guides for this type of connectivity?CheersDave.
View Replies !
View Related
Oracle Linked Server
I have an Oracle linked server in SQL Server 2000 which works fine for the most part until I try to access a table with a CLOB datatype. Then I get this error: [OLE/DB provider returned message: Data type is not supported.] And I can't access any data in that table whatsoever. Does anyone know of a way around this so I can access the data in that table? Irritatingly, I used to link the the Oracle tables into Access and I used to have no problems at all. It would just convert the CLOB datatype to Memo. Its kinda annoying that I can't do the same with SQL Server.
View Replies !
View Related
Linked Server To Oracle
I am looking for a way to increase performance when deleting or updating an Oracle table from SQL Server 7.0. The Insert I use works great but the delete (and update) take up to 15 minutes to delete one row from an Oracle table and there are indexes on the proper columns of the Oracle table. This is the INSERT command using OPENQUERY that seems to work very fast: SELECT @sqlstring = 'INSERT INTO #old_id SELECT * FROM OPENQUERY(Linked_Server,"'+ 'SELECT I_ID FROM ORDER_TABLE WHERE I_ORDERNO = ' + '''' + @order_num + '''' + '")' EXEC sp_executesql @sqlstring However, unless there is a better way...please tell me if there is...I can only get the Delete to work using linked server with this syntax and it takes forever to delete one row in the Oracle table: SELECT @sqlstring = 'DELETE FROM Linked_Server..Owner.ORDER_TABLE WHERE I_ORDERNO = 'SELECT @sqlstring = @sqlstring + '''' + convert(varchar(20), @order_num) + '''' EXEC sp_executesql @sqlstring It would really be nice if Microsoft would make it as easy to delete and update as it is to select and insert using OpenQuery. Any ideas would be appreciated to speed up this performance issue. Thanks, Jan
View Replies !
View Related
Linked Server To Oracle
Hello- We have a SQL 2000 box with a linked server to an Oracle 8i instance. We are finding cases where simple SELECT from the Oracle table results in the following error: OLE DB error trace [Non-interface error: OLE DB provider MSDAORA returned 0x0 for distinct values higher than base table cardinalities during statistics gathering] The DBCC Trace 7300 flag has been enabled. We are not clear on what we need to do to resolve this error and would be grateful for any info anyone has. Many thanks!
View Replies !
View Related
Oracle Linked Server
Hi there I'm trying to set uop the Oracle OLE-DB driver for a linked server. Does anyone know the provider info for the Oracle OLE-DB driver? The following is for the Microsoft driver: EXEC sp_addlinkedserver @server = 'EURENLP1', @srvproduct = 'MSDASQL', @provider = 'MSDASQL', @provstr = 'DRIVER={Microsoft ODBC for Oracle}; go I can't seem to find it anywhere! rgds Paul
View Replies !
View Related
Linked Server In NT To Oracle
Hi: I configured SQL Server 7.0 by Linked Server to a Oracle db. What I found was I could select data from SQL Enterprise Manage but fail to insert any data through Query Analyst from SQL Server to Oracle. The platform for the SQL Server is NT 4.0 SP6. Could you point out what I missed while inserting data from Query Analyst to Oracle? Thank you very much. Rick
View Replies !
View Related
Linked Server For Oracle RDB 6
Anyone using a SQL Server Linked Server to access Oracle RDB on a VMS/Alpha? What connection are you using? What is reuqired? I am using OLE DB Provider for ODBC to access an ODBC connection I setup using the Oracle ODBC driver for Oracle RDB 2.1 and it seems slow and limited. You can only use the OPENQUERY method to access data, etc.
View Replies !
View Related
64-bit And Linked Server To Oracle
We're having some trouble connecting SQL Srvr2005 to Oracle (10g) in our 64-bit environment. We have Oracle's 32-bit Ole Db provider installed and have been able to use that in SSIS to connect and return some data running in 32-bit debug mode (as long as the query isn't long, complicated or using parameters =) However, we have some hefty queries that we need to gather some data and want to put them in a stored proc on Oracle and call it through SSIS. We tried adding our Oracle DB as a linked server and are getting some errors. I've tried the following code replacing the provider with 'OraOLEDB.Oracle.1', 'OraOLEDB.Oracle' as well as 'MSDAORA': EXEC sp_addlinkedserver 'ORA_TSTW', 'Oracle', 'OraOLEDB.Oracle.1', 'TSTW' EXEC sp_addlinkedsrvlogin 'ORA_TSTW', false, 'user', 'lituser', 'pw' select * from openquery(ORA_TSTW, 'select * from lituser.customer') We keep getting the error for each of the providers we try. I've tried it in Management Studio as well as in an Exec SQL task (with debug set to 32-bit) to no avail. Cannot create an instance of OLE DB provider "OraOLEDB.Oracle.1" for linked server "ORA_TSTW". I've checked and the MSDAORA.dll is registered in both C:Program Files (x86)Common FilesSystemOle DB as well as C:Program FilesCommon FilesSystemOle DB. If we can't where we can call an Oracle stored proc, we were thinking of just using a script task to connect to Oracle and retrieve the data we need, but I have a feeling performance this way would be degraded. Anyone have thoughts/suggestions? TIA! Sabrina
View Replies !
View Related
Linked Server With Oracle
I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
View Replies !
View Related
MSSQL Linked Server To Oracle RDB
Hi,I am using MSSQL 2k, and I have a linked server set up to an Oracle RDBversion 7. It goes thru an OLE DB provider for ODBC drivers on a systemDSN, which is using an Oracle RDB ODBC driver version 3.0.2.The problem occurs when I send a query that returns zero rows - queryanalyzer just does not complete nor return. This problem is not seenwhen there are rows being returned.I ran a trace and this is the error message I get-:Non-interface error: OLE DB provider MSDASQL returned an incorrectvalue for DBPROP_CONCATNULLBEHAVIOR which should be eitherDBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULLAnybody who experienced this before has a solution?Thanks,Lawrence
View Replies !
View Related
Linked Server Trouble To Oracle
I'm currently trying to establish a linked server to an Oracledatabase.Setup:Connecting to 8x version of OracleUsing 9i client tools (Net Manager)SQL-Server 2000Windows 2000I installed the Oracle 9i client tools, and set up a Service for theOracle connection. Testing from the 9i client produces a successfullconnection.I then opened SQL-Server and created a new Linked Server with thefollowing setup:Name - PYR_LinkProvider - Microsoft OLE DB Provider for OracleProduct Name - PYRData Source - PYR (9i Service Name)Provider string - MSDAORAI get the unspecific 7399 error that OLE DB provider MSDAORA reportedan error, trace 'Initialize returned 0x80004005'I've searched through the archives, checked all of the relevantMicrosoft articles I could find, and still have no idea what is wrong.SQL-Server reports that the provider is registered in the system, but Ican't figure out what I'm missing.Help or a guide for troubleshooting would be much appreciated.Tim
View Replies !
View Related
Oracle Linked Server Error
Hi, Recently upgraded MDAC on server and now a simple query that looks up a value on a linked Oracle database doesn't work. This lookup happens as part of a transaction and what I noticed is that if I do lookup outside of the transaction it works fine. Example 1 below works fine but example 2 gives following error: OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-01084: invalid argument in OCI call ] OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ]. Example 1: DECLARE @ref_nb varchar(255) DECLARE @ref_nb_ora varchar(255) SELECT @ref_nb='123456789' SELECT @ref_nb_ora=CASE WHEN ORDID IS NULL THEN ORDERID ELSE ORDID END from SERV..ER1.VW_ACCOUNT_ORD_2 where TRANS_ID =@ref_nb Example 2: BEGIN TRANSACTION DECLARE @ref_nb varchar(255) DECLARE @ref_nb_ora varchar(255) SELECT @ref_nb='123456789' SELECT @ref_nb_ora=CASE WHEN ORDID IS NULL THEN ORDERID ELSE ORDID END from SERV..ER1.VW_ACCOUNT_ORD_2 where TRANS_ID =@ref_nb ROLLBACK Any help would be appreciated. Thanks David
View Replies !
View Related
|
TRACKER
