How to find any reference to a linked server in all procs, functions or views

  • 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

  • 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

  • Thanks i'll take a look at that now!

  • 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%'

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply