Linked Servers

I would like to create two linked servers to the same data source.  The first 
linked server has fairly open access to the database.  The second linked 
server will have limited access to the database (readonly on one view).  The 
two Linked Servers would be configured to access the database through 
different logins on the source server with different access to the same 
database.

The first Linked Server works fine.  I can't seem to configure a second 
linked server to the same datasource, even if I define the Server type as 
another data source.

How can I create two linked servers to the same datasource?

Thank you!!
0
Utf
8/26/2010 3:57:03 PM
sqlserver.server 1327 articles. 0 followers. Follow

2 Replies
969 Views

Similar Articles

[PageSpeed] 51

LisaM (LisaM@discussions.microsoft.com) writes:
> I would like to create two linked servers to the same data source.  The
> first linked server has fairly open access to the database.  The second
> linked server will have limited access to the database (readonly on one
> view).  The two Linked Servers would be configured to access the
> database through different logins on the source server with different
> access to the same database. 
> 
> The first Linked Server works fine.  I can't seem to configure a second 
> linked server to the same datasource, even if I define the Server type as 
> another data source.
 
EXEC sp_addlinkedserver 'SECONDLINK', '', 'SQLNCLI', 'THESERVER'

Note: I'm here assuming that the other server is SQL Server, and that you 
are on SQL 2005. If you are on SQL 2008, use SQLNCLI10 instead. On 
SQL 2000, use SQLOLEDB.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/26/2010 9:29:21 PM
Thank you Erland!
I was using the Wizard and finding it difficult that way.
Anyway, this is what I finally employed:

exec sp_addlinkedserver 
@server=N'SECONDLINK',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'server_name'

....and it worked like a charm.

"Erland Sommarskog" wrote:

> LisaM (LisaM@discussions.microsoft.com) writes:
> > I would like to create two linked servers to the same data source.  The
> > first linked server has fairly open access to the database.  The second
> > linked server will have limited access to the database (readonly on one
> > view).  The two Linked Servers would be configured to access the
> > database through different logins on the source server with different
> > access to the same database. 
> > 
> > The first Linked Server works fine.  I can't seem to configure a second 
> > linked server to the same datasource, even if I define the Server type as 
> > another data source.
>  
> EXEC sp_addlinkedserver 'SECONDLINK', '', 'SQLNCLI', 'THESERVER'
> 
> Note: I'm here assuming that the other server is SQL Server, and that you 
> are on SQL 2005. If you are on SQL 2008, use SQLNCLI10 instead. On 
> SQL 2000, use SQLOLEDB.
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 
0
Utf
8/31/2010 3:54:06 PM
Reply:

Similar Artilces:

Clustered Exchange Servers and the Exchange Router
I have a client that has recently installed the Exchange Router. We can send email out fine and it displays the GUID on the subject line and creates the appropriate closed email activity using both the browser and SFO client. The problem is that replies to these emails do not create the corresponding activity records in CRM. The user gets the email in their inbox in Outlook. We're being told by MBS support that clustered Exchange Servers are not supported and I just do not understand why this would matter. We've tried and double checked every other DOCUMENTED prerequisite and tk t...

Change link between form and subform
I have a form with a subform in it. I would like to change the way they are linked so instead of linking from Old ID, they link to New ID I don't know anything about code, is there a way to just change the cell it relies on? Thanks C Confused87 - Bring up the properties of the subform, and change the values in 'Link Child Fields' and 'Link Master Fields' on the Data tab of the properties dialog box. Make sure you have the subform selected, not the form within the subform. -- Daryl S "Confused87" wrote: > I have a form with...

