Saturday, August 21, 2010

Brief about SSP (MOSS - Shared Service Provider)

What is SSP: Shared Services Providers act as more than an intermediary between web applications and sensitive services. By creating a Shared Services Provider (SSP) you can create an interface, or a mediator, that retrieves the information you need from the sensitive service and places it in a database for query by the appropriately authorised web applications.

Best example to elaborate use of SSP is list of users/profiles. SSP can import all the users and their information from Active Directory (AD) once and make it available to other web applications. So here web applications can consume the date from SSP instead of managing a copy of identical data locally.

Some key aspects of SSP that I learned are mentioned below.

  • SSP offers five services
  1. Business Data Catalog (BDC)
  2. Office SharePoint server search
  3. Excel Services
  4. User Profile Application
  5. Session State
  • When you configure SSP, it creates two databases
    • SSP Service Database (SharedServices_DB) à For SSP configurations
    • SSP Search Database (SharedServices_Search_DB) à For storing search results of users.

Recommended Guidelines for SSP

  1. It's recommended to create different application pool for SSP web application.
  2. Not recommended to use same web application for hosting both SharePoint sites and SSP sites. (backup strategy)
  3. Its good practice to create new application for hosting my sites. Using same web application for both my site and SSP will cause problems while backup.
  4. SSP creates two databases, it is good practice to use different SQL instance for these databases rather using same SQL server for SharePoint sites.

Also you can refer to very good article/post on SSP by sharepoint MVP.
http://msmvps.com/blogs/shane/archive/2007/06/29/what-is-a-shared-service-provider.aspx

Hope you find it useful, cheers.

Saturday, August 14, 2010

How to find Tables with NO INDEXES...??

Often times when your application is not performing well as it used to, then you start blaming your SQL developer for the bad queries. But this may not be the case all the time. For your application to work fast, you should also have proper indexes in the underlying database.

When your application is not working as fast as it used to, then first check that you might want to do is to check if you have proper indexes in place. One very obvious check that you might want to do will be to indentify tables which are not having any indexes at all. Now how you do that?

Open the SSMS, and check all the tables one by one inside your database..!! Pretty time consuming and so manual, right.

I got this very handy SQL script which will do the job for you very quick. Execute the below script on your database and you will get all the list of all culprit tables.

SELECT name

FROM
sys.tables

WHERE
OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

Hope that you will find this post useful.

Cheers,

Saturday, August 7, 2010

Best Practices for SQL Queries

I was looking into best practices for writing SQL queries and came across one very interesting and useful online resource/post. This post focuses on how to write understandable, maintainable and readable queries. Good post for SQL queries code review, Below is the link.


Enjoy coding..!!!

Fetching Contents from EXCEL File..!!

Often times in .Net application, we want to fetch data from EXCEL files. Reading contents from EXCEL files is quite simple and easy task. 

There are two ways, you can read contents from EXCEL file.

  1. Using Microsoft Office Component
  2. Using Microsoft Jet Engine

As per Microsoft recommendation, it is not advisable to use Office components on the server. It means that if you want to use this library for a server application, it's not a good idea to use the Office component. And it's better to do the connection using Jet Engine.

So in this post I will be discussing the second & recommended option, which is using Microsoft Jet Engine.

Connecting To EXCEL

To make a connection to the EXCEL, you can use OLEDB objects that will treat EXCEL file as database and then required contents or data can be fetched using SQL queries. Following is the connection string that you can use.

The connection string should be set to the
OleDbConnection
object. This is very critical as Jet Engine might not give a proper error message if the appropriate details are not given

Syntax for XLS files:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=<Full Path of Excel File>; Extended Properties="Excel 8.0; HDR=No; IMEX=1".

Data Source: Specify here the path of the excel file.

Extended Properties: Includes

  1. HDR = [Yes/No]

This property will be used to specify the definition of header for each column. If the value is "Yes", the first row will be treated as heading. Otherwise, the heading will be generated by the system like F1, F2 and so on.

  1. IMEX = [0/1/2]

This property indicates the Import Export mode and addresses how contents datatypes will be treated.

  • IMEX=0 and IMEX=2: It will take the first 8 rows and then the data type for each column will be decided.
  • IMEX=1 : In this case, everything will be treated as text.

For more info regarding Extended Properties,
click here.

Finally your connection to EXCEL file will be something like this.

cn = new
OleDbConnection();

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\MyEXcel.xls'; Extended Properties="Excel 8.0; HDR=No; IMEX=1";

Fetch EXCEL Sheets from EXCEL File

As we know so far that EXCEL file is like a database, the sheets in the excel file are equivalent to tables inside a database. So all we need to know is the sheet name and we can the write a query to fetch data from the sheet. So our next step is to fetch the Sheets contained in the EXCEL file. Following code will help you fetching all the sheets contained in the EXCEL file.

System.Data.DataTable dt = null;

cn.Open();

dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if(dt == null)

{

return
null;

}

String[] excelSheets = new
String[dt.Rows.Count];

int i = 0;

// Add the sheet name to the string array.

foreach(DataRow row in dt.Rows)

{

    string strSheetTableName = row["TABLE_NAME"].ToString();

    excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1);

    i++;

}

return excelSheets;

If you already know what sheets an EXCEL file is going to have then you can directly hardcode that in your code. Above code is required when you want to get list of all the sheets in EXCEL file.

Reading Contents from EXCEL Sheet

Now we know the database (Excel File connection) and table (sheetName) name from which we have to read contents. So we only have to write one query to fetch or read data from the excel sheet. Following is the code for this.

string strComand;

string strSheetName;

strSheetName = "sheet1";     //I know the sheet name so I mentioned it here directly.

strComand="select * from ["+strSheetName+"$]"; // You can specify specific column names as well in this query instead of *.

daAdapter=new
OleDbDataAdapter(strComand,cn);

    DataTable dt=new
DataTable(strSheetName);

    daAdapter.FillSchema(dt,SchemaType.Source);

    daAdapter.Fill(dt);

    cn.Close();

    return dt ;

Above code will give you DataTable
containing all the content of the specified sheet. Now you can load this data into DataSet as follows.

DataSet ds=new
DataSet();             

    ds.Tables.Add(dt);

I hope that you will find this post useful. Feel free to provide comments and your feedbacks.

Cheers,

Mirza Ateeq