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.

• OLE DB error: OLE DB or ODBC error: Tuesday, April 5, 2011 

• Show a List/Table/Tablix/Matrix Wednesday, April 6, 2011

• How dimensions and dimension attributes works Thursday, April 7, 2011

• Named query in SSAS Friday, April 8, 2011

• Types of facts inside the fact table Monday, April 11, 2011

• Synchronization Techniques Tuesday, April 12, 2011

• Display Primary Key on different tables Thursday, April 14, 2011

• DECLARE Single, Multiple Variables Friday, April 15, 2011

• System Database in SQL Server 2005 Wednesday, April 20, 2011

• Fuzzy grouping component In SSIS. Thursday, April 21, 2011

• Login failed for "user"; Reason Monday, April 25, 2011

• Restoring an SSAS Database (CUBE). Tuesday, April 26, 2011

• Microsoft Web Matrix Wednesday, April 27, 2011

• SQL Azure Reporting Thursday, April 28, 2011 

               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:

Advantages-

·         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.

Disadvantages-

·         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

Report:

·         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

• 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

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

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

Group similar text together using Fuzzy grouping component In SSIS.

Create Txt file DATA.txt

EMPID,FNAME,LNAME

1,SUHAS,KUDEKAR

2,Suhas,KUDEKAR

3,SANTOSH,PATIL

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\

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

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.

Syntax

DECLARE

    {{ @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 ) ] ]

    [ ROWGUIDCOL ]

    [ < column_constraint > ]

< column_constraint > ::=

    { [ NULL | NOT NULL ]

    | [ PRIMARY KEY | UNIQUE ]

    | 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%'

SELECT
          au_lname,
          au_fname,
          phone

FROM
          authors

WHERE
          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

SET NOCOUNT ON

GO

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'

SET NOCOUNT OFF

SELECT
          fname,
          lname

FROM
          employee

WHERE
          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

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.

SELECT

      TC.TABLE_NAME,

      KCU.COLUMN_NAME

FROM

      INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

      ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME

WHERE

      TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2