Remote Vs. Linked
Apr 24, 2001What are the top 3 key differences between linked and remote servers ?
Thanks for your help.
Mike
ADVERTISEMENT
Linked Remote Server
Mar 23, 2000Hello all SQL Server 7 gurus,
I need your help!!! I can use SELECT to query a table on the remote server but
I can't use UPDATE,DELETE OR INSERT. Please tell me how to get around this problem.
Thanks in advance.
PS. The remote server is added by using sp_addlinkedserver
Linked Remote Server
Feb 11, 2005I need to be able to query remote server tables and join them to local tables. Thought that linked server would be best so that I can use local stored procs for parameters if needed. I have no administrative control over remote server. I have been given select permissions only to one db on remote server. Both servers are sql2000, local machine has spk3.
I have established the remote server in network client utility. I can register the remote server in EM, look at tables. I can run select statements against the remote server in QA when I log in and specify that server. I can reference an odbc data connection with web pages and display rowsets.
Next, I created a linked server under security, using sql (since it's an sql 2000 db) with the same name used above in the client utility and the em. I created a local login with the same name and password as the login used successfully above (Paragraph 2). On the security tab, I've used sa as the local login and entered the remote user and password, using the default 'be made without using a security context'. When I try to view the tables (which I can do through registered) or run this through QA:
SELECT * FROM OPENQUERY(CASCE, 'SELECT person_pk, lastname FROM dbName.dbo.tblName (NOLOCK)'),
I get: sql error 17: sql server does not exist or access denied
If I edit security, wipe out the top settings and specify the security context below using the login information, same thing. Tried messing around with different security contexts, no help or ole db error.
Any help? In addition, any tutorials out there a little more user friendly than the bol? I think I want to use the linked server option and stored procs.
Run Remote Proc Without Using Linked Servers
Aug 1, 2002Hi
Is there a way (workaround) of running a remote stored
procedure as pass through query and without having to use
linked servers. Something similar OPENROWSET when
selecting form a remote object?
Many thanks
Tariq
Linked Server Not Using Remote Index?
Jul 10, 2013Problem ms sql server 2012 not using remote index on sql server 2000 linked server with odbc
Let say my servers are:
srvdb and remotesrv
when do select on srvdb:
select * from remotesrv.database.dbo.inventory this select not using index on remoteserver
but if this select is on remoteserver like
select * from database.dbo.inventory, this select use index PK_Inventory (clustered)
Difference Between Remote Server And Linked Servers?
Oct 25, 2000Hi Everybody,
Can anyone tell me what is the difference between Remote Server and Linked Servers.
thks in advance,
Srinivasan.
Linked Server: Works Local, But Not Remote
Nov 14, 2007I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
Environment: SQL 2005, Standard Edition SP2
Thanks in advance for any suggestions.
David Rueter
drueter@assyst.com
How To Return A Varchar From Sproc On Remote Linked Server?
Aug 22, 2007The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.
DECLARE @OutParam VARCHAR(5)
EXEC ('{Call getnextpin(1)}') AT ALTTEST
GetNextPin takes a single input parameter.
I need to modify the above code snippet to return the next PIN number into @OutParam.
Any ideas?
Any help appreciated.
Regards,
Paul.
Calling Remote Procedure On Oracle Linked Servre
May 24, 2004I have linked Oracle Server as a linked server on my SQL server.
Now i want to execute the stored procedure on oracle server which has one input parameter and one output parameter.
i am using the following sql statements in SQL Server Query analyzer to execute that procedure
declare @sal float
exec [GENEVA].testDB.dbo.EmployeeOutPrm
'1',
@sal output
but getting the following error:
Server: Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'EmployeeOutPrm' on remote server 'GENEVA'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
COULD ANYBODY HELP ME TO RESOLVE THE SAME
many thanks in advance
bhavya
SQL 2012 :: Linked Server And Remote Login Password
Oct 7, 2014Is there any way to find out the password for the remote login of the Linked server
View 1 Replies View RelatedServer Aliases/linked Servers Cause Remote Queries
Apr 4, 2008Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Cannot Connect To Remote Distributor -- Linked Server Error
Mar 8, 2007I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber
originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.
Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...
Any ideas??
Calling A Function From A Remote (linked Server) Problem
May 21, 2008
Hi guys,
I am trying to call a function from a remote sql server (linked server) using the following syntax:
select [ServerName].[dbName].dbo.CLRHelloWorld('SomeMessage'). I am getting the following error:
The object name '[ServerName].[dbName].dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I need to call this function remotely and using this syntax; I don't want to use OPENQUERY...
Any hints?
Yousef
Transact SQL :: Move Data From Linked Remote Server?
Jun 18, 2015I have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
Remote Update Having A Linked Server Takes Forever To Execute
Oct 17, 2006UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
Remote Scan On Linked Server - Fast SELECT/Slow UPDATE
Aug 14, 2001In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Thank you!
Error :Execute Trigger From Remote Server To Another Server By Linked Server
Jul 22, 2007i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
how i can execute this trigger
Linked Server And Remote Server Help
Jan 14, 2004I'm documenting various server configurations such as linked and remote servers. Rather then clicking on each linked and remote server to view their properties I am trying to query sysservers. According to BOL, the column isremote is 1 if server is a remote server, else 0 if server is a linked server. When I query sysservers I see just the opposite. Is BOL wrong or am I missing something? Through Enterprise Manager I see 3 remote servers and 13 linked servers. syssservers shows 3 servers with isremote = 0 and 13 servers with isremote = 1.
Also, I understand remote servers are provided for backward compatibility. If that is the case, why is the default instance listed under remote server and not linked server. Also, why does replication list repl_distribution under remote server and in some cases the subscriber server is also listed under remote servers.
Thanks, Dave
Remote Access Server Configuration Option And Remote Query Timeout?
Jun 2, 2015- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
Remote Connections Enabled, But I Still Get Error That Remote Is Not Configured - Sql 2005
Aug 23, 2006Fellow Devs,
I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.
Any ideas why this might be happening? Is there some other configuration?
SQL Remote Connection Not Being Seen, Though Remote Connections Allowed
Oct 11, 2007HiI'm going insane.I have a club starter kit on my local server which is working fine.I have migrated the database to my remote host and seems to have transfered ok.For a brief period I was able to connect to the database remotely, but now I cannot I get the following, even though SQL Server Surface Area Config is set to accept remote connections.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)This is my connection string;<add name="ClubSiteDB" connectionString="Data Source=xx.xxx.xx.xx,xxxx; Network Library=DBMSSOCN; Initial Catalog=tets; User ID=sqladminbob;Password=sqlbob4398;" providerName="System.Data.SqlClient="/>I hav also tried to use aspnet_regsql.exe to add the remote provider but I get the same message. Thanks
View 3 Replies View RelatedError 15185: There Is No Remote User 'sa' Mapped To Local User '(null)' From The Remote Server 'DTS_FSERVER'.
Nov 10, 2006I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.
I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.
I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.
The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:
Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.
I'm sure the two errors are related. Any ideas what is going on?
Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager
Mar 25, 2002Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
DB Engine :: How To Point Linked Server To Specific Database / Rename Linked Server
Apr 24, 2015I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available
Jul 18, 2006Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
Difference Between Remote Login Timeout And Remote Query Timeout?
Mar 28, 2008I just want to find out what the difference between a remote login and a remote query is. I'd have thought that a remote query inherently requires a remote login.
View 6 Replies View RelatedRemote Tables Are Not Updatable. Updatable Keyset-driven Cursors On Remote Tables Require A Transaction With The REPEATABLE_READ
May 16, 2008
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
DT
Remote SQL
Apr 24, 2007Hello everyone, I am trying to connect to a remote SQL 2005 database from Microsoft SQL Server Management Studio.I enabled the remote connection on the server and creating a user on the server.I am trying to connect to the server from my workstation using sql server authentication with the login name already created on the server.
I am receiving this error: cannot connect to "server name"cannot open user default database. Login failed.Login failed for user "username".(Microsoft SQL Server, Error: 4064)
Can you help?
Remote
Nov 24, 2007hai,
when iam opening an application which is using sqlsever2005.
Its showing an error.
an error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
plz help.
Thanks in advance.
SQL Everywhere Remote
Aug 4, 2006
Being new to this mobile game, ive created a database locally and now wish to place it onto my server but when I change my connection string from c:folderdb.sdf to \Serverfolderdb.sdf i get an error that says "value does not fall within the expected range" ive also tried mapping it ie Q:db.sdf
what am I doing wrong?
Remote Accessing
Dec 10, 2006Hello,I just uploaded a new SQL database to my hosting server.I am using SQL 2005 and SQL Server Management Studio.How
can I remotely access the SQL database, on my hosting server, to create
or alter tables and queries, using SQL Server Management Studio? Thank You,Miguel
How To Connect To A Remote Sql DB
Feb 8, 2007Hi all,myDB is located atX:Program FilesMicrosoft SQL ServerMSSQLDatamyDB.mdfand my code is located at : C:Inetpubwwwrootapplicationfolderhow do I code my server.mapPath?
Btw, can I get VS 2005 to detect the server path when using server solution.Thanks
sub Page_Loaddim dbconn,sql,dbcomm,dbreaddbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("X:Program FilesMicrosoft SQL ServerMSSQLDatamyDB.mdf"))dbconn.Open()sql = "SELECT * FROM myTable"dbcomm=New OleDbCommand(sql,dbconn)dbread=dbcomm.ExecuteReader()myDB.DataSource = dbreadmyDB.DataBind()dbread.Close()dbconn.Close()end sub
Remote SQL Connection
Apr 24, 2007Hello everyone, I am trying to connect to a remote SQL 2005 database from Microsoft SQL Server Management Studio.I enabled the remote connection on the server and creating a user on the server.I am trying to connect to the server from my workstation using sql server authentication with the login name already created on the server.
I am receiving this error: cannot connect to "server name"cannot open user default database. Login failed.Login failed for user "username".(Microsoft SQL Server, Error: 4064)
Can anyone help?