Accessing SQL Server form SAP

[Former Member]

HI,

I want to get data on SQL Server from SAP.

What is the procedure to do the above.

Thanks.

[Former Member]

hi

in the SAP HELP Content,followthe instructions for Tutorial 2: Accessing SQL Server

<u><i><b>also take alook at this presentation</b></i></u>

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/2e81685c-0701-0010-0eb3-918c6d56...

Below: is the Tutorial 2 instructions.

In this tutorial you will create a portal component that retrieves and displays information from an MS SQL Server database. Your portal component will display information from the Northwind Categories table in a SAP NetWeaver .NET Table control. You will create a dataset to fill with the data and bind the Table control to this dataset to display data.

Note: using a dataset is one of the data access options, which is not always recommended. You will use a dataset in this tutorial for the simplicity.

To complete this tutorial, you need:

Access to a MS SQL Server with valid permissions for the Northwind sample database

Access to a running portal

A portal user account with administrator's permissions

The tutorial is split into a number of smaller pieces:

Section 1. Creating the SAP Portal Application project

Section 2. Creating and configuring the dataset

Section 3. Adding the SAP NetWeaver Table control and binding it to the data

Section 4. Adding code to fill the dataset and display the data

Section 5. Viewing the component in the portal

Section 1.

=========================================================

Creating the SAP Portal Application Project

On the File menu, point to New, and then click Project.

1. In the New Project dialog box, do the following:

(a)In the Project Types pane, choose Visual C# Projects or Visual Basic Projects.

(b)In the Templates pane, choose SAP Portal Application.

(c)Specify a different name and location, or accept defaults.

(d)Click OK.

A new SAP Portal Application project is created at the specified location. A new portal component named PortalComponent1.ascx is added to your project automatically.

Section 2.

=========================================================

Creating and Configuring the Dataset

Prior to creating a dataset, you need to create and configure the data connection and data adapter.

To create the data connection and data adapter

1. From the Data tab of the Toolbox, drag an SqlDataAdapter object onto your portal component form. Data Adapter Configuration Wizard will help you create both the connection and the adapter.

2. In the wizard, do the following:

2(a)In the Choose Your Data Connection pane, create or select an existing connection to the SQL Server Northwind database.

Important: when you configure the database connection, select the option Use a specific user name and password for server logon information. If your password is not blank, select the Allow to save password checkbox, and then select to Include the password in the connection string, when prompted. Although this is not a recommended practice, we use it in this tutorial for simplicity.

2(b) In the Choose a Query Type pane, select the Use SQL statements option.

2(c) In the Generate the SQL Statements pane, create the following SQL statement:

SELECT CategoryID, CategoryName, Description FROM Categories

If you need to build a different SQL statement, click Query Builder to open the Query Builder dialog box.

2(d) Click Finish.

The wizard adds two objects to your portal component: the SqlConnection1 object containing the database connection information and the SqlDataAdapter1 object containing the data definition.

To generate the dataset

1. From the Data menu, choose Generate DataSet. The Generate Dataset dialog box opens.

Tip: If you do not see the Data menu, click the form; the form must have focus for the menu to appear.

2. Select the New option and name the dataset dsCategories.

3. Select the Categories table in the Choose which table(s) to add to the dataset listbox.

4. Select the Add this dataset to the designer checkbox and click OK.

Visual Studio generates the new dsCategories dataset class and the dsCategories.xsd schema that is added to Solution Explorer. An instance of the new dataset class (dsCategories1) is then added to the form.

Section 3.

=========================================================

Adding the SAP NetWeaver .NET Table and Binding It to The Data

1. From the SAP NetWeaver tab of the Toolbox, drag a Table control onto the portal component.

2. Right-click it to open the Properties box.

3. Set the DataSource property to dsCategories1.

4. Set the DataMember property to Categories.

Section 4.

=========================================================

Adding Code to Fill the Dataset and Display the Data

Although the table is bound to the dataset, the data is not displayed automatically. You must explicitly fill the dataset and bind the table to its data source.

To fill the dataset and display data in the Table control

1. Double-click the form to switch to Code Editor.

2. In the Page_Load event handler, call the data adapter's Fill method, passing it the dataset you want to fill:

[C#]

sqlDataAdapter1.Fill(dsCategories1);

[Visual Basic]

sqlDataAdapter1.Fill(dsCategories1)

3. Call the DataBind method of the Table control to bind it to the dataset:

[C#]

Table1.DataBind();

[Visual Basic]

Table1.DataBind()

4. Save your project.

Section 5.

=========================================================

Viewing the Component in the Portal

Deploy the PAR to the current portal.

In Solution Explorer, right-click the portal component and choose View in Portal.

The table with the list of categories is displayed in the browser.

regards

navjot

reward accordingly

Message was edited by:

navjot sharma

[Former Member]

HI,

But i am not on netweaver, I need accessing SQL server database using abap program, through some connector.

Thanks,

[Former Member]

Hi,

1. If your sap application server is unix/aix

(and not a microsoft OS),

2. then this kind of secondary databse connection,

to MS SQL (which is microsoft system),

is at present not possible.

(BCOS the required system files, for such

cross - platform connection is not delivery by sap)

regards,

amit m.