OMA internal server error 500
Hello - I've just tried to get OMA setup and I've encountered th following error http: error 500 (internal server error Runtime erro <!-- Web.Config Configuration File -- <configuration <system.web <customErrors mode="Off"/ </system.web </configuration to display error messag Having changed the web.config file the error still persists This occurs when I logon with domain\username: passwor What is the problem? There are a few posts relating to this but n responses - if anyone responds to this I'll be most greatful ...

send outgoing mail through the front-end server
Currently, I have a front-end server that provides OWA services and receives all incoming mail, which is then forwarded to the backend server. All outbound mail is currently sent directly from the backend server out to the Internet. This, of course caused issues with mail providers that used reverse dns. Although I created a band-aid solution, I would like to know what would be the possibility and ramifications of forwarding the outbound mail through the front end server. So, then all Internet mail would flow through that device. Any suggestions are greatly appreciated. You could ...

Send To and pdf and Outlook 2007 and Server 2008 and SQL 2005
Hi all, I am having trouble with simply sending pdf documents from the screen layout window for any report generated in GP. I can however send a txt report via email using the Send To functionality. The button is active as is the option to select either Txt or Pdf but the new message window that normally opens up for you to insert a mail address (with the respective attachment) simply does not open. My client is using Outlook 2007, the server is on Server 2008 running SQL 2005 SP3. We get the same issue from all clients as well - the clients are on Windows XP or Visat combination. ...

linking #4
I am trying to link and .slk file to a .xls file all the links appear to be updating but i keep getting a message that excel cannot update 1 or all of the links. Is this common when linking with an .slk because i have several linked wrk books and have never had this problem. thanks Dean ...

Linked Forms
Hello, I am doing a project that requires two forms.. The first form contains the data for a business the second form contains data for the business owner... How do I link the two forms together...? Many thanks. Bob Send a common key piece of data from the first form (say the company name) to the second form page and include it in the second form as a hidden form field (then if using a database to store the results link with a relationship the 2 results tables by the common field) For form passing information see http://irt.org/articles/js063/index.htm -- ____...

How do I setup a recovery server and do the recovery?
How do I setup he recovery server? I have a spare 2003 server box, do I just put exchange server on to that and restore the database file into the mdsdata directory and then run integrity tools? Does it have to be the same organization or can it be a stand alone server. Thanks. Are you trying to rebuild a server to get it back online? Or are you trying to restore a database to get some data out of it. If you are trying to restore a database, try restoring to a recovery storage group. If you are just doing test restorations or testing D/R procedures, you do NOT want to put the...

[?] change Server Exchange 5.5
Hallo, I have an organization unit with 2 exchange servers 5.5, both with public IP in NAT (isa server). One of these servers is broken and I have to change it with a new server. So, I have installed win2000 server, exchange 5.5 (in the same O.U.) with sp4. Some questions: a) In Routing I have 200-300 domains; is there a way to copy and paste all of them in the new connector, or I had to manually write them? b) Is there a way to use pop3 without installing the Internel Mail Service? I have create a mailbox in the new server (without the Internet Mail Service), I have tested it: all is ok for ...

Leaving messages on server
Where do I select the option to leave messages on server so I can view them on another computer? In the advanced properties of your POP mail account settings. Without knowing your version of Outlook, hard to tell you more. Troy wrote: > Where do I select the option to leave messages on server > so I can view them on another computer? ...

Links not linking
Hello I have written a fairly big spreadsheet linking through the pages with SUM, SUMIF and SUMPRODUCT formula's What I am now finding is that when I update one page it doesn't update the rest, even if I am only typing in a figure to the SUM function. I have check and the calculations function is on automatic. is there a fix or something that I could run to make sure that all the formulas are working correctly. thanks Just a guess (since you already checked tools|options|calculation tab). How about selecting all the cells (ctrl-a (twice in xl2003)) and then edit|replace what: ...

Linked Table Manager in ACCESS
Hi, I am trying to change a field in an ACCESS table and get an error message that says the table is a linked table and fields can't be changed. After googling for some answers, I think I should be able to find out the link using "Linked Table Manager" in ACCESS. However, the "Linked Table Manager" button is grayed out. Any ideas/suggestions are welcome. Thanks. Richard Open the table in Design View. Reduce the window so that you can see the window's top bar. Right click in the top bar of the window (usually blue in color) and select ...

Link To A Cell From Chart
Hi all. I have a text box in a chart worksheet. Can I link it to an information from any cell in other worksheet in the same workbook. Thanks. Yes. Click the text box icon, then click on the chart sheet to insert the text box. Click in the formula bar and =Sheet1!A2 (or whatever cell you want to link). -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Salza" <salza@tm.net.my> wrote in message news:3fbfb0bf_2@news.tm.net.my... > Hi all. > I have a text box in a chart worksheet. Can I link it to an information from > any cell in other worksheet in...

Email Server is offline error msg
Hello, I upgraded a user's Office XP that didn't have any SPs to SP2. Now when she double-clicks the Outlook icon, the "Email Server is offline" error message is displayed. When she clicks on retry, her Outlook launches okay and her inbox appears. And her email appears to work okay. Does anyone know how to fix the error message? Thanks, Millie Sounds like a name resolution problem to me. Can you ping the Exchange server, by name, from the affected workstation? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unab...

links
Dear All, It is very critical for my business to learn the basics and the backbone of links in Excel. Are there any tutorials or articles that gives wealth of information about MS Excel links? (in Excel 9.0.6) Web addresses are also welcome. You can also post to my e-mail above. Thank you in advance. Mustafa .. I would advise you go to the newsgroup "microsoft.public.excel.links", and read everything you can about their troubles there and the solutions......... Vaya con Dios, Chuck, CABGx3 "Mustafa" <anonymous@discussions.microsoft.com> wrote in messag...

