Failed to retrieve data for this request ms sql ошибка

First published on MSDN on Nov 12, 2009

1.



After connecting to SQL Server Integration Services 2005 from SQL Server Management Studio, when you expand MSDB under Stored Packages to see list of stored packages, you may get below error.

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

F


or help, click:

——————————

ADDITIONAL INFORMATION:

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.

Login timeout expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (MsDtsSrvr)

——————————

Login timeout expired


An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (Microsoft SQL Native Client)

Cause and Resolution

SSIS uses the configuration file located on the machine at «%ProgramFiles%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml».  The content of file MsDtsSrvr.ini.xml typically looks like below.

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

If the ServerName specified between ServerName tags above is incorrect, you get the similar error mentioned above.  To correct the issue, specify the correct name of the SQL Server with the instance name.

SSIS is not recommended to configure as a clustered resource, rather it is advised to install it separately on each node. Note, after installing in each node, by default SSIS will put “.” In the ServerName tag in the config file because SSIS is instance independent and always assumes to be installed on the default instance (which is “.”). So, in a clustered environment we need to specify “ClusterVirtualNameInstance name” where MSDB Database is hosted.

We need to restart the SSIS Windows Service for the changes to take effect.

2


When you try to connect to SQL Server Integration Services 2005 from SQL Server Management Studio from a client computer and the you use a user account that is not a member of the Administrators group on the sever, you may get below error.

Cannot connect to <computer-name>.

——————————

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

——————————

Connect to SSIS Service on machine <computer-name> failed:

Access is denied.

If you use the same user account to connect to SQL Server Integration Services 2005 from SQL Server Management Studio locally on the server, you will be able to successfully connect.

Cause and Resolution

This is a permission related issue. Please follow workaround provided below KB article to resolve the error.

940232

— Error message when you use SQL Server Management Studio to connect to SQL Server 2005 Integration Services from a client computer: «Connect to SSIS Service on machine ‘<SSISServer>’ failed Access is denied»

3.


When you try to connect to SQL Server Integration Services 2005 from SQL Server Management Studio, you may get below errors.

Error 1

Cannot connect to <computer-name>.

——————————

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:

——————————

Connect to SSIS Service on machine <computer-name> failed:

Error loading type library/DLL.

Error 2

Cannot connect to <computer-name>.

——————————

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:

——————————

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80070005. (Microsoft.SqlServer.ManagedDTS)

Error 3

Cannot connect to <computer-name>.

——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————

ADDITIONAL INFORMATION:

