none
How To Find Hidden Reference?

    Question

  • We are migrating 2 old databases to a new combined database.  As such the tables have new fully-qualified names.  Most of the stored procedures still work, but I have one where we insert from a select like this:

    INSERT INTO tblTOTALTIME
     SELECT E.Code, P.PUNCHDATE, .......

    It fails with the error "Invalid object name 'CSTD.dbo.tblTOTALTIME' ".  The database name CSTD no longer exists.  I am sure the error happens on the first line because if I modify the stored procedure like this the error goes away:

    -- INSERT INTO tblTOTALTIME
     SELECT E.Code, P.PUNCHDATE, .......

    Where can I find the old reference to CSTD.dbo. in the database?  I checked and I don't see any triggers.  Where else can I look?


    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 2:54 PM

Answers

  • You need to look in the code for stored procedures or in the view definitions. I suggest you to get free SQL Search tool from Red Gates and search your database for CSTD.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by DallasSteve Thursday, February 14, 2013 3:51 PM
    Thursday, February 14, 2013 2:56 PM

All replies

  • You need to look in the code for stored procedures or in the view definitions. I suggest you to get free SQL Search tool from Red Gates and search your database for CSTD.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by DallasSteve Thursday, February 14, 2013 3:51 PM
    Thursday, February 14, 2013 2:56 PM
  • Naomi

    Thanks.  I went to Red Gates website and they have a lot of tools.  I downloaded the 14-day trial of SQL Developer Bundle.  Is that what I want?  What tool do I need to run?

    Steve


    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 3:21 PM
  • Nevermind.  I did a better search and found SQL Search tool from Red Gates.


    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 3:22 PM
  • So far I'd have to say Red Gates sucks pretty bad.  I ran the SQL Search installer and a screen appears that says select the tools to install and it has a list below with only SQL Search.  But there is no way to select it.  I clicked on everything and the only thing that works is the link to their website.  That's pretty useless.  The only other thing I can click is the Cancel button.  Is it because SQL Server is not running on my machine?

    So then I tried their developer toolbelt.  There is a list of tools you can select, but you can't select SQL Search.  I selected packager just to do the install, and it installed packager, but it didn't install SQL Search which I can't select.  Maybe I don't have some minimum system which is required for SQL Search, but that seems pretty lame. 


    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 3:29 PM
  • Looking some more I have to have SQL Server Management Studio 2008 installed for SQL Search to work.  Isn't there any other way to search for hidden references to the old database name like searching sysobjects?

    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 3:31 PM
  • Strange :( I think I have it installed on both SQL Server 2012 and SQL Server 2008 R2 and I don't have any problems. You do need to have SQL Server installed and SSMS installed. I have both installed locally, so I haven't run into your scenario of having SQL Server installed on the Server.

    There is another useful thing that also allows search and I have it installed as well for SQL 2012 instance only.

    http://www.ssmsboost.com/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 14, 2013 3:34 PM
  • OK, I did some thinking, painful as that may be, and decided to try to install SQL Search on the server since I have access.  Duh.  That appears to be working.

    Thanks


    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 3:37 PM
  • I installed SQL Search and it doesn't appear in Windows Programs Start.  I checked the Program Files folder for Red Gates and it installed a SQL Search dll.  Now what?  I looked in SQL Server Management Studio and I don't see how to run SQL Search.  They're not making this easy.


    MCSD .NET developer in Dallas, Texas


    • Edited by DallasSteve Thursday, February 14, 2013 3:40 PM
    Thursday, February 14, 2013 3:39 PM
  • It should be inside your SSMS (management studio). That's why I use it for scenario where I have both SSMS and SQL Server installed locally. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 14, 2013 3:43 PM
  • Naomi

    I saw on Red Gates that their icon appears in the toolbar in SSMS, but it wasn't in mine.  I couldn't find any way to add it to the toolbar and their website didn't have any instructions.  So in a desperate last resort I re-started SSMS and now it appears.  I guess you have to restart SSMS.  I tried their search and it looks like it is finding the answer I need.  It looks like a good tool after all.

    Thanks


    MCSD .NET developer in Dallas, Texas

    Thursday, February 14, 2013 3:51 PM
  • A couple of ideas. 

    First it might be a synonym.  You can use synonyms to do things like

    Create Synonym tblTOTALTIME For CTSD.dbo.tblTOTALTIME;

    If you have run the above code and then make any reference to tblTOTALTIME, SQL will use CTSD.dbo.tblTOTALTIME instead.  You can check for synonyms by running

    Select name, base_object_name From sys.synonyms;

    Another would be to look for the character string CSTD anywhere in any code (stored procedures, functions, triggers, etc).  That could be done with

    Select o.name, o.type_desc
    From sys.sql_modules m
    Inner Join sys.objects o on m.object_id = o.object_id
    Where m.definition like 'CSTD';

    Tom

    Thursday, February 14, 2013 3:52 PM