MSDB Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

MSDB Error

Author

Topic

maranbe
Starting Member

28 Posts

Posted - 2008-08-04 : 05:58:09

Hi all,

Suddenly every time i click on MSDB, i encountered this error. Recent changes was on the service account password. But decided to restore back the old password but to no avail. Please help.

===================================

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()

===================================

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Client unable to establish connection
Encryption not supported on SQL Server. (MsDtsSrvr)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String sPackageFolder, String sServerName)
at Microsoft.SqlServer.Dts.SmoEnum.PackageEnum.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)

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-04 : 23:16:18

Which service account's password changed? In which tool did you get this error? Did you connect to ssis in ssms? Check ssis connects to which sql instance in C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml file.

maranbe
Starting Member

28 Posts

Posted - 2008-08-05 : 01:54:45

SQL Agent was changed but I decided to restore the password back. I am connecting using ssis in ssms. I've checked the xml file and no changes have been made. Please see below.

MsDtsSrvr.ini.xml
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 02:02:05

Have you tried restoring the msdb database?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

maranbe
Starting Member

28 Posts

Posted - 2008-08-05 : 03:54:52

Hi Tara,

Not yet at this stage. If i may ask what's specific table(s) affecting my MSDB to fail. Thanks.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 12:29:02

I don't have any answer as to what tables would cause this. I'd just do a restore. If the problem persists, then I'd get on the phone with Microsoft if this were production. Msdb affects your ability to run jobs, most importantly scheduled backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-05 : 23:23:15

Is sql agent service running? Can you open msdb in ssms -> database engine? Tried restart ssis service?

maranbe
Starting Member

28 Posts

Posted - 2008-08-06 : 05:23:36

sql agent service is running. Yes I can open msdb in ssms. Tried several times restarting ssis service.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-06 : 22:53:01

Try replace . with sql instance name in '<ServerName>.</ServerName>' then restart ssis.

maranbe
Starting Member

28 Posts

Posted - 2008-08-11 : 21:15:58

Tried restoring the msdb but still encountering problem. Decided to store the packages using file system and it's working perfectly fine. will keep on looking for solutions, thanks all.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

valdivie
Starting Member

1 Post

Posted - 2008-10-03 : 23:42:26

maranbe, how did you store the packages on the file system?

Thank you in advance.

Regards

maranbe
Starting Member

28 Posts

Posted - 2008-12-15 : 04:22:14

valdivie,

sorry for the late reply. please read on "How to: save a package to the file system" in BOL.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources