How do you add a linkserver to an exsternal server on

Posted on 2011-10-14
Last Modified: 2012-06-21
Hi I have a hosted server on 2008 and a internal server win2005.
Using sql 2008r2 server management how do I add a linked server.
in the security tab I have entered to remote username and  password but get error ad hoc update are not allowed.

Please help i need this to do the updates/sync every hour.
Question by:taz8020
    LVL 59

    Assisted Solution

    by:Kevin Cross
    How are you trying to add the linked server? Is the server accessible via the Internet? If so, do you have a specific IP address and instance name. You would connect to that as X.X.X.X\Instance in the linked server as you would any local instance of SQL you were using as a linked server. The trick is just navigating Firewalls.
    LVL 59

    Expert Comment

    by:Kevin Cross
    And maybe you are seeing the issue in this connect thread, though I have not experienced it connecting to linked servers internally, but maybe I missed it as I have been toying with SQL Denali's SSMS and just connect to my SQL 2008/2005 instances from there. Anyway, here is the thread, click on the workarounds tab:
    LVL 8

    Accepted Solution

    You can link from SQL 2005 to MS SQL 2008 R2

    EXEC sp_addlinkedserver  

    EXEC sp_addlinkedsrvlogin 'BSIRP', 'True', '\jayant.das', 'Password'

    Or use SSMS UI

    --select Query
    --Insert query
    INSERT INTO bsirp.master.dbo.a (Id, Name) values (1,'jayant')
    --update Query
    UPDATE bsirp.master.dbo.a set Name='somename' where Id=3
    --delete query
    DELETE FROM bsirp.master.dbo.a where Id=4
    LVL 3

    Author Comment

    Hi thanks all. yes it is a webserver i want to link to. I Just dont get how i put the query together
    Lets say the ip of the server is and the database is Called MyDataBase, Username is MyUserName And Passwoed is myPassword. I have also tried from my laptop which uses sqlexpress.

    Would you put the query like this?

    EXEC sp_addlinkedserver  

    EXEC sp_addlinkedsrvlogin ''Webserver'', 'True', 'MyUserName', myPassword

    LVL 59

    Expert Comment

    by:Kevin Cross
    You are welcome! I have been out most of the day, so just catching up. Not sure which came first out of the comment above and your accepting. Just in case you did not get your question sorted, the data source should be the server name or IP address and instance name. Your database would be specified through @catalog parameter.

    Here is a link to the BOL for other details:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now