Online Content Vacuum Technology in SQL Server 2008

As per SSWUG.org reports in their newsletter, Microsoft has announced an excellent and cool feature for Data Mining lovers. It is Online Content Vacuum Technology. Following is the announcement from Microsoft:


SEATTLE, WA - Microsoft today announced that the next release of SQL Server would include a new data scraping and categorizing utility, code-named “Vacuum” in the next CTP release of SQL Server.  The functionality allows SQL Server to apply its world-class search technologies to content found on the web, and allows companies to “bring the cloud” down to their local networks for searching.

“This technology will be key to using many of the vast resources that are simply too tough to navigate, too tough to find what you need in today’s world,” says Steve Ballmer, Microsoft CEO.  “The Internet and social networks, blogs and other resource represent the biggest form of business intelligence resources available, but incorporating them into your work is difficult.  By using Vacuum, you simply identify the root sources of information you trust and let SQL Server pull in and categorize information for you automatically.  From there, use the new features of SQL Server 2008 R4.1 to query, report on and learn from all of those sources.  It’s what business intelligence needs to be to really enhance your ROI.”

By using Vacuum, users will be able to provide instant backup to the data represented in their systems.  At the same time, by pulling data from the cloud and bringing it onto the local network databases, companies can better control information presented, can update their databases and can avoid the abstract nature of working on the Internet.

“We’re excited to bring this announcement to the IT Professional community.  It’s clear that we can provide real value by automatically tying online resources to reports and other local resources for reporting.  We’ve been working very hard on the fact-recognition logic in SQL Server 2008 R4.1 so that it can relate information on your systems with information online, bringing the two together automatically,” said Donald Farmer of Microsoft.  “We spent a lot of time building out the great functionality in the PowerPivot toolset.  By adding this Vacuum tool to the mix, we’ll be able to back up any fact you may be looking to share, all automatically.  It’s simply incredible.”

The release of the Vacuum tools should be ready in the next CTP of SQL Server 2008 R4.1.

Search anything in your Database schema right from SSMS

In my one of earlier articles here I wrote about a way to find any value in any database column. Today, I found a great free tool, from RedGate, to find dependencies of any object. The tool “SQL Search” is available here. This can be used for impact analysis, improving the performance by replacing “Select *” with actual column name etc.

The best part is that it installs as an SSMS plugin, making it very easy to use it.

I recommend every SSMS user to give it a try.

How to Install SQL Server 2005 Client Tools?

Further to my earlier post SQL Server Client Tools Setup Fails due to MSXML 6 , in which I mentioned a solution for SQL Server Client Tools setup failure, if you are still unable to install them then following steps may help you.

  1. Navigate to \Tools\Setup folder of your SQL Server Setup CD
  2. Launch SqlRun_Tools.msi

It will launch the installation wizard directly and in 90% percent of the cases you will be able to install the client tools successfuly.

How to Query Active Directory (AD) from SQL Server

There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:

Step 1: Add Linked Server for Active Directory

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Step 2: Query the Active Directory

