Linking Problem 'sp_addlinkedserver' - Help Me !!!!
May 18, 2000
I'm trying to link access table and execute in Query analyzer or query window.
but I'm pretty sure that I connect properly....
1)
sp_addlinkedserver 'linkedserver', 'Access 97', 'Microsoft.Jet.OLEDB.4.0','C:Mag.mdb'
after i ran this it displays....
(1 row(s) affected)
(1 row(s) affected)
Server added.
2)
sp_addlinkedsrvlogin 'linkedserver', true, 'sa', 'sqluser','sqlpassword'
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
3)
after that when i try to run the sql statement like
Select * from linkedserver..tablename
I GOT THIS ERROR MESSAGE:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'linkedserver..tablename'.
I appreciate if anybody have idea about that.
Thankx a lot
View 6 Replies
ADVERTISEMENT
Aug 22, 2007
Hey guys and gals,
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
View 10 Replies
View Related
Mar 31, 2008
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.
Any ideas?
Thanks,
Terry
View 4 Replies
View Related
Feb 8, 2008
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.
View 1 Replies
View Related
Aug 9, 2000
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
Thanks
VENU
View 3 Replies
View Related
Aug 9, 2000
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
Thanks
VENU
View 1 Replies
View Related
Aug 9, 2000
Hi
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
Thanks
VENU
View 1 Replies
View Related
Dec 6, 2005
HI!
i m using sp_ADDLINKEDSERVER for getting data from the other server name 'db est'.
And when i write
'
EXEC SP_ADDLINKEDSERVER 'db est', 'SQL SERVER'
GO
EXEC sp_addlinkedsrvlogin 'db est', FALSE ,NULL ,'sa','sa'
GO
SELECT * FROM db est.Practice.DBO.Employees
'
And then run it in query analyzer. it gives following error due to '' in the server name.
Incorrect syntax near ''.
how to resolve this issue?
Regards,
Shabber Abbas Rizvi.
View 5 Replies
View Related
Jan 10, 2008
Hi,
Iam trying to use database in different server. i have searched for that i got to know there is something called
"sp_addlinkedserver" & "sp_addlinkedsrvlogin " stored procs exist. i have tried using this code block to register server to sys servers.
Code Block
exec sp_addlinkedserver @server='ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname='ServerName', @useself=false, @rmtuser='testUser', @rmtpassword='pwd'
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.
Thanks,
-Badri
View 1 Replies
View Related
Feb 10, 2008
QUESTION 1
Is it possible to run a JOIN on tables that are on seperate servers and youre not able to link the servers using "sp_addlinkedserver?"
QUESTION 2
I try to run
"exec sp_addlinkedserver 'sqlserver1'"
and I get the following:
"Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36
User does not have permission to perform this action."
I try to then run
"SELECT *
FROM server1.database1.schema1.table1 AS a
JOIN server2.database2.schema2.table2 AS b
ON a.[column1] = b.[column2]"
and I get the following:
"OLE DB provider "SQLNCLI" for linked server "ITCMSARPT" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
It would appear the the server is now linked, yet it does't accept my credentials. I do have the ability to log into the server on a seperate query.
Can anyone provide a solution to my issue?
View 5 Replies
View Related
Nov 30, 2000
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
View 1 Replies
View Related
Mar 1, 2007
I wrote the following stored procedure. Note that SERVER_BK is an ODBC connection on the server that links to the remote server database.
--Creates the stored procedure that will be called in the Tablecounts script to send the comparison out to admins
if exists (select * from dbo.sysobjects where id = object_id(N'sp_compare_table_record_counts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_compare_table_record_counts
go
create proc sp_compare_table_record_counts as
----Creates the connection to the Remote Database. We need to make sure that the
----ODBC connections on each of the Production and Development Servers
----are named the same, so that we can use the same script and it will refer to the correct server
EXEC sp_addlinkedserver
@server = 'SERVER_BK',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
set quoted_identifier off
select substring(remote_table.tablename,1,40) as Tablename, local_table.tablerowcount as L_SERVER,
remote_table.tablerowcount as R_SERVER,
case when remote_table.tablerowcount = local_table.tablerowcount then 'Counts Match'
else 'Counts Do Not Match'
end as Result_of_Compare
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table, tablecounts local_table
where remote_table.tablename = local_table.tablename
order by Result_of_Compare, Tablename
-- table name on R_SERVER but not on L_SERVER
select substring(local_table.tablename,1,40) as 'Tables not on R_SERVER'
from tablecounts local_table where local_table.tablename not in (select * from OpenQuery([SERVER_BK], 'select tablename from tablecounts'))
-- table name on L_SERVER but not on R_SERVER
select substring(remote_table.tablename,1,40) as 'Tables not on L_SERVER'
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table where remote_table.tablename not in (select tablename from tablecounts)
----Deletes the connection to the Remote Database to free up resources. This is just cleanup.
EXEC sp_dropserver 'SERVER_BK', 'droplogins'
I tested, and the stored procedure works OK when it is run as a New Query (i.e. I copy the guts and run it). When I execute the following, it fails:
exec msdb..sp_send_dbmail
@profile_name = 'DB Mail Profile',
@recipients = 'Recipient List',
@query_no_truncate = 25,
@query_result_separator = ' ',
@subject = 'Record Counts',
@query = 'exec db..sp_compare_table_record_counts', <--db is the appropriate db where the sp is
@body_format = 'text'
Here is the error I get...any ideas would be greatly appreciated. I'm kind of new at this:
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 7399, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
The OLE DB provider "MSDASQL" for linked server "SERVER_BK" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER_BK".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
View 10 Replies
View Related
Mar 14, 2006
Error Message:
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.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @server = N'CONVERTSDB', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'F:Converts.mdb';
Environement:
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';
View 4 Replies
View Related
Apr 23, 2007
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?
View 6 Replies
View Related
Jun 4, 2008
HiI am getting the above error message when I try and bind a stored procedure to a gridview:string connectionString2 = ConfigurationManager.ConnectionStrings[project].ConnectionString; SqlConnection con_room_code2 = new SqlConnection(connectionString2); SqlCommand cmd_sofa = new SqlCommand("report", con_room_code2); cmd_sofa.CommandType = CommandType.StoredProcedure; cmd_sofa.Parameters.Add(new SqlParameter("@code", SqlDbType.VarChar)); cmd_sofa.Parameters["@code"].Value = Session["code"].ToString(); SqlDataSource1.ConnectionString = connectionString2; SqlDataSource1.SelectCommand = cmd_sofa.ToString(); plreport.Visible = false; plreport.Visible = true; test.Text = Session["code"].ToString();does any1 know why this is? thanks
View 2 Replies
View Related
Apr 4, 2007
i already find the answer
thanks
View 1 Replies
View Related
Sep 2, 2007
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
View 2 Replies
View Related
Jun 27, 2001
I need to have database access via an
application to both sql server 6.5 & 7
the 6.5 uses mixed mode security,
the 7.0 uses trusted. Using
sp_addlinkedsrvlogin I can get from a client to
the 7.0 server, but not to the 6.5.
Is this even possible?
View 2 Replies
View Related
Nov 7, 2000
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
View 2 Replies
View Related
Mar 8, 1999
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 Related
Nov 28, 2001
How would you link an online application form done in php to an SQL 7 server?
View 1 Replies
View Related
Jan 11, 2005
this is the query I need help with
select a.RecordID,a.RelocateID,a.WhenConfirmed,b.RequestI D,b.DocumentID,b.RequestWhen from RelocateeDTA a inner join
docRequests b on ({a.RecordID}=' + CONVERT(VARCHAR, a.RecordID)'= b.WhereClause)
because I am trying to link RelocateeDTA that has RecordID to docRequests that has the same piece of data in field WhereClause but is a varchar, and I am getting an error message (syntax error or access violation)
Could somebody help me please
View 14 Replies
View Related
Oct 14, 2005
Hello there, im new here and wanted some help, i would be very grateful to anyone that can help:
I am setting up a DSN for my website (www.t-f-l.com, it may make more sense if you visit the site) i need it so members can sign up to the site (for the exact problem, click 'sign up now' on the website
I have set up the DSN but it says i need to link it to the database tables via MS access, im not sure at all how to do this...please can someone help
please explain in real idiot language....as i am new to this
View 1 Replies
View Related
Apr 24, 2007
dear experts,
i have two tables, tblcustomer and tblform. in tblcustomer i have cutomerid PK and i have also a field that link these two tables customername. in tblforms i have formname. customername and formname are not PK and customername has a lot of null values.
my question is that i want to link these two tables in customerid not the name and also to get the forms used by this customer. the thing is that i have to write a join query.
any suggestion to do that?
thanks my friends
View 14 Replies
View Related
Aug 22, 2007
I am not a database manager, but I work with people that use databases for sales and customer service... so I am not too familiar with the code and lingo... please bear with me.
We use two databases - one is in SQL, and it is for pre-sales customer service... i.e. tracking notes, phone calls, emails, and opportunities.
The second database is in Progress, and it is for actual quotes, tracking time, labor, materials, cost, and revenue.
As of now, our sales people have to enter new customers in the SQL database, and then duplicate the information in the Progress database. It's very frustrating, because everything needs to be entered twice - name, address, phone, shipping info, etc. etc. etc... When we first started this - 10 years ago - we were told that it could all be linked.
So my question is - is it possible to link these databases together? When our sales people enter a new contact in CRM, I'd like it to be automatically entered into the Progress database. If they make a change to a phone number, I'd like that to be updated in the Progress database. Also the other way around - if a shipping address is changed in the Progress database, I'd like it updated automatically in the SQL database.
Please tell me this is possible - and easy!!! Thanks for any help you can provide at all.
Jon
View 3 Replies
View Related
Mar 16, 2004
Does Exchange 2000 and SQL 2000 have to be the same machine to use exoledb.dll?
I want to link to an Exchange server and gain access to a Public Folder Contact Object for use as a lookup table within an Access front end. Someone directed me to use the Exchange OLE DB Provider, but it does not exist on my SQL machine.
Any help is appreciated
View 14 Replies
View Related
Apr 30, 2008
Sorry If I have posted this in the wrong head,
I would like to Establish connectivity to a Oracle9i DB thro Linked Server, Can any of you guide me to suitable links...
Thanks
View 3 Replies
View Related
May 6, 2008
I would like to use MVJ's formula for creating a date table.
I would like to use it with our main ERP database. However, I am reluctant to make changes to it because I fear that at some point when we upgrade that software and it's database that the upgrade program will delete my table.
So, here is my question. Performance wise, does it matter whether I add the date table to our ERP database or if I create another database (on the same server) for the custom date table? Does linking between databases take substantially longer than linking within the same database?
View 1 Replies
View Related
Mar 24, 2006
I know it's possible to link tables between Access and SQL Server using ODBC....however can u use DAO to do this? If there's a way i would appreciate some help. Thanks in advance.
View 2 Replies
View Related
Nov 16, 2006
I link to a sql server for the first time.
I change my .provider and .connectionstring to ADO
And know in one of my forms i get a debug Error; Invalid table Name "tablename"
can someone help me with this.
Thanks eddied
View 6 Replies
View Related
Mar 28, 2007
We are considering to implement Microsoft CRM 3.0 in our (small) organization. But we already have some customer information in another system (Unit4). We want to duplicate this information to our new CRM-system. This data will be read-only in the CRM-application.
Both the applications are installed on different servers with their own SQL databases(server 2000 and server 2005)
How can we duplicate the customer information from the unit4-system to Microsoft CRM? Can we simply link those databases using ?ODBC? (I heard something about ODBC but don’t know what it really is.)
Could someone clearly explain me what we have to do to make a construction as described above. (you don't have to give me all details) Is this construction possible anyway?
Thanks!
View 4 Replies
View Related
May 4, 2007
I am have created a test table in enterprise manager v8 and linked this table to an access database. I am able to append to the table from access but cant delete or update.
Ive set the permission settings to allow the user to update and del so cant work out why this isnt working?
ANyone got any ideas?
View 2 Replies
View Related
Jul 20, 2007
Hi,
I have a default instance that is Server1 (2000) and a named instance Server12005 (2005)
How do I link theses servers together so that my 2005 database can query the 2000 database.
Cheers
Leon
View 1 Replies
View Related
