Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, July 6, 2015 4:48 AM
Points: 6, Visits: 331
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 717, Visits: 2,281
Hi,

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

Thanks,


Simon




MCSE: Data Platform
MCSE: Business Intelligence
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, July 6, 2015 4:48 AM
Points: 6, Visits: 331
Thanks i'll take a look at that now!
Post #1481187
Posted Wednesday, August 7, 2013 11:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 3,481, Visits: 8,072
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%'




"If you break a few eggs, then make an omelet."
Post #1481979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse