sp_setnetname (T-SQL)

Sets the network names in sysservers to their actual network computer names for remote computers running Microsoft® SQL Server™. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing invalid SQL Server identifiers.

Syntax

sp_setnetname @server = 'server',
@network_name = 'network_name'

Arguments

@server = 'server'

Is the name of the remote server as referenced in user-coded remote stored procedure call syntax. Exactly one row in sysservers must already exist to use this server. server is sysname, with no default.

@network_name = 'network_name'

Is the network name of the computer to which remote stored procedure calls are made. network_name is sysname, with no default.

This name must match the Microsoft Windows NT® computer name, and it can include characters that are not allowed in SQL Server identifiers.

If a DB-Library alias matching the network_name is defined on the SQL Server computer, the connection string information in that alias is used to connect to the remote SQL Server.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

Some remote stored procedure calls to Windows NT computers can encounter problems if the computer name contains invalid identifiers. Use this procedure to differentiate the values in sysservers.srvname versus sysservers.srvnetname.

Because linked servers and remote servers reside in the same namespace, they cannot have the same name. It is possible, however, to define both a linked server and a remote server against a given server by assigning different names and using sp_setnetname to set the network name of one of them to the underlying server's network name.

--Assume sqlserv2 is actual name of SQL Server --database server

EXEC sp_addlinkedserver 'sqlserv2'

GO

EXEC sp_addserver 'rpcserv2'

GO

EXEC sp_setnetname 'rpcserv2', 'sqlserv2'

Note Using sp_setnetname to point a linked server back to the local server is not supported. Servers referenced in this manner cannot participate in a distributed transaction.

Permissions

Execute permissions are restricted to members of the setupadmin fixed server role.

Examples

This example shows a typical administrative sequence used on SQL Server to issue the remote stored procedure call.

USE master

EXEC sp_addserver 'Win_NT1'

EXEC sp_setnetname 'Win_NT1','Win-NT1'

EXEC Win_NT1.master.dbo.sp_who