Vote for Service Packs

Service Pack 4 for SQL Server 2005 is due about now, but there has been no word from Microsoft. If you are running servers on SQL Server 2005, vote for Service Pack 4. Also, vote for Server Pack 2 and demand the level of support and service we deserve for our platform. Vote here:

• Service Pack 4 for SQL Server 2005
• Service Pack 2 for SQL Server 2008

BUG: Connecting to SSAS from Visio 2010

You can connect to SQL Server Analysis Services (SSAS) from Visio to generate certain reports. In Visio 2007, you have the option got Microsoft SQL Server Analysis Services.

 

However, in Visio 2010, this is the same dialog box.

In this, you can see that SSAS is missing

Introduction to SQL Azure

SQL Azure is still at early stages. This article is to give an introduction the SQL Azure.

http://www.sql-server-performance.com/articles/dev/intro_sql_azure_p1.aspx

Could not find stored procedure 'sp_filestream_configure'

Filestream is a new feature that came along with SQL Server 2008. You need to enable FileStream before using it.

If you google, syntax for enabling FileStream is EXEC sp_filestream_configure @enable_level = 3;

However, you will end-up with Could not find stored procedure 'sp_filestream_configure' error.

Above syntax if for CTPs not for RTM!. So the correct syntax follows.

USE master;

EXEC sys.sp_configure N'filestream access level', N'2'

GO

RECONFIGURE WITH OVERRIDE

GO

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Following error is occurring while trying to access report server or report manager in SQL Server Reporting Service 2005.

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Reporting Services in SQL Server 2005 runs as on IIS 7.0. After configuring Reporting Service, two virtual directories will be created by default their Application pool is DefaultAppPool.

Every Application pool has Pipeline mode and there are two types of Pipeline modes namely, Integrated and Classic.

If the Pipeline mode of the Application Poll attached to virtual servers is Integrated you will get the above error.

You need to change the Integrated to Classic as shown below in IIS.

You do not have to restart neither IIS nor SQL Server Reporting Services.

There is another method. You need to create new application pool with Classic and attached that Application pool to the both virtual servers from Reporting Server Configuration Tool.

Spelling Mistake in Back Up Database Task of SSIS

In the SSIS task there are Maintenance Plan Tasks. Under the Maintenance Plan Tasks, there is a task called Back Up Database Task to backup databases. After drag and dropping the task to the control flow, press F4 to get properties. There is a property called TaskAllowsDatabaseSelection which is mis-spelled as shown in the following image.

Is SQL Server's latest security hole a real threat?

Last week Sentrigo brought a security hole in SQL Server to my attention. The issue is simply that SQL Server stores passwords in clear text in memory, so it’s quite possible to sniff the memory to retrieve passwords of other users. That in itself doesn’t sound dangerous at first glance because you have to have system admin rights in the database to do this. However, there are two basic situations where this could be horrendous.

Because most users have the same password, once you gain access to this password, you’ve gained access to all the boxes that account is on. This works on only native SQL passwords, not on Windows passwords. This is because when you use a Windows account to log into SQL, you don’t give it a password; you only pass in your security token, and there’s no password to steal. With SQL passwords, however, you pass in the SQL username and password, and this combination is stored in clear text in memory.

read more http://www.infoworld.com/d/data-management/sql-servers-latest-security-hole-real-threat-801

Connecting to a Database without Entering Parameters Every time from SSMS

If you are using SSMS regularly, you know that you have supply database name every time. You can avoid this by supplying a default database for a user. However this is not recommended because in case that database is dropped or renamed, user will not be able to login to SQL Server and will receive this error.

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

SQL Server Management Studio is executed from a exe called SSMS which resides by default at  "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

For this you can supply server name and database name as following.

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S Dinesh-PC -D AdventureWorks2008

You can create a short cut with above values and when ever you click the short cut SSMS will database as AdventureWorks2008. However, problem with this is that, Object Explorer will not load objects unless you use conventional connect dialog box.

Delete Old Backup Files

If you have used SQL Server 2000, you know that in SQL Server 2000, deleting previous backup files are easy. In the SQL Server 2000 , the Specify Backup Disk Directory you simple have to specify the period and the extension as shown in following figure.

Where is this option in SQL Server 2005? Well, this is FAQ in many forums and therefore, thought of writing this blog post.

In SQL Server 2005, if you drag and drop the Back Up Database Task, you will get the options to create backup file. However, there won’t be any place to enter remove backup option as in SQL Server 2000.

Because of this, many users are using scripting mechanisms to delete them. However, if you want to delete files inside subfolders, the script will become complex.

In the tool box of Maintenance Plan Tasks there is another task called Maintenance Cleanup Task which can be used to delete old backup as shown in following image.

After configuring this task, you need to connect the task to the backup task as shown in following image.

Changing Parameters for Select Top and Edit Top

In SQL Server 2008, Object Explorer of the SQ Server Management Studio, there are couple of options which are highlighted in the following image.

In SQL Server 2005, there was an option named Open Table, which will open entire table. If that table has lot of rows and columns it will take quite lot of time and resources.

However, in SQL Server 2008, Select Top 1000 Rows will list first 1000 rows and Edit Top 200 Rows will give you the option of editing only first 200 rows.

Problem is how to edit these numbers in case of a need.

In the option menu item of the SQL Server Management Studio, you can change those values as shown in the above diagram. If you want to get all the all the rows, you need to type 0 at the desired place.

Also, this values will be immediately available upon clicking the ok button thus you don’t have to restart SQL Server Management Studio.

Error when modifying table from SQL Server Management Studio in SQL Server 2008

In SQL Server 2008, when you change table from SQL Server management studio, if that change needs table to be dropped and re-created, it will return and error and change will not be affected. Error is shown in following image.

This will effect operations like changing int column property to identity and changing varchar column to int etc.. In case of table recreation, it can take lot of time if there is lot of records in the tables.

However, there is an option to allow these changes to occur.

Select Options from the Tools menu. In that dialog go to Designers option and de-select highlighted option. By default this option is selected.

Country’s ‘Most Valuable Professionals’ dedicated to serving local IT community

This article is about Sri Lankan MVP and my views are also taken into this. Read the full article at http://www.adaderana.lk/news.php?nid=3026

Change Tracking in SQL Server 2008

SQL Server 2008 includes  several inbuilt features for Auditing – namely Change Data Capture (CDC) and Auditing and Change Tracking - this article we are going to discuss  Change Tracking.

more at http://www.sql-server-performance.com/articles/audit/change_tracking_2008_p1.aspx

Two Books on SQL Server 2008

I had the privilege of becoming a contributing editor for two SQL Server 2008 books published by Syngress. Those books are The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design  and The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance.

I was involved around seven books to date and this is the first time one of my books are sold in Amazon. 

Working with Sinhala Letter in SQL Server

Being a Sri Lankan and SQL Server professional, I would love to see Sinhala font in SQL Server.

In SQL Server 2005 there is a matching collation for Sinhala called Indic_General_90_BIN. In SQL Server 2008,  there is a collation called Indic_General_100_BIN
Refer http://msdn.microsoft.com/en-us/library/ms188046.aspx

DROP TABLE fruits

CREATE TABLE Fruits

    (

      NAME NVARCHAR(50) COLLATE Indic_General_90_BIN

    )

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අඹ')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කොස්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නාරං')           

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'දං')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'ඇපල්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'කෙසෙල්')

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අන්නාසි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'ලාවලු') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කැකිරි') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'රඹුටන්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  ( N'දෙලුම්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නෙල්ලි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අලි පේර')

           

You can order them by the Sinhala alphabet. 

SELECT  * FROM    [Fruits] ORDER BY NAME

It also works with normal searching operations such as = and like.

SELECT * FROM Fruits WHERE NAME = N'රඹුටන්'

SELECT * FROM Fruits WHERE NAME Like N'ක%'

SELECT * FROM Fruits WHERE NAME Like N'අ%'

Restoring a Database with Symmetric Encryption

Encryption was introduced into SQL Server with SQL Server 2005. This series of articles will tell you how to implement Encryption to protect your valuable data.

This blog is to how to restore a database to another server.

1. Backup the database on the [SOURCE] server.

2. Backup the service master key on the [SOURCE] server.

BACKUP SERVICE MASTER KEY TO FILE = 'C:\ENCRYPT_KEYS\MYSERVICE.KEY'
     ENCRYPTION BY PASSWORD = 'pa$$w0rd'

3. Restore the service master key on the [DESTINATION] server.

RESTORE SERVICE MASTER KEY FROM FILE ='C:\ENCRYPT_KEYS\MYSERVICE.KEY'

DECRYPTION BY PASSWORD = 'pa$$w0rd'    [FORCE]

4. Restore the database on the [DESTINATION] server.

Collation, Danish_Norwegian Vs Latin1_General

Collation is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.  We all think that Collation is effected for Sorting and Case Sensitive data.

 CREATE TABLE t ( c char(2) )

GO

INSERT INTO t VALUES ( 'a' )

 

INSERT INTO t VALUES ( 'Aa' )

INSERT INTO t VALUES ( 'Za' )

INSERT INTO t VALUES ( 'A' )

INSERT INTO t VALUES ( 'B' )

--Result 1

SELECT * FROM t ORDER BY c COLLATE Danish_Norwegian_CI_AS

--Result 2

SELECT * FROM t ORDER BY c COLLATE Latin1_General_CI_AS

Following are the two result sets.

