Prefetch objects failed Error when added more than 2 table as arti -- sql server replication

John Luo
12/12/2006 5:22:00 PM

Hello,
I got error message "Prefetch objects failed for Database â€˜MyDBâ€™" when I try
to run the snapshot agent to create a snapshot. It is a SQL 2005 merger
replication. Further more I find it can generate the snapshot if there is
only one table in articles. If I add one more table the error occurred. It
doesnâ€™t matter which table or how simple the table is. However I can create
a snapshot by adding more than one stored procedure in the articles at the
same time without error. (E.g. one table, 3 procedures but not 2 tables).
Any help would be greatly appreciated.
John

Message:
Error messages:
â€¢ Source: Microsoft.SqlServer.Smo
Target Site: Void PrefetchObjectsImpl(System.Type,
Microsoft.SqlServer.Management.Smo.ScriptingOptions)
Message: Prefetch objects failed for Database 'MyDB'.
Stack: at
Microsoft.SqlServer.Management.Smo.Database.PrefetchObjectsImpl(Type
objectType, ScriptingOptions scriptingOptions)
at
Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.PrefetchObjects()
at
Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoPrefetchWithRetry()
at
Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.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.SqlEnum
Target Site: System.String GetString(Int32)
Message: Unable to cast object of type 'System.DBNull' to type
'System.String'.
Stack: at Microsoft.SqlServer.Management.Smo.DataProvider.GetString(Int32
i)
at Microsoft.SqlServer.Management.Smo.ObjectKeyBase.CreateKeyOffset(Type
t, IDataReader reader, Int32 columnOffset)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateNewObjectFromRow(AbstractCollectionBase
childColl, Type childType, Int32 namePropOffset, IDataReader reader, Int32
columnIdx, Boolean hasSchema, Boolean isOrderedByID)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResultsRec(SqlSmoObject
currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader
reader, Int32 columnIdx, Object[] parentRow, Boolean forScripting)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.AdvanceInitRec(SqlSmoObject
currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader
reader, Int32 columnIdx, Int32 columnOffset, Object[] parentRow, Boolean
forScripting)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResultsRec(SqlSmoObject
currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader
reader, Int32 columnIdx, Object[] parentRow, Boolean forScripting)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.AdvanceInitRec(SqlSmoObject
currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader
reader, Int32 columnIdx, Int32 columnOffset, Object[] parentRow, Boolean
forScripting)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResultsRec(SqlSmoObject
currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader
reader, Int32 columnIdx, Object[] parentRow, Boolean forScripting)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResults(Urn levelFilter, IDataReader reader, Boolean forScripting)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn
levelFilter, ScriptingOptions so, Boolean forScripting)
at
Microsoft.SqlServer.Management.Smo.Database.PrefetchTables(ScriptingOptions
options, String tableFilter)
at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjectsImpl(Type
objectType, ScriptingOptions scriptingOptions) (Source:
Microsoft.SqlServer.SqlEnum, Error number: 0)
Get help: http://help/0

Message:

Raymond Mak [MSFT]
12/12/2006 6:16:31 PM

Hi John,

The snapshot agent determines whether to perform meta-data prefetch through
SMO (as a performance optimization for scripting) based on the ratio of a
particular object type (tables, views, stored procedures, or functions)
that you have published in your publication out of the total number of
objects of the same type in your publisher database. I am guessing that
having two tables articles has exceeded the internal prefetch threshold of
the snapshot agent for your particular publisher database which then causes
the snapshot agent to prefetch all table meta-data via SMO. Unfortunately,
it appears that the SMO table meta-data prefetch logic is causing problems
in your case although you can explicitly disable it by specifying the
undocumented /PrefetchTables 0 option on the snapshot agent command line.
Disabling table meta-data prefetch can substantially degrade scripting
performance although you shouldn't notice that as long as it takes longer
for the snapshot agent to generate bcp files (which happens on separate
threads). Nevertheless, we are very much interested in understanding why SMO
prefetch is having problems with a subset of your table meta-data (doesn't
matter whether you published the problematic tables or not) and so we would
really appreciate if you can send us a backup (or the detached database
files) of your publisher database (with sensitive information removed if
necessary) by logging an feedback item at the Microsoft Connect site
(http://connect.microsoft.com)

Without further information, I am guessing that there may an unexpected NULL
constraint\index\column name in your table meta-data. Was your publisher
database upgraded from a previous version of SQL Server?

-Raymond

[quoted text, click to view]

sql server replication : Prefetch objects failed Error when added more than 2 table as arti