Has anyone successfully linked to an Linux Oracle server running 9i? We are unable to get the link working.....have installe dvarious drivers and still get connection refused. Any thoughts?
View 1 RepliesIs 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.
Hi,
I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.
Is there a way to improve performance in querying?
Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.
Thanks a lot,
Stara
Can anyone tell me how to link to a oracle user? DO I use a predefined DSN? Do I just specify the server and the user and password?
Your assistance is appreciated,
David
I need to query both Oracle and SQL Server tables for reporting and processing, ....no updates, insert or deletes.
Is creating a linked server the only way? My company does not want me to see the entire database.
Can you just link tables? Like Access or Excel does?
Any articles or links would be appreciated.
Thanks,
Mike
I'm trying to set up an Oracle database as a linked server in SQL server 7 but having some problems.
The client software is installed and seems to be working fine (Used access to link some tables). I set the product name to Oracle 8.1 and the Datasource to the system dns name. I left provider string, location and catalog blank. Under the security tab I had it set to map to the oracle account and also tried the impersonate option, but always got the same error.
Error: 7399 OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed]
ORA-12640: Authentication Adapter initialization failed]
This is my first time trying to set up and Oracle database as a linked server, any help is appreciated.
hello
we suppose to link the form in MS Access with Oracle
can anyone tell me how to write the code for these buttons
i tried to write as I learned in VB.NET,
But it seems doesn't work :(
http://www3.speedyshare.com/data/191404757/10803567/78224332/db2.jpg
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
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?
I'm writing my first web site using VS 2005 and with my login page I'm trying to link with my SQL database using the following setting in my web.config file
<appSettings> <add key="SQLDB" value="server=STEVE-LT; database=MasterDB; uid=sa pwd="/></appSettings>
Then in my Login.aspx file I use the following code to access the SQL database
Dim Sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("SQLDB"))Dim SqlCmd As SqlCommand = New SqlCommand("Select Password, Name from Login")SqlCmd.CommandType = CommandType.StoredProcedure
Sc.Open()SqlCmd.Connection = Sc
This raises the following error message
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Could someone please tell me where I am going wrong. Thanks
I can't seem to link a 6.5 database from 7.0 and there's nothing in help which seems to help. Is it a "SQL Server" or do you have to use the OLE DB Provider? If the latter, what's supposed to be in the Product, Data Source, Provider String and Catalog fields? What about security? I've tried everything I can think of and can't get it to work. I also get errors creating a new DTS package that says "Could ot create Component Categories Manager", and then "Error during creation of DTS Package". I installed the desktop version of 7 on an NT Workstation. Has anyone successfully linked a 6.5 server in this environment?
View 4 Replies View RelatedAbout linking server
Hi,
I am using sqlserver2005, when i make linked server connection between sqlserver2005 and oracle10g . It shows an error like that " Particular table doesnot exist or user doesn't have permission to connect ti this table"and below this one message is shown which is " Message 7314, line16".But i am giving it a proper "username/password ". In this linked server i am using Oracle10g new provider "ODAC101040.exe". so, please elaborate my such problem and also gives me the solution of this problem.
Thanx
regards
gautam
Hi all, hoping someone can help.
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.
Is this possible?
View 2 Replies View RelatedWe recently upgraded a number of databases from MS Access 2000 to SQL Server 2000. I need to make it so that users can display the data from the SQL Server so I created some link tables in Access for them to view the data without using SQL Server Enterprise Manager. This works fine in tables with only a few thousand rows of data. However some of the tables have 100K+ rows of data, and these are not displaying the data. When someone tries to view the data in one of these tables all they see is #Deleted in every column. Is it possible to link to tables in SQL Server with large numbers of rows and view this info in Access?
Miranda
Rather than using the upsize wizard in MS Access to connect to tables on a backend SQL server, how would I go about linking an Access Database to an existing table on an SQL server?
Thanks, Phil
OK here is my situation: I work in a laboratory with a 10 year old instrument that stores results in an Access 97 DB. The DB is beautifully designed and implemented. The problem is that since the software is so old, once the DB gets above a few MB in size the application starts to fail. So their solution is to Backup (make a copy) and Cleanup (delete data older than X days) once a week. So what I end up with is a bunch of copies of the DB, which fragments the data that I need. In order to get the data results I have been using an Access 2003 DB linked to this one (because I can't create Views in that database). The lab users then use the linked DB directly or I have written some automation code in Excel VBA (ADO) to simplify it for the other users. This allows us to query data immediately as it is produced.
My hope is that I can somehow get all of the data into a SQL Server DB. Now I have imported one of the backup copies of the DB in and the table structure and data imports fine into SQL Server, but I end up with duplicate rows because the different DB backup files have overlapping data, and for some reason there aren't PKeys. Also, I'd prefer to have some way of automating the input. Something like a trigger on the Access table so that any new data is imported into the SQL DB automatically. I doubt this is possible, but that is generally what I am trying to do and I am out of ideas. The ultimate goal is that the SQL DB will always be "updated" and I can use that as my query source and take advantage of the power of SQL Server.
Hopefully that all made sense... if anyone has any ideas I would love to hear them.
Thanks for your time.
Our client wants some changes/modifications in his DB but he does notwant us to touch the original Server. What would be the best/ fastestsolution for two SQL Servers connecting each other?Thx.
View 1 Replies View RelatedFirst off, I am a novice in regards to SQL server and have the rudimentary skills to navigate, execute, query under SQL Server Manager. So, please bear with me.
I am working on a program that was not developed by myself, it is for my business and the company that developed it has gone under, so I have no technical support. The program uses SQL Server and ASP.net. We had a recent hard drive crash and I am in the predicament of having to re-install a new instance of the server onto a new computer. I have the most recent saved "data" files within "Microsoft SQL server/MSSQL/Data" directory. It includes the master, model, MSDB, etc. files.
Long story short, I have been successful in making the program operational again and I can connect to the server properly. It even works when web connecting with internet explorer. The problem I am facing is that all of the data is missing. When I connect through SQL Server Manager, I can see that there are two databases, I will name them "data1" and "data2". When I run a query of the tables, all the data is in "data2". All the stored procedures and tables are identical in both "data1" and "data2".
I am assuming (hopefully I'm right) that "data1" and "data2" are somehow linked or joined in some way. Any insight in how to get these two tables to link would be very much appreciated. Again, I am a novice, and will need help with specific coding that I can put into a query window and execute.
Thanks in advanced.
I'm new to SQL Server 2000. I decided to purchase this server because my old company (UPS) used the server to help expedite queries and manage on-line reporting. So, now it's up to me to utilize and understand the new server. I have been using MS Access as my front-end for several ad-hoc databases, so I'd like to keep utilizing this method. I want to test my first database using the SQL Server.
I've got everything set up. The IT department has installed the server software on my computer. Within my Access database, I link in tables from a database called "Progress" using a Merant driver. Queries, forms, macros and modules are all pulling from these "linked" tables. Do I need to link these tables into the SQL Server? How do I transistion this database over to SQL? I guess I'm confused about the relationship of how all of this should work.
Right now it looks like this:
PROGRESS ..... linked....... MS Access ....... reporting/results
Where does SQL fit in?
Hi,
I have an existing Access DB with many Tables, Forms, Queries and Reports. I need to put a SQL Server Back end on this database.
Do I need to code a mirror images of these objects or is there some way to export these into SQL Server?
Also, where do I deal with assigning usernames and passwords? Access or SQL Server. I need to have different views for different users based upon their rights (user, admin)
Thanks,
Mark
Hello everyone.
I am trying to implement linked servers and I am having some trouble. The setup is as follows: I have two machines with the same SA login/pwd. BOTH machines have a database called: Federated_Bridge.
On machine 1: Federated_Bridge contains a table Fed_Entity_1
On machine 2: Federated_Bridge contains a table Fed_Entity_2
Machine 1, when I look it up in SQL Enterprise Manager it says: (local)
Machine 2, when I look it up in SQL Enterprise Manager it says: REALIBM2
When I expand those, I can see the databases folder and I can then see Federated_Bridge respectively.
On machine 1 (local) I am trying to create a linked server. I use the following SP call:
---------
use master
go
EXEC sp_addlinkedserver
@server = 'TEST3',
@srvproduct = 'SQLServer OLEDB Provider',
@provider = 'SQLOLEDB',
@datasrc = 'REALIBM2Federated_Bridge'
EXEC sp_addlinkedsrvlogin 'TEST3', 'false', 'sa', 'sa', 'mysapwd'
--------
The above queries seem to execute fine. Afterwards, when I check in Enterprise manager, I can see under linked servers TEST3 .. but no tables or views are showing.
When I run the following select query from query analyzer, I get an error message:
SELECT * FROM Test3.Federated_Bridge.dbo.Fed_Entity_2
SQL Server Does Not Exist Or Access Denied
Please help!!
Thanks.
Hi all.
I need to create a Linked Server in SQL 2000 to a text file. Importing the file is not an option. It has to be linked. I tried creating a ODBC link to it but I must be doing something wrong because it didn't work.
Please help.
Thanks,
ODaniels
I am trying to link an existing SQL 2000 server to a new 2005 box, but for some reason it will not link. I tried another 2000 server and that one worked perfectly well.
When I link it and perform a simple query...
select a, b from [server1].[db1].[dbo].tablec
I receive a 7314 message with text "OLE DB provider "SQLNCLI" for linked server "server1" does not contain the table "].[db1].[dbo].tablec". The table either does not exist or the current user does not have permissions on that table.
What's odd is that I was able to link the problematic server to other SQL 2000 servers with no previous issues.
Any ideas?
Thanks,
Steve
Hi,
I am using an MDB file to talk to a SQL Server. I would like to use pass-through queries to keep as much of the processing on the server as possible. However, I have been told that the results of such queries will be read-only. How do I keep the processing on the server but generate read-write answers to my queries?
Thanks
Matt
I have table1(Client widgets) and Table2(Client Toys) I want to link both Table1 and table2 up to Table3(ClientNames)
All tables have a clientid field
I want to right join them so I get all the client widgets and Client toys for each client...
Can anyone halp me with this?
I want to link/access informix database to Sql Server stored procedure.
Hi, I have 2 similar databases on the same SQL server (due to licencing and customisation issues for off the shelf software).
I would like to create a third database with site specific lookup tables to map data across to site codes, to do summary queries for data reporting, and to create a single point for data access. I can't modify the 2 databases.
I have created the third database and added the necessary tables, relationships, views, etc. But, how do I link to tables in the other databases on the same server so that I can do joins in a query/view ? I have SQL Server Enterprise manager.
Thanks.
I need to connect SQL Server 2000 to DB2 on z/OS through DB2 Connect 8.I can successfully connect and query data through a System DSN, buttrying to link the server using this DSN and MSDASQL fails (can't seemto find the DB2 Connect server). I've also tried IBM's OLE/DB driver(IBMDADB2).Has anyone successfully linked to DB2 on a mainframe through DB2Connect 8.x via either ODBC or OLE/DB drivers?
View 3 Replies View RelatedHello all!
How do I go about linking two SQL Server Express servers? I can't seem to find information on this.
Also, I need to import data from MSDE. Is there anyway to do this?
Regards,
Andrew Cappella-Pakos
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error:
EXEC sp_addlinkedserver
@server = 'AITdb_be2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access',
@datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AITdb_be2',
@useself = 'false',
@rmtuser = 'Admin',
@rmtpassword = ''
GO
-- List the tables on the linked server
EXEC sp_tables_ex 'AITdb_be2'
GO
ERROR:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
I have a sql server 2005 db.
Is there anyway I can link access to be used as a frontend by my client for viewing the data?
is this possible through linked table manager?
Please advise?
I added a linked server successfully; but the only tables accessible areonly the system tables!I do have all the rights on both servers (Windows & SQL server).All the non system tables are designated as DBO.Strange thing is that I was able to access the non system tables onlyfrom the query analyzer (not from the SQL Manager tree) by using thefollowing script:EXEC sp_addlinkedserver 'LINKED_SERVER_NAME', N'SQL Server'GOSet ANSI_NULLS onSet ANSI_WARNINGS onGOSelect * From LINKED_SERVER_NAME.Database_name.DBO.table_nameGOThank you very muchMike*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View Related