CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

 CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
  • Breadrumbs:

  • SQL Server Change Multiple Table Owner Names - SQL Server error '80040e14' Invalid object name

    • CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
    Share With Friends (Updated 6-8-2010)
  • Article ID:
    161
  • Date Created
    Wednesday, January 11, 2012
  • Last Updated
    Friday, March 29, 2013
  • This Article Has been Viewed
    1949 times
  • Short Desc
    When you import a database that was originally downloaded from your hosting provider, you may experience a table owner issue, that will result in the "Invalid object name" error.
  • Details
    When you import a database that was downloaded from a Hosting Provider, or other outside source, you may experience an issue with the table owner being not what it needs to be for your project to run in-house.

    In this case, you will receive the following error.

     

    Microsoft OLE DB Provider for SQL Server error '80040e14'
    Invalid object name 'ColName'.
    /page.asp, line 56


    This error is happening, because your table name is not

    dbo.ColName
    Instead it is
    122334.Username.ColName
  • Recreate Issue
    To recreate this issue.

    When you import a database from an outside source, you will encounter the name change of the table owner.
    This usually happens when you download your database from a Hosting Provider.
    When you bring these database's into your local development environment, you will have naming issues, when you try to work with your projects in-house.
  • Resolve Issue
    To resolve this issue.
    [Right Click] on the Table.
    Choose [New Query]
    Paste the following script into the Query Editor.
    Change the [CHANGEFROM] and the [CHANGETO] to the names you need.

    Code is fixed,
    WHILE FETCH_STATUS = 0
    was missing 2 signs.
    WHILE FETCH_STATUS = 0

     


    DECLARE @currentObject nvarchar(517)
    DECLARE @qualifiedObject nvarchar(517)
    DECLARE @currentOwner varchar(50)
    DECLARE @newOwner varchar(50)

    SET @currentOwner = 'CHANGEFROM'
    SET @newOwner = 'CHANGETO'

    DECLARE alterOwnerCursor CURSOR FOR
    SELECT [name] FROM dbo.sysobjects
    WHERE xtype = 'U' or xtype = 'P'
    AND LEFT([name], 2) <> 'dt'
    OPEN alterOwnerCursor
    FETCH NEXT FROM alterOwnerCursor INTO @currentObject
    WHILE FETCH_STATUS = 0
    BEGIN
    SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
    EXEC sp_changeobjectowner @qualifiedObject, @newOwner
    FETCH NEXT FROM alterOwnerCursor INTO @currentObject
    END
    CLOSE alterOwnerCursor
    DEALLOCATE alterOwnerCursor


    Code originally from digory @ weblogs.asp.net«

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles