I have windows 64bit and a Informatica client 32bit installed on it, so I added a DSN entry using the ODBC administrator for 32bit with driver native client "sql server" to connect to a Mssql 2008 R2 database on another pc. I added an entry in system DSN tab because informatica service is using System account. No problems when I have to import source and target in informatica using that ODBC DSN 32 bit "TARGET_DB_sciolto" (see odbc.ini below) entry. I build up my mapping and in the workflow session property than I change the connection from mssql server to ODBC. The worflow is failing with same RR_4036 error.I think that the only thing I still have to try is to modify my ODBC.ini file since I read that I must add "enablequotedidentifiers=1" so I opened the file expecting to see a lot of entries but I only see following few lines and don't know what to do and would really appreciate if someone would help:

              [ODBC 32 bit Data Sources] test_infa=SQL Server Native Client 10.0 (32 bit) TARGET_DB_sciolto=SQL Server (32 bit) Target_DB=SQL Server (32 bit) [test_infa] Driver32=C:\WINDOWS\SysWOW64\sqlncli10.dll [TARGET_DB_sciolto] Driver32=C:\WINDOWS\system32\SQLSRV32.dll [Target_DB] Driver32=C:\WINDOWS\system32\SQLSRV32.dll

asked Sep 25 '20 at 6:34

1 Answer 1

You need to set it up using lots of parameters. Please make sure you add the connection info in below format. Pls mention host,port,database at least. also make sure informatica machine has access to the datbase.

                  [TARGET_DB_sciolto] Driver32=C:\WINDOWS\system32\SQLSRV32.dll Description=DataDirect 7.1 SQL Server Wire Protocol AlternateServers= AlwaysReportTriggerResults=0 AnsiNPW=1 ApplicationName= ApplicationUsingThreads=1 AuthenticationMethod=1 BulkBinaryThreshold=32 BulkCharacterThreshold=-1 BulkLoadBatchSize=1024 BulkLoadOptions=2 ConnectionReset=0 ConnectionRetryCount=0 ConnectionRetryDelay=3 Database=<database_name> EnableBulkLoad=0 EnableQuotedIdentifiers=0 EncryptionMethod=0 FailoverGranularity=0 FailoverMode=0 FailoverPreconnect=0 FetchTSWTZasTimestamp=0 FetchTWFSasTime=1 GSSClient=native HostName=<SQL_Server_host> HostNameInCertificate= InitializationString= Language= LoadBalanceTimeout=0 LoadBalancing=0 LoginTimeout=15 LogonID= MaxPoolSize=100 MinPoolSize=0 PacketSize=-1 Password= Pooling=0 PortNumber=<SQL_Server_server_port> QueryTimeout=0 ReportCodePageConversionErrors=0 SnapshotSerializable=0 TrustStore= TrustStorePassword= ValidateServerCertificate=1 WorkStationID= XML Describe Type=-10

answered Sep 25 '20 at 12:14

2

  • thanks a lot for the answer. I saw your driver file name is " DWsqls27.so" mine "sqlncli10.dll" is there a reason? What if I install an informatica client 64 bit on the other pc where I have mssql server? If I let communicate infa server of the machine 1' and infa client 64 bit on the other machine 2' do you think I will have to add less settings and will work smoothly?

    Sep 28 '20 at 6:05

  • Sorry, it should be SQLSRV32.dll or whatever is in your odbc.ini file. Now, i dont think less settings will work. And its just one time entry.

    Sep 28 '20 at 7:25

Not the answer you're looking for? Browse other questions tagged dns sql-server-2008-r2 odbc informatica-powercenter or ask your own question.