Update Strategy Transformation
How should the target table be updated if there is no primary and unique key defined?
May 3, 2017
L1 and L2 Support
L1 and L2 Support
May 26, 2017
Show all

Test Connection Exception for SQL Server as repository database during Informatica installation

One of the issues which is faced during the installation of Informatica 9x/10x with Microsoft SQL Server 2014 as repository database. Created the Repository database and granted the user with DBA privileges.

However when we start installing Informatica server and try to connect to SQL Server database at the Create Domain screen, one of the errors which we face is the below.

***************

This log was generated during Informatica 9.6.1 Services installation

Components installing are … : server,ODBC7.1,isp,services,java,tomcat,tools,DataTransformation,jasperreports-server,plugins,externaljdbcjars,connectors,pwxmfplugins

Disk Space Check Enabled : true

Enter the installation directory : C:\Informatica\9.6.1

Required Disk Space: 6,310 MB

Available Disk Space: 382,106 MB

Configuring files
=================
C:\Informatica\9.6.1\services\MetadataManagerService\utilities\mmcmd\mmcmd.bat
C:\Informatica\9.6.1\services\MetadataManagerService\utilities\mmrepocmd\mmrepocmd.bat
*************************************
Creating Shortcuts

Installing VS C++ 2008 RT
**********************************
C:\Informatica\9.6.1\tools\VS2008\vcredist_x64.exe /q

COMMAND_EXITCODE: 0

Generating keystore… 2:35:20 PM
**********************************

Command Executed : C:\Informatica\9.6.1\java\jre\bin\keytool -genkey -alias “infa_dflt” -keyalg “RSA” -keypass ***** -storepass ***** -keystore “C:\Informatica\9.6.1\tomcat\conf\Default.keystore” -dname “CN=ajaved-w7.na.ds.lexmark.com,OU=Informatica,O=Informatica,L=RedwoodCity,S=California,C=US”

Exit Code : 0

OutPut :

Test Connection Exception -java.sql.SQLNonTransientConnectionException: [informatica][SQLServer JDBC Driver]Error establishing socket to host and port: AJAVED-W7:1433. Reason: Connection refused: connect

**************

For resolving this issue, there is something called as READ_COMMITTED_SNAPSHOT which is by default OFF and it should be set to ON. The below SQL command can be used to update the property.

SELECT is_read_committed_snapshot_on from sys.databases where name=’infarep’ — This should be 1, which is equal to ON

ALTER DATABASE infarep

SET READ_COMMITTED_SNAPSHOT ON

Leave a Reply

Your email address will not be published. Required fields are marked *

Share
+1
Tweet
Pin
Share