Validate Email Addresses in SQL Server

I will be honest here – doing email address validation in SQL Server is one of the most challenging tasks a T-SQL programmer faces.

Stefan Plattner sometime back on the forums shared a query to validate Email Address. I have been using this query in my applications and thought of sharing this with all of you

Let us create a sample table

DECLARE @TT table
(
ID smallint,
StudentID smallint,
EA nvarchar(30)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1,  'jill@abcdef.com');
INSERT INTO @TT VALUES ( 102, 2,  'matt@abcdef.com');
INSERT INTO @TT VALUES ( 103, 3,  'abcdef.com');
INSERT INTO @TT VALUES ( 104, 4,  '@abcdef@deamon.com');
INSERT INTO @TT VALUES ( 105, 5,  'carlos@abcdef.com');
INSERT INTO @TT VALUES ( 106, 6,  'sachin@abcdef.com');
INSERT INTO @TT VALUES ( 107, 7,  'terri.jake@abcdef.com');
INSERT INTO @TT VALUES ( 108, 8,  'triplet@@abcdef.com');
INSERT INTO @TT VALUES ( 109, 9,  'robert@abcdef.com');
INSERT INTO @TT VALUES ( 110, 10, 'bill@abcdef.com');

Now write the query to print valid email addresses

-- Print Valid Email Addresses
SELECT * FROM @TT WHERE
CHARINDEX(' ',LTRIM(RTRIM(EA))) = 0
AND  LEFT(LTRIM(EA),1) <> '@'
AND  RIGHT(RTRIM(EA),1) <> '.'
AND  CHARINDEX('.',EA , CHARINDEX('@',EA))- CHARINDEX('@',EA ) > 1
AND  LEN(LTRIM(RTRIM(EA )))- LEN(REPLACE(LTRIM(RTRIM(EA)),'@','')) = 1
AND  CHARINDEX('.',REVERSE(LTRIM(RTRIM(EA)))) >= 3
AND  (CHARINDEX('.@',EA ) = 0
AND CHARINDEX('..',EA ) = 0)

OUTPUT

As you can see the query eliminates email addresses of ID 103, 104 and 108 since they are invalid.

Feedback:

Find Number of Weeks in a Month using SQL Server

I have been asked this question plenty of times – How do I calculate the number of weeks in a month. The answer to this query depends on how do you define a week. For simplicity purposes, I will take a week from Sunday to Saturday as depicted in the calendar

So November has 5 weeks!

Let us write the query to calculate the number of weeks in each month of this year. This query was originally written by Michael Jones and I have modified it to suit the requirement

DECLARE @Yr SMALLINT
SET  @Yr = 2009
  
;WITH NumWeeks
AS
(
 SELECT Number + 1 as mth,
 DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,0))/7 AS fst,
 DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,30))/7  AS lst
 FROM master..spt_values
 WHERE Type = 'P' and Number < 12
)
SELECT DateName(mm,DATEADD(mm,mth,-1)) as [MonthName],
lst - fst + 1 AS [NumberOfWeeks]
FROM NumWeeks;

OUTPUT

Feedback:

Getting started with Report Builder 3.0 – Report Parts

Report Builder 3.0 supports SQL Server 2008 R2 Reporting Services and is a separate MSI file for download - Report Builder 3.0 download

One of the new features of Report Builder 3.0 is to create, publish and reuse the report parts. Report parts are the items stored on report server. You can even store them on a SharePoint server. Each report part created and stored has a unique id associated with it. With this functionality, one person can create some report item and keep it on server, which can be used by the other person in her/his report. The item can be a chart, image, table, matrix etc. After creating a report part, it can be changed by the creator or any other person depending upon the permissions available.

Publish Report Parts

Once the unique id is created for a report part, report server always uses the same id even though the report part is changed or replaced later. The dataset also gets saved along with the report part when you publish the report part on server. Off course, a dataset can be saved as a shared dataset.

You can publish the report parts with the default settings in a default location.

The default location is Report Parts folder on the Report Server. While publishing a report part, you must be connected to the server. If you are not already connected, it will prompt for server name.

It is a good practice to give proper name and description for the report part so that other people can easily find the required item.

An already created report part can be reused in other report. You can add multiple report parts at a time. Each time you open a report, Report Builder checks to see if any published report part has been updated or not and displays the following screen:

You can decide which report parts you need to update or reject. After accepting, the previous part will be replaced by the new one. You can also publish a report item as a new one instead of updating the existing one.

The check for updates can be turned off in the following manner. Click Options > Settings tab and clear Show updated to report parts in my report.

The above screen also shows the default folder taken for report parts.

Feedback:

Exploring New Features of SQL Server 2008 R2 with Excel 2010

SQL Server 2008 R2 November CTP is released and can be downloaded from here .

There is a lot of new functionality available with Business Intelligence with SQL Server 2008 R2. In this article, we will use Excel 2010 as a client for SQL Server Analysis Services to explore some new features.

To get started, we first need to install SQL server 2008 R2 and later Office 2010 to get the Gemini tab. ‘Gemini’ is the official name for SQL Server PowerPivot for Excel and SharePoint.

We will discuss creating pivot table with Excel 2010 using PowerPivot (working with SQL Server 2010 November CTP). After installing Office 2010 and PowerPivot, we get the following tab in Excel 2010.

I have used the sample database for SQL Server 2008 R2 November CTP available on codeplex

After clicking on PowerPivot window, select a SQL Server database and use database AdventureWorksDW2008R2.

From Table Import Wizard, select 3 tables namely DimDate, DimSalesTerritory and FactInternetSales as follows

You can even give them friendly names as shown. You will get following the Success screen after you click Finish.

The data is as shown

You can click on Pivot table and create a complete Pivot table.