Result1 (Danish_Norwegian)	Result2 (Latin1_General)
a	a
A	A
B	Aa
Za	B
Aa	Za

You can see that in the in the both result sets only difference is placement of Aa.

If you run following two statements, things will be quite annoying.

--Result 3

SELECT

* FROM t WHERE c LIKE 'A%' COLLATE Danish_Norwegian_CI_AS

--Result 4

SELECT

* FROM t WHERE c LIKE 'A%' COLLATE Latin1_General_CI_AS

Result 3 will return 2 rows, a and A and Result 4 will return a, A and Aa. Which means that Aa is missing in Danish_Norwegian collation. Above results were obtained from SQL Server 2005 SP3 and these results are same with SQL Server 2008 SP1.

Error when SSRS Report Running from a .Net Report Viewer

When report viewer report added to the .Net application and report path is set to a deployed report to the report server following error message will occur.

The permissions granted to user '<DOMAIN>\IUSR_<DOMAIN>' are insufficient for performing this operation. (rsAccessDenied)

1. Open Internet Explorer.
2. Type the following in the Address bar:
   http:// application-tier/Reports/Pages/Folder.aspx 
   You can find the name of the report server by opening Team Explorer, expanding the Reports node, and viewing the properties of a report.
3. Click the Properties tab and then click New Role Assignment.
4. In Group or User Name, and add the Windows logon name for the person you want to add to this group.
5. In Role, select Content Manager, and then click OK.

Report Builder is not Launching

In SQL Server Reporting Services (SSRS) 2005 clicking the Report Builder option in Report Manager does not launch Report Builder Tool

You need dotnet framework 2.0 in your client machine to launch Report Builder. Verify whether you have installed dotnet framework 2.0. If it is already installed check the application log from the event viewer and verify whether there are any errors relevant to the Report Builder.

Error When Configuring SSRS Database

Error is occurring when trying to configure SSRS Database Setup from the Reporting Service Configuration Manager. Following is the error you get.

The database version (C.0.8.40) does not match your reporting services installation.  You must upgrade your reporting services database.
Couldn't generate the upgrade script.  There is no upgrade script available for this version.

Answer:

Error is occurring when it can't match with the database version of your reporting service installation. This is happening when you install you components in following order.

1. Install SQL Server 2005 Database Services.

2. Install SQL Server 2005 Service Pack 3.

3. Install SQL Server Reporting Services.

Because of this, SQL Server Report Services and Database Services versions are mismatching. Therefore, you need to install SQL Server Service Pack 3again so that both versions are equal.

SQL Server Reporting Server (SSRS) Service is Failing to Start

After a server reboots the SQL Server Reporting Server (SSRS) service is failing to start giving following two errors in the event log.

1. The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

2. Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect.

This issue may occur if the service times out before it starts successfully. This issue is more likely to occur if your computer is heavily loaded.

To resolve this issue, increase the time-out value for service startup process. When you increase this value, the Microsoft ISA Server Storage service has more time to load when the computer starts. To increase the service startup time, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Name: ServicesPipeTimeout

Type: REG_DWORD

Data: The number of milliseconds that you want to give the services to start in

Typically, a data value of 35,000 is sufficient to keep the service from timing out. However, you can reduce or increase this value according to your specific startup requirements. For example, to use a time-out value of 60 seconds, assign a data value of 60,000 to the ServicesPipeTimeout registry entry. A larger data value does not decrease your computer's performance. To create this registry entry, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

3. Right-click Control, point to New, and then click DWORD Value.

4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.

5. Right-click ServicesPipeTimeout, and then click Modify.

6. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.

For example, to wait 60 seconds before the service times out, type 60000.

7. Quit Registry Editor, and then restart the computer.

OUTPUT in SQL Server 2005

This small video describes how to use Output command in SQL Server 2005.

How Send the SSRS Report From SSIS?

This is a requirement when you need to send a SSRS report in Excel, PDF format to different users from SSIS after performing a data load.

First you need to create subscription to the report. You can create a SSRS report subscription from Report manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, SQL Server Agent Job will be created.

From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

How to uninstall SQL Server 2005 manually

Sometimes, you may find that, though you can't see any SQL Server 2005 instance in the programs list and when try to install the SQL Server 2005, it gives messages saying that there is SQL Server instance or some components like SQL Server Management Studio. Reason for this is existence of corrupted SQL Server instance and you need to remove the SQL Server instance manually.

At a command prompt, run the following command:
"%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove"

Uninstall the SQL Server components one at a time until all the SQL Server components are uninstalled.

Note Add or Remove Programs also runs the ARPWrapper.exe program by using the /Remove option. However, the reference to the ARPWrapper.exe program may have been deleted. Make sure that all the services (if exists) relevant to this SQL Server instance is stopped.

SQL Server 2008 SP1 Download Available

You can download SQL 2008 SP1 here.  If you get a 404 error, come back and try it again.  Seems that the download servers aren’t all synced up.

The April 2009 update to the SQL Server 2008 Feature Packs is also available today. In this release few bugs were fixed.

SQL Heart Beat

There are plenty of tools to monitor your SQL Server Database engine. However, there are hardly any tools to measure performance of SQL Server Analysis Service (SSAS) database.

To fill this gap in the industry company named SQL Solutions who delivers top of the line database performance tools and specialized consulting services for the Microsoft SQL Server platform, has come with a FREE tool called SQL Heart Beat.

Basic features of SQL Heart beat are,

• Monitor server wait categories
• Monitor IO activity
• Check your current Cache Hits ratio
• Analyze your I/O system performance
• Get details about active processes
• Display locks and deadlocks

Following image shows how you can measure/views these from the SQL Heart beat tool.

Also, you have the option of measuring these performance of multiple SSAS servers from a single instance of a SQL Heart beat.

You can download this tool and experience the benefits.

Using Merge Command in SQL Server 2008

This video will show you how to use Merge Command in SQL Server 2008.

How to alter a User Defined Data Type?

 

The only way to do it is to create a new User Define Data Type (UDDT), and change out all existing column to that UDDT, then you can drop the original one, and recreate it and change out the change you made previously. The problem is that you can drop the UDDT if it is in use.(PN: this is same with SQL Server 2008)

In case of the UDDT is in use you need to follow these steps.

1. Allocate different data type for the fields which are using UDDT. Rather than allocating arbitrary data type it is better to allocate data type of the UDDT, so that there won’t be any issue with the existing data.

2.Drop Stored Procedures which are using UDDT. You can drop them by using following T-SQL. However, you need to get the scripts of those SPs to create them later.

DECLARE @dropsps VARCHAR(8000)
SET @dropsps = ''
SELECT  @dropsps = @dropsps + ',' +
        OBJECT_NAME(id)
FROM    sys.syscomments
WHERE   TEXT LIKE '%latitude%'
        AND OBJECT_NAME(id) LIKE 'usp%'
SET @dropsps = 'DROP PROC ' + RIGHT(@dropsps, LEN(@a) - 1)
EXEC ( @dropsps
    )

3. Drop the UDDT

4. Create new UDDT

5. Allocate new UDDT to the fields which had previous UDDT.

6. Create Dropped Stored Procedures

You can use following script and you can change the first variable value according to your requirement. This script was tested for few data types.

SET NOCOUNT ON

DECLARE @udt VARCHAR(150)

DECLARE @udtschema VARCHAR(150)

DECLARE @newudtschema VARCHAR(150)

DECLARE @newudtDataType VARCHAR(150)

DECLARE @newudtDataSize smallint

DECLARE @OtherParameter VARCHAR(50)

SET @udt = 'Name' -- Existing UDDT

SET @udtschema = 'dbo' -- Schema of the UDDT

SET @newudtDataType = 'varchar' -- Data type for te new UDDT

SET @newudtDataSize = 500 -- Lenght of the new UDDT

SET @newudtschema = 'dbo' -- Schema of the new UDDT

SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL

DECLARE @Datatype VARCHAR(50),

@Datasize SMALLINT

DECLARE @varcharDataType VARCHAR(50)

DECLARE @Schemaname VARCHAR(50),

@TableName VARCHAR(50),

@FiledName VARCHAR(50)

CREATE TABLE #udtflds

(

Schemaname VARCHAR(50),

TableName VARCHAR(50),

FiledName VARCHAR(50)

)

SELECT TOP 1

@Datatype = Data_type,

@Datasize = character_maximum_length

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Domain_name = @udt

SET @varcharDataType = @Datatype

IF @DataType Like '%char%'

AND @Datasize IS NOT NULL

AND ( @newudtDataType <> 'varchar(max)'

OR @newudtDataType <> 'nvarchar(max)'

)

BEGIN

SET @varcharDataType = @varcharDataType + '('

+ CAST(@Datasize AS VARCHAR(50)) + ')'

END

INSERT INTO #udtflds

SELECT TABLE_SCHEMA,

TABLE_NAME,

Column_Name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Domain_name = @udt

DECLARE @exec VARCHAR(500)

DECLARE alter_cursor CURSOR

FOR SELECT Schemaname,

TableName,

FiledName

FROM #udtflds

OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName

+ ' ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType

EXECUTE ( @exec

)

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

END

CLOSE alter_cursor

SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'

EXEC ( @exec

)

SET @varcharDataType = @newudtDataType

IF @newudtDataType Like '%char%'

AND @newudtDataSize IS NOT NULL

AND ( @newudtDataType <> 'varchar(max)'

OR @newudtDataType <> 'nvarchar(max)'

)

BEGIN

SET @varcharDataType = @varcharDataType + '('

+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'

END

SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '

+ @varcharDataType + ' ' + @OtherParameter

EXEC ( @exec

)

OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName

+ ' ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema

+ '].[' + @udt + ']'

EXECUTE ( @exec

)

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

END

CLOSE alter_cursor

DEALLOCATE alter_cursor

SELECT *

FROM #udtflds

DROP TABLE #udtflds

Application Name & SQL Server Profiler

SQL Server Profiler is a tool which you can use to gather information about what are the events happening at the SQL Server.  Following is the sample which you will get from the Profiler Trace file.

If you pay attention to the Application Name Column (3rd Column form your left hand side), You can see there are entries. For the First four rows, you can see a SQL Prompt and next four lines you will see Microsoft SQL Server Management Studio. This is due to the fact that those applications are using this particular instance of SQL server. If you look closer, you will see that next 3 lines has a entry name .Net SqlClient Data Provider.

Obvious question is what is this application. Well, this is because Application Name is not specified by the application. You have to set it from the client application by adding another parameter to the connection string.

string connectionstring = "Data Source=.; Integrated Security=SSPI; Initial Catalog=DB;Application Name=MyApplication";

NB: Whenever the Application Name is not specified it will use default application Name, .Net SqlClient Data Provider.

After if you analyze the Profiler trace again you will see the correct application name.

This very important option as this will allow you to analyze your Profiler Trace for application wise. Also, you can use filter option for Application Name in Profiler to filter by application.

Database Implications if IBM Acquires Sun

Reported or rumored merger discussions between IBM and Sun are generating huge amounts of discussion today (some links below). Here are some quick thoughts around the subject of how the IBM/Sun deal — if it happens — might affect the database management system industry.

• IBM is already serious about supporting multiple database management systems. DB2 on open systems is IBM’s flagship DBMS. But DB2 on mainframes and at least one flavor of Informix seem to be getting maintained and enhanced fairly seriously as well. And IBM has further DBMS products as well (e.g., DB/2 on the AS/400). There’s little reason to think IBM would orphan MySQL or any other DBMS product.
• IBM is very open-source-friendly. For a company that grew up for decades on proprietary software — and still is a huge software products vendor — IBM is very serious about open source. If you doubt that, I have two words for you: “Linux” and “Eclipse”.
• MySQL might finally get its industrial-strength act together. IBM is good at database management and good at open source. MySQL becoming a no-apologies transactional DBMS would obviously put pressure on Ingres, PostgreSQL, and EnterpriseDB, although there surely would be lots of happy talk about the open source DBMS market being validated, lifting all the vendors and so on. Also, a better MySQL could be bad news for Microsoft SQL Server too.
• Sun has a lot DBMS partnerships right now. Obviously, Sun owns MySQL, and has partnerships with MySQL storage engine vendors such as Infobright and Kickfire. Sun also has a substantial partnership with Greenplum, and a Barneyesque* one with ParAccel. And of course Sun has strong working relationships with major database vendors such as Oracle and Sybase. What’s more, on a case-by-case basis, Sun may cooperate in the field with yet other DBMS sellers. E.g., I’ve confirmed at least one instance of a Sun sales rep recommending a Kognitio DBMS.
• IBM partners with outside DBMS vendors too. You’d think IBM’s gazillion DBMS product lines would be enough. But nooooo. I frequently hear rumblings of IBM’s hardware or services operations working with other DBMS products as well. (This is, of course, actually to their credit.)
• Short-term, there probably would be little effect on partnerships. Greenplum runs on Sun’s Thumper/Thor line of boxes. DB2 doesn’t, and certainly isn’t optimized for same. In the short term, to sell Thors, Sun would presumably continue to sell Greenplum.
• Longer-term, there could be a DBMS rationalization. DB2, Informix, MySQL + storage engines, and big independent vendors such as Oracle and Sybase would surely always get attention. That’s a lot. There might not be room for much mind share for many database products and vendors beyond that list.

*A Barney partnership is one in which two or more vendors get on stage and do a song and dance about how much they love each other, with little substance beyond that.

Related links

• Larry Dignan thinks the IBM/Sun deal is sensible and ripe to happen.
• Dana Gardner thinks otherwise.
• Matt Asay seems to agree that IBM understands the open source business.
• Before IBM acquired it, solidDB was scheduled to provide a serious MySQL transaction processing engine.

From http://www.dbms2.com/2009/03/18/database-implications-if-ibm-acquires-sun/

Multiple CTEs in a Single T-SQL Statement

What is CTE

From BOL,

Common Table Expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

However, in BOL there is no example, how to write a single T-SQL statement with multiple CTEs. hence I have seen lot of queries about this in many forums. Following is the format you should use.

WITH CTEA (ID,Col1)

AS

(

Syntax for CTEA

),

CTEB (ID,Col2)

AS

(

Syntax for CTEB

)

SELECT CTEA.ID,CTEA.Col1,CTEB.Col2 FROM CTEA

INNER JOIN  CTEB

ON CTEA.ID = CTEB.ID

Notice that two CTEs are separated by , and for the second CTE you SHOULD NOT use WITH statement.

Here is an example, where you want to compare sales values between year 2003 and 2004 for each product. 

WITH Prod2003 (ProductNumber,Amount)
AS
(
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2003
GROUP BY Prod.ProductNumber),
Prod2004 (ProductNumber,Amount)
AS
(
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2004
GROUP BY Prod.ProductNumber
)

SELECT Prod2003.ProductNumber,
Prod2003.Amount Sales2003,
Prod2004.Amount Sales2004 ,
Prod2004.Amount - Prod2003.Amount SalesIncrement
FROM Prod2003
INNER JOIN   Prod2004
ON Prod2003.ProductNumber = Prod2004.ProductNumber

Output for the above query is displayed in following image.

Start Data Collector Set of Reliability & Performance Monitor from C#

Reliability & Performance Monitor a.k.a perfmon is a valuable tool to have for a Database Administrator, as it provides various counters to measure your system with defined time interval.

Data Collector Sets (Similar to Counter Logs in Windows 2003 and XP or before versions)  will give you the opportunity to collect these data to file so that it can be analyzed later.

In the Data Collect Set, you need to start the defined Data Collector Set. Problem here is if you want to analyze something soon after you do it, there will be a time delay. For example, if you want to measure % of Processor soon after the execution of a stored procedure. Normally, you need to execute the stored procedure and manually start the Data Collector Set which lead to some delay. Instead, you can include both the executing the Stored Procedure and starting the Data Collector Sets inside a C# or VB.net application.

So the next question is, how to start Data Collector Sets from C#. Though you have perfmon with previous versions of OS you can start Data Collector Sets from C# only with Vista and Windows 2008.

First, add PLALibrary to the reference from the %windir%\System32\PLA.dll and include using PlaLibrary; at the top of your code. Following code will start, Data Collector Set named Page Split.

private void Start_PLA()
        {
            IDataCollectorSet cs = new DataCollectorSetClass();
            string name = "Page Split";
            cs.Query(name, null);
            cs.start(false);
        }

Similarly, there are many functions available with PLALibrary for other operations like, Create and Stop Data Collector Sets.

Integrating perfmon and profiler

perfmon and profiler are valuable tools for DBAs to monitor your SQL Server instance. In SQL Server 2005 you have the option of integrating these two tools. Following video show how to integrate them.

Video1 Video2 Video3 Video4 Video5 Video6

This is my second video and it took me more than just pain to get this video done. Initially I couldn’t get this video for one avi file. Then I did the video for 3 videos and found a simple tool to integrate them. After integrating them, final file size was 500+ MB, which was not possible to upload. Then found another tool to split them. After all manage to find to split into six avi files.

As said before, I am not a flashy presenter and please forgive any Language errors. Please send your comments.

Two Books from SQL Server Central

SQL Server Central, most popular web site on SQL Server, has released two books called Best of SQLServerCentral, Volume 5 and Best of SQLServerCentral, Volume 6. Two articles of mine, The OUPUT Command (Vol 5) and SSAS Database Backup (Vol 6) are in those books.

Visio & SSAS

Do you know that you can connect to a SSAS database from Microsoft Visio. Yes from Visio. Most people don’t. If you are a one of them falling to don’t category, my latest article is for you.

Warren Buffets Advice for 2009

We begin this New Year with dampened enthusiasm and dented optimism. Our happiness is diluted and our peace is threatened by the financial illness that has infected our families, organizations and nations. Everyone is desperate to find a remedy that will cure their financial illness and help them recover their financial health. They expect the financial experts to provide them with remedies, forgetting the fact that it is these experts who created this financial mess.

Every new year, I adopt a couple of old maxims as my beacons to guide my future. This self-prescribed therapy has ensured that with each passing year, I grow wiser and not older. This year, I invite you to tap into the financial wisdom of our elders along with me, and become financially wiser.

* Hard work: All hard work bring a profit, but mere talk leads only to poverty.

* Laziness: A sleeping lobster is carried away by the water current.

* Earnings: Never depend on a single source of income. [ At least make your Investments get you second earning ]

* Spending: If you buy things you don't need, you'll soon sell things you need.

* Savings: Don't save what is left after spending; Spend what is left after saving.

* Borrowings: The borrower becomes the lender's slave.

* Accounting: It's no use carrying an umbrella, if your shoes are leaking.

* Auditing: Beware of little expenses; A small leak can sink a large ship.

* Risk-taking: Never test the depth of the river with both feet. [ Have an alternate plan ready ]

* Investment: Don't put all your eggs in one basket.

I'm certain that those who have already been practicing these principles remain financially healthy. I'm equally confident that

those who resolve to start practicing these principles will quickly regain their financial health.

Let us become wiser and lead a happy, healthy, prosperous and peaceful life.

Debugging SQL Server Stored Procedures

If you are a developer, it is needless to stress the importance of debugging you complex codes. In Visual Studio, you have the luxury of debugging you codes with fancy features. Now you can integrate SQL Server Stored Procedures to Visual Studios to debug those. See the step by step information from the following six videos.

Video1 Video2 Video3 Video4 Video5 Video6

Running SQL Profiler without sysadmin Privileges

In SQL Server 2000, if you wish to run SQL Profiler you need grant sysadmin permission to the user. This was not encouraging for the system administrators.  In SQL Server 2005, you have the option of granting permission only for SQL Profiler, so that the users should not be a member of sysadmin group.

USE master
GO

GRANT ALTER TRACE TO username;
GO

In case you need to revoke the permission, you granting permission only for SQL Profiler.

USE master
GO

DENY ALTER TRACE TO username;
GO

SQL Server BI Comes into Fashion

Apranga Group, the leading fashion retailer in the Baltic States, has contracted Microsoft partner New Vision to implement its business intelligence solution based on ProfitBase and Microsoft SQL Server.

Apranga Group currently operates 71 stores in Lithuania, 21 in Latvia and seven in Estonia. The company was experiencing a challenge in selecting a single standardized business intelligence (BI) platform. A short implementation time was a top priority, as Apranga had a very strict deadline. The ability to modify and extend the deployed BI solution easily was another important requirement.

Following an extensive evaluation process, Apranga Group contracted Microsoft partner New Vision to implement its business intelligence solution which uses Microsoft SQL Server. This BI solution is based on Association for Retail Technology Standards (ARTS) templates from ProfitBase. The templates enabled Apranga to kick-start the BI solution rapidly, saving time on analysis and implementation, minimizing risks and reducing costs. New Vision’s competence in Microsoft Dynamics NAV combined with data connectors from ProfitBase saved a lot of effort loading a data warehouse. Integrating historic legacy data sources was easy and the customer had an Online Analytical Processing (OLAP) system finished in only two and a half months.

Following the implementation of the BI solution, Apranga Group is able to analyze sales and inventory in a single BI system using SQL Server Reporting Services and Office Excel Pivot Tables tools. Previously the same data was analyzed by several different reporting and BI systems.

The standardized BI solution at Apranga Group now covers all stores and chains it operates and contains nine years of historic data. The solution has become a central point of information for many employees. The company is planning to extend the BI solution with new functionality according to their business needs.

This article first appeared in the spring 2009 edition of Retailspeak magazine.

Accessing Cubes from Excel 2007

Microsoft Excel can be used to access SSAS cubes databases. Latest article by me written on www.sql-server-performance.com is to explorer features of Excel to connect to SSAS cubes.

Book on SQL Server Replication

Two articles of mine on replication DDL Replication in SQL Server 2000 and 2005 and Peer-to-Peer Transactional Replication in SQL Server 2005 are selected for a SQL Server Replication book. This book is published by E-COM and book is available for sale. This book is in Russian and i would like to thank Alexander Gladchenko  for giving me this opportunity. I will get a copy of this book though I can’t read it :).

dbfriend Video Center

I have started creating on SQL Server and those are published at http://www.dbfriend.magnify.net. To start with, I have created a short video on how to display SSRS tables with different row colors.

I am not a flashy presenter and these video may not be in super quality. However, I am trying to improve and sorry for any inconvenience.

Microsoft To Cut 5,000 Jobs As Profits Fall 11%

Total sales at the Microsoft were up a marginal 1.6% year over year in the second quarter, to $16.63 billion, but Microsoft's net income slumped 11% during the period, to $4.17 billion. Earnings per share fell 6% to 47 cents.

Revenues from sales of the Windows operating system dropped 8%, to $3.98 billion. In its report, filed Thursday with the Securities and Exchange Commission, Microsoft blamed the decline on "PC market weakness and a continued shift to lower-priced Netbook PCs." Netbooks are inexpensive, low-powered laptops used mostly for e-mail and Web surfing. Many models are not capable of running Vista, the current version of Windows.

But Windows' problems can't be blamed entirely on the recession. Many consumers and businesses have rejected Vista , launched in January 2007, because of its steep hardware requirements, intrusive security measures, and incompatibility with older software.

By contrast, Apple earlier this week reported that sales of its Macintosh computers rose 9% in the most recent quarter, despite the economic slump. Worsening the comparison with Apple, sales of Microsoft's Zune MP3 player plunged 54% in the last quarter, while iPod sales increased 3%.

