I have used this command
sp_addlinkedserver 'linkedserver', 'Access', 'Microsoft.Jet.OLEDB.4.0','C:CATS_MDBintrax_1_db.mdb'
to link an .mdb databse. the trouble is, I do not know how to query tbl_user which is inside. When I try
Code Snippetselect * from [linkedserver].catalogs.dbo.tbl_user
it throws this error:
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linkedserver". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
I got asked a question yesterday about queries that JOIN between two databases on a single server... Simple enough
SELECT e1.employee_number FROM Database1.dbo.employees As e1 INNER JOIN Database2.dbo.employees As e2 ON e1.employee_number = e2.employee_number
But what about joining tables from two separate servers? I figured that this would use full 4 part naming
SELECT e1.employee_number FROM Server1.Database1.dbo.employees As e1 INNER JOIN Server2.Database2.dbo.employees As e2 ON e1.employee_number = e2.employee_number
But I got the following error message
Server: Msg 7202, Level 11, State 2, Line 1 Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
So off I went to trusty BoL and Google to see what sp_addlinkedserver was all about and came up with the following
EXEC sp_addlinkedserver 'Server2', N'SQL Server'
Is this syntax correct?
I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first... Is this process reversible? I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!) Does the link affect all databases on a single server, or just the one I'm playing with? How do I connect to a specific database on a specific server? Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it! EDIT: highlighted in pink
Our Production server is Windows 2003 and SQL 2000 Enterprise. The server name is VB-GP.
I dumped 2 of the databases from the production server and restored them to server name SQL2K5-Pub (a TEST server) which was a fresh install of Windows 2003 and SQL 2000 Enterprise. I then upgraded this "TEST" server to SQL 2005 Enterprise. All is fine so far.
Now I'm setting up Transactional Replication in my test environment. I configured SQL2K5-Pub as a Distributor which went well. I created a a Publication of 1 database which went well. I attempted to create a Publication of the second database and received the following error.
Error Message ********************************************************************* Could not find server 'VB-GP' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Could not use view or function '[dbo].ContractBillingStatus_ *********************************************************************
Remember - server name VB-GP is the production server but I have restored to a test server with a different server name. I do not want the production server to have any part of my test environment.
Hi I could add a server with the following statement..
USE MASTER GO EXEC sp_addlinkedserver 'SiebInt','SQL Server' GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
Hi I could add a server with the following statement..
USE MASTER GO EXEC sp_addlinkedserver 'SiebInt','SQL Server' GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
HOW DO WE MAP THE LOGINS USING EM AFTER ADDING THE SERVER USING SP_ADDLINKEDSERVER
Can anyone explain me as to how to map my local_userid to the linked server. Under the security tab of the linked server properties what options are to be used..any help in this regard is greatly appreciated
when i login using Domain user it is throwing an error as "Login Failed for the User(Domain user)", but when i use "sa" user it works fine. Is there any settings which i have to take care while using domain user. can anyone help on this.
I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24 Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @server=N'CONVERTSDB', @droplogins='droplogins';
a different scenario leads to the same problem, the error message: Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers"
I browsed the thread for the same error message and the solution doesn't applies to me. Here is my system settings. we have a kind of backup system. a primary server is set up to copy the database files to a standby server. when the primary is off, the standby server will take over primary's name and ip. and our application runs on the standby as if in the primary. in both machine the odbc connection are set to point the primary server.
this setup works well in SQL server 2000 for both primary and standby servers, but it gives the error message on SQL 2005 in the standby server. I noticed that this is because the application opens an empty table. after inserting a record by hand, the error has gone away.
I know it doesn't make too much sense, but it 's what I observed. Could anyone give me some input how could this happens and what' s the solution?
