Sql Server Blog

Insert Rows in between a SQL Server Table with Identity Column

noreply@blogger.com (Suprotim Agarwal) — Sat, 07 Nov 2009 12:55:00 +0000

I have seen a lot of users asking this question – “I have accidentally deleted a row in a Table with Identity Column. How do I insert that row again?”

Let us assume that we have a table with ‘CustId’ as the Identity Column. The row for CustId 18 has got deleted by mistake. Now if you go ahead and insert the row for CustID=18 (specifying the value of the identity column explicitly) as shown below:

INSERT INTO YourTableName(CustId, FirstName, LastName)
VALUES (18, 'Paul', 'Adams')
GO

SQL Server throws an error as shown below:

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table ‘yourtablename’ when IDENTITY_INSERT is set to OFF.

To insert and specify the value in an Identity Column, use the following query:

SET IDENTITY_INSERT YourTableName ON

INSERT INTO YourTableName(CustId, FirstName, LastName)
VALUES (18, 'Paul', 'Adams')
GO

SET IDENTITY_INSERT YourTableName OFF

You will now be able to insert details for CustId=18 successfully!

Create a SSRS Report based on a Cube and Deploy it

noreply@blogger.com (Gouri Sohoni) — Thu, 05 Nov 2009 12:55:00 +0000

In our previous article, we took an overview and need of SSRS and various kinds of report delivery. In this article, we will discuss how to create a report and deploy it. We will create a report based on the cube we created in the article Creating a Cube and Dimension using SSAS

1. Add a new Report Server Project Wizard in the same solution and name it ReportDemo. As we have selected the wizard, it will automatically start with report creation.

2. Since we need to create this report on a cube, we need to change the type to Microsoft SQL Server Analysis Services and click on Edit button as shown below:

Enter the server name for analysis services and select the previously created DemoCube, click Next.

3. Click on Query Builder which will take us to the query designer. Drag Line Total and Order Quantity from measures on to design. Also drag and drop Product Category and Year – Quarter – Month – Data hierarchies. Select Time dimension in filter area and specify the Year as hierarchy. Select Operator as equal, select years 2003 and 2004 and click the checkbox for the parameter as follows

Click Ok. The created query will be shown in next screen. Click Next.

4. Select Matrix as the report type and click Next.

5. Do the selection for design the matrix as follows

Note the check box for Enable drilldown.

Click Next. Select Corporate as the style and click next.

6. In ‘Choose the deployment location’ screen, enter the Report Server name and the deployment folder. If you have the default instance then you can keep the URL as it is. If you have the named instance enter URL as http://<machine name>/ReportServer_<instance name. Enter the report name as Product Information and click Finish. You can click the check box for Preview Report if required.

7. Select Build and deploy the report project or right click on project name in solution explorer and select deploy. To view the deployed report enter the URL for Report Server as follows:

http://localhost/ReportServer (default instance)
http://<machine name>/ReportServer_<instance name> (named instance)

The URL for Report Manager is
http://localhost/Reports (default instance)
http://<machine name>/Reports_<instance name> (named instance)

Note: if the port number is other than 80 it will be given with the machine name followed by semicolon (:)

Overview of SQL Server Reporting Services (SSRS)

noreply@blogger.com (Gouri Sohoni) — Tue, 03 Nov 2009 15:56:00 +0000

In the previous articles, we have discussed the following:

Overview of SQL Server Integration Services (SSIS)

Creating your first SSIS package with the help of Import Export Wizard

Overview of SQL Server Analysis Services (SSAS)

Creating a Cube and Dimension using SSAS

In this article we will discuss an overview and need for SSRS and various kinds of report delivery. In the next article, we will discuss how to create a report and deploy it.

We have discussed how organizations maintain data in various forms, how the data can be fetched and placed at a uniform platform. We also discussed 2 different ways of storing data, for transactional requirement (OLTP – Online Transactional Processing) and for historical usage (DW - Data Warehouse).

We need to now present this data in a user friendly format so that business decisions can be taken based on it. The business decisions example can be - what may be the planned percent of business growth? or which business units to focus on for expansion? and so on. For making these decisions it is better to base the decisions on some reports, rather than making abstract decisions without any data support. There can two kinds of reports that are required by business. One of them is trend analysis, in which trends in the business transactions can be displayed. The trend can be in the form of - how different customers have given business and which customers are giving more business. The trend can provide us output in the form of list of customers which have given us more business. The management can concentrate on this list to increase business. The other form of report is a status or snapshot report. There can be snapshot of inventory of material with ABC analysis for items in the organization depending upon the fast, medium and slow moving items. It can be in the form of pricing of items, high priced, moderate priced and low priced. The snapshot can comprise of current status of organization regarding the employees working, getting retired towards year end.

In order to create these kinds of reports we require processed and organized data. It can come in the form of transactional data or OLAP (Online analytical processing) data. We have discussed how to fetch data on a common platform with the help of SQL Server Integration Services and how the data can be aggregated and made available for querying with the help of SQL Server Analysis Services.

Let us see how the reports can be created with the help of SQL Server Reporting Services (SSRS). This tool is one of the major tools for report creation provided by Microsoft. The other tools are Report Builder 1.0 and Report Builder 2.0. Check this article to find out some differences between the three.

With SSRS, the data related to reports is stored in 2 databases namely ReportServerDB and ReportServerTempDB. Item like reports, linked reports, shared data sources, report models subscriptions and schedules, report snapshots which are managed by report server are stored in Report Server database. Session and execution data are stored with temporary database.

Report Server architecture provides 2 main features namely Report Manager and Report Server Web Service. Report Manager provides web interface to the report server web service. Report Manager runs in the browser on client side. Nothing is stored on client side like files or settings. Any setting to a particular user is stored in database and retrieved when required. You can associate URL for Report Manager. Report Server Web service is the main feature which provided all reports and report models. The user authentication and authorization is handled by web service for any report processing.

There can be different delivery kinds of reports, pull delivery, push delivery and local processing. In pull delivery the URL for the report is given to view the report. In push delivery a subscription for the reports is created and the report gets automatically delivered to the concerned person or in a shared folder when the criterion for subscription matches. In local processing the report can be viewed on client with the help of report viewer with windows form or web form.

In this article, we took a quick overview of the SSRS and the various kinds of report delivery. In the next article, we will create a report based on the cube created in the last article Creating a Cube and Dimension using SSAS

Identify Valid Numeric Expressions in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Sun, 01 Nov 2009 17:32:00 +0000

Sometimes the solution to a weird ‘looking’ problem lies in simple SQL Server functions!

I was recently analyzing data of a SQL Server table with a varchar column that contained both numbers and alphabets. The client however now wanted to filter out the rows that contained only numbers in them. Here’s how the requirement was solved

DECLARE @TT table
(
ProductID int,
CodeIdentification varchar
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 'A2')
INSERT INTO @TT VALUES ( 203, '2');
INSERT INTO @TT VALUES ( 305, '2');
INSERT INTO @TT VALUES ( 403, '3');
INSERT INTO @TT VALUES ( 553, 'B3');
INSERT INTO @TT VALUES ( 634, '3');
INSERT INTO @TT VALUES ( 744, '3');
INSERT INTO @TT VALUES ( 838, '4');

SELECT * FROM @TT WHERE IsNumeric(CodeIdentification) = 1

The IsNumeric function determines if the expression passed to it is valid, by returning 1; else it returns 0

The output on running the above query is as shown below:

Creating a Cube and Dimension using SSAS

noreply@blogger.com (Gouri Sohoni) — Fri, 30 Oct 2009 23:06:00 +0000

In my previous article, we took an Overview of SSAS and its various components. In this article, we will create a cube by using Business Intelligence Development Studio (BIDS).

We will continue with the example we discussed with SQL Server Integration Services where we created a database with the help of Import Export wizard

1. In the existing solution named DemoBeginning, add an Analysis Services Project and name it DemoCube.

2. In the Solution Explorer, right click on Data Sources and click on New Data Source. This action launches the Data Source Wizard. Connect to the database created earlier named DemoDB. Select the impersonation information as ‘Use the Service Account’. Keep the default name and click Finish.

3. In the Solution Explorer, right click on Data Source View (DSV) and select New Data Source. Select 3 tables from the Production schema and SalesOrderDetail from Sales schema. We will fetch the last table in a short while. Keep the default name for DSV and click Finish. In this DSV you will see that no tables are related to each other.

Note we did not fetch any keys or relationships from the original database to the newly created database.

4. We will create the logical relationships in the DSV. Right click on each table from the Production schema and make ProductID, ProductSubCategoryID and ProductCategoryID as logical primary keys. To create relationships, drag ProductSubCategoryID from Product table and drop on ProductSubCategory. Verify the relationship is as follows:

Similarly drag ProductCategoryID from ProductSubCategory table and drop it on ProductCategory table. Drag ProductID from SalesOrderDetail and create the relationship by dropping it on ProductID of Product table. We have now created primary keys in dimension tables and subsequent foreign keys in fact tables.

5. Dimensions for cube can be created in 2 ways. One is by right clicking on Dimension in Solution Explorer and the other when we create a cube, the dimensions automatically get created. Right click on the cube in Solution Explorer and start the cube wizard. Select existing tables from the ‘Select Creation Method’ screen. Click Next.

6. Click on the Suggest button from the next screen and you will see that the table named SalesOrderDetails is picked as the table with measures. This suggestion is based on the relationship we created in the previous step. Click Next.

7. Select Order Qty, Unit Price and Line Total as the only measures and click Next

8. Click Next, enter the name DemoCube and click Finish. The result will be as shown below.

You can now see the relationship which we created.

9. Right click on the project and select properties. Click Deployment tab and enter your server name. For default instance you can keep it as localhost; for named instance it will be <machine name>\<instance name>. Select Build, Deploy DemoCube.

10. After successful deployment ,select the Browser tab for cube. If you drag and drop Unit Price, Line Total to the data area and Product category Id to the row area, you will see the result as follows.

This result shows category id which is not very user friendly, it will be better if we see the name instead.

11. Go to solution explorer and double click on Product dimension.

Go to properties for Product Id and change NameColumn property as shown below

Similarly change the NameColumn for Product Subcategory ID and Product Category Id.

Create a hierarchy named Product Category as follows:

We need to process the dimension with the changed properties. Now the previous result is as shown below

12. We can format the Line Total by selecting Cube structure tab, select Line Total, select properties and specify FormatString as currency with the following result:

13. We get a warning for the dimension as “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies”. To implement this best practice, select each attributes and change its AttributeHierarchyVisible property to False.

14. Let us add one more dimension for Time. Select DSV, right click on the empty area and select Add/Remove tables, add SalesOrderHeader and click. Specify SalesOrderId from SalesOrderDetails as logical primary key and establish relationship to the newly added table.

15. Right click on Dimensions in solution explorer and add a new dimension. Select ‘Generate a Time Table’ on server, and enter details as shown below:

Click Next, Next again and finally Finish. Verify that the wizard automatically creates one hierarchy with year, quarter, month and Data as attributes

16. We need to add this dimension to the cube, so select the cube structure tab, right click on the empty area for dimensions and select add a cube dimension and select Time. Process and deploy the changes.

17. Go to Browser tab, click reconnect and you will see the newly created dimension added.

18. We can filter the data in following manner

In next article, we will discuss an overview of SQL Server Reporting Services and create a report based on this cube.

Overview of SQL Server Analysis Services (SSAS)

noreply@blogger.com (Gouri Sohoni) — Wed, 28 Oct 2009 15:05:00 +0000

In a previous article, we discussed SQL Server Integration Services, its components and how to create package by using Import Export Wizard. In this article we will take an overview of SSAS and its various components.

With SSIS, we discussed how data can be fetched and cleansed before it is put into a uniform platform. With SSAS, as the name suggests, we can use the data for analysis.

With any organization we have two kinds of data, one which stores transactional details and the other which has historical (Data Warehouse - DW) details. The data in DW will be populated periodically from the data from transactional (OLTP – OnLine Transactional Processing). The data in OLTP is optimized for faster insertion and updations, but the data in DW is meant for faster querying. With DW we can store duplicate data, computed columns, non normalized data as the storage is not important. The important aspect is that when a query is fired, it should give faster results.

When we want to take any decisions based on data, the data needs to be in an analyzed format. For e.g. we want to find out the code churn for developers in an organization. We need to have all the details for each developer; such as how many lines of code were written, modified or deleted. When we need the report, if we start calculating the numbers for each developer for a day, a week, a month or a quarter, it will be time consuming job. If I already have this aggregated data in cache, my job will be very easy. This aggregated data is put in cubes which are termed as multidimensional databases. As the normal cube has multiple dimensions, the data can be viewed with various axes. In this case one axis can be for each developer; another can be for a particular period and third can be for a particular department or for a particular project. Thus we can create different slices for looking at the same data. These slices are termed as dimensions and the actual data is the fact. The sales related data (like the sales person information, data for sales, quantity, and price) will be termed as fact and the axes (like sales person wise information, location wise details, quarterly details) will be termed as dimensions.

The thumb rule says that facts are numeric measures while dimension has primary keys. Thus primary keys in dimension tables are related to foreign keys in fact tables so as to get the details as and when required. There are 2 dimensional models namely star schema and snowflake schema. In star schema, single fact table is related to multiple dimension tables based on primary key in dimension tables (which resembles start like shape) as follows.

In snowflake schema, one of the dimension acts as a fact for another dimension thus giving snowflake like appearance as below

With SSAS, we first need to specify the source from which data is to be fetched. Later the data can be analyzed. After the source, we can create a Data Source View (DSV) to the source. If the source database has 50 tables, we need not want to work with all of them in a given situation. So we create a window to the data with required tables in it. We can also group tables from different sources and put it in the same DSV.

We can even create logical relationships amongst the table in the DSV where the source database remains intact. We are just creating a logical view to the tables.

In this article, we took an overview of SSAS and its various components. In the next article, we will create a cube by using Business Intelligence Development Studio (BIDS).

Creating your first SSIS package with the help of Import Export Wizard

noreply@blogger.com (Gouri Sohoni) — Mon, 26 Oct 2009 23:31:00 +0000

In my previous article Overview of SQL Server Integration Services (SSIS), we had a quick overview of SSIS and the different features it provides. In this post, I will show you how to create your first SSIS package with the help of Import Export Wizard

1. Start BIDS and create a solution named ‘DemoBeginning’

2. Add a SQL Server Integration Services Project with name ‘DemoImportExport’ to the solution.

3. Delete the default package given in the solution explorer > right click on packages and select SSIS Import Export Wizard

4. We will fetch tables from the sample database AdventureWorks and put them in a newly created database named ‘DemoDB’

5. After the initial screen of the wizard, select AdventureWorks database from the database server as shown below:

6. Click Next > Click on New tab for destination database and give name DemoDB. Keep all default settings and click OK.

7. From specify table copy or query screen selec,t copy data from one or more tables or views and click Next

8. Select tables Production.Product, Production.SubCategory, Production.ProductCategory, Sales.SalesOrderDetail and Sales.SalesOrderHeader. We will keep all the default settings, hence will not do any Edit Mappings > click Next

9. On complete the Wizard screen, you will see the following screen:

Click Finish and click on Close after the required actions.

10. In solution explorer you will a see a package created with 2 tasks in control flow and 5 data sources and 5 destinations in data flow task. We can see only 2 connection managers, one used for source and one for destination.

11. We have just created the package but the data is not present in database. You can verify that the database is created without any tables in it. The ‘Prepare SQL Task’ from control flow creates the tables.

12. Execute the package by right clicking on the package in solution explorer and execute package.

You can verify the creation of tables and the data after successful execution. The components will first be shown in yellow color which will subsequently turn to green on execution or red if any error encountered. You can verify that the newly created tables do not have any primary keys and also any relationship. If we want to persist this we need to select Edit Mappings and do the necessary changes.

In next article we will continue with the same database and create a cube based on it.

Overview of SQL Server Integration Services (SSIS)

noreply@blogger.com (Gouri Sohoni) — Sat, 24 Oct 2009 23:24:00 +0000

In one of the previous article Exploring Business Intelligence (BI) with SQL Server , I discussed an overview of Business Intelligence and the three main tools provided by Microsoft for the same. The three tools are SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS). In this article we will discuss the various components in SSIS, its architecture and how to create a small package using SSIS.

BI is a set of tools and technologies provided to make better business decisions. These tools can be used for querying and reporting, data mining, data gathering or OLAP (Online analytical processing). Every organization deals with a lot of data. Every department in any organization has its own way of data storage. Some prefer to store in excel, some use access while some departments may store the data in relational database like SQL Server. If we are creating a report for Order Processing and we get the customers’ orders in printed form we require data entry for this. Suppose this data is entered with excel sheet. We have an Inventory processing system purchased from a third party where the data is stored in Oracle and we store all our customers’ information in SQL Server. We want to create a report which uses data from all these disparate sources. We can put all these data in a common platform with the help of SSIS.

As the name suggests SSIS is used for fetching data (from disparate sources), doing some changes on it (like changing the type from excel to SQL, or creating calculated field) and put the data on a uniform format which can be subsequently used for further analyzing or reporting.

SSIS is a service so you can have only one service on a single box. We have 2 runtime engines in the form of control flow and data flow. It is commonly called as ETL (Extract, Transform and Load). With Business Intelligence Development Studio (BIDS) we have graphical tools as well as wizards for creating and debugging the packages. Packages form the intricate component in SSIS template. Each package is a basic unit of deployment. Every package has control flow and data flow element. Each package has single control flow which is like the workflow for the package. Data flow is a component of control flow but provided with a separate tab as most of the packages will use it for ETL.

With ETL, apart from extracting and loading data from various sources and destinations we can do a lot of transformations to the data. We can merge data from 2 sources into one, we can convert column data type to suit the destination data type, the data can be sorted before putting in, and we can split the data by giving a condition so as to continue with only conditional data and many more tasks.

When we start the template of SQL Server Integration services, with the help of BIDS, we get following screen

The template automatically creates a package with the name as “Package.dtsx”. The extension dtsx stands for data transformation services with XML format. Everything in the package will be stored in XML format. The following screen shows the separate tab for Data Flow.

The following screen shows some of the components from control flow tab from toolbox

We can just drag and drop the required component on the design area.

With the data flow we get three distinct components -- Data sources, Data Transformations and Data Destinations as shown here

With Data flow, we normally fetch data from various sources, do any data cleansing on it if required and finally load into destination.

We also have debugging facility with control flow, where we can add break points when required. With Data flow we have data viewer as a debugger which will show the data in grid, histogram, scatter plot or column chart.

Apart from control flow and data flow, other components of package include connection managers, variables, Event handlers and log providers. When we are working with data source or data destinations we require specifying the connections from which we can fetch data or load data into. These can be created by dragging and dropping data source or destination component on the design area and configuring it or by right clicking in the empty area for connection managers as shown below

We can create variables with different scope with each package. SSIS has 2 types of variables, system variables and user defined variable. System variables store information about the running packages. The scope of user defined variables can be for complete package or for a control on the package. The variable name is case sensitive. These variables can be used for passing properties at run time, specify the value for loop iterations etc.

At run time, control flow components like loops, sequence containers and also packages raise events. Event handlers are used to enhance functionality depending upon the event raised like OnError, OnProgress, OnTaskFailed etc. The events for Package are shown below:

Remember if any event has no handlers, event is raised to the next container up in hierarchy for package.

Logging can be provided for tasks, containers or packages. When you add logging to the package, you need to specify the provider and the location. The following figure shows types of log.

You can configure log by giving the name and description. You also need to give the connection manager log needs.

In this article, we had a quick overview of SSIS and the different features it provides. In the next article, I will show you how to create your first SSIS package with the help of Import Export Wizard

SQL Data Services (SDS) Series

noreply@blogger.com (Suprotim Agarwal) — Thu, 22 Oct 2009 23:31:00 +0000

SDS is a part of the Azure platform. If you have missed out on the SQL Data Services (SDS) series written by Gouri Sohoni, here’s the link list for you:

SQL Data Services (SDS)

SQL Data Services (SDS) Part II

SQL Data Services (SDS) Part III

Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

SQL Data Services (SDS) Part IV

SQL Data Services (SDS) Part V

SQL Data Services (SDS) Part VI

Some Similarities and Dissimilarities between 3 different Report Creation Tools from Microsoft

noreply@blogger.com (Gouri Sohoni) — Tue, 20 Oct 2009 14:48:00 +0000

The three main Report Creation tools from Microsoft are Business Intelligence Development Studio (BIDS), Report Builder 1.0 and Report Builder 2.0

- The main similarity in all the three of them is that all these tools create RDL (Report Definition Language)

- BIDS provides integrated environment for working with reporting services, integration services and analysis services, which is available with Visual Studio interface. Report Builder tools facilitate working for users who are not familiar with Business Intelligence Development Studio (BIDS).

- BIDS provides more flexibility in terms of deployment configurations as well as provides different rendering extensions.

- The query designer is available for BIDS and Report Builder 2.0 but is not available with Report Builder 1.0.

- We can only create reports on report models with Report Builder 1.0. With BIDS as well as Report Builder 2.0, the report can be based on data source other than report model like relational data sources.

- Multidimensional data source are directly available with query designer for BIDS. For Report Builder, we can create report models on multidimensional data and then subsequently reports can be created based on them.

- Use of query files (.sql) is available with BIDS and Report Builder 2.0

- Report designing wizard is available only with BIDS. We also have a wizard for Report Builder 2.0 for report creation.

- All the three tools provide rendering extensions for Word, Excel, PDF, CSV (Comma limited).

- Viewing of .RDL source is only supported with BIDS.

SQL Data Services (SDS) Part VI

noreply@blogger.com (Gouri Sohoni) — Sun, 18 Oct 2009 12:09:00 +0000

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query. We have also seen how to write T-SQL statements with SQL Azure.

In this post, we will discuss how XML data type works with SQL Azure using some sample queries:

How let clause works with FLOWR expression in SQL Azure

--Let clause with FLOWR expression for XML is supported in SQL Azure

declare @x xml = '';
select @x.query('
for $i in (1,2,3,4)
return $i')
go
-- returns 1 2 3 4

declare @x xml = '';
select @x.query('
for $i in ("A","B","C")
order by $i descending
return $i')
go
-- returns  C B A

declare @x xml = '';
select @x.query('
let $x := 1
return $x')
go
-- returns 1

declare @x xml = '';
select @x.query('
let $x := ( <one>2</one> )
return $x')
go
-- error:
-- XQuery [query()]: let  is not supported with constructed XML

-- When we use let inside a loop, it is evaluated each time for the loop

declare @x xml = '';
select @x.query('
for $i in (1,2)
let $j := "try"
return ($i, $j)')

-- returns 1 try 2 try
-- $j is evaluated 2 times

How XQuery works with SQL Azure

CREATE TABLE #Depts
(DeptID integer IDENTITY PRIMARY KEY,
 DeptName nvarchar(40),
 Manager nvarchar(40),
 Names xml)

INSERT INTO #Depts
VALUES
('SQL zure','Sane','<Names>
<Name FirstName="Geeta" LastName="Sohoni"/>
<Name FirstName="Mani" LastName="Raje"/>
<Name FirstName="Raja" LastName="Tembhe"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server','Dani','<Names>
<Name FirstName="Suruchi" LastName="Risbud"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server 2005','Kulkarni',NULL)

SELECT * FROM #Depts

The result is as follows:

The following query gives similar results:

SELECT DeptID, DeptName,Manager,Names.query('
/Names/Name')
FROM #Depts

The result of following query:

SELECT DeptID, DeptName,Manager,Names.value('
(/Names/Name/@FirstName)[2]','char(10)') SecondPerson
FROM #Depts

is as follows:

as the last 2 records have a single person

To fetch the Manager Name

SELECT DeptName,Manager FROM #Depts
WHERE Names.exist('/Names/Name/@FirstName[1]') = 1

The following 2 queries gives exclusive results -- one returns data where there are no people under manager and the other where at least one person has a manager

SELECT DeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name/@FirstName[1]') = 0

--using exist

SELECTDeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name') = 1

The following query will insert one of the relational column in XML as though it is an XML tag

SELECT DeptName, Names.query('<Names>
    <Mgr>{sql:column("Manager")}</Mgr>
    {
     for $i in /Names/Name
     return $i
    }
    </Names>')
FROM #Depts

--use modify method and insert a column
UPDATE #Depts
SET Names.modify('insert element Peon {"Raju"}
  as first
  into (/Names)[1]')
WHERE DeptID = 1

--delete the newly added tag
UPDATE #Depts
SET Names.modify('delete (/Names/Peon)[1]')
WHERE DeptID = 1

SQL Data Services (SDS) Part V

noreply@blogger.com (Gouri Sohoni) — Fri, 16 Oct 2009 14:27:00 +0000

We discussed some T-SQL statements in my previous post SQL Data Services (SDS) Part IV. In this post, I will show some more T-SQL statements with SQL Azure and discuss the compulsion of using clustered index, and how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause

Note that SQL Azure does not support heap tables. You need to create clustered index. If a table is created without a clustered index, you must create one before inserting data. If you have no clustered index for a table and you try entering data in that table, you get following error in SQL Azure

Following is the example with Try Catch and SET XACT_ABORT_OFF or ON

IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (id int primary key identity,a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SELECT * FROM t2

Even if we use Transaction, we see that the statement which gives error is not executed. The remaining 2 inserts for which there was no error, are successful

as we see with the select statement

When we use the following statement, none of the records are inserted as required:

SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
SELECT * FROM t1
SELECT *
    FROM t2;
GO

And SELECT statement for table t2 shows same result as what was before the insert statements

In this post, we discussed the compulsion of using clustered index, how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause.

In next article we will discuss how to use the XML data type with SQL Azure

SQL Data Services (SDS) Part IV

noreply@blogger.com (Gouri Sohoni) — Wed, 14 Oct 2009 11:55:00 +0000

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query and also programmatically. When working with SQL Azure T-SQL provided is a subset of T-SQL for SQL Server.

Using T-SQL with SQL Azure

1. When referring to an object in SQL Azure following are the conventions. Note that server name is not allowed in the reference

schema name.object name

i. Create a new query in SQL Server Management Studio (SSMS)
Enter query

CREATE SCHEMA MySchema

ii. Create table as follows

CREATE TABLE MySchema.MyTable
(Id int identity primary key,
UserName nvarchar(15))

iii. Insert rows as follows

INSERT INTO MySchema.MyTable
VALUES (Name1),('Name2')

Note you need to specify the schema name with the object. Currently using database name along with schema and object name is not supported

2. All normal data types are supported in SQL Azure. SQL Azure does not support User Defined Data type. It supports XML data type.

3. SQL Azure database does not support any of the SQL system table

a. There is no provision and requirement of backup and restore

b. There is no log shipping or replication requirement

4. A lot of T-SQL statements like ALTER SCHEMA, ALTER ROLE, DROP LOGIN, DROP USER, CAST, CONVERT, and SET @variable are supported. We will discuss some of them

a.    --create a stored proc
CREATE PROC DispNames
AS
SELECT * FROM Names

-- execute the stored proc
EXEC DispNames

--alter the existing stored proc
ALTER PROC DispNames
AS
SELECT UserId, [User Name] FROM Names

--execute changed stored proc
EXEC DispNames
--delete the proc
DROP PROC DispNames

Notice the first variable data is truncated due to wrong length

c. Merge statement works with SQL Azure in the same manner as SQL Server as follows

CREATE TABLE t1
(Id int NOT NULL primary key, FullName varchar(100))

CREATE TABLE t2
(Id int NOT NULL primary key, FullName varchar(100))

INSERT INTO t1 VALUES
(1,'Smita Sane'),
(5,'Sarita Bhave'),
(6,'John')

INSERT INTO t2 VALUES
(1,'Smita Sohoni'),
(5,'Sarita sonu Bhave'),
(7,'Danny')

SELECT * FROM T1

SELECT * FROM T2

The result for this is as follows

MERGE t1
USING
(SELECT * from t2) target
ON t1.Id=target.Id
WHEN MATCHED
THEN UPDATE SET t1.FullName=target.FullName
WHEN  NOT MATCHED by target
THEN INSERT VALUES (target.Id,target.FullName);

SELECT * FROM t1
SELECT * FROM t2

After merge the result is as follows

So we see that in table T1 records are updated as well as inserted depending upon the condition

DROP TABLE T1
DROP TABLE T2

In next article we will discuss some more T-SQL statements with SQL Azure

Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

noreply@blogger.com (Gouri Sohoni) — Mon, 12 Oct 2009 11:55:00 +0000

In order to connect to SQL Data Services, you should have an invitation for working with SQL Azure CTP (discussed in previous article working with SQL Data Services –SDS)

1. Start SSMS 2008. When it asks for a connection to the services, click on Cancel. If by mistake you try connecting using this window, you get following error

2. Click on New query and enter following details. We can only connect using SQL Server authentication as that is the only mode supported in SQL Azure.

3. Click on Options and enter the database name as follows

If you select <default> option or <browse Server> option from Select Database we get errors

You will get a warning as shown below:

Click OK and you are connected to SQL Azure.

SQL Data Services (SDS) Part III

noreply@blogger.com (Gouri Sohoni) — Sat, 10 Oct 2009 11:55:00 +0000

In my previous article SQL Data Services (SDS) Part II, we discussed how to create database and tables with the help of a query in SQL Server Management Studio (SSMS).

While writing the application we have the choice of using ADO.NET provider or SQL Server 2008 ODBC driver. In this example, we are using ADO.NET in the code snippet.

Following code is for a console application which creates a table named T1 with 3 columns in it.

// Provide the following information to connect to server
private static string userName = "<administrator name>";
private static string password = "<password>";
private static string dataSource = "<data source name";
// data source will be server name we gave for SSMS query in last article
private static string sampleDatabaseName = "<name of database we created>";

static void Main(string[] args)on we hav
{

    // Create a connection string for the existing database
    SqlConnectionStringBuilder connStringBuilder;
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dataSource;
    connStringBuilder.InitialCatalog = sampleDatabaseName;
    connStringBuilder.Encrypt = true;
    connStringBuilder.TrustServerCertificate = true;
    connStringBuilder.UserID = userName;
    connStringBuilder.Password = password;

    // Connect to the existing database, create table and insert records
    using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
    {
        using (SqlCommand command = conn.CreateCommand())
        {
            conn.Open();

            // Create a table
            command.CommandText = "CREATE TABLE T1([id] int primary key," +
                "FirstName varchar(20), LastName varchar(20))";
            command.ExecuteNonQuery();

            // Insert sample records
            command.CommandText = "INSERT INTO T1 ([id],FirstName, LastName)" +
            "values (1, 'Name 1','Last1'), (2, 'Name 2','Last2')," +
            " (3, 'Name 3','Last3')";
            int rowsAdded = command.ExecuteNonQuery();

            // Query table and view data in while loop
            command.CommandText = "SELECT * FROM T1";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Id: {0}, First Name: {1}, Last Name: {2}",
                                    reader["Id"].ToString(),
                                    reader["FirstName"].ToString(),
                                    reader["LastName"].ToString());
                }
            // Update a record
            command.CommandText =
                "UPDATE T1 SET [FirstName]='change name 2' WHERE [Id]=2";
            command.ExecuteNonQuery();

            // Delete a record
            command.CommandText = "DELETE FROM T1 WHERE [Id]=1";
            command.ExecuteNonQuery();

            }
        }
    }
    Console.WriteLine("Press enter to continue");
    Console.ReadLine();
 }

After you run this application, you can verify the creation of table, insertion, modification and deletion of records by connecting to SQL Azure with the help of SQL Server Management Studio as discussed in last article.

SQL Data Services (SDS) Part II

noreply@blogger.com (Gouri Sohoni) — Thu, 08 Oct 2009 11:55:00 +0000

In the last article, we discussed the overview of SDS and how to start using the CTP by asking for an invitation.

Once you receive the invitation, you will have to go to URL https://sql.azure.com and login with your Windows Live Id where you have received the invitation. You will be shown a single project in My Projects tab with “SDS – only CTP project” and provided with ‘Manage’ as action.When you click on ‘Manage’ you will be shown Server name, Server administrator and the location for the server. You will need this information for further usage. You will observe that master database already exists. You can create a database of your choice by selecting Create Database button and enter name of the database.

In this article we will create our first table in the newly created database. Currently you cannot connect to SQL Azure by using SQL Server Management Studio (SSMS). This feature will be made available later. We can still make use of ‘New’ query, enter the server name and specify SQL Server authentication we created. SQL Azure only allows SQL Server authentication.

Make sure that you have selected the proper window for connection. Do not specify credentials in connect window for SSMS, as you will get an error similar to the one shown below

Make sure you click on Options tab and specify the database you need to connect to as shown. Do not select browse on server but enter name of the database. Keep the remaining properties as default.

You will get a warning as follows

Click on OK and continue.

Enter first query as SELECT @@ version

Let us create a table in the database with the name as Names and 3 columns as UserId , User Name and password. Make sure you are connected to the newly create database.

Add some records to the table by writing T-SQL as

INSERT INTO Names VALUES (1,’Name’,’Password’)

And finally enter the query

SELECT * FROM Names

to view the added rows.

In this post, we created a database, created a new table in the database, added rows to the table and even retrieved the newly added rows. In the next post, we will view and edit the table programmatically.

SQL Data Services (SDS)

noreply@blogger.com (Gouri Sohoni) — Tue, 06 Oct 2009 11:55:00 +0000

SDS is a part of Azure platform. Azure platform offers the following services: Windows Azure (Operating System in cloud), Microsoft .NET Services (set of WCF based services), SQL Azure (relational database in cloud)

SQL Azure

This service provides SQL server capabilities in cloud. We can create a database which is currently in the CTP (Community Technology Preview) form. We can have all the benefits of working with SQL Server plus no hazards of doing the administration. Thus administration tasks like replication, availability will be automatically made available to us. There will be 3 copies of data maintained out of which 2 will be synchronized and the third one may lag a bit. This also leads to limiting the size of the database to 10 GB. The size limitation is for 2 reasons, one for availability (in order to replicate the data in cloud, it needs to be within limit) and the second being shared database box for multiple users (as the same box will be used for storing the data from many users. If the size of data is huge for a single user, then the box will not be scalable to other users hence limit in size)

When we are maintaining data in the cloud again, we have 2 options of creating applications. One in which data is near the code, which means we have application running on the same box where we have data. Another will be where data is far from the code, in which we will be always manipulating data from the cloud (in case of web based application client).

How is the service provided?

The service is made available with the help of TDS (Tabular Data Stream) protocol. There are accounts available for billing purpose which will own one or more servers as per requirement. Each server will have one or more databases. These servers will use SQL Server authentication model. The databases will have one or more SQL users with respective permissions. Thus maintaining logical administration in the form of the creating views, creating triggers, tuning queries, tuning indexes etc. will be the job requirement of DBA rather than physical management in the form of how many file groups will be required, taking backup, recovery etc.

As of now, functionality like service broker, CLR (Common Language Runtime) functionality is not available with SDS, but will be subsequently made available.

How can SDS be used?

There can be various scenarios in which this service can be used.

- In small organizations, IT groups for maintaining relational database may not be available.
- In big organizations, if there is an inter department requirement for maintaining another copy of relational database, proving IT support becomes very difficult as IT staff is already overburdened.
- For creating web applications in which maintaining SQL Server for the web application may be very costly.

How can I start using SQL Azure?

You will have to register for using the current CTP over here http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx

You will receive the invitation for using SQL Azure which can be used with your Windows Live ID.

SQL Server 2008 Compliance Features – Some Resources

noreply@blogger.com (Suprotim Agarwal) — Sun, 04 Oct 2009 11:55:00 +0000

Compliances are standards, regulatory requirements or organizational policies which help organizations to operate securely and efficiently. SQL Server 2008 has got a number of compliance-related features that makes compliance relatively easier. Here are some documents and videos related to the same:

Compliance Guide for SQL Server 2008

SQL Server Auditing and Compliance

Compliance for Decision Makers Video

Compliance for IT Professionals Video

You can get more details about it in the SQL Server 2008 Compliance page

Specify a Condition while Editing TOP Rows using SQL Server 2008 Management Studio (SSMS)

noreply@blogger.com (Suprotim Agarwal) — Fri, 02 Oct 2009 11:55:00 +0000

I had recently posted about Using SSMS to change the Edit TOP Rows option . A user Greg commented asking me if it was possible to specify a condition while Editing the TOP Rows.

Here’s my observations. I am using the Purchasing.VendorContact table from the AdventureWorks database as a sample. Right click on the VendorContact table > Edit Top 5 rows

I get the following results:

Now let us say that using SSMS, you want to Edit only those rows having ContactTypeID=2. SQL Server 2008 gives you no option out of the box to do so – at least I couldn’t find one.

Here’s how I solved Greg’s requirement.

Update: Psy has shared the right way to do this. Follow these steps:

* Click edit top x rows. Click the "view sql pane" button (the letters SQL in a white box) from the query designer toolbar that appears in edit mode.

* Change the sql as needed.

* Click the red exclaimation mark to re-query the dataset.

* Edit as needed.

* You can also click the 'view sql pane' a 2nd time to hide it and still keep the changed query. You can also hit the execute icon as many times as needed to refresh the data.

The other way to handle this requirement is to Create a View! However this solution should be avoided:

Right click Views > New View. Type the following query:

Save this view as ‘EditVendorContact’. Now Right Click this newly created view > Edit Top 5 Rows

And here’s what you get

Using SSMS to change the Edit TOP Rows option

noreply@blogger.com (Suprotim Agarwal) — Wed, 30 Sep 2009 11:55:00 +0000

SQL Server 2008 Management Studio has an option to Edit the Top 200 Rows. Just Right Click on the Table and you get the following options:

If your table contains many columns, opening 200 rows to edit them would be slow to execute. However not many know that this number can changed using a simple setting.

So let us say we want to edit only the TOP 5 Rows. A quick way to do this would be to go to Tools > Options > SQL Server Object Explorer and change the ‘Value of Edit Top <n> Rows Command to 5

Now when you Right Click the Table, you see the option to Edit only TOP 5 rows

A very simple tip, but known to many!

Enable and Disable All the Triggers in a SQL Server Database

noreply@blogger.com (Suprotim Agarwal) — Mon, 28 Sep 2009 11:55:00 +0000

A database developer recently asked on the MSDN forums for a quick way to enable and disable all the triggers in a SQL Server 2005/2008 database. In my opinion, the quickest option is to use the undocumented stored procedure sp_MSforeachtable

To Disable All the Triggers

sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

To Enable All the Triggers

sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"

Similarly to Enable and Disable all the Constraints, use this query:

sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

List All Tables and Views with the XML Data Type in SQL Server 2005/2008

noreply@blogger.com (Suprotim Agarwal) — Sat, 26 Sep 2009 11:55:00 +0000

Here’s a simple way of listing all tables and Views with the XML datatype using the INFORMATION_SCHEMA views.

SELECT DISTINCT col.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE  
FROM INFORMATION_SCHEMA.COLUMNS as col
WHERE col.DATA_TYPE = 'xml'

As given in the Books Online “Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.”

OUTPUT

Working With Report Model in SSRS – Part III

noreply@blogger.com (Gouri Sohoni) — Thu, 24 Sep 2009 11:55:00 +0000

In last 2 articles Working with Report Model Part I and Working with Report Model Part II, we discussed overview of report model and how to create report model with the help of designer. In this article, we will create Report with Report Builder 2.0 based on the Report Model created in the previous article (Working with Report Model Part II)

Report Builder 2.0 can be downloaded from here

1. For working with Report Builder 2.0 go to Start > All Programs > Microsoft SQL Server 2008 > Report Builder 2.0

2. Create a New Report > Select table or matrix report > the connection to data source screen will be shown. Click on Browse (ensure you have given proper URL for report server) > in select data source it displays folders for models > Select “Report Model Demo” > click on Next.

3. A Design query screen will be provided as follows:

4. Under Entities, if you double click on ‘Customer’, all the entities will be automatically added to drag and column fields area. But we will not be working with only Customer.

5. Select Product from entities; double click “English Product Name” from fields. You will see that once we select Product, only related entities to Product are displayed in entities. Select Product SubCategory and double click on “English Product Subcategory Name” so as to get selected. Select Product Category and double click on “English Product Category Name”.

6. Select Internet Sales from entities. Double click on “Total Unit Price” and “Total Unit Quantity”.

7. Select Order Date from entities. Double click on “Calendar Quarter Desc”, “Calendar Year Desc” and “Month Name” and click on Next.

8. Drag and drop the fields so that the screen looks as follows:

and click on Next.

9. Keep the default settings for Choose the layout screen and click Next.

10. Select the required style and click Finish.

11. After doing certain changes like changing names of some columns, providing format for Unit Price the report looks as follows:

And there you have your first report created using a custom Report Model in Report Builder 2.0

Working with Report Model in SSRS - Part II

noreply@blogger.com (Gouri Sohoni) — Tue, 22 Sep 2009 11:55:00 +0000

Creating Report Model using BIDS (Business Intelligence Development Studio)

In Part 1 of this series, Working With Report Model in SSRS – Overview - Part 1, we discussed the Report Model, the items in Report Model and its uses. Let us now see how a Report Model can be created with the Report Model Designer. Report Model Project is provided as a template with BIDS. With this tool you can do certain customizations to the Report Model items as required.

1. Select Report Model Project template and give it a name - “Report Model Demo”

2. Create a Data Source named “Adventure works DW 2008” pointing to AdventureWorksDW2008 database. It can be downloaded from following link http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407

3. Create a Data Source View (DSV) with following tables

namely “Adventure Works DW2008”

4. Before creating model let us do the following changes to DSV. Change the friendly names of the tables so that they look as follows:

5. Add 3 calculated fields as follows :

- Calendar Quarter Description: 'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY ' +
CONVERT(CHAR (4), CalendarYear)
- Calendar year description: 'CY '+ CONVERT(CHAR (4), CalendarYear)
- MonthName: EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)

6. In the solution explorer, right click on Report Model and select Add New Report Model which will start the wizard for model creation. It is based on the DSV created in the last step.

7. Keep default settings for Report Model Creation Rules, Collect Model Statistics, name for the model and click on Run and finally Finish.

8. Remove attributes such as Spanish Education, French Education, Spanish Occupation, French Occupation from Customer entity. You will observe that Internet Sales and Geography are added to this entity as Roles. If you select properties for any one you will see the foreign key relationship between the two specified in Bindings. Similarly you can remove descriptions from Product.

9. You can also remove attributes from Total Children like avg, min and max. You can change the alignment of numeric fields to Right.

10. Right click on “Report Model Demo” in the solution explorer and select properties. Check if the TargetServerURL is correct.

In case of a named instance of SQL Server, it should be http://<machine name>/ReportServer_<instance name>.

If the port number is other than 80 it should also be included as follows http://<machine name>:8080/ReportServer_<instance name>. Machine name can be replaced by localhost.

11. Deploy this Report Model

12. Start Report Manager by giving the URL. In case of named instance of SQL Server it should be http://<machine name>/Reports_< instance name> and verify that the newly created model is available in Models Folder. After installing Report Builder we can specify the report builder setting within which report server URL can be provided.

In next article we will discuss how to create a report using this report model with the help of Report Builder 2.0

Working with Report Models in SSRS - Overview – Part I

noreply@blogger.com (Gouri Sohoni) — Sun, 20 Sep 2009 11:55:00 +0000

When we are creating reports with the help of SSRS (SQL Server Reporting Services) using BIDS (Business Intelligence Development Studio), we are dealing with predefined report formats. Sometimes it is required to create certain reports on the fly. We do not have a set format for these reports. These report formats are required to be created as and when required by the client. For this reason, a very important feature has been provided called ‘Report Builder’ in SSRS. With the help of Report Builder, ad-hoc reports can be created. These reports are based on Report Models.

Business users who want to create reports on demand may not be familiar with the physical data source schemas of the databases. Report Model provides user friendly and easy to understand entities based on which report creation becomes easy. These people may also not be familiar with the designer for creating reports.

Report Model thus becomes a prerequisite of Report Builder, when working with Report Builder 1.0. However with Report Builder 2.0, it is no more a prerequisite, as we can create ad-hoc reports without Report Models created previously. A report model contains metadata of data source and the relationships within. This metadata is in the form of SMDL (Semantic Model Definition Language). This is an XML based way of presenting model items. Models can be created with Report Model Template provided by BIDS. Models can also be created with Report Manager or Microsoft Office SharePoint Server 2007, but using BIDS provides maximum flexibility. You can base the model on Analysis Services cube if required. If we change the cube later, we need to regenerate the model. Report Model on Analysis Services cube cannot be created using designer.

Report Models comprise of following components

1. Entities: similar to objects in tables (entities have attributes and roles)

2. Attributes : similar to columns in tables

3. Roles: specifies relation between entities

4. Folders: used to organize entities and perspectives, certain model items can be grouped

5. Perspectives: subset of model, useful when models are very large, perspectives in themselves can contain entities, roles, folders etc.

In next article we will discuss how a model can be created using BIDS.