Microsoft's quarterly report revealed trouble in other key segments. Office sales to businesses rose 7%, but sales of the desktop productivity package to consumers were off 23%. Office has been hit by competition from low-cost or free alternatives, such as IBM's Lotus Symphony suite.

Online advertising sales increased 7% to $664 million, but overall revenue at Microsoft's Internet unit were flat. Meanwhile, the group posted a $471 million operating loss, nearly double the loss from a year ago. Microsoft is hoping to catch Google in the search market but efforts to date, including last year's $1.2 billion acquisition of Fast Search & Transfer, have borne little fruit.

On the upside, Microsoft said sales of server software and related tools increased 15%, to $3.74 billion, on the strength of corporate demand for Windows Server 2008 and SQL Server 2008.

Microsoft is taking drastic steps to deal with the malaise. The company on Thursday announced that it would cut 5,000 jobs across the board in departments ranging from IT to human resources. 1,400 of those positions will be eliminated by the end of the month, the company said.

Microsoft, which didn't offer future guidance, also said it would curtail spending on travel, marketing and other areas with an eye to saving $1.5 billion annually. It's also eliminating merit raises.

Source : www.informationweek.com

Writing From Windows Live Writer

This is my first post here from Windows Live Writer and if you wish you can download it from http://download.live.com/writer

Cumulative Update 3 For SQL Server 2008 is Released

Microsoft has released Cumulative Update 3 For SQL Server 2008. The fixes included in Cumulative Update are listed here. You can go here to request the Cumulative Update from Microsoft.

Display only given number of records per page in SQL Server Reporting Services 2005 (SSRS)

Question:
In SQL Server Reporting Services 2005 (SSRS), how to display only given number of records per page?
Answer:
If you want to display only 20 rows per report page, it is not easy task as number of rows will depend on the font size and other printer defaults. Apart from this problem, if there are rows with lengthy text it tends to move to another line which will upset the number of rows in your report. So it is necessary to add some coding to get this feature to your reports.
Let us say, you want to have only 20 rows per page, add a group to the report with following expression.
=int((RowNumber(Nothing)-1)/20)

Then select page break at the end option for the group.

Error when Inserting for Replication Stopped Databases

Question: There is a database which was enabled for replication before. After replication was when trying to insert records following error is raised. Invalid object name ‘dbo.MSmerge_contents’.
Answer: When replication is enabled, additional columns, triggers are created to your replicated articles. When you stop the replication, there are times that newly added tables are not removed. Things will get worse only partial objects are removed. For example, triggers are not dropped while tables are dropped. When trigger try to insert records to the relevant tables, it will find that tables are missing and error is raised.
To overcome this you can run sp_removedbreplication @dbname = 'dbname' and it will remove that replicated related objects from the database.

Why Features are Decreased in SP3?

This sounds bit strange for me. Have I missed anything? let me know. This is the scenario.
I have two SQL Server database servers where I have a database with exactly similar schema. This I can assure and I checked this twice if not more. One SQL Server has SP2 applied and SP3 has applied to other. We all expect SP3 to work smarter.
Now I need to Create a view with two table and these two table have a foreign key between them. When I add two tables to the view following image shows the how the behavior in both SP2 and SP3.

In SP2 database server, it shows relation between two relational keys which is the correct way. In the SP3 database server, link has made between the two columns named ID instead of two relational keys. In SP3, I have to drop this wrong relation and then create the right one again.
Both Views were created from one SQL Server Management Studio which drop the issue of version mismatches of SQL Server Management Studio. Only thing I can see is the service packs difference and all the other configurations are same.

How to Unzip a File in SSIS?

For SSIS packages, most of the times you will get zip, rar or tar etc files to extract them during the extraction operation in SSIS. Let us see how we can do this from SSIS.
You can use Execute Process Task in Control Flow task. Drag and drop an Execute Process task to the control flow and configure as following image.

In this, you need to perform three configurations.
1. Executable: This is the path of the application you are going to use. This specific example has used Winrar.
2. Arguments: in this you need to supply the arguments to extract the zipped files. In the particular example e stands for Extract files to current directory. Then the full path name of the zipped file. –o+ is a parameter to say overwrite files if existing. This parameter will hide the conformation dialog which will come in case of an existing file.
3. Working Directory: This is the current directory for the process. In the given example test1.rar will be extract to the directory given in the Working Directory attribute.
However, Most of the time there will be several zip file to extract and path may not be defined this clearly. For this you can you for each container and include process task inside it like following image.

In the for each loop container, enumerator is Foreach File Enumerator which is the default enumerator. In the Execute process task all the configuration are same as above but with in expressions, arguments were set to "e " +@[User::currentFile] + " -o+"
Download the sample SSIS package here.