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?

Thanks

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 http://sqln.blogspot.com/2008/10/tsql-code-compare-tables-of-databases.html?_sm_au_=iQHNFskH8qqFMDpq



--Change the database name to the appropriate database
USE [MyDB];
GO

CREATE PROCEDURE sp_CompareTables_Different_RowCount
 AS
 
 CREATE TABLE #tblNew
( tblName varchar(50), CountRows int )

INSERT INTO #tblNew
( tblName, CountRows)
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY o.name

CREATE TABLE #tblOLD
( tblName varchar(50), CountRows int )

INSERT INTO #tblOLD
( tblName, CountRows)
SELECT lo.name AS "Table Name", li.rowcnt AS "Row Count"

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

CREATE TABLE #tblDiff
( OldTable varchar(50), OldRowCount int, NewRowCount int, NewTableName varchar(50))

INSERT INTO #tblDiff
( 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
         
DROP TABLE #tblNEW
DROP TABLE #tblOLD
DROP TABLE #tblDiff
sqdperuAsked:
Who is Participating?
 
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 = '127.0.0.1, 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 srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @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 


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

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

Open in new window

0
 
sqdperuAuthor Commented:
Awesome!

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!
0
 
sqdperuAuthor Commented:
Exactly what I needed.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.