| Author |
Topic |
|
Dabbler
Starting Member
7 Posts |
 Posted - 2007-07-08 : 17:51:07
|
| I'm trying to run queries between two servers and SQL databases.remote hosted server: myremotesql.com, SQL Server 2005 (no sysadmin privileges) db=remotedbmy localhost server SQLExpress 2005 database=localdbwhile connected to my remote server, my add server cmd:use remotedbexec sp_addlinkedserver @server='linkedsql', @srvproduct= '', @provider='SQLOLEDB', @datasrc='MYPC\SQLEXPRESS', @location='', @provstr ='DRIVER={SQL Server};SERVER=MYPC\SQLEXPRESS;UID=sa;PWD=sa'this command completes but when I try and query a table on the linked server as in:select * from openqry(linkedsql, 'select * vendors') I get error "Could not find server 'linkedsql' in sys.servers"if I exe sp_linkedserver I don't see linkedsql in the list just the myremotesql.com I was connected to. Any help with the sp_addlinkedservers parameters would be appreciated!Dabbler |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-08 : 19:52:51
|
| Tried create linked server in ssms? |
 |
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-07-08 : 20:46:15
|
| Three things I see off the top of my head:"(no sysadmin privileges")But you do have ALTER ANY LINKED SERVER permissions, right?"UID=sa"No way, now how would I ever do this. Even on a development system. And especially if you're not the owner of the remote server. I always create an application specific SQL account for linked servers, if I can't use Windows authentication."select * vendors"That's not going to work on any server, linked or not. :-) Cut and paste error?I also notice that you're using a domain name to refer to the remote server, and using a NetBIOS name to refer to your local PC. If they're not on the same network, does the remote machine have a path to your local machine? Meaning, does 'myremotesql.com' even know where 'MYPC' is?____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
Dabbler
Starting Member
7 Posts |
Posted - 2007-07-09 : 12:34:30
|
| Not sure how to tell if I have ALTER ANY LINKED SERVER permission?I use sa on my local machine only, it only has test data on it, is behind a firewall and NAT so a remote machine can't see mine. Probably not best practices ;)Yeah, that select was a typo... sorry I was trying to simplify my actual scenario.The remote machine does not have a path to my machine...due to NAT etc.But since I'm running this request from my local machine I assume it doesn't need to?I did try with SSMS but it creates a linked server with sql.myserver.net name, then when I try and name the server in OPENQUERY(sql.myserver.net ... it complains about all the dots!Thanks for looking at this.Dabbler |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-07-09 : 13:58:56
|
quote:
Originally posted by Dabbler
The remote machine does not have a path to my machine...due to NAT etc.But since I'm running this request from my local machine I assume it doesn't need to?
Yes, you're running it from your local machine. But you're running it on the remote machine. Telling the remote machine to connect to your local machine. Which it doesn't have a path to in the first place. See the problem? Not possible under your current network setup. sp_addlinkedserver is more than happy to create a connection with just about any working set of parameters. It doesn't test the connection, however, so you won't know if it works until you try running something on it.I'm guessing that the openquery complains about the dots because it thinks it's a multi-part identifier. You would need to put the server name in brackets [] for it to be recognized as a single entity. What I would do in this case, is create the linked server on your machine, pointing to the remote machine instead, and query that from your local machine.____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
Dabbler
Starting Member
7 Posts |
Posted - 2007-07-09 : 14:30:46
|
| Thanks for that clarification. I did try to create link from local to remote but then I run into the sysadmin issue, since our remote provider doesn't allow shared hosting accounts to have sysadmin role. Guess I can't get there from here.Thanks for your help!Dabbler |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-07-09 : 15:42:44
|
| What is it you're trying to do? Maybe there's another way.____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-09 : 15:44:48
|
| I don't understand why readysetstop is saying that it will run on the remote machine. If you are running your application from your local machine, then have the application store the information from the remote machine into say a data table for temporary storage, then insert that data into your local database. This way the remote SQL Server and the local SQL Server do not need to communicate with each other. Your application would handle everything.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Dabbler
Starting Member
7 Posts |
Posted - 2007-07-09 : 16:22:14
|
| what I'm trying to do is use the data from one database table to update a table on another server ( I need to do this for multiple tables, but not the whole database). both databases are on remote hosted servers (so can't use BCP etc). one is sql2000, the other is sql2005. Since I have SQL Express locally I don't have Enterprise Manager. The linked server was recommended by someone else to avoid having to move tables from one server to another (apparently no mean feat either). I'm open to any suggestions that lets me easily move a table from one server to another,which although not ideal, would allow me to complete my task.Dabbler |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-07-10 : 09:15:10
|
| Tara, I never said anything would run on the remote machine. What I was understood was, that he was trying to create a linked server on the remote server, to his local machine, which it does not have a network path to. (By running sp_addlinkedserver on the remote machine.) From what I understand, that's not going to work. Unless there's something about sp_addlinkedserver that I don't understand?I thought we were just moving data, here... via a query or something. When did we start talking about 'applications'?Dabbler, if you can see both remote servers from your box, then I see no reason you can't create linked servers on your box that point to the remote servers, and just use a simple query to transfer the data, or import/export any number of ways, just bringing the data to your box, and transferring it to another.Sorry if I misunderstood things, please correct me if I'm wrong.____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-10 : 10:31:17
|
| Yes, you need direct connection to create linked server. |
|
|
|
Dabbler
Starting Member
7 Posts |
Posted - 2007-07-10 : 10:51:49
|
| readysettop - yes, that's all I'm trying to do, use query to move a few tables back and forth (I need to combine data from both databases). The problem is I'm unsure what the parameters of the linked servers should be, especially the provider string. My pc is SQL Express trying to connect to one SQL2000 and one SQL2005 server, I don't have sysadmin privileges in either remote server.So if this can be done I would love to have a clue as to what the sp_addlinkedserver parameters should be.Thanks!Dabbler |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-10 : 11:16:17
|
| You can create linked server in ssms with gui if don't know how to use sp_addlinkedserver. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-10 : 12:39:52
|
| Do we even know if the two remote servers can talk to each other? Can they ping each other or communicate in any way? If they can't, then using a linked server is not going to work and the solution will need to run from your local machine using SSIS, DTS, bcp, or a custom application.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2007-07-10 : 14:53:08
|
| Is your login info the same on both the remote machines?____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
|
sp_addlinkedserver confusion