Library not registered. (Exception from HRESULT: 0x8002801D

(TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap)

Causes and Resolutions

1.    Unregister and register DTS.dll.  If the SSIS is a 64-bit installation, unregister and register both 32-bit and 64-bit DTS.dll and  MsDtsSrvrUtil.dll files.  Commands are below.

Regsvr32 /u “C:Program Files (x86)Microsoft SQL Server90DTSBinnDTS.dll»

Regsvr32 /u “C:Program FilesMicrosoft SQL Server90DTSBinnDTS.dll»

Regsvr32 /u “C:Program Files (x86)Microsoft SQL Server90DTSBinn MsDtsSrvrUtil.dll»

Regsvr32 /u “C:Program FilesMicrosoft SQL Server90DTSBinn MsDtsSrvrUtil.dll»

Regsvr32 “C:Program Files (x86)Microsoft SQL Server90DTSBinnDTS.dll»

Regsvr32 “C:Program FilesMicrosoft SQL Server90DTSBinnDTS.dll»

Regsvr32 “C:Program Files (x86)Microsoft SQL Server90DTSBinn MsDtsSrvrUtil.dll»

Regsvr32 “C:Program FilesMicrosoft SQL Server90DTSBinn MsDtsSrvrUtil.dll»

This holds good for Error #1(Error loading type library/DLL.) and Error #3(TYPE_E_LIBNOTREGISTERE))

This error is encountered frequently when a Service pack is applied over an existing installation of Sql Server. During SP installation Sql automatically unregisters these dlls and on successful completion re-registers them. We have seen issues where the re-registration fails and Customer gets the above errors while connecting to SSIS after installation of a Service Pack.

2.    Unregister and register SqlTaskConnections.dll.  If the SSIS is a 64-bit installation, unregister and register both 32-bit and 64-bit SqlTaskConnections.dll files.  Commands are below.

Regsvr32 /u “C:Program Files (x86)Microsoft SQL Server90DTSBinn SqlTaskConnections.dll»

Regsvr32 /u “C:Program FilesMicrosoft SQL Server90DTSBinn SqlTaskConnections.dll»

Regsvr32 “C:Program Files (x86)Microsoft SQL Server90DTSBinn SqlTaskConnections.dll»

Regsvr32 “C:Program FilesMicrosoft SQL Server90DTSBinn SqlTaskConnections.dll»

3.    Make sure that the SSIS startup account has full permissions on folder

C:Program FilesMicrosoft SQL Server90DTSBinn where the SSIS binaries reside.

4.    Re-install Sql Server 2005 Client tools and Workstation Components.

Author : Deepak (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Debarchan (MSFT) , SQL Developer Engineer , Microsoft

SQL Server setup would fail with below error in message box “Failed to retrieve data for this request”

{

TITLE: SQL Server Setup failure.

——————————

SQL Server Setup has encountered the following error:

Failed to retrieve data for this request..

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xE8A0C283%25400xAC7B1A58%25401233%254053

——————————

BUTTONS:

OK

}

In Summary.txt you will notice error similar to one below

Overall summary:

Final result: Failed: see details below

Exit code (Decimal): -2146233088

Exit facility code: 19

Exit error code: 5376

Exit message: Failed to retrieve data for this request.

Start time: 2015-02-19 19:48:58

End time: 2015-02-19 19:50:47

Requested action: Install

Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.2342.0&EvtType=0xE8A0C283%400xAC7B1A58%401233%4053&EvtType=0xE8A0C283%400xAC7B1A58%401233%4053

Exception summary:

The following is an exception stack listing the exceptions in outermost to innermost order

Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException

Message:

Failed to retrieve data for this request.

HResult : 0x80131500

Data:

HelpLink.ProdName = Microsoft SQL Server

HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink

HelpLink.LinkId = 20476

HelpLink.EvtType = 0xE8A0C283@0xAC7B1A58@1233@53

DisableWatson = true

Stack:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.ProcessDTbl(DataTable dt, Int32 level)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectSqlDiscoveryData(String machineName)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectDiscoveryData(String machineName)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.LoadData(IEnumerable`1 machineNames, String discoveryDocRootPath, String clusterDiscoveryDocRootPath)

at Microsoft.SqlServer.Configuration.SetupExtension.RunDiscoveryAction.ExecuteAction(String actionId)

at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)

at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()

at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

Inner exception type: Microsoft.SqlServer.Configuration.Sco.SqlRegistryException

Message:

The network path was not found.

HResult : 0x84d10035

FacilityCode : 1233 (4d1)

ErrorCode : 53 (0035)

Data:

WatsonData = Microsoft.SqlServer.Configuration.Sco.SqlRegistryException@Win32Error

Stack:

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.CreateBaseKey(ServiceContainer ctx, String machineName, IntPtr hKey, String keyName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.GetLocalMachine(ServiceContainer ctx, String machineName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineRootKey(ServiceContainer ctx, String machineName, RegistryView registryView)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName)

at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent)

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

Watson error

Watson bucket data:

Bucket param 1: SQL Server 2014@RTM@

Bucket param 2: 0x6785B09D

Bucket param 3: 0xE8A0C283

Bucket param 4: 0x74E34741

Bucket param 5: 0xAC7B1A58@1233@53

Bucket param 6: RunRemoteDiscoveryAction

Bucket param 7:

Bucket param 8:

Bucket param 9: 0xD195CE25

Bucket param 10:

From detail.txt

(01) 2015-02-19 19:49:25 Slp: Discovery on nodename failed due to exception

(01) 2015-02-19 19:49:25 Slp: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. —> Microsoft.SqlServer.Configuration.Sco.SqlRegistryException: The network path was not found.

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.CreateBaseKey(ServiceContainer ctx, String machineName, IntPtr hKey, String keyName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.GetLocalMachine(ServiceContainer ctx, String machineName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineRootKey(ServiceContainer ctx, String machineName, RegistryView registryView)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName)

at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent)

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

— End of inner exception stack trace —

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

Resolution

1. Check the connectivity between node from which you are installing SQL Server to other nodes. (\Othernodes should work when you are installing SQL on box which is part of windows cluster regardless of SQL Cluster).

I have been writing a program in c# using Visual Studio 2012. This program makes numerous calls to the database, and everything has always worked perfectly; never had to mess with any configurations to get it to run. A few days back, I received a new computer. After setting everything up, I was trying to run my program but it kept failing on the database calls because it was timing out. I deleted the old connection string and tried connecting to our backup database. Using the Server Explorer, I created a new connection; after filling in all the information, I used the Test Connection button and was told the test succeeded. From the server explorer, I then tried opening the Tables folder, but it didn’t work. After about 30 seconds, I got the following error:
Failed to retrieve data for this request.
Lock request time out period exceeded.

I tried refreshing the connection several times, restarting the db, restarting visual studio, even restarted my computer. I tried connecting back to the original db and got the same result, so I’m sure it’s computer-specific and not related to the db. I have already verified that the windows account I am logged on as is a sysadmin. We use SQL Server Authentication, and the account Visual Studio is attempting to connect to is also a sysadmin. I have completely disabled my firewalls as well. I have searched high and low on the internet for an answer, but the only cases where I could find error messages similar to mine were very specifically related to ASP. What I don’t understand (and what’s difficult to search for) is being able to successfully connect to the db, yet unable to retrieve any data. Any help would be greatly appreciated!

Additional Information:
I am using LINQ to SQL class in my project. Target framework = 4.5

Update:
I marked the answer as Accepted, but I’ll elaborate here in case anybody else ever runs into this same problem. There were two open processes, one of which belonged to me, which is what I assume was allowing the Test Connection to work. After killing all open processes, I wasn’t able to connect back to the DB at all. I then restarted the DB service (which is on our LAN, not local) and that worked. I no longer think it had anything to do with the new computer; it just happened simultaneously to whatever caused the issue and caused me to go down the rabbit hole.

We use cookies on our website to provide you with the most relevant experience by remembering your preferences. No personal data is stored. By clicking on «Accept All», you consent to the use of ALL cookies. However, you can visit «Cookie Settings» to provide controlled consent. Read More

Read the complete post at blog.sqlauthority.com/…/

• Jon.Morisi

  SSChampion

  Points: 12846

• richieStick

  Old Hand

  Points: 361

  Hi

  We have a secure network with vlans and multiple firewalls as well as the windows firewall on the servers. All I’m trying to do is connect to the integration server using the management studio from my PC — I can connect if I’m on the server using the same credentials.

  I suspect that the firewalls are the issue and DCOM being the problem.

  I’m investigating this and will come back with the results.

  Rich

• Brandie Tarvin

  SSC Guru

  Points: 172926

  Another thought. Have you verified that the server accepts remote connections?

  That’s my suspicion if the only time it works is when you are on the server itself. On your desktop, log into SSMS (not SSIS) and try to connect to the server. See if it gives you a «server may not be set up for remote connections» kind of error or if you can log in.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• richieStick

  Old Hand

  Points: 361

  Hi Brandie

  Yes, I can connect to the server if I want to connect to the database server using SSMS with no issues using the same admin credentials.

  Thanks anyway

  Rich

• Brandie Tarvin

  SSC Guru

  Points: 172926

  Last question then. Are you using Bulk Insert tasks that are failing or is it the whole package with other tasks that are failing on connection?

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• richieStick

  Old Hand

  Points: 361

  Hi Brandie

  When you are using MS SQL Server Management Studio, one of the options is to connect to the Integration Server. It is this that I was having problems with. When I chose the Database Server it was fine. Nothing to do with any Packages or Visual Studio.

  The problem turned out to be firewalls and DCOM (we have strict security).

  Thanks anyway.

  Rich

• richieStick

  Old Hand

  Points: 361

  It was the firewalls and DCOM. Security is such that we can’t do much without a rule in the firewall. Trouble is that DCOM isn’t firewall friendly and SSMS uses DCOM to talk to the Integration Server. Adding port 135 and the Integration Service Program wan’t enough. Had to allow all ports to be able to connect from specific PCs.

  Rich

After an in place upgrade of SQL server 2005 to SQL server 2008 R2 , I tried to open SQL errorlog from Management> SQL server logs but I got the following error

Refreshing the node will pop the error up again.

Maybe we try T-SQL!

EXEC xp_readerrorlog


Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found


As far as I know , SSMS calls a system stored procedure xp_enumerrorlogs to return the list of error logs and associated last change date.

The location of the SQL server errorlog ,and master database files, is stored in registry under following key

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.instance_nameMSSQLServerParameters

Instance_name is SQL server instance name currently being accessed ; it was default in my case so it was MSSQLSERVER

This is the value of SQL sever errorlog entry

-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG

I then verified that the folder exists and SQL server service account has enough permissions to read/write the file.

Looked at Event viewer , Found following three events

Event Type: Failure Audit
Event Source: MSSQLSERVER
Description:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: local machine]

Event Type: Error
Event Source: MSSQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18452, OS: 18452 [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication..

Event Type: Error
Event Source: MSSQLSERVER
Description:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: local machine].

What’s causing these login failures? the connection is certainly local so “untrusted domain” error looks misleading.

Xp_readerrorlog calls a DLL ,xpstar.dll, but there’s not much info how xpstar.dll works. However,the original error carried some guidance represented in “Failed to open loopback connection” part.

To me, the word loopback used to mean local communication on same server when addressing IP address 127.0.0.1 or Localhost computer name (I see it has more uses now).

ALL I did was to open Windows® hosts file C:WINDOWSsystem32driversetchosts

and that was it , I didn’t find a map for LOCALHOST nor servername; all I did was to add that map

127.0.0.1 localhost server_name

After that , I could open the errorlog from SSMS and Xp_readerrorlog came back with results.

Hope this helps someone , It can be really annoying.

Cheers,
M

Want to talk with an expert? Schedule a call with our team to get the conversation started.