Defining MSSQL database
Defining MSSQL database
Reporting Services provides a data processing extension which supports report data retrieval from Microsoft SQL Server relational databases. This MSSQL data processing extension retrieves data from the relational data tables, views, stored procedures, and other data structures defined on the underlying data source. After you get connected to a data source, you can create a report dataset that defines the data you want to use from that data source in your report. A Reporting Services dataset includes a data source, a query that retrieves data when the report is processed, and a collection of fields. This dataset definition is saved in the report definition. The Datasets window shows the current dataset definitions in a report.
You must define a dataset by creating a query using Transact-SQL query text or stored procedure calls, or by choosing a table and retrieving all the data from it. The collection of fields for a dataset is created automatically from the query. You can edit fields or add calculated fields. Calculated fields have expression-based values evaluated when the query runs. You can also create filters to limit the data at the source before it is sent to a report.
There are two query designers for relational data sources:
1) A generic query designer and
2) Graphical query designer.
The generic query designer opens by default. Use the generic query designer to enter Transact-SQL text directly, to paste query text from another source, to enter complex queries that cannot be built using the graphical query designer, or to enter query-based expressions. Use the graphical query designer to browse the metadata on the data source to interactively design a query or to view a list of stored procedures or tables on the data source.
To open the query designers in Report Designer, select the Data tab. To switch from one query designer to the other query designer, click the Generic Query Designer (Icon of the Generic Query Designer button) toggle button on the Data view toolbar.
After defining a query, you can also specify properties such as Timeout and CaseSensitivity, and define which report parameters pass values to query parameters. When you run this query, you see a result is set from the data source. Columns in the result set map to the fields in the dataset. When the report is processed, report items bound to the dataset fields display the retrieved or calculated data. A report processes only the first result set retrieved by any query.
The query designers for relational data have the following three command types:
1) Text type accepts Transact-SQL statements.
2) This Stored Procedure type accepts calls to stored procedures on the data source. This mode does not support output parameters.
3) Table Direct type accepts the name of a table on the data source and returns all columns for the table.All data does not providers support Table
Direct.
How to Create a Dataset in MSSQL ?
a) On the Data tab, from the Dataset drop-down list, select < New Dataset >.
b) On the Dataset dialog box, from the Data source drop-down list, select New Data Source.
c) On the Data Source dialog box, from the Type drop-down list, select Microsoft SQL Server.
e) Specify a connection string that works with your SQL Server data source.
Now contact your database administrator for connection information. The following connection string example specifies the sample AdventureWorks database on the local client.
Data Source=localhost;Initial Catalog=AdventureWorks
f) Set the database credentials in the Data Source dialog box on the Credentials tab.
g) Click OK.
How to define a connection string for the database server in MSSQL ?
For MSSQL Server, used with the SQL Server data provider, we can write the connection string like this:
VB.Net Version
for Sql Server
Dim connectionString As String = “server=P-III; database=programmersheaven;” + _ “uid=sa; pwd=;”
C# Version
// for Sql Server
string connectionString = “server=P-III; database=programmersheaven;” +_
“uid=sa; pwd=;”;
At first we have defined the instance name of the server, which is P-III on my system. Next we defined the name of the database, user id (uid) and password (pwd). Since my SQL server doesn’t have a password for the System Administrator (sa) user, I have left it blank in the connection string. (because I know this is very dangerous and is really a bad practice – never, ever use a blank password on a system that is accessible over a network)
For Oracle Database Server, used with the Oracle data provider, we can write the connection string like this:
C# Version
string connectionString = “Data Source=Oracle8i;User Id=username;” +
“Password=pwd; Integrated Security=no;”;
VB.Net Version
Dim connectionString As String = “Data Source=Oracle8i;User Id=username;” + _
“Password=pwd; Integrated Security=no;”
For MS Access Database, used with the OLE DB data provider, we can write the connection string like:
C# Version
// for MS Access
string connectionString = “provider=Microsoft.Jet.OLEDB.4.0;” +
“data source = c:\programmersheaven.mdb”;
VB.Net Version
‘ for MS Access
Dim connectionString As String = “provider=Microsoft.Jet.OLEDB.4.0;” + _
“data source = c:programmersheaven.mdb”
At first we have defined the provider of the access database. Then we have defined the data source which is the address of the target database.
For MS SQL Server, used with the ODBC data provider, we can write the connection string like:
C# Version
string connectionString = “Driver={SQL Server};Server=FARAZ;Database=pubs;Uid=sa;Pwd=;”;
VB.Net Version
Dim connectionString As String = “Driver={SQL Server};Server=FARAZ;Database=pubs;Uid=sa;Pwd=;”