SELECT * FROM OpenQuery
(
ADSI,   --Name of the linked server for Active directory, created in step 1
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn   --Fields you want to retreive
FROM ''LDAP://DC=TestDomain''    --Name of your Active Directory Domain
where objectClass = ''User'''         --Type of objects you want to query e.g. User, Person etc...
)

NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

What is not Data Mining? - A Myth Buster

While watching a web cast by John Weston, I noticed a very important thing which is the clarification of what is not Data Mining? It is very common that people sometime get confused with what actually is Data Mining and start referring different terms and techniques used in normal data processing as Data Mining activities.

Following is a list which describes different data processing techniques and why they can not be reffered as Data Mining?

1. Ad Hoc Query:

Ad Hoc queries just examines the current data set and gives you result based on that. This means you can check what is the maximum price of a product but you can not predict what will be the maximum price of that product in near future? A Data Mining Algorithm can do it.

2. Event Notification:

You can set different alerts based on some threshold values which will inform you as soon as that threshold will reach by actual transactional data but again you can not predict when that threshold will reach? A Data Mining Algorithm can do it.

3. Multidimensional Analysis:

You can find the value of an item based on different dimensions like Time, Area, Color but you can not predict what will be the value of the item when its color will be Blue and Area will be UK and Time will be First Quarter of the year? A Data Mining Algorithm can do it.

4. Statistics:

Item Statistics can tell you the history of price changes, moving averages, maximum values, minimum values etc. but it can not tell you how price will change if you start selling another product in the same season. A Data Mining Algorithm can do it.

So in simple words…Data Mining is not history…It is Future!

SQL Server Client Tools Setup Fails due to MSXML 6

If you are trying to install SQL Server 2005 client tools and continuously getting failure message because of MSXML 6 installation which already exists on your machine then it may be due to the corrupted installation of MSXML 6 which blocks the installation of client tools. You can fix it as follows:

Go to Control Panel-> Administrative Tools->Add Remove Programs and try to remove it. If it removes smoothly then just install the Client Tools as before and you are done.

OR

If you found that you are unable to uninstall the MSXML 6 because of some wiered error then you need a tool called Windows Install Clean up from here

Donwload it and remove the corrupt installation of MSXML 6. Once removed, you can now install the Client tools as usual.

How to create Custom Database Role and Manage its permissions

Custom database roles is the best way to manage object permission for the given database in structured way. Following script will demonstrate the steps necessary to create a new role and manage object permissions through it

--- Step 1 -------- Create Role ------------
USE [MyTestDB]
GO
CREATE ROLE [TestRole1] AUTHORIZATION [dbo]
Go

--- Step 2 -------- Assign Permissions to role  ------------
USE [MyTestDB]
GO
GRANT ALTER,EXECUTE, SELECT,INSERT,UPDATE,DELETE
ON SCHEMA ::dbo
TO TestRole1
Go

USE [MyTestDB]
GO
GRANT BACKUP DATABASE,BACKUP LOG To [TestRole1]
Go

--- Step 3 ---- Add user to our role --------------
USE [MyTestDB]
GO
EXEC sp_addrolemember N'TestRole1', N'SPUser7'
GO

-- ******  Create user *********************

--- Step 1 ---- Create Windows Login For SQL Server ------------
USE [master]
GO
CREATE LOGIN [TestDomain\SPUser7] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTestDB]
GO

--- Step 2 ---- Create Database User for our database --------------
USE [MyTestDB]
GO
CREATE USER [SPUser7] FOR LOGIN [TestDomain\SPUser7]
GO

TSQL Function to encode HTML Text

While browsing through the Sharepoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely used in XML tags and other html rendering requirements.

Please note, this function is originally provided in Sharepoint content database and I have just copied it because of its usefulness. PLEASE DO NOT CALL it directly from Sharepoint database as it is not recommended and supported by Microsoft. Just create this function in your own database and modify it as per your requirement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[fn_HtmlEncode](
@Value nvarchar(1023),
@PreserveNewLine bit)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @Result nvarchar(4000)
SELECT @Result = @Value
IF @Result IS NOT NULL AND LEN(@Result) > 0
BEGIN
SELECT @Result = REPLACE(@Result, N'&', N'&')
SELECT @Result = REPLACE(@Result, N'<', N'<')
SELECT @Result = REPLACE(@Result, N'>', N'>')
SELECT @Result = REPLACE(@Result, N'''', N'&#39;')
SELECT @Result = REPLACE(@Result, N'"', N'&quot;')
IF @PreserveNewLine = 1
SELECT @Result = REPLACE(@Result, CHAR(10), CHAR(10) + N'<br>')
END
RETURN @Result
END

How to use more than 4GB RAM on SQL Server?

SQL Server is an highly optimized and well tuned piece of code. It try to utilise Operating System resources as optimum as possible. Due to this, most DBA think that if they will increase RAM on the server then SQL Server will automatically start using it, right? Wrong, SQL Server can only use RAM more than 4GB if and only if,

  • You are running Enterprise Editions of Windows 2003 Servers or Advanced/Datacenter version of Windows 2000
  • You have enabled Operating System to use the extra RAM you have installed. Please check here for the details and how to do it.
  • You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server
  • you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

Here it is:

USE [AdventureWorks]
GO

IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
    @val as BigInt,
    @base as int
)
returns varchar(63)
as
Begin
    /* Check if we get the valid base */
    If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;

    /* variable to hold final answer */
    Declare @answer as varchar(63);

    /*    Following variable contains all
        possible alpha numeric letters for any base
    */
    Declare @alldigits as varchar(36);
    Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    /*    Set the initial value of
        final answer as empty string
    */
    Set @answer='';

    /* Loop until your source value is greater than 0 */
    While @val>0
    Begin
        Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
        Set @val = @val / @base;
    End

    /* Return the final answer */
    return @answer;
End