sqlData statement

The sqlData statement lets you retrieve data from any SQL Server or Oracle database.

    <sqlData tableName="">
       <providerName></providerName>
       <connectionString></connectionString>
       <commandText></commandText>
    </sqlData>

sqlData attributes

tableName

Lets you set a unique name to the resulting data. The resulting data can be further manipulated using a sqlQuery statement.

sqlData elements

providerName

Used to specify the name of the provider that will be used. Possible values are:

Value	Description
System.Data.SqlClient	Retrieves data from any SQL Server database.
System.Data.OracleClient	Retrieves data from any Oracle database (starting from 8i).

connectionString

Specifies the Connection String to use to connect to the database.

Some parts of the Connection String differ when you are using Oracle or SQL Server. The following samples are made for SQL Server, but this is similar with Oracle.

Sample

  Data Source= *ServerName* ;Initial Catalog= *DatabaseName*;Integrated Security=true;

You can specify you want to use the credentials specified at the data source level by specifying neither the “Integrated Security” nor the “User ID” options.

Sample

    Data Source=*ServerName* ;Initial Catalog= *DatabaseName*;

Data Source variables may be used inside the Connection String, it makes it easier for moving reports from a development environment to a production environment by simply modifying the shared data source.

Sample 1 – A data source variable per connection string option

Using the following data source connection string:

server=http://spsdemo; $source!=mySqlServer; $user!=myUser; $pass!=myPass;

You can use a sqlData statement like this:

    <sqlData tableName="SqlData">
       <providerName>System.Data.SqlClient</providerName>
       <connectionString>
         Data Source=$source!; User ID=$user!; Password=$pass!; ...
       </connectionString>
       <commandText>SELECT ... </commandText>
    </sqlData>

Sample 2 – A data source variable for the whole connection string

Using the following data source connection string (please note the quotes around the connection string that are needed if you want to use characters such as “=” or “;” inside an option value):

server=http://spsdemo; $sqlServer!='Data Source=mySqlServer;User ID=myUser;Password=myPass;';

You can use a sqlData statement like this:

    <sqlData tableName="SqlData">
       <providerName>System.Data.SqlClient</providerName>
       <connectionString>$sqlServer!</connectionString>
       <commandText>SELECT ... </commandText>
    </sqlData>

commandText

Specifies the command to execute to retrieve data from the database.

The following statement will return the data from the “Categories” table of the “Northwind” database of the “spsdemo” SQL Server, using current Windows Credentials.

Sample

    <sqlData tableName="SqlCategories">
       <providerName>System.Data.SqlClient</providerName>
       <connectionString>Data Source=spsdemo;Initial Catalog=Northwind;Integrated Security=true;</connectionString>
       <commandText>
           SELECT * FROM Categories
       </commandText>
    </sqlData>

The following statement will return data from the “Employees” table of the “spsdemo” Oracle database, using HR credentials.

Sample

    <sqlData tableName="OracleEmployees">
       <providerName>System.Data.OracleClient</providerName>
       <connectionString>Data Source=spsdemo;User ID=HR;Password=HRPassword;</connectionString>
       <commandText>
           SELECT Employee_ID, First_Name, Last_Name FROM Employees
       </commandText>
    </sqlData>