Sunday, March 15, 2009

Failed to retrieve data for this request Microsoft.Sqlserver.SmoEnum

when i tried to expand the MSDB folder in SQL 2005 Integration Services i got the following error.

"Failed to retrieve data for this request. Microsoft.Sqlserver.SmoEnum"

SQLIntergratoinServices

without any problem Management Studio successfully connects to the Notification Services and was sure too that i have allowed remote connections via named pipes and TCP/IP. But :( when i tried to expand the MSDB folder i m getting that error. so i was thinking and trying to find a solution for this.

finally, Hurrah!

the solution is to update a configuration file with the proper servername\instancename.

you have to update the MsDtsSrvr.ini.xml located in the path : C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml as below :

Original file without the modification :

<?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>

file with the updated values :

<?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>MACHINENAME\SQLINSTANCENAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

more details :

http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx

0 comments: