How to create SQL Server Login programmatically?

April 28, 2009

I got this question from Daniweb C# forum, and of course when I need to do something against SQL Server object from .net I go to SMO (SQL Server Management Objects) which provides a great functionalities to do anything with SQL Server instead of going on SSMS and create some scripts then embedding them into stored procedure then call it…. off…

Anyway to avoid showing how much I’m talkative…

Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo
and Microsoft.SqlServer.SqlEnum

Server sqlServerInstance = new Server(
new Microsoft.SqlServer.Management.Common.ServerConnection(
new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=Master;Integrated Security=True")));// your connection string I place mine for illustration..
Login loginObj = new Login(sqlServerInstance, @"DomainName\UserName");
loginObj.DefaultDatabase = "Master";
loginObj.LoginType = LoginType.WindowsUser;
loginObj.Enable();
loginObj.Create("password"); //set the password
//there're many properties to do some tasks related to Login object...

If you used LoginType.WindowsUser, be sure to provide valid windows username
and if you aren’t on Domain use the machine name instead.

If you need to create SQL login use LoginType.SqlLogin…

You can explore Login class more on http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.create.aspx

Advertisements

TOP 10 DEMOS FOR MICROSOFT BI

April 25, 2009

I liked this post from THE BI BLOG and I like to share it with you

Here’s a collection of some cool Microsoft BI Demos that I use regularly to showcase aspects of the Microsoft BI stack and show interesting examples of what insight to information can actually accomplish.

Top 10 demos for Microsoft BI

10. Sales Dashboard in Silverlight – visualize sales performance, select regions, identify who’s a performer and who’s not in a very visual and intuitive way.

9. Hello Hockey fans – a fun example of how you could track your favorite hockey team using NHL data and Microsoft Silverlight.

8. Sustainability Dashboard – how can lowering your organizations carbon footprint equal more money to the bottom line? See how BI can help gain access to information that will help your company become more efficient and greener

More on: http://blogs.msdn.com/bi/archive/2009/04/23/top-10-demos-for-microsoft-bi.aspx


Building ASP.NET Reporting OLAP-Based Application Part-3

April 20, 2009

In the previous two posts building the Cube and building the Report, we discovered how BIDS helps in developing BI applications in robust, managed and organized way. I believe if you have few BI concepts you can do this walkthrough in 10 minutes.
Let’s come to the last layer which I see it’s the presentation layer. One question may come to your mind why I build ASP.NET application over Reporting Service? Why I don’t give the end user Reporting Service Server URL. Because of security? NO. Reporting Service Server can manage different types of security which doesn’t put headache on your development team but what if your end user need UI layer say in Silverlight? How can you embed in the Reporting Service. We have ReportViewer Windows\Web control which provides a very rich of functionality to View Reporting Service (Local and Server) Reports. In our walkthrough we use Server Report.
Let’s open our previous solution and add a new ASP.NET web application project: InternetSalesWebsite
Default.aspx page open in source view, switch to Design view. 

rs-p3-1

Read the rest of this entry »


Building ASP.NET Reporting OLAP-Based Application Part-2

April 19, 2009

In the previous post we learned how to build simple cube using BIDS, and if you’re follower I’ve forgot something important is deployment the cube on the Analysis Services.
1- We just need to add some attributes to the dimensions(DimPromotion, DimProduct) as each of which pure (contains just the key) for some reasons we need to show the user the name of product, or the promotion because it very hard to make human treat with numbers contrary to machines which prefer them. We shall add some attributes to the dimensions to make it meaningful.

2- Double click on DimPromotion; the dimension design window opened you find Data Source View, Hierarchies and Attributes; simply drag EnglishPromotionName from the Data Source View table to Attributes.
3- Repeat the previous step with the DimProduct to add EnglishProductName

rs-p2-7
Read the rest of this entry »


Building ASP.NET Reporting OLAP-Based Application Part-1

April 18, 2009

Here I’ll talk about how to integrate your asp.net application with Reporting Service through ReportViewer web control which provides rich functionality to deliver Reporting Service Report through asp.net applications.

In real life applications; Analysis Service and Reporting Service on server machine, Web Server another server machine and clients usually on another domain access the website to do something.

Let’s take a look on this diagram to visualize what happened in intra applications which one of its functionality shows reports this reports actually based on OLAP Data (SSAS used here)

Infrastructure Architecture

Here we build OLAP Cube as Report data source.

So, let’s begin to build our report based on Cube which built from AdventureWorksDW Read the rest of this entry »


SSW Rules to Better SQL Reporting Services

April 16, 2009

A away from being talking about SSRS 2005, but it presents really useful and beneficial information and tricks to SSRS developers and designers 

Rules to Better SQL Reporting Services

  1. Do you know the 5 user experiences of Reporting Services: Vanilla, Website, Email, Windows?
  2. Do you know when to use Reporting Services?
  3. Do you check that “RS Configuration Manager” is all green ticks?
  4. Do you check out the built-in samples?
  5. Do you know your 2 migration options to show your Access reports on the web?
  6. Layout – Does your report print and display on the web correctly?
  7. Layout – Do you include a useful footer at the bottom of your reports?
  8. Layout – Do you avoid using word ‘Report’ in your reports?

More on:
http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx


Migrating to SQL Server 2008

April 14, 2009

Microsoft SQL Server 2008

SQL Server 2008 delivers exceptional value in Database Management and Business Intelligence causing many customers to choose SQL Server over other database solutions. The information on this page will help you find the resources needed to migrate you data to SQL Server 2008.

To read more about What’s new on Microsoft SQL Server 2008, how to migrate from different RDBMSs to Microsoft SQL Server 2008 check this http://www.microsoft.com/Sqlserver/2008/en/us/migration.aspx