locked
There is already an object named "..."

    Question

  •  

    I'm seeing some pretty strange beahviour from Data Dude.  (with SR1)

     

    I get an error message of "There is already an object named ".  I then go into the database created by Data Dude (with the GUID attached to the database name.)  Looking through SMS treeview, I do not see this particcular stored proc.  When I query INFORMATION_SCHEMA.ROUTINES, it shows up!!!

     

    So, I run a manual drop statement, and then go back to Data Dude, and all is well.  This is just one of the many strange errors I see in my project.  I don't understand how SQL Server is allowing a stored proc to be created, but not displayed in the treeview.  Furthermore, why is it that this project ran into that problem in the first place?  Really wierd.  I am sifting through a whole raft of other such wierd issues, and this almost seems like a loosing battle.  Help!

     

    dcb99

    Wednesday, September 05, 2007 10:41 PM

Answers

  •  dcb99 wrote:

    1. No way to reference other databases that I have found so far (on same server.)  Any such stored proc do not compile.  What is the recommended path to solve this?  Currently can only reference objects in the database for this project.

     

    Seems like you are not using Service Release 1, please see the Database References blog entry

    http://blogs.msdn.com/gertd/archive/2007/07/26/database-references.aspx

     

     dcb99 wrote:

    2. While although my instance is not case-sensitive, when I pulled this database in Data Dude errored out on all sorts of case-sensitivity problems in stored procs and views.

     

    Variables and parameter case sensitivity is based on the case sensitivity of the server installation, not on the database, if you have a mismatch between the local design time validation instance and the target database this can lead to mismatches.

     

     dcb99 wrote:

    3. Some columns that exist, are reported by Data Dude as not existing (and thus the stored procs don't compile.)

     

    My guess is that this is caused by either case sensitivity mismatches, on which objects are is this happening or queries are ambiguous. Please post the exact error from the error list.

     

    -GertD

    Thursday, September 06, 2007 6:51 PM

All replies

  •  

    I'd be happy to help you work through this to isolate any problems.

     

    First, what were the steps that led to this error?  Did the error appear at the time of project creation?  Did you import a database?  Import scripts?  Add new objects to your project?

     

    Next, what is the name of the stored proc you're seeing? 

     

    Finally, what are some of the other issues you're seeing?

     

     

     

    Jeff

    Thursday, September 06, 2007 1:00 PM
    Moderator
  • INFORMATION_SCHEMA objects live in the global namespace and are defined in master. You can not create objects inside that namespace inside a user database.

    -GertD

     

    Thursday, September 06, 2007 5:35 PM
  • Gert,

     

    INFORMATION_SCHEMA.ROUTINES gives you object names of procs stored in other databases.  I was not saying the proc was created there.  Only that when I queried it, it showed that the stored proc existed.

     

    dcb99

     

    Thursday, September 06, 2007 6:10 PM
  • Jeff,

     

    Steps:

     

    1.  Created a new database project.

    2.  Used Schema Compare to pull in the existing database that I wanted to track in this project.

    3. Went through the error list and found this error.

    4. Determined there were no other stored procs with this name (by doing a find in files.)

    5. THen actually queried the GUID database in INFORMATION_SCHEMA.ROUTINES and found the stored proc that _wasn't_ showing up in SMS.

    6. Deleted stored proc.

    7. Went back into Data Dude and the error went away.

     

    The name of the stored proc was not prefaced with "sp_".

     

    Other issues:

    1. No way to reference other databases that I have found so far (on same server.)  Any such stored proc do not compile.  What is the recommended path to solve this?  Currently can only reference objects in the database for this project.

    2. While although my instance is not case-sensitive, when I pulled this database in Data Dude errored out on all sorts of case-sensitivity problems in stored procs and views.

    3. Some columns that exist, are reported by Data Dude as not existing (and thus the stored procs don't compile.)

     

    #1 is the biggest headache right now.  I will certainly not be able to use the tool if I cannot resolve #1.

     

    Thanks,

     

    dcb99

     

     

    Thursday, September 06, 2007 6:37 PM
  • Sorry, misread the error. Can you post the proc definition that is causing the problem?

     

    Global namespace objects are defined in the following two files and the content is used to resolve names

    C:\Program Files\Microsoft Visual Studio 8\DBPro\
    Microsoft.VisualStudio.TeamSystem.Data.Sql80.externalids.xml
    Microsoft.VisualStudio.TeamSystem.Data.Sql90.externalids.xml

    Hence we should never try to create them which is what seems to be happening based on the error message.

     

    -GertD

     

    Thursday, September 06, 2007 6:42 PM
  •  dcb99 wrote:

    1. No way to reference other databases that I have found so far (on same server.)  Any such stored proc do not compile.  What is the recommended path to solve this?  Currently can only reference objects in the database for this project.

     

    Seems like you are not using Service Release 1, please see the Database References blog entry

    http://blogs.msdn.com/gertd/archive/2007/07/26/database-references.aspx

     

     dcb99 wrote:

    2. While although my instance is not case-sensitive, when I pulled this database in Data Dude errored out on all sorts of case-sensitivity problems in stored procs and views.

     

    Variables and parameter case sensitivity is based on the case sensitivity of the server installation, not on the database, if you have a mismatch between the local design time validation instance and the target database this can lead to mismatches.

     

     dcb99 wrote:

    3. Some columns that exist, are reported by Data Dude as not existing (and thus the stored procs don't compile.)

     

    My guess is that this is caused by either case sensitivity mismatches, on which objects are is this happening or queries are ambiguous. Please post the exact error from the error list.

     

    -GertD

    Thursday, September 06, 2007 6:51 PM
  • 1. As far as I know I have the SR1. The following displays in my help | about: Update for Microsoft Visual Studio 2005 Team Edition for Database Professionals - Trial - ENU (KB936612) This Update is for Microsoft Visual Studio 2005 Team Edition for Database Professionals - Trial - ENU. If you later install a more recent service pack, this Update will be uninstalled automatically. If this is SR1, then how would I do this in SR1? 2. ok. I have no reason to believe that the collations on the database are different from the server, but I will check. 3. Well, I have found cases where it is not case-sensitivity. I will focus on 1 and 2 for now, and if there still remain issues I will revisit this.
    Thursday, September 06, 2007 6:59 PM
  • Ignore my last question.  I am looking at the link you sent on adding database references now.

     

    Thanks,

    dcb99

    Thursday, September 06, 2007 7:03 PM
  •  dcb99 wrote:
    1. As far as I know I have the SR1.

     

    For other people who read this.

     

    SR1 can be detected by checking the version number in Help->About inside Visual Studio.

     

    It should state:

    Visual Studio 2005 Team Edition for DB Professionals   90613-246-0000016-41675
    Microsoft Visual Studio Team Edition for Database Professionals Version 2.0.50727.354

     

    Thanks,

    -GertD

    Thursday, September 06, 2007 7:08 PM
  • Strangely I have 2.0.5.0727.354 for the second number, but for the first number I have 7-41560 instead of your 16-41675.

     

    Thursday, September 06, 2007 7:24 PM
  • That number does not matter, only the version number is interesting.

    -GertD

    Thursday, September 06, 2007 7:47 PM
  • dcb99 -

     

    I'm doing my regular check on any threads that are not marked as answered. Has your issue been resolved at this point, or are you still blocked on a subset of the original problem?

     

    thanks,

     

     

    Monday, October 08, 2007 8:41 PM
    Owner
  •  

    Steven,

     

    This thread helped, and I am past those problems.

     

    Thanks,

     

    dcb99

    Monday, October 08, 2007 9:42 PM