Find Partition Range in a SQL Server 2005/2008 PartitionTable

In one of the previous articles, we saw how to Find Partition Information in a SQL Server Table. This blog post shows you how to find Partition Ranges for a PartitionFunction. Here’s the query that lists down the partition range values for the partition function ‘TransactionRangePF1’

SELECT psch.name as PartitionScheme,
prng.value AS ParitionValue,
prng.boundary_id AS BoundaryID
FROM sys.partition_functions AS pfun
INNER JOIN sys.partition_schemes psch ON pfun.function_id = psch.function_id
INNER JOIN sys.partition_range_values prng ON prng.function_id=pfun.function_id
WHERE pfun.name = 'TransactionRangePF1'

OUTPUT

Feedback:

SSIS - Working with the File System Task

In this blog post, we will look into the SQL Server Integration Services (SSIS) feature provided by the File System task, which is available in the control flow tab as shown below:

As the name suggests, this task is used to perform operations on files in the system(computer). With this task we can move, delete folders and files. We can also use it to specify the attributes of files.

The following activities can be performed with the help of File System Task:

• Copy directory: To copy all the files from a folder to another directory. In this case we need to configure the source connection and destination folders.
• Copy File: To copy a single file from one location to another.
• Create Directory: To create a folder, you need to specify the source connection where the folder is to be created (You need to have NTFS rights for creation, if not rights run time error is thrown)
• Delete Directory: To delete the specified folder, you need to specify source connection. (you need NTFS rights for deletion)
• Delete Directory Content: To delete all entries in a folder
• Delete File: To delete the specified file from the specified location
• Move Directory: To move complete folder from one location to another
• Move file: To move the specified file from the specified location to new one.
• Rename file: To rename the specified file
• Set Attributes: to set the attributes for files as well as folders, which can be hidden, readonly, achieve and system.

Let us take a small example where a variable stores the information about the destination folder.

1. Create a new Integration Services Project > rename the default package to FileSystemDemo.dtsx
2. Create 2 variables with package scope namely ‘SourceConn’ and ‘DestFolder’ of string type > specify the value as the required folder names.
   Note: To create a variable, select variables from the SSIS menu > select Add variable (ensure that no task is selected so that the scope of the variable is for complete package)
3. Drag and drop the File System Task control on to the designer
4. Configure the File System Task in following manner
   
5. You will have to be careful while giving the source and connection values as variables, as the required folder and files must be existing. Otherwise we will end up in getting a run time error.
6. To execute the package from Business Intelligence Development Studio (BIDS) use F5.

Feedback:

Find Partition Information in a SQL Server 2005/2008 Table using T-SQL

In my previous article http://www.sqlservercurry.com/2009/08/how-to-partition-tables-of.html , I demonstrated how to use a ready made script provided by Microsoft to create partitions on the AdventureWorks database. A user left a comment - “How can I programmatically determine the tables that have partitions and also determine information like PartitionScheme.

Here’s how -

SELECT DISTINCT part.object_id as ObjectID,
OBJECT_NAME(part.object_id) as TableNm,
idx.name as IndexNm,
idx.type_desc as IndexType,
psch.name as PartitionScheme,
pfun.name as FunctionName
FROM sys.partitions part
INNER JOIN sys.indexes idx
ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
INNER JOIN sys.data_spaces dsp
ON idx.data_space_id = dsp.data_space_id
INNER JOIN sys.partition_schemes psch
ON dsp.data_space_id = psch.data_space_id
INNER JOIN sys.partition_functions pfun
ON psch.function_id = pfun.function_id

OUTPUT

Feedback:

How to Partition Tables of the AdventureWorks Database

A SQLServerCurry viewer contacted me to find out if there was an easy way to create partition tables on the AdventureWorks database. He wanted to study it for educational purposes.

For all those who are looking out for an existing partition script to partition tables of the AdventureWorks database, follow these steps:

Go to http://www.codeplex.com/MSFTScrptProdSamples and download the ‘PartitionScript’ of the SQL Server database version you have installed. I have SQL Server 2008 and I have downloaded the script from here (SQL2008.Data_Engine_Admin_Script.Samples.x86.msi)

Double click and install the .msi. Once the installation is over, look out for PartitionAW.sql in “C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\Partitioning\Scripts” . This script partitions the AdventureWorks tables -- TransactionHistory and TransactionHistoryArchive.

As given in the MSDN documentation:

