Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find any reference to a linked server in all procs, functions or views Expand / Collapse
Author
Message
Posted Monday, August 05, 2013 4:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:28 AM
Points: 4, Visits: 171
Hi,

I'm currently trying to replicate an entire SQL2000 database to another server. The distributer fails while trying to deliver the snapshot to the subscriber with this error

- Could not find server 'UKSQL01' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (Source: MSSQLServer, Error number: 7202)

The server UKSQL01 no longer exists, however it is still being referenced somewhere in the database i'm trying to replicate. I tried to create a linked server anyway with that name so there would be a reference in sysservers but that failed too.

Is there a way i can check all procs views and functions for references to this defunct linked server? Or a peice of software i can use to search for this reference in all objects? So i can remove it.

Thanks
Post #1480860
Posted Monday, August 05, 2013 5:06 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 07, 2014 1:34 AM
Points: 702, Visits: 2,163
Hi,

You could try SQL Search from RedGate that has proved to be an invaluable (free too!) tool for me at times.

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1480875
Posted Tuesday, August 06, 2013 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:28 AM
Points: 4, Visits: 171
Thanks i'll take a look at that now!
Post #1481187
Posted Wednesday, August 07, 2013 11:05 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 1,477, Visits: 4,281
You can search text in syscomments table like so:
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%UKSQL01%'




"Winter Is Coming" - April 6, 2014
Post #1481979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse