Need help with sp_addlinkedserver

I'm on SQL Server 2008 R2.  I am the admin for both servers (Prod and Dev).  In SSMS, I have both servers connected (as sa) and can do whatever I want with either of them.
I got a stored procedure off the internet to compare row counts from tables on Prod server to same database\tables on the Dev server.

When I try to "CREATE PROCEDURE" on the Prod server I get the error:
"Could not find server 'DEV' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

So I try to run the sp_addlinkedserver the site said I need to do before running the "CREATE PROCEDURE":

EXEC sp_addlinkedserver    
    @server='DEV',             -- Remote Computer Name
    @srvproduct='',                 -- Not Needed
    @catalog='MyDB',             -- Database
    @provider='SQLNCLI',            -- SQL Server Driver
    @datasrc='DEV'                       -- SQL Server name

It gives me the error: "The server 'DEV' already exists."

So what give?   One says it cannot find the server and the other is complaining because it does!

Any ideas?


If it matters, here is the Stored Procedure I'm trying to create:

-- Use this script to compare the row counts of all the tables in a database on a server with the same on another server.
-- Script came from

--Change the database name to the appropriate database

CREATE PROCEDURE sp_CompareTables_Different_RowCount
( tblName varchar(50), CountRows int )

( tblName, CountRows)
SELECT AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
AND indid IN(0,1)
AND xtype = 'u'
AND <> 'sysdiagrams'

( tblName varchar(50), CountRows int )

( tblName, CountRows)
SELECT AS "Table Name", li.rowcnt AS "Row Count"

FROM [Prod].[MyDB].[dbo].[sysobjects] lo,  
    [DEV].[MyDB].[dbo].[sysindexes] li
AND indid IN(0,1)
AND xtype = 'u'
AND <> 'sysdiagrams'

( OldTable varchar(50), OldRowCount int, NewRowCount int, NewTableName varchar(50))

( OldTable, OldRowCount, NewRowCount, NewTableName )
SELECT ol.tblName, ol.CountRows, nw.CountRows, nw.tblName
From    #tblNew nw
JOIN #tblOLD ol
ON (ol.tblName = nw.tblName AND ol.CountRows <> nw.CountRows)

-- Use line below instead of line above if you to see all tables and compare row count (even if rowcount is equal)
--ON (ol.tblName = nw.tblName AND ol.CountRows = nw.CountRows)
SELECT * FROM #tblDiff
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Here's the script I use when creating a linked server.

You're using 'DEV' for the datasource which is incorrect.  The datasource needs to be the name of the production server\instance

Declare  @server nvarchar(50) 
	,@source nvarchar(50)
    ,@rpc varchar(50)
    ,@rpc_out varchar(50)
	,@user_name varchar(150)
	,@password varchar(150)
Set @server = 'DEV'; -- alias  how you want to refer to it
Set @source = ', 2005';  -- the actual server name or IP, & Port if not using the standard 1433.  Here you would put in the actual server name
set @user_name = 'SqlAccountToAccess'; -- sql user name 
set @password = 'SqlSecur3Password'; -- sql password

set @rpc = 'true';  -- stored procs and the like?
set @rpc_out = 'true'; -- out values

-- Drop existing server if it exists.
IF  EXISTS (SELECT FROM sys.servers srv WHERE srv.server_id != 0 AND = @server)
EXEC master.dbo.sp_dropserver @server, @droplogins='droplogins'

-- create the linked server
Execute master.dbo.sp_addlinkedserver 
	@server = @server
	,@srvproduct = N'SQLServer OLEDB Provider'
	,@provider = N'SQLNCLI'
	,@datasrc = @source

-- add the login credentials.
Execute master.dbo.sp_addlinkedsrvlogin 
	@rmtsrvname = @server
	,@useself = N'False'
	,@locallogin = NULL
	,@rmtuser = @user_name	
	,@rmtpassword = @password 

EXEC master.dbo.sp_serveroption @server=@server, @optname=N'rpc', @optvalue='true'

EXEC master.dbo.sp_serveroption @server=@server, @optname=N'rpc out', @optvalue='true'

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqdperuAuthor Commented:

I didn't know @server was an alias and didn't know how to enter login info.   Got it working now.   Thanks for the quick response!
sqdperuAuthor Commented:
Exactly what I needed.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.