How To: Lists Linked Server

The following Stored procedure will returns the list of linked servers defined in the server in which we are executing it.

sp_linkedservers

This procedure will return the following informations about the linked server

Column name	Data type	Description
SRV_NAME	sysname	Name of the linked server.
SRV_PROVIDERNAME	nvarchar(128)	Friendly name of the OLE DB provider managing access to the specified linked server.
SRV_PRODUCT	nvarchar(128)	Product name of the linked server.
SRV_DATASOURCE	nvarchar(4000)	OLE DB data source property corresponding to the specified linked server.
SRV_PROVIDERSTRING	nvarchar(4000)	OLE DB provider string property corresponding to the linked server.
SRV_LOCATION	nvarchar(4000)	OLE DB location property corresponding to the specified linked server.
SRV_CAT	sysname	OLE DB catalog property corresponding to the specified linked server.

Or... you can use following SQL Scripts to lists all linked servers

select * from master..sysservers

Hope this helps!

Lists All SSRS Reports & Datasources

The following SQL Scripts helps you to list out all the reports with associated Data-sources which are all deployed at Microsoft Reporting server(SSRS)

Use ReportServer

SELECT

      Catalog.Name AS ReportName,

      Catalog.path AS Report_Location,

      DataSource.Name AS DataSourceName

FROM

      [Catalog] INNER JOIN DataSource

            ON Catalog.ItemID = DataSource.ItemID

order by Catalog.path

Hope this Helps!

SSIS Excel Connection Manager Error

Problem:

I've created a simple package to fetch the data from SQL Server to pupulate the Excel file using Excel Destination SSIS Component, when I tried to Execute, it throwed below error

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Solution:

This issue is occurred because my system has 64 bit OS and Office Excel object. most of the office products does not have 64bit provider and hence the error above on Excel connection. This Issue can be fixed by setting Run64BitRunTime property as False at SSIS Project properties.
 

Go to properties page for the overall SSIS solution, simply set the Run64BitRunTime = False. 

Refer the screenshot below for the further reference

SSIS: OLEDB.12.0 provider is not registered

Problem:

Today I tried to develop a simple SSIS package to populate the data to excel file using Excel Destination of SSIS components, but It throws the below error when I select excel sheet in Excel Destination Editor.

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Solution:

This issue is resolved by installing 2007 Office System Driver: Data Connectivity Components

To install this download:

1. Download the file by clicking the "Data Connectivity Components" link (above) and saving the file to your hard disk.
2. Double-click the AccessDatabaseEngine.exe program file on your hard disk to start the setup program.
3. Follow the instructions on the screen to complete the installation.

Hope this helps!