Ok.. forget I even mentioned one is on 2000 and the other on 2005.
I can have both on 2000, that's barely even an issue.
I executed instcat.sql.... no difference.
Let me get this clear first....
1) Remote server (say in FL,USA) is on SQL2k
2) Local Server (say in CA,USA) is on SQL2k
3) I need to import data from Local Server into the corresponding Remote Server tables.
4) When i try executing sp_addlinkedserver on remote server, it says I have no permission to perform this action.
5) So I tried using openrowset on the remote server, as mentioned in the above post. And now it says the Server (Local Server IP in the query) does not exist or Access is denied.
6) I'm so sure there's something I'm doing wrong but I juts can't put a finger on it!
What am I expected to do if I need to perform this operation and have no way of accessing the Remote macine except through SSMS/Enterprise Manager.. whichever.
I'm lost and confused now.
When I execute the openrowset on local server with remote server IP, I can query the reqd. tables... WHAT IS HEPPENING? I'm tearing my hair out!!!!
FROM dcorpdb.dbo.tbl_Designation') AS a;