472,791 Members | 1,578 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 software developers and data experts.

Logging Into Linked Server

After creating a linked server to a remote server, I needed to log in using
sp_addlinkedsrvlogin to get my stored procedure to work. However, I noticed
that after stopping SQL Server and the DTC and then restarting both, that my
stored procedure worked without having to execute sp_addlinkedsrvlogin.

Is the log-in information stored in the machine, such that if SQL Server is
stopped or the server is rebooted, on does not have to execute
sp_addlinkedsrvlogin again? Or is there a point at which one would have to
re-log-in to a linked server?

Thanks.

May 15 '06 #1
6 4689
Neil (no****@nospam.net) writes:
After creating a linked server to a remote server, I needed to log in
using sp_addlinkedsrvlogin to get my stored procedure to work. However,
I noticed that after stopping SQL Server and the DTC and then restarting
both, that my stored procedure worked without having to execute
sp_addlinkedsrvlogin.

Is the log-in information stored in the machine, such that if SQL Server
is stopped or the server is rebooted, on does not have to execute
sp_addlinkedsrvlogin again? Or is there a point at which one would have
to re-log-in to a linked server?


You appears to have missunderstood the purpose of sp_addlinkedsrvlogin. The
procedure does not login into the remote server. I have not checked, but I
would assume that you can run sp_addlinkedsrvlogin without the linked server
being available.

What sp_addlinkedsrvlogin does, as you already have discovered, is to store
information, so that when you issue a query to the linked server, SQL Server
can log in to that data source. So this is a configuration procedure that
you run once, or possible when you need to give a new user access to the
linked server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 15 '06 #2
Speaking of linking to a server, is it possible to assign a linked server an
alias at the time it is linked? The reason is that our linked server is a
web server, and it's possible that sometime in the not-too-distant future
its address will change, and, at that point, I'd have to go into the stored
procedures and manually change the server name. Can I assign an alias so
that if a new server has to be linked it can have the same alias?

Thanks.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Neil (no****@nospam.net) writes:
After creating a linked server to a remote server, I needed to log in
using sp_addlinkedsrvlogin to get my stored procedure to work. However,
I noticed that after stopping SQL Server and the DTC and then restarting
both, that my stored procedure worked without having to execute
sp_addlinkedsrvlogin.

Is the log-in information stored in the machine, such that if SQL Server
is stopped or the server is rebooted, on does not have to execute
sp_addlinkedsrvlogin again? Or is there a point at which one would have
to re-log-in to a linked server?


You appears to have missunderstood the purpose of sp_addlinkedsrvlogin.
The
procedure does not login into the remote server. I have not checked, but I
would assume that you can run sp_addlinkedsrvlogin without the linked
server
being available.

What sp_addlinkedsrvlogin does, as you already have discovered, is to
store
information, so that when you issue a query to the linked server, SQL
Server
can log in to that data source. So this is a configuration procedure that
you run once, or possible when you need to give a new user access to the
linked server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

May 15 '06 #3
Neil (no****@nospam.net) writes:
Speaking of linking to a server, is it possible to assign a linked
server an alias at the time it is linked? The reason is that our linked
server is a web server, and it's possible that sometime in the
not-too-distant future its address will change, and, at that point, I'd
have to go into the stored procedures and manually change the server
name. Can I assign an alias so that if a new server has to be linked it
can have the same alias?


If you are on SQL 2005, you can always use synonyms.

If you are on some earlier version of SQL Server you can use
sp_addlinkedserver. You see, what you define with sp_addlinkedserver is
really an alias.

In its simplest form, you just say:

sp_addlinksedserver 'THATSERVER'

and THATSERVER will refer to a server with that name. However, you
can also say:

sp_addlinkedserver 'MYSERVERNAME', '', 'SQLOLEDB', 'THATSERVER'

so that you can use MYSERVERNAME as a reference to THATSERVER. Thus,
when you web server changes, you just drop the server, and recreate
it with the new information.

Note: the syntax above may not work exactly like that. I usually have
problem with more advanced usage of sp_addlinksedserver myself. But
it usually sorts out when I've been looking at the topic for
sp_addlinksedserver in Books Online for a while.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 15 '06 #4
> In its simplest form, you just say:

sp_addlinksedserver 'THATSERVER'

and THATSERVER will refer to a server with that name. However, you
can also say:

sp_addlinkedserver 'MYSERVERNAME', '', 'SQLOLEDB', 'THATSERVER'


So, basically:

sp_addlinkedserver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.

Neil
May 15 '06 #5
Neil (no****@nospam.net) writes:
So, basically:

sp_addlinkedserver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.


Yeah, but as I recall the second parameter, @srvproduct, may not be NULL.
But you'll find out. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 16 '06 #6
Yup, you were right. Used '' and it worked fine. Thanks!

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Neil (no****@nospam.net) writes:
So, basically:

sp_addlinkedserver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.


Yeah, but as I recall the second parameter, @srvproduct, may not be NULL.
But you'll find out. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

May 16 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Christian.Gruber | last post by:
>Description: It would be nice if general query logging could be switched on and off while the MySQL server is running, and not only at startup time. While debugging an application that uses...
0
by: Moritz Steiner | last post by:
Ok, but this only works for the current session, if I close and restart = the client the settings are reset... -----Urspr=FCngliche Nachricht----- Von: Victoria Reznichenko =20 Gesendet:...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
23
by: Rotem | last post by:
Hi, while working on something in my current project I have made several improvements to the logging package in Python, two of them are worth mentioning: 1. addition of a logging record field...
16
by: Einar HÝst | last post by:
Hi, I'm getting into the Trace-functionality in .NET, using it to provide some much-needed logging across dlls in the project we're working on. However, being a newbie, I'm wondering if some...
3
by: nicholas.petrella | last post by:
I am currently trying to use the python logging system as a core enterprise level logging solution for our development and production environments. The rotating file handler seems to be what I...
9
by: Rasika WIJAYARATNE | last post by:
Hi guys, Please check this out: http://rkwcoding.blogspot.com/2007/07/error-logging.html
6
by: Larry Bates | last post by:
Every time I look at the logging module (up until now) I've given up and continue to use my home-grown logger that I've been using for years. I'm not giving up this time ;-) I find that I...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.