Thursday, April 28, 2011

Today is exactly four month completed of my Blog.

Hello Each One,

Today is exactly four month completed of my Blog; I started this drive of writing a blog on different topics of MSBI, SqlServer 2005, 2008 today I have reached first mile stone. There are so many great experiences I had during this four month time’s span. I gain knowledge of lot of things and enjoyed the learning of new things for posting on my blog. So everyone has been wonderful part of this blog Thanks for Every one for your supports.

I want to thank you for cheering me and I want to express my appreciation to all my friends without their support, I would have not reached here. Today I have reached my first milestone my journey of completing the four month, have just started so continue reading and offer your views on different topics.

I am glad to announce that the Four month of Wait Types and Queues is very successful. I am fortunate to have some of the excellent comments throughout the series. Here is the complete list of the blog posts in this series.

I enjoyed writing the series and I plan to continue writing similar series.

               Thanks for every one and keep Reading.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

SQL Azure Reporting.

SQL Azure Reporting

SQL Azure Reporting enables developers to enhance their applications by embedding cloud based reports on information stored in a SQL Azure database.  Developers can author reports using familiar SQL Server Reporting Services tools and then use these reports in their applications which may be on-premises or in the cloud.

First we need to understand what is SQL Azure & SQL Azure Database?

Microsoft SQL Azure
Microsoft SQL Azure delivers on Microsoft’s SQL Server Data platform vision of extending the Data platform capabilities to the cloud. SQL Azure provides data services, including a relational database, reporting; and data synchronization with mobile users, remote offices and business partners.

Sign Up for a free account to Access SQL Azure
Two ways you can try SQL Azure FREE of charge:
·         Sign up for this limited-time promotion, and you’ll get TWO 1GB Web Edition databases for one month.
·         Get a 1GB Web Edition database for no charge for 3 months.

Microsoft SQL Azure Database
Microsoft SQL Azure Database is part of Microsoft SQL Azure. It is a cloud-based relational database service built on SQL Server technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database enables easy provisioning and deployment of multiple databases.

Benefits of the Azure platform for Azure Reporting Services are:


·         Highly available, the cloud services platform has built-in high availability.
·         Cloud services platform automatically scales up and down.
·         Reports and SQL Azure databases are on a safe place in the cloud.
·         Don’t have to set up servers and you don’t have to invest in managing servers.
·         Use the same tools you use today to develop your solutions. Just develop your reports in BIDS or Report Builder and deploy to Azure.


·         No subscriptions or scheduled delivery available.
·         No developer extensibility in the first version, so no custom data sources, assemblies, report  items or authentication
·    SQL Azure databases are the only supported data sources in the first version, more data sources are expected to come.
·         No Windows Authentication, only SQL Azure username/password is supported in the first version, similar to SQL Azure database. When SQL Azure database gets Windows Authentication

Difference between SQL Server Reporting Services (SSRS) & SQL AZURE Reporting

Developer Tool:
·         SSRS-Business Intelligence Design Studio (BIDS)Report Builder
·         SQL Azure Reporting-Business Intelligence Design Studio (BIDS)
Report Builder (Authoring only - deployment to SQL Azure Reporting through BIDS)

Supported Data Sources:
·         SSRS-Diverse data sources
·         SQL Azure Reporting-SQL Azure databases

·         SSRS-Embed reports into your apps
·         SQL Azure Reporting-Embed hosted reports into your apps

Management & Delivery:
·         SSRS-Report Manager or SharePoint Portal display reports rendering to multiple formats subscriptions scheduled delivery
·         SQL Azure Reporting-SQL Azure Development Portal display reports rendering to multiple formats

Developer Extensibility:
·         SSRS-Extensible for custom data sources, assemblies, report items, authentication, etc.
·         SQL Azure Reporting-Extensibility is not yet enabled

Security Model:
·         SSRS-Windows Authentication
·         SQL Azure Reporting-SQL Azure Username/password

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Wednesday, April 27, 2011

• Microsoft Web Matrix is a web development tool designed for creating ASP.NET web pages.

Microsoft Web Matrix

·         Microsoft Web Matrix is a web development tool designed for creating ASP.NET web pages includes the web server, database, programming model and a deployment tool.
·         Web Matrix is a free web development tool from Microsoft that includes everything you need for website development.
·         It’s a more complete web authoring tool for beginner web designers, with added support for easy-to-use web site templates, and third party web apps such as WordPress.
·         Start from open source web applications, built-in web templates or just start writing code yourself. It’s all-inclusive, simple and best of all free. Developing websites has never been easier.
·         The Web Matrix Project on the other hand, was designed with ASP.NET in mind. It's very small Has great design features, and best of all, it's free!

Web Matrix Supported Operating Systems

·         Windows 7;
·         Windows Server 2003 Service Pack 2;
·         Windows Server 2008;
·         Windows Server 2008 R2;
·         Windows Vista Service Pack 2;
·         Windows XP Service Pack 3
·         Microsoft Windows XP or higher.

 Web Matrix Features

·         This means it's quick to download, copy, or distribute to friends. It also means it doesn't take much memory or disk space.
·         A simple database can make it easy to build database driven application without the need for a full blown production database server.
·         It has got great design features, such as drag-and-drop page designer, templates for existing web pages, pre-supplied code.
·         Web Matrix just uses a single file for each web page, and doesn't rely on any built-in features.
·         The Gallery includes popular open source web applications such as Word Press, Joomla, DotNetNuke and more.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Tuesday, April 26, 2011

Restoring an SSAS Database (CUBE).

If you need to restore a database, you will need to be a server administrator.
Additionally, if the backup file was created with a password, you’ll need to provide that password to successfully restore the file.

Right-click the Databases folder (SSAS tree), and then click Restore.
Your screen looks like below

·         You have the option to restore the database using a different name. If the database already exists on the server, you need to explicitly select the option to Allow Database Overwrite. Also, you can choose whether to keep or discard security information associated with the database.
·         In the Restore Database box, type 'Restored Database' a SSAS Database name to restore backup file.
·         Back file having the extension as abf please select backup file on clicking on Browse button as disply in above diagram.
·         Click the Browse button and expand the C:\Program Files\Microsoft SQL Server \MSSQL.2\OLAP\Backup folder, select Backupfile.abf click ok or Put location of backup file stored.
·         In the Password box, type the password that you used when you archived the database in the previous procedure, and then click OK if not then no need to write down.
·         In After success full restored go to object Explorer, right-click the Databases folder for the Analysis server, and click  Refresh you will get the 'Restored Database' that we restored in SSAS Database.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Monday, April 25, 2011

Login failed for "user"; Reason: Not associated with a trusted SQL Server connection

Solution 1

During a logon process to SQL server the following error may appear: "Login failed for user 'username'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)".

The SQL server has been configured to operate in "Windows Authentication Mode (Windows Authentication)" and doesn't allow

This problem happens when the account information (user name and password) is not specified or does not match the authentication method that you chose for Microsoft SQL Server.

To fix the problem, choose "SQL Server and Windows" as the authentication mode in Microsoft SQL Server.

To choose "SQL Server and Windows" as the authentication mode

·         Click Start > Programs > Microsoft SQL Server > Enterprise Manager.
·         On the SQL Server Enterprise Manager tree, click Microsoft SQL Server > SQL Server Group.
·         Right-click the database name.
·         Click Properties.
·         On the Security tab, click SQL Server and Windows to designate the authentication mode.
·         Restart the computer.

Solution 2

On SQL Server database create new user by expanding
DatabaseNode > Security > Login > Create New User and add this new user with Windows Authentication radio button selected.

This user can be only added by selected Windows Authentication it is Operating system’s User Login.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Thursday, April 21, 2011

Group similar text together using Fuzzy grouping component In SSIS.

Create Txt file DATA.txt


We can see that the meanings of the “SUHAS” and “suhas” are the same except for the case sensitivity. So we need to group and clean those two rows.

You can do this by referring to the below Steps:

·         Create a flat file named “DATA.txt”, and copy the above data into it.

·         Create a Flat File Connection under the Connection Manager panel. Name it “FuzzyGroupConn” and set the filename to the path of the file “DATA.txt”.

·         Drag a Flat File Source to the Data Flow panel and configure it to use the FuzzyGroupConn connection.

·         Drag a Data Conversion component to the panel, connect the Flat File Source to it and convert the data type of the FNAME column to DT_WSTR, and type the output alias "FNAMENEW".

·         Drag a Fuzzy Grouping component to the Data Flow panel. Connect the output of the Data Conversion to the Fuzzy Grouping. Go to the Fuzzy Grouping’s property editor, select the Column tab, choose "FNAMENEW" as input column. Then go to the “Advanced” tab, and set the Similarity threshold to .50.

·         Drag an OLE DB Destination to the Data Flow Panel, connect the Fuzzy Grouping component to it, right click the green connection line, click Data Viewers…, and click the “Add…” button to add a data viewer. The results look like this

·         You can find that the data is cleaned and grouped by similar title values.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2\

Wednesday, April 20, 2011

System Database in SQL Server 2005.

These are the following System Database in SQL Server 2005:

1. master
2. tempdb
3. model
4. msdb
5. Resource

1-      Master Database

·         Master database is system database and it contains information about running server’s configuration.
·         Only Master database is the one which is absolutely must have database. Without Master database SQL Server can not be started.
·         This database is the head of the entire related database. It is also recommended not to give permission of master database to any user & it is also important to update the backups of master database which may take effects on other databases related to this master database.
·         The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings

2- Tempdb Database

·         This database is a temporary database that holds all temporary tables and stored procedures.
·         Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc
·         All the temporary tables & result generated by GROUP BY, ORDER BY & DISTINCT clauses are stored in tempdb database.
·         In cases where a high volume of users and operations are performed with SQL Server the tempdb database can grow to use a significantly large amount of disk space.
·         No need to back up the tempdb database You are actually prevented from performing backup or restore operations on tempdb by SQL Server 2005.
·         The tempdb database is implemented in the tempdev.mdf and templog.ldf files.

3- Model Database
·         When the “create database” statement is used, SQL Server copies the contents of the model database to the newly created database.
·         Provides templates or prototype for a new database means whenever a database is created the contents of model database will be copied to new database.
·         User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
·         The modeldev.mdf and modellog.ldf physical files implement the model database.
·         It is a probably a good idea to periodically back up the model database. The size isn’t all that large, and if you make changes, those changes will be preserved.
·         By default when you are installing SQL Server 2008 the Model database related data and log file are created in the following folder location Drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\.

4- Msdb Database
·         The msdb database is used by SQL Server to store information on operations performed by SQL Server.
·         SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc Primary database to manage the SQL Server Agent configurations.
·         Functanality - SQL Server Agent Jobs, Operators and Alerts,DTS Package storage in SQL Server 7.0 and 2000,SSIS Package storage in SQL Server 2005.
·         This database contains task scheduling, exception handling, alert management & system operator information needed for SQL Executive Service. For example: I can query the how many emails have been sent to administrator, scheduled backup for next time & also the history of previous scheduled backups.
·         The msdb database uses the msdbdata.mdf and msdblog.ldf files.

5- Resource Database
·         The Resource database is responsible for physically storing all of the SQL Server 2005 system objects.
·         The Resource database is a read-only, hidden system database that contains all the system objects that are included with SQL Server.
·         This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
·         Resource database uses the mssqlsystemresource.mdf and mssqlsystemresource.ldf files.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Friday, April 15, 2011

DECLARE Single, Multiple Variables In SqlServer.

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values with either a SET or SELECT statement.
In SQL Server 2008 when variables are declared they can be assigned values as well.


    {{ @local_variable [AS] data_type }
        | { @cursor_variable_name CURSOR }
        | { table_type_definition }
    } [ ,...n]
< table_type_definition > ::=
    TABLE ( { < column_definition > | < table_constraint > } [ ,... ]
< column_definition > ::=
    column_name scalar_data_type
    [ COLLATE collation_name ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed, increment ) ] ]
    [ < column_constraint > ]
< column_constraint > ::=
    { [ NULL | NOT NULL ]
    | CHECK ( logical_expression )
< table_constraint > ::=
    { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] )
    | CHECK ( search_condition )

1-      Use DECLARE with Single variables

This example uses a local variable named @find to retrieve author information for all authors with last names beginning with Ring.

USE pubs
DECLARE @find varchar(30)
SET @find = 'Ring%'

          au_lname LIKE @find
2-      Use DECLARE with two variables

This example retrieves employee names from employees of Binnet & Hardley (pub_id = 0877) who were hired on or after January 1, 1993.

USE pubs
DECLARE @pub_id char(4), @hire_date datetime
SET @pub_id = '0877'
SET @hire_date = '1/01/93'
-- Here is the SELECT statement syntax to assign values to two local  variables.
-- SELECT @pub_id = '0877', @hire_date = '1/01/93'

          pub_id = @pub_id and hire_date >= @hire_date

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Thursday, April 14, 2011

Query to Display Primary Key on different tables from database in Sql Server.

Use below Query to find out the Primary key column from the diffrent table.


Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Tuesday, April 12, 2011

Synchronization Techniques in SSAS.

Analysis Services 2005 (and later versions) support synchronization, a feature that greatly simplifies migrating analytical databases among servers. It also eases the separation of querying and processing duties among multiple instances of Analysis Services. In this tip, I'll introduce you to how synchronization is implemented and how to use this powerful feature for scaling your analytical applications.

The Synchronize Database Wizard copies both metadata and data from a database on source server to a database on target server. If the database doesn’t exist on the target server, the Wizard will copy the entire database else the wizard will only copy changes.

Though this method is kind of similar to Backup/Restore method, using Synchronize database method will let you do the operation in a single step without having to manually manage backup files.There are four main techniques for synchronizing an Analysis Services database from one server to another. In general the attach/detach, backup/restore, and synchronize methods are great for individual database synchronizations.

  1. Analysis Services Synch Method
    -What is great about this particular method is that it is easy to operate and does not require a lot of development to get it to work in your production environment
    -This process scans the differences between two databases on the two servers and transfers over only files that have been modified.
    -performance of this new synchronization is quite impressive and is due to the changes to the underlying file system as part of Analysis Services 2008.
  2. Backup/Restore Database
    -The backup/restore method is the most common data recovery method for an Analysis Services database and can be adapted as a synchronization technique
    -You can copy it to multiple target servers, and then execute the restore method on to your target servers.
    -The advantage of this approach is that if your restoration of the database fails, users can still query the original database.
  3. Attach/Detach Database
    -As part of SQL Server 2008 Analysis Services , you have the option to attach/detach your Analysis Services database and set it to read-only.
    -The advantage of this approach is that while you have to robocopy the full database, this involves simply attaching a database
  4. Robocopy Method
    -The idea is to use a fast copy utility, such as Robocopy, to copy the changed or added (delta) files within the Analysis Services data folder. A limiting factor is that you must copy all of the files within this data folder, which means that you will be copying all of the OLAP databases on the processing server
    -SSIS package can execute multiple threads of Robocopy to copy the entire Analysis Services data folder (by default, this is C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data) from your Analysis Services processing server to the idle instance data folders of the query servers.
When to use Synchronize Database Wizard

Use this wizard for deployment when you can’t afford to process your analysis services database on a production server. For example, let’s say that you have a farm of production servers and you want to deploy the changes to each server; Instead of processing the analysis services database on each server, it would be more efficient to process the database on a dedicated staging server and then synchronize each of the production servers with the staging server.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2