TheTransactionHistory table contains sales records for the current year. This table is used primarily for inserting new records and updating them as required. The TransactionHistoryArchive table contains sales records older than the current year. This table is used primarily for SELECT queries and as a staging table for moving data into a data warehouse. For more information about the partitioning design of these tables, see Planning Guidelines for Partitioned Tables and Indexes.

After running the query, the output is as shown below:

Feedback:

Tips when working with SSRS (SQL Server Reporting Services)

Some tips when working with SSRS (SQL Server Reporting Services) are as follows:

Tip 1: Following are some steps we need to take when migrating from one Report server to another instance

a. Backup All Files and Data

b. Install SQL Server Reporting Services (another instance)

c. Move the Report Server Database to the newly created instance

d. Configure the Report Server as required

e. Check Windows Service Group

f. Confirm and check your Deployment

Tip 2: Report server periodically monitors the schedules known to the system and checks the corresponding SQL Agent Jobs for

a. Existence

b. Changes

If the Agent job does not exist, a new one is created. If any changes were made to the Agent job, it is updated with the original settings as saved in the SSRS server.

Tip 3: You should not recreate Report Model from scratch unless you want to start over completely with the model. It also is true for all reports that use the model. Since models use GUID identifiers, recreating the model from scratch always results in a model that is unusable by all existing reports.

Tip 4: Report Models do not support SQL queries. You need to use Query Builder to create queries. The language is XML based, difficult to write and edit queries.

Tip 5: A Textbox can have a maximum of characters up to 32,000

Tip 6: When you are using Custom Code and want to debug the report from BIDS, you need to add the assembly in the location ‘C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE’

If not added then the report cannot be debugged.

You need to add reference to Microsoft.ReportingService.ProcessingObjectModel.dll

Feedback:

SQL Server 2008 R2 August CTP and Report Builder 3.0 August CTP Released

SQL Server 2008 R2 August CTP can be downloaded for evaluation here. Here are the download links for MSDN and TechNet subscribers. As given on the Microsoft Site:

SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 to help your organization scale with confidence and improve IT and developer efficiency with new and enhanced tools for application and multi-server management, master data services and complex event processing. The new Self Service BI capabilities will empower end users to access, integrate, analyze and share information using business intelligence tools they already know – Microsoft Office.

The August Customer Technology Preview (CTP) includes Application and Multi-server Management which will help organizations manage database environments efficiently at scale with increased visibility and control across the application lifecycle. This CTP also includes Microsoft SQL Server StreamInsight, Microsoft’s new complex event processing technology to help businesses derive better insights by correlating event streams from multiple sources with near-zero latency.

Check out - What’s New in in Reporting Services 2008 R2 August CTP

Report Builder 3.0 August CTP has been released. A stand-alone MSI for Report Builder 3.0 August CTP, is now available for download here: http://go.microsoft.com/fwlink/?LinkID=160384

Feedback:

Find Constraints of All Tables in a SQL Server 2005/2008 Database

Here’s a real simple query if you need to list the constraints of all the tables in your database. This query lists the constraints of all the tables in the AdventureWorks database

USE AdventureWorks;
GO
SELECT OBJECT_NAME(object_id) as [Constraint],
    OBJECT_NAME(parent_object_id) AS [Table],
    type_desc AS [Constraint Type],
    create_date AS [Creation Date]
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
ORDER BY [Table]
GO

OUTPUT

Feedback:

Working with SQL Server AMO (Analysis Management Objects) – Part 2

In the first part of this article, we saw how to connect to the analysis server and list all the databases. We then chose a database and displayed the cubes, measures, partitions and dimensions in a ListBox.

In this part of the article, we will see how to take backups of MultiDimensional Anaysis Service Databases, explore the security part of how to add a role, a member to role and how to provide the necessary permissions. We will also see how to create new databases, data sources, data source views and required tables . Lastly I will also demonstrate how AMO offers to wok without connecting to the analysis services instance.

AMO can be very useful for taking backups of multidimensional databases, we can show all the available databases, ask the user to select the one he needs to backup and ask the user to specify device(s) for backup.

With following code we can take backup of any analysis services database and again restore it:

//declare objects
Server server;
Database db=new Database();
//connect to analysis server
server = new Server();
server.Connect(@"data source=<server name>");
db = server.Databases["<database name>"];
//take backup of analysis services database
//the extension is abf
db.Backup(@"<path>");

//restore database
//connect to analysis server
Server server;
server = new Server();
server.Connect(@"data source=<server name>");
 //restore database with abf file name and new name for the
 //analysis services database
server.Restore(@"<path and abf file name>", "<database name>");

We can use AMO for adding security related objects, like creating roles and later adding users to the roles. This can be a very useful feature where in we can provide the security for analysis services database access on the fly. When we want to revoke permission for a particular we can even remove a particular member from a role or even remove the complete role itself.

The following code explains how to add a role, a member to role and how to provide the necessary permissions

Server server;
try
{
   //connect to analysis server
   server = new Server();
   server.Connect(@"data source=<server name>");
   //pick up a particular database
   db = server.Databases[0];
   Cube cube=db.Cubes[0];
   //create a new role
   Role role;
   role = new Role("<role name>");
   //add member to the role
   RoleMember member = new RoleMember();
   member.Name = @"<domain name>\<user name>";
   role.Members.Add(member);
  db.Roles.Add(role);
   role.Update();
   //provide permissions as administrator
   DatabasePermission perm;
   perm = db.DatabasePermissions.Add(role.ID);
   perm.Administer = true;
   perm.Update();
   //add permission for cube
  CubePermission cubeperm;
  cubeperm = cube.CubePermissions.Add(role.ID);
  cubeperm.Read = ReadAccess.Allowed;
  cubeperm.Update();
}
catch (AmoException amoex)
{
        MessageBox.Show(amoex.Message);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
 }
}

We can use AMO to create new database, data sources, data source views and required tables in them.

With following code we will use adventure works as database and choose Person.Contact table into DSV

try
    {
        //connect to analysis server
        server = new Server();
        server.Connect(@"data source=<server name>");
        //create new database
        db = new Database("<database name>");
        db = server.Databases.Add("<database name>");
        db.Update();
        DataSource datasrc = db.DataSources.FindByName("<database name>");
        // Create the data source
        datasrc = db.DataSources.Add("<data source name>", "<data source id");
        datasrc.ConnectionString = @"Provider=SQLNCLI10.1;Data Source=<server name>;
        Integrated Security=SSPI;Initial Catalog=adventureworks";
        // Send the data source definition to the server.
        datasrc.Update();
        //add Data source view to the newly created data source
        DataSourceView dsv = null;
        dsv = db.DataSourceViews.Add();
        dsv.DataSourceID = "<data source id>";
        dsv.Name = "<data source view name>";
        dsv.Schema = new DataSet();
        System.Data.OleDb.OleDbConnection cn =
            new System.Data.OleDb.OleDbConnection(dsv.DataSource.ConnectionString);
        cn.Open();
        //add table
        System.Data.OleDb.OleDbDataAdapter daContact =
           new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Person.Contact", cn);
        daContact.FillSchema(dsv.Schema, SchemaType.Mapped, "Contact");
        DataTable[] dts =
            daContact.FillSchema(dsv.Schema,SchemaType.Mapped, "Contact");
        DataTable dt = dts[0];
        dt.ExtendedProperties.Add("TableType", "Table");
        //this step will specify the schema name for table
        dt.ExtendedProperties.Add("DbSchemaName", "Person");
        dt.ExtendedProperties.Add("DbTableName", "Contact");
        //following step can be ommitted if we want to keep same friendly name
        dt.ExtendedProperties.Add("FriendlyName", "Contact");
        dsv.Update();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Another facility which AMO offers is to wok without connecting to the analysis services instance. This metadata can be kept for later usage for deployment.

With following code we process partition, dimensions and cube without actually executing. Later we execute all in one batch in parallel.

try
    {
        //connect to analysis server
        server = new Server();
        server.Connect(@"data source=<server name>");
        server.CaptureXml = true;
        //list all the databases from the server in listbox
        db = server.Databases["<database name>"];
        foreach (Cube cube in db.Cubes)
        {
            cube.Process(ProcessType.ProcessFull);
            foreach (MeasureGroup measuregrp in cube.MeasureGroups)
            {
                foreach (Partition partition in measuregrp.Partitions)
                {
                    partition.Process();
                }
            }
        }

        foreach (Dimension dim in db.Dimensions)
        {
            dim.Process();
        }
        //stop capturing xml
        server.CaptureXml = false;
        //execute actually on server
        XmlaResultCollection results = server.ExecuteCaptureLog(true, true);
        foreach (XmlaResult result in results)
        {
            foreach (XmlaMessage message in result.Messages)
            {
                MessageBox.Show((message.Description));
                if (message is XmlaError)
                {

                    // the processing failed, there is at
                    //least one error
                }
            }
        }

    }
    catch (AmoException amoEx)
    {
        MessageBox.Show(amoEx.Message);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);

    }

}

Feedback:

Working with SQL Server AMO (Analysis Management Objects) – Part 1

AMO provides us with a collection of classes with which we can manage objects of a running instance of SQL Server Analysis Services programmatically. These classes are available in namespace Microsoft.AnalysisServices. The assembly is available at the location <drive name>:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

With AMO we can create, modify, delete and view objects like dimensions, cubes, analysis services databases etc. We can also process the cubes, partitions as and when required.

One important thing to remember is you cannot query data with AMO; for this you will need ADOMD.NET.

AMO gives commands from client application and are converted to XMLA (XML for Analysis Services) and delivered to analysis services instance. AMO class library is equipped with various classes with the topmost class being the ‘Server.’' It follows the database, cubes, measure groups, measures, dimensions, perspectives.

The analysis services instance can have multiple databases, with AMO you can connect to one of them or there can be multiple instances of analysis services.

We need to connect to the server which has analysis services installed. Following code will list all existing analysis service databases from a server

//declare object
Server server;
try
{
       //connect to analysis server
       server = new Server();
       //if it is named instance server name should follow instance name
       server.Connect(@"data source=<server name>");
       //list all the databases from the server in listbox
       foreach (Database database in server.Databases)
       {
             listBox1.Items.Add(database.Name);
             //list all the data sources for each database in listbox
              foreach (DataSource  ds in database.DataSources)
             {
                  listBox2.Items.Add(ds.Name);
              }
        }
  }
  catch (Exception ex)
  {
         MessageBox.Show(ex.Message);
   }

AMO can be useful if we want to show users when were the cube or partitions last processed.

With the following code, we will choose one database from the analysis server and display the cubes, measure groups, measures, partitions and dimensions from it in a Listbox

//declare objects
Server server;
Database db=new Database();

try
{
      //connect to analysis server
      server = new Server();
      server.Connect(@"data source=<server name>");
      db = server.Databases["<database name>"];
       foreach (Cube cube in db.Cubes)
      {
           //will process full cube          
          cube.Process(ProcessType.ProcessFull);
          listBox3.Items.Add("Default measure: " + cube.DefaultMeasure);
          //list all cube names
          listBox3.Items.Add("Cube: " + cube.Name);
          //last processed is current time as we processed full
          listBox3.Items.Add(cube.LastProcessed);
          foreach (MeasureGroup measuregrp in cube.MeasureGroups)
          {
                //list all measure groups
                listBox3.Items.Add("Measure Group: " + measuregrp.Name);
                foreach (Partition partition in measuregrp.Partitions)
                {
                      //give partition name
                       listBox3.Items.Add("Partition: " + partition.Name);
                       //list data and time when partition was last processed
                       listBox3.Items.Add(partition.LastProcessed);
                 }
                 foreach (Measure measure in measuregrp.Measures)
                 {
                       //list measure for current measure group
                       listBox3.Items.Add("Measure: " + measure.Name);
                 }
            }
        }
        foreach (Dimension dim in db.Dimensions)
        {
             //list all dimensions
              listBox3.Items.Add("Dim: " + dim.Name);
         }
  }
 //capture specific exception
  catch (AmoException amoEx)
  {
          MessageBox.Show(amoEx.Message);
  }
  catch (Exception ex)
  {
          MessageBox.Show(ex.Message);
  }
}

This post showed how to connect to the analysis server and list all the databases. We then chose a database and displayed the cubes, measures, partitions and dimensions in a Listbox.

In the next part of this article, I will demonstrate the foolowing

- how to take backups of MultiDimensional Anaysis Service Databases,

- explore the security part of how to add a role, a member to role and how to provide the necessary permissions.

- how to create new databases, data sources, data source views and required tables .

- how AMO offers to wok without connecting to the analysis services instance.

Feedback:

Display List of All Databases with their Sizes in SQL Server 2005/2008

Here’s a query that lists the properties of a database using the sys.master_files and sys.databases, both System Views for retrieving Database information.