Linked Tables Over A LAN
Hi, I have a problem with a PC that is sharing an Access database over a LAN. I'm hoping someone may be able to give me a little advice. By the way, I'm a bit of an amatuer so go easy on the technical terminology ;-). I've got four PCs networked through a router which provides internet access. Two PCs are running XP Pro and two are running Vista Business 32bit. One Vista machine holds my full database while the other PCs have a similar database but with tables linked to the first machine. Been running this setup for several years, on various older PCs, with no problems. My proble...

Links
Every time I open a spesific workbook, I get the question if I want t use the old or the new data. This is very irritating! How do I disabl the link that is the reason for this message??? Please help me befor this drives me CRAZY!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Siri You will have a formula somewhere within the wordbook that is linked to another workbook. You can look for them manually and the copy>paste special>values... to kill it. or you could d...

email links in Publisher pdf
Why won't Publisher 2007 convert my email links correctly when saved in pdf format? It puts "mail to:" in twice automatically. It is converting website links without a problem. If memory serves the Office 2007 SP1 fixed this in Publisher. The SP2 is also now available. There have been some reports of not being able to open existing Publisher files after installing it, and a report that a fix for that bug is due by the end of the month....you might want to wait to install SP2 until after the first of the month, or just install SP1. DavidF "Rora" <Rora@discu...

ssl without isa server
Hello, Has anyone implemented SSL on the CRM web client without ISA server? If so, what are the steps that were taken? In our case, the site works fine over port 80 (http). If SSL is not required in IIS, the site even works well from over port 443 (https) -- I assume because it is still doing something non-SSL after hitting the site. Once SSL is required in IIS, the left menu appears, but not the middle/ main section of the application. The server is <servername> and the cert is registered to crm.<company>.com. I have tried all combinations with the serverurl registry entry (...

Replicating Public Folders on different exchange servers
Hello, I have a question about the replication service in Exchange 2003. I am using the following method to replicate the folders: "To replicate a folder using the push method, select the folder on its home server. Open the Replicas tab in the folder's Properties box. Select the sites you want to replicate the folder to, select a server within each site to hold the folder, and click Add. The push method lets you set up replicas of a folder on several servers in one operation." I have quite a few public folders and this job looks very tideous. At the moment, I am selecting ea...

Chart template and links
Hi, I have something "strange " (at least for me) going on ... I have an xls workbook, containing some data sheets and also 3 chart templates. These chart templates are copied via a macro to the output xls with the statement : Sheets(ChartName) _ .Copy Before:=Workbooks(targetfile.Window).Sheets(1) When I now look to this chart template in the output xls I got a link to the original xls workbook in which the macro is running. (I can see that via EDIT>LINKS...) Now I do NOT want that link to the originator xls workbook at all ! Is there anybody who knows how I can co...

Second Virtual Server
Hi, If I were to create a second virtual server on port 26 and wanted to route all outbound mail through that port (which would be defined with a specific smarthost) how would I do it? I would still want to receive all inbound mail on the default on port 25. Thanks, D How to Creat an Additional SMTP Virtual Server How to change the IP Address and Port Number How to Set a Smarthost http://support.microsoft.com/kb/266686/en-us For folks that absolutely insist on attempting the sample SMTP Transport Event Sink in the KB, you can create a second virtual server, route the first through ...

which evet will fire when the client closes its connection with Socket server?
Hi, I created a Stream socket server derived from CSocket and some clients r connected to it.Which event is triggered when the Client closes its connection with server? or How do i know there is no connection between server and client? Reny ...

Linked Graphs from Excel
I have linked graphs from excel into my publisher document using Paste Special. The problem is that when they paste in they reshape (I have pie charts and they come in publisher looking like tall skinny ovals). I can fix the shape of the charts manually after pasting them in, but when I update the links the reshaping reverts to its original, incorrect size. Any suggestions on how I can use the linking to excel and not have to resize every time I update? "Paste Special" as what format? Have you tried an image format? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http...

Linking to ODBC Linked Tables
Hi: I wonder if you have any suggestions for this. We have moved our Access tables into Oracle and have created a MS Access Database that links to these ODBC tables. Each of our users has a local copy of our Reporting Database that formerly linked to Access tables, but now we want them to link to the Ms Access Database which contains the linked ODBC tables on our shared drive. In trying to link to already linked ODBC tables, Access gives an error and will not link to these tables. Is there a way to let our users link to the Access database that contains the linked ODBC tables? Any...