BI to MS SQL

[Former Member]

Hi I trying to connect BI with SQL via manual http://msdn.microsoft.com/en-us/library/dd299430%28v=sql.100%29.aspx but get error

[MartinMaruskin]

This is RFC related issue. Program ID is not registered. Try first to see in Tcode SM59 if RFC connection used to connect your SQL server is working. There is connection test functionality in SM59.

cheers

m./

[Former Member]

I did connection as written in manual (origin post) Tried to do test cnnection - screen lower. I reach BI from SQL , enter in SQL my logon and pass. How it can be test from BI? (I didnt enter any logon )

[Former Member]

In transaction code SM59 on SAP BI, create a new HTTP connection with type T (TCP/IP Connection), as shown in Figure 2. Under Activation Type, select “Registered Server Program”. Then, fill in an appropriate Program ID, which can be any descriptive short text. The RFC Destination and Program ID will be used later to set up the connection manager in Integration Services.

There are two Open Hub implementation options in SAP BI: the legacy InfoSpoke, and the new Open Hub Destination via Data Transfer Process (DTP). The InfoSpoke is marked as obsolete in SAP NetWeaver BI. Therefore the Microsoft Connector 1.0 for SAP BI officially supports only the Open Hub Destination.

In Admin Workbench on SAP BI (transaction code RSA1), create a new Open Hub Destination with Destination Type “Third-Party Tool”, and specify the previously created RFC Destination name (Figure 3). Save and activate the new destination.

[Sriram2009]

Hi

Could you refer the SAP Note for RFC issue

353597 - Registration of RFC server programs

1893014 - program <program ID> not registered

BR

[Former Member]

If I try to reach from SQL to BI. in sm59 in field GATEWAY HOST\SERVICE whicH adress should I put BI or SQL?

iS there any condition anout Role -PFCG authorization?

[Sriram2009]

While access that time are you getting any authorization issue? or you can try with DDIC equivalent user id

[Former Member]

I dont know know what is this problem (Im Bi cons not basis) most probably yes authoriz, all errors sreens are here

[Sriram2009]

If possible could you paste or attach the error screen?

[Former Member]

FIRST SCREEn in original post - erorr from RSA1, SECOND IN THE MIDDLE of topic - test rfc from sm59. Click on them will see full picture

[Sriram2009]

Could you share you BI version & SP level?

[Former Member]

[Sriram2009]

Thanks for you info

Could you refer the SAP KAB
1707919 - JCO Connection error when running a Data Service job from Financial Information Management...

& implement the SAP Note

1932092 - P33; DTP: No setting of source request to "FURTHER" for open hub

[Former Member]

Reproducing the Issue

1. Create a datastore in FIM on SAP BW
2. Openhub/DTP/Process chain visible in FIM job creation
3. Executing jobs from FIM result in the BW process chain starting.
4. DTP processes data without error, but does not finish -> JCO error message

Resolution

Delete the process chain and create the same.

- just dellete chain , will it work? have some doubts

1932092 - P33; DTP - it says DTP long time runs, my dtp runs as usual just in the end stops on error

[Sriram2009]

 - just dellete chain , will it work? have some doubts

I am not sure about the delete & recreate the process chain. but still you can try once

[Former Member]

It is not chain something wrong with RFC connection (COnnection test failed ) , I send our basis team  353597 - Registration of RFC server programs maybe it will helps, but still cant anderstand I didnt write any ip or adress how  system find the server?

[Former Member]

We had the same problem and everything seemed to be fine in SAP. By SSIS, please note the correct servername and gateway to every client. Check this in both the load BI package, and SSIS Connector. Our SAP Basiteam is incredible, and they were the ones who found the cause of the problem.

Message was edited by: Tore Fredriksen

[Former Member]

Good day!

Names and settings are right. Could U ask Your team what is the problem( place ,functionality) will resend info to our basis.

[Former Member]

Have the SSIS-server that you use database at another server? If yes, then SAP Basisteam must also add that value into reginfo.dat (if Windows) During troubleshooting I noticed that the RFC Connection MSSAPBI open only when request comes from the remote server, and closed again afterwards when it is finished.

Examples of reginfo.dat:

P TP=MSSAPBI_PROG HOST=PRDBISQL07  CANCEL=* ACCESS=*

# the following line should be the LAST line in the reginfo,

# see Note 1592493!

P TP=* HOST=local CANCEL=local ACCESS=*

P TP=* HOST=internal CANCEL=internal ACCESS=*

[Former Member]

Add

#VERSION=2

#

# created by TARASOV-MI at 20141105 101319

#

# local access should be allowed by default

# P TP=* HOST=local

#

# internal (server from the same SID) access should be allowed by default

# P TP=* HOST=internal

#

# list of registered programs form SM59 which must be explicitly defined

#

#add manually by M.Tarasov, this line enable Connection from MS SQL to BI

P TP=MSSAPBI_PROG HOST=s5000sqldev003 CANCEL=* ACCESS=*

#this lines should be last in the file

P TP=* HOST=local CANCEL=local ACCESS=*

P TP=* HOST=internal CANCEL=internal ACCESS=*

but again same error, any over suggestion?

[Former Member]

The error message is identical to the one we had. In our case was the error in SSIS, the load BI package (or) Connector. It had the wrong value when the server name or gateway. It appears from the picture that the connection to BW is working correctly into the SAP, but when it should return data stops that MSSAPBI_PROG fail to register, and thus not open connection MSSAPBI in SM59. Based on your message I assume s5000sqldev003 contains both SSIS and database on the same server.

See if you can find anything in traces in  SSIS server \system32\ xxxxxxxx.trc

[Former Member]

Working, everything right, adapter was on another adress, big help ,marked Your answer

[Former Member]

Good day , I tried to create chain of such hub processes . We tried to start same chain (with 3 proc) 5 times rapidly , sometimes it went 3 from 3 , sometimes 1 from 3  (see screens )cant understand from what it depends , can U help me ?

[Former Member]

found same problem but without solution

[Sriram2009]

Hi

Could you refer the SAP Note  to connect the external MS Sql Server

1601608 - How to access an external Microsoft SQL Server database

128126 - Database connect for external tools

And also refer the SCN link

Regards

Sriram