Microsoft .NET Data Provider for mySAP Business Suite
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create a package that can connect to a mySAP Business Suite solution and then execute commands on the server. You can also create Reporting Services reports against a SAP server. The Microsoft .NET Data Provider for mySAP Business Suite is tested on SAP R/3 versions 4.6C and higher. Earlier versions of SAP R/3 are not supported.
You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard, the Script task, the DataReader source, and the Script transformation that Integration Services provides, as well as the data processing extensions in Reporting Services.
The Microsoft .NET Data Provider for mySAP Business Suite is not included in SQL Server 2005, you must download it from https://msdn.microsoft.com/downloads/.
Prerequisites
Before you can use the Microsoft .NET Data Provider for mySAP Business Suite, you must install the following:
- The SAP DLL librfc32.dll. The SAP DLL is installed by running the executable SAPgui. The SAP DLL must be installed on the same computer as the Microsoft .NET Data Provider for mySAP Business Suite and Integration Services or Reporting Services.
- Two custom Remote Function Calls (RFCs) must be installed on the SAP server. The RFCs are installed first on the same computer as the SAP DLL, and then are transported to the SAP server by SAP Admin.
To learn more about installing the Microsoft .NET Data Provider for mySAP Business Suite, see the Readme that is downloaded when you download the provider from https://msdn.microsoft.com/downloads/.
Features
The Microsoft .NET provider for SAP supports two types of commands: Execute RFCs/BAPIs and SELECT queries against SAP tables.
Execute RFCs/BAPIs
A command can be specified against the Microsoft .NET provider for SAP to execute a Remote Function Call (RFC) or to execute a call against the SAP Business Application Programming Interfaces (BAPI).
Syntax
The following syntax is supported for the EXEC command:
EXEC rfc_name
[{value | @variable [OUTPUT]}][,...n]
[@parameter = {value | @variable [OUTPUT]}][,...n] [;]
The following table lists the syntax elements.
| Element | Description |
|---|---|
rfc_name |
Specifies the name of the function, which can be either an RFC or BAPI. |
@parameter |
Specifies the name of the parameter in the function interface definition. |
Value |
Specifies the parameter value. The value can be a string, integer, or XML constant. |
@variable |
Specifies the variable that contains the parameter value. |
OUTPUT |
Indicates whether the RFC parameter is OUTPUT or INPUT/OUTPUT. |
Examples
The following examples show the syntax for EXEC statements.
The following EXEC statement executes a BAPI that has no input parameters.
EXEC BAPI_COMPANYCODE_GETLIST
The following EXEC statement executes an RFC that has one input parameter. The parameter specifies a name and a value.
EXEC RFC_CUSTOMER_GET @NAME1='Contoso'
The following EXEC statement executes an RFC that has an input parameter. No name is specified, only a value is used.
EXEC RFC_CUSTOMER_GET '1001'
The following EXEC statement executes an RFC that has an input parameter specified in a variable.
To execute this statement, you must create a SAPParameter object with name @var, set its value to 1001, and add it to the SAPCommand object corresponding to the query. The value of the variable must be 1001, because the first parameter for RFCRFC_CUSTOMER_GET corresponds to KUNNR.
EXEC RFC_CUSTOMER_GET @var
The following EXEC statement executes an RFC that uses two parameters. The value of the first parameter, KUNNR, is set to *, to indicate all customer numbers, and the second parameter, NAME1, is set to the value of the variable, @var1.
To execute this statement, you must create a SAPParameter object with the name @var1, specify its value, and add the new object to the SAPCommand object corresponding to the query. The direction of the SAPParameter object must be input.
RFC_CUSTOMER_GET @KUNNR='*',@NAME1=@var1
The following EXEC statement executes a BAPI that returns company names and information about each company.
To execute this statement, you must create a SAPParameter with the name @tableVar, specify its value, and add the SAPParameter to the SAPCommand object corresponding to the query. The direction of the SAPParameter object created must be inputoutput.
EXEC BAPI_COMPANYCODE_GETLIST @COMPANYCODE_LIST=@tableVar OUTPUT
Select Queries Against an SAP Table
A command can be specified against the Microsoft .NET provider for SAP to execute a limited syntax of SELECT queries against SAP tables, including transparent, cluster, and pool tables.
Note
The query syntax is not standard Transact-SQL. It is a limited syntax and can only be applied to a single table.
Syntax
The supported syntax for the SELECT command follows:
SELECT [TOP n] {ColumnName [AS Alias] | TableName.* | *}[1,..n]
[INTO FILE FileName [DELIMITED]]
FROM TableName [AS Alias]
[WHERE search_conditions] [OPTION 'no_conversion'] [;]
The following table lists the clauses that the SELECT syntax supports.
| Clause | Description |
|---|---|
SELECT select_list |
The columns of the result set. The select_list is a comma-separated list of expressions. A column name may be fully qualified. You can assign an alias to each column. The wildcard character * indicates all columns. |
INTO FILE FileName |
Specifies the name of the file to which the extracted data is written when using the flat file mode in the query. |
FROM TableName |
The table from which the data is retrieved. |
WHERE search_conditions |
The filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. |
OPTION |
The macros that apply to data extracted from the SAP table. If this option is set to 'no_conversion', no macros are applied. |
To learn more about the SELECT clauses, see Parts of a SELECT Statement.
The following table lists the logical and comparison operators that the SELECT syntax supports.
| Operator | Transact-SQL topic |
|---|---|
AND |
|
OR |
|
NOT |
|
() |
|
BETWEEN |
|
LIKE |
|
TOP |
|
= |
|
!= |
|
> |
|
>= |
|
!> |
|
< |
|
<= |
|
!< |
Examples
The following examples show the syntax for SELECT statements.
The following SELECT statement returns all the columns in the SPFLI table.
SELECT * FROM SPFLI
The following SELECT statement writes the data from the SPFLI table to the file, flight.txt, located in the Extracts folder on the SAPSERVER computer.
SELECT * INTO FILE '\\SAPServer\Extracts\flight.txt' FROM SPFLI
The following SELECT statement returns all the columns in the SPFLI table for flights that fly from New York to San Francisco.
SELECT* FROM SPFLI WHERE cityfrom='NEW YORK’ AND cityto=’SAN FRANCISCO’
The following SELECT statement returns all the columns in the SPFLI table for the flights from New York whose identifier is between 1000 and 5000.
SELECT * FROM SPFLI where cityfrom=’NEW YORK' AND (connid BETWEEN 1000 and 5000)
You can use the following statement to return the same result set.
SELECT * FROM SPFLI where cityfrom='NEW YORK' AND (connid>1000 AND connid<5000)
The following SELECT statement returns all the columns in the SPFLI table for flights from New York to a city that is specified by the value of a variable.
To execute this statement, you must create a SAPParameter with the name @variable, specify its value, and add the SAPParameter to the SAPCommand object corresponding to the query.
SELECT * FROM SPFLI WHERE cityfrom='NEW YORK' AND cityto=@variable
See Also
Other Resources
Configuring Reporting Services to Use the Microsoft .NET Framework Data Provider for mySAP Business Suite
Integration Services Connections
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|