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.
I was wondering whether it is possible to query linked servers in SQL Server Management Studio Express 2005 if those servers linked to are SQL Server 2000 machines?
I have added some linked servers to express and queried them, however the login timeout is expiring. I thought it might be because of an incompatability.
I have SQL Server 2005 on IA based server and SQL Server 2000 and I have to make linked server on SQL 2005. But I had an error: The OLE DB provider "SQLNCLI" for linked server "DENEB" reported an error. Authentication failed.Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "DENEB".OLE DB provider "SQLNCLI" for linked server "DENEB" returned message "Invalid authorization specification". (.Net SqlClient Data Provider). I searched Web for resolve this problem and find what i need run sql script instcat.sql for the Sql Server 2000. Script was saccesuful complete, but problem did not resolve.
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?
I have an MS-Access database on a shared drive.The Access database has tables linked to a SQL server database.When I try to query the tables in MS-Access database by accessing the share drive,I get the ODBC--call failed error.So I tried creating an ODBC driver for the SQL Server and when I try to link the tables,no tables are displayed.Can anyone help me out with this problem?
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server. 2) Linking x64 or IA64 servers to x86 servers. 3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are: ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054) ------------------------------ And ------------------------------ The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB". OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
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?
Looks like the Office 2003 MS Access has no support for creating a linked server and implement design changes. While you could link to the SQL 2005 tables, which show up as queries in MS Access 2003, any further use of the linked queries are not supported unless the Access 2003 version came out after the SQL 2005 release. What kind of an update is needed, or fix is needed so that Access projects can be developed?
I have an app that creates and attaches tables from SQL Server into an Access mdb using an ODBC link from VB6 using DAO 3.51 on Windows 2000. Recently I upgraded to SQL Server Express edition and tested it. It worked fine against the Express Edition. So, I installed SQL Server 2005 Standard Edition on our Server hoping that they operate identically. How wrong one can be! the same code that worked against Express Edition does not work on Standard Edition.
It is strange that the App works for about 5 seconds which attaches about 38 tables and after that the connection drops and I have a hell of a time to get the rest of the tables linked. The error that comes up is:
Connection failed: SQLState: '01000' SQL Server Error: 10060 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()). Connection failed: SQLState: '08001' SQL Server Error: 11 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
The PC that I am using to connect is in a different domain to the Server where the SQL Server database resides. I thought that domains might be the reason. But even joining the PC to the same domain as the server still does not work and behaves the same. So I do not think it is a domain related issue.
I have also looked at the SQL Server and database configurtaions. I cannot see anywhere any specific time out of around 5 seconds. Even the time out through the SQL Server ODBC driver is set to 30 seconds by default as far as I know. So, it looks like there is a time out somewhere but I am absolutely confused as to where this 5 second time out is comming from.
I also thought permissions on the database or tables might be the issue but after looking at the database and table permissions, they all look OK. The user has prettymuch the same access as the SA on the database.
Has anybody came across this problem? If so, I appreciate if someboday can give me a hint as to where I can start.
Hi is the process for linking servers the same in 2005 as it is in 2000 ?
For example in SQL server 2000 I add an external data source (like an Oracle DB) by going to Security->Linked Servers, is the process the same in 2005 ?
or can anyone linkify a good tutorial page on this ?
Hi, I need to link an Access 97 database to sql server 2000. I can't use Enterprise Manager so I have created a simple asp.net page using sp_addlinkedserver and sp_addlinkedsrvlogin.
The mdb file is on a network share, then I have permissions problems: here is a sample
I have no problems executing these stored procedures (the linked server is added in sysservers table), but when I try to query the access database
SELECT * FROM test...profile
I get this error
Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\SVKNFS080Pshareaccess97dboutlet.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
The mdb file is not locked, I think is a permission's problem.
Hello,We have a database in MS SQL 2000 and I'm trying to link to it from MSAccess XP. I've created a user with full access to one specific tablein the MS SQL database, but Access won't write to it.I can open this table in MS Query Analyzer and log-in as the user, andI can do update, delete, and select queries just fine... but when Ilink to the table from MS Access using the same MS SQLusername/password, I can only read the data... no write or delete.Since I can update data fine in MS Query Analyzer, I assumepermissions are setup properly. Anyone know why I can not edit thelinked data from MS Access?Thanks,Alex.
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
I know this is an easy one for most of you but this is new territory for me. I am trying to link a production application database to an existing SMS database. Both of these databases are SQL 2000. I have sa access to both of them. I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.
For simplicity I will define them both. Application Database we will call €œA-Database€? SMS Database we will call €œB-Database€?
I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab. In the Security Tab I selected the €œBe made using the security context€? and used €œsa€? for the Remote login and the CORRECT sa password for the With password option. The server options tab remain the same with €?RPC, RPC Out, & Use Remote Collation€? selected.
It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:
SQL Server 2000 as Publisher/Distributor
SQL Server 2005 Standard as Subscriber
The connection is via the Internet with snapshots using FTP.
I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.
II am setting up the same scenario for location B. Here is my problem:
In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next
HERE IS THE PROBLEM:
On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.
I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).
WHAT'S DIFFERENT:
Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).
The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied. SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then MessageBox.Show("Open") End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
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?
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.
We 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?
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?
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.
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.
First 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.
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
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)
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'
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
