Search this blog

Friday, February 15, 2013

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!

Wednesday, February 13, 2013

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!