SELECT sysDa.Name,sysDa.create_date,sysDa.recovery_model_desc, temp.DBSize8KBPage
FROM
(
    SELECT sysMas.database_ID, sysMas.size, SUM(size)  as DBSize8KBPage
    FROM sys.master_Files sysMas
    GROUP BY sysMas.DataBase_ID, sysMas.size
)
temp
INNER JOIN
Sys.Databases sysDa
on temp.Database_ID = sysDa.DataBase_ID

OUTPUT

Feedback:

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

While programming a Data Layer, I often refer to the database and its tables for the datatypes and its sizes. It’s real handy to be able to print a list of the properties of the columns. Here’s a query that will help you fetch the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database.

The following query fetches the Schema, DataTypes and Sizes of all columns of all tables in the AdventureWorks database

USE AdventureWorks;
GO
SELECT
    OBJECT_NAME(col.OBJECT_ID) as [TableName],
    col.name AS [ColName],
    SCHEMA_NAME(typ.schema_id) AS type_schema,
    typ.name AS type_name,
    col.max_length
FROM sys.columns AS col
JOIN sys.tables AS tbl on col.object_id =  tbl.object_id
JOIN sys.types AS typ ON col.user_type_id = typ.user_type_id
ORDER BY [TableName];
GO

OUTPUT

Feedback:

24 hours of free SQL Server training on September 2, 2009

24 Hours of PASS, is a FREE community event featuring some awesome SQL Server Sessions by the top SQL Server speakers in the industry. Here are some important links related to this 24 hours non-stop Free SQL Server Training

List of SQL Server Sessions

Register here

The event starts at 00:00 GMT (UTC) on September 2, 2009. To find out your local timezone, use the external time zone converter

Feedback:

Working with Report Definition Language (RDL) and Change it to add a column

We often come across a requirement to change the RDL generated through a wizard. This post will show you how to add a column to the RDL.

Step 1: You can get the xsd against which the rdl file is validated from following URL in the browser

For Default Instance

http://<servername>:<port number>/<report server>/reportdefinition.xsd
or

For Named Instance

http://<servername>:<port number>/<report server_instance name>/reportdefinition.xsd

Port number need not be specified if it is default port.

With this schema we see a lot of elements available. We will be concentrating on a few of them specific to a report.

Step 2: This report is based on AdventureWorks database and the Person.Contact table in it. The select query is

SELECT FirstName, LastName, EmailAddress FROM Person.Contact

This report is created using report creation wizard, with table and all the elements in details for the table. The style ‘Corporate’ is selected for the report.

The xsd for this report looks as follows:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:tns="http://schemas.microsoft.com/SQLServ
er/reporting/reportdesigner"
attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/S
QLServer/reporting/reportdesigner"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="DataSourceID"
type="xs:string" />
<xs:element name="SecurityType"
type="xs:string" />
<xs:element name="TypeName" type="xs:string" />
<xs:element name="UseGenericDesigner"
type="xs:boolean" />
<xs:element name="DefaultName" type="xs:string" />
<xs:element name="ReportID" type="xs:string" />
<xs:element name="ReportUnitType"
type="xs:string" />
</xs:schema>

Step 3: Let us see how to change certain properties by changing the RDL. You can view this code by right clicking on the .RDL file and selecting the option for ‘View Code’

Step 4: We will add a column in the existing table. First we need to change the select statement for it.

SELECT Title,FirstName, LastName, EmailAddress FROM Person.Contact

Step 5: We need to add this in the dataset also. We do the changes as follows:

<Field Name="Title">
  <DataField>Title</DataField>
  <rd:TypeName>System.String</rd:TypeName>
</Field>

This element Field gets added in the Fields

Step 6: We need to incorporate these changes in the table by adding a column, specifying the heading for it and also giving the field from which it gets populated. This being the first column in the table, we need to add it before First Name

We do the change to the TablixBody, in ReportItems in Body as follows:

<TablixColumn>
  <Width>0.5in</Width>
</TablixColumn>

For this column, we are keeping the width less as compared to other columns to .5 inches

Step 7: We need to add TablixCell as follows:

<TablixCell>
     <CellContents>
         <Textbox Name="textTitle">
         <CanGrow>true</CanGrow>
         <KeepTogether>true</KeepTogether>
         <Paragraphs>
      <Paragraph>
                 <TextRuns>
      <TextRun>
      <Value>Title</Value>
      <Style>
                  <FontFamily>Tahoma</FontFamily>
          <FontSize>11pt</FontSize>
          <FontWeight>Bold</FontWeight>
          <Color>White</Color>
      </Style>
      </TextRun>
      </TextRuns>
      <Style />
      </Paragraph>
         </Paragraphs>
  <rd:DefaultName>textTitle</rd:DefaultName>
  <Style>
  <Border>
  <Color>DimGray</Color>
  <Style>Solid</Style>
  </Border>
  <BackgroundColor>#1c3a70</BackgroundColor>
  <PaddingLeft>2pt</PaddingLeft>
  <PaddingRight>2pt</PaddingRight>
  <PaddingTop>2pt</PaddingTop>
  <PaddingBottom>2pt</PaddingBottom>
  </Style>
</Textbox>
</CellContents>
</TablixCell>

Step 8: We now need to add the detail line for the new column title as follows

<TablixCell>
<CellContents>
<Textbox Name="Title">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
  <TextRuns>
 <TextRun>
 <Value>=Fields!Title.Value</Value>
 <Style>
 <FontFamily>Tahoma</FontFamily>
 </Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Title</rd:DefaultName>
<Style>
<Border>
<Color>DimGray</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 </CellContents>
 </TablixCell>

Step 9: Finally we need to add <TablixMember /> in TablixMembers in TablixColumnHierarchy.

And the task is done!

Feedback:

Creating a Drill Down Report with SQL Server 2008

This report is based on the AdventureWorks sample database

Step 1: Create a new shared data source for AdventureWorks database

Step 2: Create following query

SELECT    
Production.ProductCategory.Name AS Category,
Production.ProductSubcategory.Name AS [Sub Category],
Production.Product.Name AS Product,
Purchasing.ProductVendor.StandardPrice,
Purchasing.Vendor.Name AS VendorName
FROM  Production.Product INNER JOIN
Production.ProductSubcategory ON
Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
Production.ProductCategory ON
Production.ProductSubcategory.ProductCategoryID =
Production.ProductCategory.ProductCategoryID INNER JOIN
Purchasing.ProductVendor ON
Production.Product.ProductID =
Purchasing.ProductVendor.ProductID INNER JOIN
Purchasing.Vendor ON
Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID AND
Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID AND
Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID

Step 3: Now create a simple report in the table in the following manner

The report preview will look like

Step 4: Let us add a group for Vendor

Step 5: After giving the name to the group as ‘VendorName’, making the font bold and inserting a row in the group as shown above, the preview looks like

Step 6: From Row groups > select VendorGroup details (group Details and not group header) > go to Group Properties > select Visibility tab > select Hide > click on Display can be toggled by this group item and select the name of the group

Step 7: We can also change the drill down and make it reverse by setting ‘InitialToggleState’ to True and by selecting the group properties (header), visibility tab and click Show

Feedback:

Learning LINQ can be useful for Database Developers

A few Database Developers I know have shown keen interest in learning XML and LINQ as they see a use of it in their database career. The part that queries are now part of the programming language, fascinates them. I have been receiving request for posting LINQ articles over here. I want my viewers to know that I have been posting a lot of LINQ stuff on dotnetcurry.com as well as devcurry.com

Here are some useful MSDN links as well some articles about LINQ written by me

LINQ Video Tutorials

LINQ to SQL: .NET Language-Integrated Query for Relational Data

Some Common Operations using LINQ To XML - Part I

Some Common Operations using LINQ To XML - Part II

Some Common Operations using LINQ To XML - Part III

Short LINQ Queries

Other LINQ Articles

Feedback:

Creating a Self-Signed Certificate in SQL Server 2005/2008

In simple worlds, Certificates can be used to secure data in SQL Server. There are a number of ways to create a certificate - from a file, from a signed executable file or from an assembly. You can also create a Self-Signed Certificate which is the easiest of all as shown here. They are called self-signed as they are not generated by an authority but by SQL Server itself.

To secure an object, you can first create a certificate, map it to a database user and then sign the object with that certificate.

Here’s how to create a self-signed certificate.

USE Social;
CREATE CERTIFICATE SocialCert
   ENCRYPTION BY PASSWORD = 'q35RED6gg577gghy53FF'
   WITH SUBJECT = 'ID CERTIFICATE',
   EXPIRY_DATE = '12/31/2009';
GO

You can read more about symmetric keys, asymmetric keys and other kind of certificates over here

Feedback: