none
Issue during script generation for an entire database - SQL Server 2016-2017

    Question

  • Hi,

    I'm trying to generate the script to re-create an entire database on another machine, by using the related feature available in SSMS.

    The db has about 67.000 tables.

    The errore message is this:

    Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: Error creating scripts for objects. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: The attempt to retrieve data for the object failed for the Server object 'DB-CPRO16'. ---> Microsoft.SqlServer.Management.Smo.MissingObjectException: The object '[dbo]. [xxx]' of type Table does not exist on the server. in Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec (Urn urn) in Microsoft.SqlServer.Management.Smo.Server.GetSmoObject (Urn urn) --- End of internal exception stack trace --- in Microsoft. SqlServer.Management.Smo.Server.GetSmoObject (Urn urn) in Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishModel.ValidateUrnList (IEnumerable`1 urnList) in Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishModel.OnScriptingProgress (Object sender, ScriptingProgressEventArgs e) in Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.OnScriptingProgress (Object sender, ScriptingProgressEventArgs and) in Microsoft.SqlServer.Management.Smo.ScriptMaker.OnScriptingProgress (ScriptingProgressStages scriptingProgressStages, IEnumerable`1 urns) in Microsoft.SqlServer.Management.Smo.ScriptMaker .DiscoverOrderScript (IEnumerable`1 urns) in Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker (List`1 urns, ISmoScriptWriter writer) in Microsoft.SqlSer ver.Management.Smo.ScriptMaker.Script (Urn [] urns, ISmoScriptWriter writer) in Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript (ScriptOutputOptions outputOptions) --- End of internal exception stack trace --- in Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork (Object sender, DoWorkEventArgs and) in System.ComponentModel.BackgroundWorker.OnDoWork (DoWorkEventArgs e) in System.ComponentModel.BackgroundWorker.WorkerThreadStart (Object argument)

    However, the signaled table exists in the database.

    Any helps to me, please? Thanks

    Wednesday, May 16, 2018 7:35 AM

All replies

  • which is the SSMS version?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 16, 2018 7:44 AM
  • Hi Visakh,

    the SSMS version is 17.1, the scripted database is in 2008 compatibility mode.

    Thanks

    Wednesday, May 16, 2018 7:51 AM
  • Hi,

    To troubleshoot your issue, first I would suggest you to update your SSMS to the newest version as 17.7. You can find it in below link. And then run the script generation in this new SSMS see if the error persists.

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

    Then as the error told "The attempt to retrieve data for the object failed for the Server object 'DB-CPRO16' ". What is this server object 'DB-CPRO16'? Please make a check.

    And is your database case-sensitive? Since the error said that " The object '[dbo]. [xxx]' of type Table does not exist on the server. ", if your database is case-sensitive please make a verification with the right table name.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 17, 2018 7:33 AM
    Moderator
  • Hi Visakh,

    the SSMS version is 17.1, the scripted database is in 2008 compatibility mode.

    Thanks

    Is DB-CPRO16 name of linked server or a synonym created?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 17, 2018 7:38 AM
  • Hi,

    the name that you have read is the name of the machine, "xxx" is the obscured name of the database.

    Gentlemens, I cannot write the real names of the involved object for privacy reasons.

    Now, why do I need to update SSMS? Is there a bug? The script generation functions for other selections.

    Perhaps, does it exist any limitations for the script generation?

    Thanks

    Thursday, May 17, 2018 7:56 AM
  • Hi,

    So the server object 'DB-CPRO16' is a remote server or just a local server?

    => why do I need to update SSMS? Is there a bug? The script generation functions for other selections.

    If you update SSMS to the newest version and do script generation without any issue. Then we can know that it might be a bug of old SSMS and your issue can be resolved. If it doesn't fix your issue, then your issue may not related to the version of SSMS. And we can continue to troubleshoot other points.

    => does it exist any limitations for the script generation?

    As I know, there's no limitations for this feature.

    By the way, have you checked if your database is case-sensitive?

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, May 21, 2018 8:33 AM
    Moderator