June 29, 2009 at 5:37 pm
Hello,
I am a new DBA and currently looking over our existing Production SQL Server (9.00.3042.00SP2Standard Edition (64-bit)). There are currently no maintenance plans on the server, and recently I discovered an error when trying to add a maintenance plan.
When I click on the "Maintenance Plans" from a MSSQL Management Studio, or try to save a plan; the following message is presented:
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.GetChildren()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.SummaryView.get_Items()
at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.ListViewVirtualStorage.CopyItems(ISummaryView summaryView)
at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.ListViewVirtualStorage..ctor(ISummaryView summaryView, List`1 columnDescriptors)
at Microsoft.SqlServer.Management.UI.VSIntegration.AppIDPackage.RightPaneListViewer.PopulateView()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(StringCollection query, Object con, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
===================================
Invalid column name 'from_msx'.
Invalid column name 'has_targets'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
Server Name: van-sql01
Error Number: 207
Severity: 16
State: 1
Line Number: 4
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
I searched google, and some of the links refered to a corrupt SP2 installation and suggested to re-run the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\sysdbupg.sql" which should fix this issue but I am afraid of the after-effects of running this on a Production server.
Anyone able to help that has experienced similar issues?
June 30, 2009 at 9:39 am
Anyone have any idea?
June 30, 2009 at 4:51 pm
/bump ?
Is there a way to restore the tables that did not seem to get "created/updated" when SP2 was applied to SQL Server without breaking anything?
June 30, 2009 at 5:20 pm
Yes, i had this issue 5 months back and i checked version of resource database. It was still 1399 (RTM) . So i run dbupgrade script from install folder and then i was able to create maintanance palans. If you are running sql server 2005 on windows server 2008 with windows cluster and node names are in small alphabates then you are hitting microsoft windows server's bug and you might not able to upgrade resource database. If you have further problem you can directly contact me at gandhiji111@gmail.com
June 30, 2009 at 5:58 pm
How could I check the version of the resource database?
Thanks..
July 1, 2009 at 11:42 am
July 2, 2009 at 8:22 am
I tried your suggested trick:
regsvr32 msxml6.dll
Still no go, I think in this case there are tables that are missing, which should have been created/updated when SP2 was applied (long before my time).
As suggested by one of the posts regarding the resource database...
It was last updated: 2007-02-10 00:39:35.480
Version: 9.00.3042
Is this RTM? or SP2?
July 2, 2009 at 3:56 pm
RESOLUTION
=============================================
1. Find MSXML6.dll on the machine (typically located at c:\winnt\system32 folder).
Run regsvr32 \msxml6.dll - and test creating / saving an SSIS Project. If you don’t
have MSXML6.dll on your machine, please check control Panel-> Add Remove Programs
and verify if you see MSXML 6 Parser. If you don’t see it there, please download it
from
2. If the above step doesn’t help , find msxml3.dll and msxml4.dll on the machine
as well ( typically c:\winnt\system32 folder) - run regsvr32 \msxml4.dll and
regsvr32 \msxml3.dll
=============================================
HTH!
MJ
July 6, 2009 at 4:40 pm
SELECT SERVERPROPERTY('resourceversion')
July 8, 2009 at 2:38 pm
I ran the two commands you suggested, and both msxml3.dll and msxml6.dll is registered.
Also, checking in Add or Remove Programs I found the following:
MSXML 6.0 Parser
SQLXML4
The issue is still there...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply