• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

How do you add a linkserver to an exsternal server on winhost.com

Hi I have a hosted server on winhost.com 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.
0
taz8020
Asked:
taz8020
  • 3
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
How are you trying to add the linked server? Is the winhost.com 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.
0
 
Kevin CrossChief Technology OfficerCommented:
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:

http://connect.microsoft.com/SQLServer/feedback/details/444560/editing-linked-server-properties-thorws-ad-hoc-updates-error
0
 
Andrei FomitchevCommented:
You can link from SQL 2005 to MS SQL 2008 R2

EXEC sp_addlinkedserver  
   @server='BSIRP',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='jayant_PROD_2005'

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

Or use SSMS UI

--select Query
SELECT * FROM OPENQUERY (bsirp, 'SELECT * FROM a')
--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
0
 
taz8020Author Commented:
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 255.255.255.255 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  
   @server='Webserver',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='255.255.255.255/MyDataBase'

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


0
 
Kevin CrossChief Technology OfficerCommented:
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: http://msdn.microsoft.com/en-us/library/ms190479.aspx
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now