Connect to a remote server, different name than source 1

[ESquared]

It's easy enough to add a remote server through Enterprise Manager.

And it's easy enough to do it in Query Analyzer:

sp_addlinkedserver
   'serverName',
   N'SQL Server'

I want to connect to one of our servers that doesn't have an alpha network name, just an IP address. But I want to use an alpha server name so my direct references to the server don't have to be [10.1.2.3].database.Owner.Table. I can't figure it out.

sp_addlinkedserver
   @server='ArbitraryServerName' -- NOT the server's address
   , @srvproduct = N'SQL Server'
   , @datasrc = '10.1.2.3' -- The real address of the server
   , @provider = 'SQLOLEDB'
   , @catalog = 'DatabaseName'

Errors:

- You cannot specify a provider or any properties for product 'SQL Server'.

- You must specify a provider name with this set of properties.

- '(null)' is an invalid product name.


Help please? I'm also not feeling good at the moment so maybe I am less than scintillating right now. Perhaps even rather matte.

 

[donutman]

You might try an alias. In Client Network Utilities instead of EM.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

It works.
-Karl

 

[ESquared]

It's not working. Does the alias have to be added from the actual server itself or can I do it from my workstation?

What do I actually do to create the linked server once I've added a TCP/IP alias?

Just using the new name as though it was an existing SQL server in EM does add a linked server but when I try to view its tables I get:

- SQL Server does not exist or access denied.

I tried it both for the remote WAN server I'm trying to access as well as another LAN server here.

 

[ESquared]

Oh, and this is weird, too, because I'm halfway sure I did this a week or two ago somehow.

 

[donutman]

I registered the remote server (alias) and added it to the group in EM. I can work with the tables when logged into that server. The linked server approach is failing (I think) because there is no connection string in sysservers. I'm not at the office so, it's a drag trying to do this with VNC. I need to install EM at home.
I'll play with it tomorrow. It works through VB when you provide the connection string and the alias. Sorry, I jumped to the conclusion that I was using the linked server when in fact I was just using the alias and registered server in EM! I think the solution is going to be to use a different provider.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

If you go into QA without selecting a db, then you have to open a connection to a server. So I'm thinking that this is a limitation of QA. DTS can handle multiple connections. Just thinking out loud.
But I'm going to test a manual manipulation of sysservers to include the UID and PWD values similar to the Jet connection requirements. Also want to try using an ODBC DSN.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I tried several ideas, none worked. Any luck on your end?
-Karl

 

[ESquared]

No luck, but I didn't really get a chance to work on it today.

Did I post this question in the wrong forum?

 

[donutman]

It may in fact be the password issue. Check these links out:

http://www.samspublishing.com/articles/article.asp?p=131009&seqNum=4

http://www.sql-server-performance.com/linked_server.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;274098

The first one may do it. I'll check tomorrow.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I forgot to work on this. Did you?

 

[ESquared]

I pulled up the first link this morning and got so busy, I forgot. I still need it for daily querying of live data from our live site (but letting the local test servers do the work because I want minimal load on the production server).

Will get to it Monday, probably.

 

[ESquared]

I figured out the problem.

The product name is 'SQLServer' not 'SQL Server' like it says in Books Online.

I finally caught it because when I followed instructions on the first link you gave, it worked. At first I thought it was with named variables but that wasn't it.

All happy now!

 

[donutman]

That's strange. I got it to work by mapping the password. And it's interesting that the login information isn't stored in sysservers not at least in the viewable columns as it is for an access database. I used SQL Server with the space!
I also got it to work with svrProduct='SQLOLEDB' with the appropriate login data (where it is viewable).
Don't know what to make of our different results. You're using the alias name for the link, right? It's surprising to me that the space alone solved your problem, because it looks clear from the above link, that a space should be alright. I did notice the grid that shows it without a space.
I'm a little bumbed by Nigel's point about doing cross server joins. I'd really like to be able to put the issue into perspective, but without more data on the performance implications it's rather hard to. I wonder if he was speaking of WAN links only? Nah, he said cross server joins, not remote servers where there's more chance for ambiguity.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I don't think you can categorically say a cross-server join is always bad. It depends on what the join entails and how big the tables are. You can also force processing to be done all one one server or the other.

Granted, you're definitely not going to want to have a remote server send a million-row table just so you can join it locally. That's why I said what I did in the other thread about careful use of conditions to return a different rowset. What if you made the query to the other server a derived table that had some very restrictive WHERE conditions? If it's not an often-run query, and you only return a few hundred rows from remote, and then do most of the work on the local server, who cares?