Following table shows Countries in columns, months in rows and sum of Order quantity for calendar years 2007 and 2008.

I added a vertical slicer for SalesTerritoryGroup and the result is as shown:

Finally, I also added a PivotChart as shown below:

Feedback:

Update a Column with Random Numbers in SQL Server

In this blog post, I will show you how to update a database column with Random Numbers

Let’s create a sample table

DECLARE @TT table
(
ID smallint,
StudentID smallint,
DayAlloted smallint
)
-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1,  0);
INSERT INTO @TT VALUES ( 102, 2,  0);
INSERT INTO @TT VALUES ( 103, 3,  0);
INSERT INTO @TT VALUES ( 104, 4,  0);
INSERT INTO @TT VALUES ( 105, 5,  0);
INSERT INTO @TT VALUES ( 106, 6,  0);
INSERT INTO @TT VALUES ( 107, 7,  0);
INSERT INTO @TT VALUES ( 108, 8,  0);
INSERT INTO @TT VALUES ( 109, 9,  0);
INSERT INTO @TT VALUES ( 110, 10, 0);

Observe that the ‘DayAlloted’ column has 0. To allocate random numbers in that column, fire this query:

UPDATE @TT
SET DayAlloted = CONVERT(smallint, RAND(CHECKSUM(NEWID())) * 30)

OUTPUT

Feedback:

Convert Month Number to Month Name in SQL Server

One of my blog readers mailed me asking a simple way to convert a month number to month name. Here’s the simplest way in my opinion:

DECLARE @Mth smallint
SET @Mth = 11
SELECT DateName(mm,DATEADD(mm,@Mth,-1)) as [MonthName]

OUTPUT

Similarly if you want to list all the month names for a year using a T-SQL statement, you can do this:

SELECT Number + 1 as [MonthNumber],
DateName(mm,DATEADD(mm,Number,0)) as [MonthName]
FROM master..spt_values
WHERE Type = 'P' and Number < 12

OUTPUT

Feedback:

Executing a Stored Procedure at regular intervals using SQL Server Express Edition

SQL Server Express does not contain SQL Agent which can help us scheduling jobs. So to execute a stored procedure at regular intervals in SQL Express, we can use a Windows scheduler or Task Scheduler to execute a SQL Procedure using a SQLCMD.

I have covered Executing a Stored Procedure using SQLCMD, so I won’t be repeating the steps here. The only additional step is to create a batch file (Create a .txt and rename it to .bat) and add the commands to the batch file. Then use the Task Scheduler to execute this batch file at regular intervals

To see a practical example, like backing up a database at regular intervals using SQL Server Express , SQL Server MVP Jonathan Kehayias has covered these steps in an article over here called How to Automate Database Backups with SQL Server Express.

Feedback:

SQL Server 2008 R2 November CTP Now Available

The latest Community Technology Preview (CTP) for SQL Server 2008 R2 is now available

Some of the new capabilities added to SQL Server in this release are:

- improved support for virtualization through Hyper-V with Live Migration in Windows Server 2008 R2

- enhanced data compression with support for Unicode UCS-2.

- New visualization features and a Report Part Gallery to Report Builder 3.0

As mentioned on the site - "SQL Server 2008 R2 includes enhancements designed to help administrators centrally monitor and manage multiple database applications, instances or servers. In addition, it has enabled high-scale complex event-stream processing through SQL Server StreamInsight, and expanded business intelligence capabilities with SQL Server PowerPivot for Excel"

You can download Community Technology Preview of Microsoft SQL 2008 R2 over here

Feedback:

Executing a Stored Procedure using SQLCMD

Here’s how to execute a SQL Server Stored Procedure using SQLCMD. For demonstration purposes, I will be executing the ‘CustOrderHist’ Stored Procedure in the Northwind database. This procedure accepts the CustomerID and returns a list of orders placed by the Customer.

Open your Command Prompt. I have a named instance and use Windows Authentication, so I will be connecting to SQL Server using the following statement:

sqlcmd -S <ComputerName>\<InstanceName>

In my case, it is:    sqlcmd -S SUPROTIM-PC\SUPROTIM2

Now enter the following commands:

:Setvar CustomerID ALFKI
EXEC dbo.CustOrderHist $(CustomerID)
Go

The output is as shown below:

Feedback:

Programmatically Change the default path for SQL Server Database creation

The extended stored procedure "xp_instance_regwrite" helps you read/write from the registry. You can use this stored procedure to programmatically change the path where the data files for a new SQL Server database gets created by default. Here’s how to use this stored procedure

USE [master]
GO
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
REG_SZ,
N'D:\MyDatabases'
GO

EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
REG_SZ,
N'D:\MyDatabases'
GO 

Note: After running this query, a folder called ‘MyDatabases’ should exist in D drive before a new database is created, else you will get an error.

Feedback:

Some Changes in the latest CTP of SQL Azure Service

Some new features have been introduced to the latest October CTP 2 of SQL Azure Service. The updated CTP still uses previous accounts, so you do not need the new invitation code again.

Following are some changes made to the new CTP:

With SQL Azure Services, you can now specify the maximum size for a database at the time of creation.

In addition, you now need to specify a list of IP addresses using the firewall feature which will connect to SQL Azure.

Once the IP addresses are specified and firewall settings in place, you can give the following command

CREATE DATABASE MyDB (MAXSIZE = 1GB)

You can read more on the list of changes over here

Feedback:

Insert Rows in between a SQL Server Table with Identity Column

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!

Feedback:

Create a SSRS Report based on a Cube and Deploy it

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 (:)

Feedback:

Overview of SQL Server Reporting Services (SSRS)

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

Feedback:

Identify Valid Numeric Expressions in SQL Server

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:

Feedback: