Skip to Content
Author's profile photo Estrella Santacruz

SAP BW 7.3: How to extract data from SQL Server using DB Connect

In this document (my first one) I will comment how to create a connection from SAP BW to SQL Server step by step and the small problems that I encountered during the process. I hope it can help to someone who has to perform this process in the future.

Step 1. Create the Source System.

RSA1 –> Source Systems –> Right click on “DB Connect” –> Create…

/wp-content/uploads/2013/11/1_325025.jpg

Step 2. We need to fill the next fields:

Logical System Name: in our case we have named “MSSQLS”.

Source System Name: Text field (40 Characters Long).

Type and release: here we can specify a type and a release of this source system.

/wp-content/uploads/2013/11/2_325026.jpg

Step 3. Once named the system we have to create the connection parameters:

DB Connection: the logical name with which we have created our connection.

DBMS: the database type to which you want to connect. In this case “MSS”. There are the following ones:

/wp-content/uploads/2013/11/3_325027.jpg

User Name and DB Password: user and password with which we are going to connect to the database.

Conn. Info: specific information for database connection. In our case:

MSSQL_SERVER=SERVERNAME1 MSSQL_DBNAME=DBNAME1 OBJECT_SOURCE=SAP

With:

MSSQL_SERVER: name of the server we are going to connect.

MSSQL_DBNAME: name of the database from which we will extract data.

OBJECT_SOURCE: name of the schema in which the tables are available.

Permanent: related to what happens when an open database connection is lost due to a database failure or lost network connection.

Connection Limit: Maximum Number of DB Connections

Optimum Conns: Optimal number of DB Connections

/wp-content/uploads/2013/11/4_325031.jpg

Step 4. In SQL Server the user must have the following permissions:

/wp-content/uploads/2013/11/5_325032.jpg

Step 5. We check that the connection is correct.

/wp-content/uploads/2013/11/6_325033.jpg

/wp-content/uploads/2013/11/7_325034.jpg

Step 6. We select the tables or views from which we want to extract the information.

Right-click –> Additional Functions –> Select Database Tables (3.x)

/wp-content/uploads/2013/11/8_325035.jpg

Then we press “Execute”:

/wp-content/uploads/2013/11/9_325036.jpg

The following tables and views have been found:

/wp-content/uploads/2013/11/10_325037.jpg

… the same views we wanted to access:

/wp-content/uploads/2013/11/11_325038.jpg

Important note: as seen in the image below, we cannot access “View_2” because the names of the tables/views (and their fields) must be in UPPERCASE.

/wp-content/uploads/2013/11/12_325039.jpg

Step 7. If we select any of the first four tables and we press “Edit DataSource” we can see the fields (Field Name, Database Type, Length in Database …) of each table:

/wp-content/uploads/2013/11/13_325040.jpg

Step 8. If we press “Display Table Contents” /wp-content/uploads/2013/11/14_325041.jpg  we can see the content:

/wp-content/uploads/2013/11/15_325042.jpg

Step 9. If we press “Check DataSource” /wp-content/uploads/2013/11/16_325043.jpg  we can verify the DataSource. In this case displays a warning because the length of one of the fields is larger than 16:

/wp-content/uploads/2013/11/17_325044.jpg

Step 10. And if we press “Generate DataSource” /wp-content/uploads/2013/11/18_325045.jpg  we create the DataSource in BW side.

/wp-content/uploads/2013/11/19_325046.jpg

A message shows that the DataSource (6DB_tablename) has been successfully generated:

/wp-content/uploads/2013/11/20_325047.jpg

Step 11. If we return to the Workbench we can see that our DataSource has been generated (in version 3.x):

/wp-content/uploads/2013/11/21_325048.jpg

Step 12. Click the right mouse button and migrate the DataSource to version 7.0.

Step 13. Create the transformation rules (as we can see below):

/wp-content/uploads/2013/11/22_325049.jpg

Step 14. Create the Infopackage and DTP.

Step 15. Execute the Infopackage and DTP and we can see that our Infoobject (ZCCAA2) has been loaded correctly:

/wp-content/uploads/2013/11/24_325050.jpg

Best regards,

Carlos

Assigned tags

      45 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Harish Allachervu
      Harish Allachervu

      very informative really helped lot in gaining the knowledge pertaining to DB connect data extraction thanks lot..........

       

      Thanks & Regards,

      Harish

      Author's profile photo Carlos Pinto Camarero
      Carlos Pinto Camarero
      Blog Post Author

      Hi Harish,

       

      I'm glad you find it useful! Many thanks for your comment.

       

      Regards,
      Carlos

      Author's profile photo CH Raman
      CH Raman

      Really very helpful doc and good presentation too. Thanks for sharing Carlos.

       

      Thanks

      Raman

      Author's profile photo Carlos Pinto Camarero
      Carlos Pinto Camarero
      Blog Post Author

      Hi Raman,

       

      Thanks for your comment! I really appreciate it!

       

      Regards,

      Carlos

      Author's profile photo Charles Koekemoer
      Charles Koekemoer

      Hi Carlos,

       

      I am trying to setup the connection from a BW system running on AIX platform with Oracle as the database to MSSQL database. I am getting the below error thus wondering if this is supported from SAP point of view.

      Error: No shared library found for the database with ID MSSQLS

       

      I have gone though many links and some even suggesting to have the application server running on windows platform.

       

      Kind Regards,

      Charles Koekemoer (CK)

       

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi Carlos,

       

      Its useful information and good presentation.

       

      Regards,

      Ganesh Bothe

      Author's profile photo Carlos Pinto Camarero
      Carlos Pinto Camarero
      Blog Post Author

      Hi Ganesh,

       

      Thank you, I'm glad you liked it!

       

      Regards,
      Carlos

      Author's profile photo Michael Fusco
      Michael Fusco

      Very nice documentation, thanks.

      Regards,

       

      Mike

      Author's profile photo Former Member
      Former Member

      Hi Carlos,


      Good - step by step explanation with your first document. Thank you for providing third party data extraction details in well manner.


      - Kumar

      Author's profile photo Mike Howles
      Mike Howles

      Good post - You may want to mention that MSSQL only works if your SAP BW system is installed on a Windows Server or Linux OS.  If you are UNIX, this won't work

      Author's profile photo Former Member
      Former Member

      Very useful document.

       

      Regards,

      Sushant

      Author's profile photo Carlos Pinto Camarero
      Carlos Pinto Camarero
      Blog Post Author

      Many thanks to all. Your comments motivate me to write more documents!

       

      Regards,
      Carlos

      Author's profile photo Former Member
      Former Member

      I really like read more documents from you.

       

      Regards,

      Sushant

      Author's profile photo Jayaprakash Subramanian
      Jayaprakash Subramanian

      Hi Carlos,

       

      Thanks for this article.
      I have tried to connect to BW with SQL Server 2008.
      I am getting an error while creating the source system :

       

      "No shared library found for the database with ID MSSQL"

       

      Here MSSQL - Source system name that I have given.
      Am I missing something ?

      Please help me.

       

       

       

      Thanks,
      Jay.

      Author's profile photo Carlos Pinto
      Carlos Pinto
      Author's profile photo Jayaprakash Subramanian
      Jayaprakash Subramanian

      Hi Carlos,

      Thank you so much.
      I have used MS SQL and unfortunately, Application Server OS is Linux and DB is Oracle.
      With your answer, it makes lots of sense 🙂 Let me try with Oracle DB connection.

       

      Thank you so much Carlos.. You are genius 🙂

       

       

      Thanks,
      Jay.

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Hi Jay,

       

      I am glad you solved it. Good luck with Oracle connection!

       

      Best Regards,

      Carlos

      Author's profile photo Former Member
      Former Member

      Can anyone let me know if this functionality has been removed?

      My DBCO connection is set-up and as far as I can tell, using check, it is succesfully connected? However the Additional Function > Select Database Table (3.x) path does not exist on my context menu. as per scren shot below.

       

      /wp-content/uploads/2015/03/2015_03_30_10_00_34_674055.png

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Hi Gareth,

       

      The functionality has not been removed. Please, review the connection info (MSSQL_SERVER MSSQL_DBNAME OBJECT_SOURCE) and make sure that the user has enough permissions in both SQL Server and BW systems.

       

      Regards,

      Carlos

      Author's profile photo Former Member
      Former Member

      Hey Carlos

       

      Thanks for your response...Im going to try be a bit more specific. And I am new to this so apologies if this is a bit poor.

       

      SAP BW version is 7.4

       

      MSS user ID is uppercase and this user has permissions to all tables and views in the server instance.

       

      MSS connection string is accurate as far as I and the MSS DBA can confirm.

       

      The only area that you are giving me some doubt is the BW system permissions. Please could you elaborate on these.

       

      Kind regards

      Gareth

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Hi Gareth,

       

       

       

      Don't worry, I will try to help in anyway I can. What roles and authorizations has your BW user (t-codes: SU01, PFCG...)? I am not sure but probably he should have the next one:

       

       

       

      Data Warehousing Workbench - Objects                         S_RS_ADMWB

      Activity                                                   03                            ACTVT

      Administrator Workbench object               SOURCESYS           RSADMWBOBJ

       

       

       

      Please, investigate in this direction and let me know.

       

       

       

      Regards,

      Carlos

      Author's profile photo Former Member
      Former Member

      And so im clear when you are talking about BW user, you mean my standard developer login? or arew you thinking of a "internal SAP" system user?

       

      Cheers

      Gareth

      Author's profile photo Carlos Pinto
      Carlos Pinto

      I mean the user which you are using when viewing the context menu.

       

      Regards,

      Carlos

      Author's profile photo Former Member
      Former Member

      Hello Carlos,

      I've tried those steps with adding new roles to both users - the one that is viewing the context menu and the SQL user. Unfortunately, none of these works.

      Do you know some other solutions?

      Regards,
      Bartosz
       

      Author's profile photo Former Member
      Former Member

      Hi,

      in case you could not solve this problem:

      https://launchpad.support.sap.com/#/notes/0001940873

       

      This can be done in:

      1. Transaction RSCUSTV32:
      2. or from transaction SPRO: SAP Netweaver -> Business Warehouse -> General settings -> Hide 3.x Functions / Show obsolete Functions

      Regards

      Steve

      Author's profile photo Vivek Ankit
      Vivek Ankit

      Hey Carlos,

       

      Very informative.Many Thanks

       

      Regards,

      Vivek

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Thanks for your comment Vivek!

       

      Regards,

      Carlos

      Author's profile photo Former Member
      Former Member

      Really very helpful doc and good presentation too.


      Thanks,

      Natalya

      Author's profile photo Carlos Pinto
      Carlos Pinto

      I am glad to be helpful! Thanks for your feedback Natalya!

       

      Regards,

      Carlos

      Author's profile photo Moshe-Eliashar Yerushalmy
      Moshe-Eliashar Yerushalmy

      Hello,

       

      We connected our BWD to SQL and it worked fine.

      We then connected our BWP to another SQL and the connection is fine as well, HOWEVER , we get GIBRISH see below .  Any ideas?

       

      Thanks,

      Moshe

       

      Gibrish.png

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Hi Moshe,

       

      That screenshot seems to be at BEx level. Have you loaded 'Customer' texts in BWP? For example, if you are using 0CUSTOMER infoobject as 'Customer' you have to load its texts:

       

      0CUSTOMER.gif

       

      Hope it helps!

       

      Best regards,

      Carlos

      Author's profile photo Former Member
      Former Member

      Very Nice document...

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Thank you Mithun Patil

      Author's profile photo Former Member
      Former Member

      Wow Really it's an wonderful information & nice presentation tooo...

       

      Thanks

      Sree Harsha

      .

      Author's profile photo Carlos Pinto Camarero
      Carlos Pinto Camarero
      Blog Post Author

      Thanks for your comment Sree Harsha !

      Author's profile photo Former Member
      Former Member

      Hi Carlos,

      great documentation and presentation!

       

      Thanks,

      Regys

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Thanks for your comment Regys M. I really appreciate it!

      Author's profile photo Former Member
      Former Member

      Hi Carlos,

      Its a good document.

      But i have a different problem that was faced on sql replication.

      Initially when i extracted the data i did and i have successfully loaded..Later in sql table a filed name was changed, but its not getting replicated after many tries in to BW

      I read that in  sql we can not replicate change, is that true?? if not how can i replicate the changed table field to BW.

      please help me.

      Thanks,
      Karthik

      Author's profile photo Carlos Pinto
      Carlos Pinto

      Hi Karthik,

      If you change a field in SQL table you have to refresh the default system proposal on BW side (Datasource: 'Proposal' tab). Then you have to add the new field -or to make sure the new name is correct ('Field' tab). And finally, please check that the datasource is bringing values for that field ('Preview' tab).

      Thank you,

      Carlos

      Author's profile photo Former Member
      Former Member

      I am trying to extract data from MS SQL into SAP BW. the tables are in lower case. The lower case tables are not accessible for data load.
      The SQL guys gave me a synonym table in upper case but this table I am not able to see in the list of tables available for extraction.
      How can we access or extract data from MS SQL into SAP BW whose tables are in lower case.

       

      Author's profile photo Former Member
      Former Member

      At your info provider setting, turn on (allow lower case). and you can load your data.

      Author's profile photo Former Member
      Former Member

      The Blog is very informative. Thanks for your work.

      Author's profile photo Former Member
      Former Member

      Hi Carlos Pinto,

      When i connect to My MSSQL, I don't read my Tables and my views

       

      Picture to My Tables MSSQL

       

      Thinks for your attention !

      Author's profile photo Sachin Kumar
      Sachin Kumar

      View's name should be in UPPERCASE only

      Author's profile photo Mukesh Singh
      Mukesh Singh

      Can someone give me some idea if I want to connect with MS SSAS cubes. Can I follow the same steps? or can I able to write MDX queries to pull the data from the cubes?

       

      Thanks,

      Max