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 5, 2013 4:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:19 AM
Points: 5, Visits: 257
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 5, 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, September 29, 2014 2:55 AM
Points: 702, Visits: 2,179
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 6, 2013 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:19 AM
Points: 5, Visits: 257
Thanks i'll take a look at that now!
Post #1481187
Posted Wednesday, August 7, 2013 11:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 1,677, Visits: 4,785
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%'

Post #1481979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse