script failur on user defined table type

I have transaction replication configured.  but while creating initial snapshot, replication agent fails:

script failed for UserDefined Table Type 'dbo.InvoiceShippersList'.

I can not find this table anywhere in the database.  How can I find what view / stored procedure is trying to create this table to reference the table type?

Here is full error details:

Error messages:

<dir><dir></dir>

Source: Microsoft.SqlServer.Smo

Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)

Message: Script failed for UserDefinedTableType 'dbo.InvoiceShippersList'.

Stack:    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)

   at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)

   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)

   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.ScriptOneTextModeOffObject(Scripter scripter, TextWriter scriptFileStream, SqlSmoObject smoObject, Boolean generatePlaceholderImplementationForObjectPotentiallyReferencingFullTextFunctions, Boolean quotedIdentifierStatus)

   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.ScriptQuotedIdentifierOnObjects(Scripter scripter, SqlSmoObject[] smoObjectList)

   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonArticleObjectSchScript(NonArticleSmoObjectWrapper nonArticleObjectWrapper, SqlSmoObject smoObject, Scripter scripter, Boolean quotedIdentifierOn)

   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonArticleObjectScripts(ArticleScriptingBundle articleScriptingBundle)

   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)

   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()

   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()

   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()

   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()

   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)

Get help: http://help/0

Source: Microsoft.SqlServer.Smo

Target Site: Void CheckTargetVersion(Microsoft.SqlServer.Management.Smo.SqlServerVersionInternal, Microsoft.SqlServer.Management.Smo.SqlServerVersionInternal, System.String)

Message: Error with User Defined Table Type [InvoiceShippersList].  User Defined Table Types are not supported in SQL Server 2005.

Stack:    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckTargetVersion(SqlServerVersionInternal targetVersion, SqlServerVersionInternal upperLimit, String exceptionText)

   at Microsoft.SqlServer.Management.Smo.UserDefinedTableType.ScriptCreate(StringCollection query, ScriptingPreferences sp)

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)

   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)

   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)

   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired) (Source: Microsoft.SqlServer.Smo, Error number: 0)

Get help: http://help/0

</dir>

August 20th, 2015 3:08pm

Hi JimHoopsDSC,

Firstly, you can execute the following T-SQL statements to check if the User-Defined Table Type exists in your database. Then you can search the User-Defined Table Type from the definition of published view or stored procedure. You can right click the view/ stored procedure, choose Script View as/Script stored procedure as and CREATE TO to script out view/stored procedure definition.

use <YourDatabase>

 go

 SELECT * FROM sys.table_types where name ='InvoiceShippersList'


Secondly, are you publishing database to SQL Server 2005? As the above error message indicates, User Defined Table Types are not supported in SQL Server 2005. If you need to replicate user defined data types, you should work with SQL Server 2008 or later.

Moreover, please note that user defined data types are not replicated by default. You will need to create them on the subscription database in advance, or use the pre-snapshot script. For more details about  pre-snapshot script,  please review this article:https://msdn.microsoft.com/en-us/library/ms961891.aspx .

Thanks,
Lydia

Free Windows Admin Tool Kit Click here and download it now
August 24th, 2015 11:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics