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

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

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

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

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

b.

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)

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

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

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)

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

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)

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