Jag Kandasamy

Wednesday, March 5, 2014

How to Remove Special Characters Using SQL

Remove Special Characters Using TSQL

CREATE FUNCTION [dbo].[RemoveSpecialCharacters](@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

Declare @KeepValues as varchar(50)

SET @StrValue = LTRIM(RTRIM(@StrValue))

SET @KeepValues = '%[^a-z0-9]%'

WHILE PATINDEX(@KeepValues, @StrValue) > 0
SET @StrValue = STUFF(@StrValue, PATINDEX(@KeepValues, @StrValue), 1, '')

Return @StrValue
END

Result:

SELECT DBO.RemoveSpecialCharacters('Welcome %To $Axil - 007')

WelcomeToAxil007

Remove / Trim Leading Zeros - SQL Server

TSQL - Removing leading zeros from varchar value:

How to remove leading zeros from a number using TSQL

ALTER FUNCTION [dbo].[RemoveLeadingZero] (@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
SET @StrValue = ISNULL(@StrValue,'')

SET @StrValue = SUBSTRING(@StrValue, PATINDEX('%[^0]%', @StrValue+'.'), LEN(@StrValue))

Return @StrValue
END

Result:

SELECT DBO.RemoveLeadingZero('00050407')

50407

SELECT DBO.RemoveLeadingZero('00050470')

50470

SELECT DBO.RemoveLeadingZero('000.5430')

.5430

Monday, August 12, 2013

SQL - Get Date From Week Number & Year

Convert week number back into the date range for that week using T-SQL

DECLARE @Week INT
DECLARE @Year NVARCHAR(4);

SELECT @Year = 2014, @Week = 40

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 6, '1/1/' + @Year) + (@Week-1), 6) AS StartOfWeek;
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 5, '1/1/' + @Year) + (@Week-1), 5) AS EndOfWeek;

Wednesday, March 13, 2013

List of Country

Tuesday, February 5, 2013

MS-SQL Shrink Database - DBCC SHRINKFILE

DBCC SHRINKFILE

Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

DBCC SHRINKFILE applies to the files in the current database.

DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

USE AdventureWorks2012;

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AdventureWorks2012

SET RECOVERY SIMPLE WITH NO_WAIT;

-- Shrink the data file.

DBCC SHRINKFILE (AdventureWorks2012,10);

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks2012_Log, 1);

-- Reset the database recovery model.

ALTER DATABASE AdventureWorks2012

SET RECOVERY FULL WITH NO_WAIT;

How to check the status / progress of Database Shrink Operation - DBCC SHRINKFILE

Here is the command to show the current status SHRINKFILE Operation

SELECT PERCENT_COMPLETE FROM SYS.DM_EXEC_REQUESTS

WHERE COMMAND = 'DBCCFILESCOMPACT'

Thursday, January 17, 2013

MicroSoft SQL Server - Clear the transaction logs

Shrinks the size of the log files in the specified database.

USE DBNAME

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE DBName SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (DBName_Log, 1);

-- Reset the database recovery model.

ALTER DATABASE DBName SET RECOVERY FULL;

Jagadeesh

M: +61 425359215 | H: +61 296881489

Friday, September 28, 2012

RadListBox - Multi Select With Select All Option

This article will show you how to have an item which checks/unchecks all items.

Select All OR Un Select All - sing Rad Combo / List Box Control

The aspx File

function OnClientItemChecked(sender, eventArgs) {

var lstBoxControl;

lstBoxControl = $find(sender.get_id());

var items = lstBoxControl.get_items();

if (eventArgs.get_item().get_index() == 0) {

var firstIndex = eventArgs.get_item().get_checked();

for (var i = 0; i < lstBoxControl.get_items().get_count(); i++) {

items.getItem(i).set_checked(firstIndex);

}

else {

items.getItem(0).set_checked(false);

}

</script>

<telerik:RadListBox AppendDataBoundItems="true" TabIndex="1" Width="200px" CheckBoxes="true" ID="lstClassificationTypes" DataSourceID="dataClassificationTypes" runat="server" DataTextField="SelectionName" DataValueField="SelectionId" OnClientItemChecked="OnClientItemChecked">

<Items>

<telerik:RadListBoxItem Text="Select / UnSelect All" Value=0 BackColor=Gray Font-Bold="true" ForeColor=White />

</Items>

</telerik:RadListBox>

The aspx.cs File

Find Selected Item

protected void btnGenerate_Click(object sender, EventArgs e)

{

StringBuilder sb = new StringBuilder();

IList<RadListBoxItem> collection = ((RadListBox)fview.FindControl("lstClassificationTypes")).CheckedItems;

foreach (RadListBoxItem item in collection)

{

// Your Code Here >> item.Value

}

If you need additional assistance, do not hesitate to contact me.@ tkjagadheesh@gmail.com

Regards,

JAG

Tags: RadListBox, C#, CheckBox, Java Script, Multiple Items, ASP.NET, AJAX, Rad Controls check all, Un check all, Select all, Multi Select Combo, MultiSelect List Box

TSQL / SQL Calculating Cumulative Total

Calculating a moving cumulative total using TSQL statement.

The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window.

You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

SELECT BusinessEntityID, TerritoryID

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

),1) AS MovingAvg

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5

ORDER BY TerritoryID,SalesYear;

Jagadeesan Kandasamy

Wednesday, May 30, 2012

MS Excel Transpose Rows and Columns

Tags:

Convert Row to a Column in Excel

Excel rows to columns

Switch Excel Columns to Rows

MS Excel Change Row into Column

Convert Rows to Columns and Columns to Rows in Excel

How to Convert Rows to Columns in Excel?

How to Convert Columns to Rows in Excel?

Transpose Excel Rows and Columns

MS Excel transpose data from rows to columns OR columns to rows

Step 1:

Select the table or colums & rows you wish to flip.

Step 2:

Copy the entire table and click Pate >> Paste Special.

Step 3:

Select the Transpose Check box.

Step 5:

All done here is the result..

Posted By

Jagadeesan Kandasamy

Friday, January 20, 2012

First date of a week using SQL

How to get the first date / day in a week using SQL Server.?

The example gets the date of the first day in the week that GETDATE() is in.

SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)

It's yery simple and quite elegant, Happy Coding!! :)

Tuesday, November 1, 2011

SQL Server 2008 - Error on Restore Database

What's the Issue?

I've taken backup of SQL Server 2008 Database on server.

BACKUP DATABASE DBName TO DISK='D:\DBBackup\FileName.bak' with FORMAT

When I'm trying to restore that database, it gives me following error.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

The media family on device is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

What's the Solution?

1) Ensure Backup copy of database is good and files copied properly.

2) Run "Select @@Version" and verify the version. (Though I'm using SQL Server 2008 express, it is showing that i am using SQL Server 2005)

3) Run the SQL Server 2008 R2 Express install program again, and during Name Instance configuration, specify a Name Instance with a different name. Example: MachineName\instancename after installation, logged in with New Instance name created.

Create a new database with a table then try to restore again, It will work this time.

Jagadeesan Kandasamy

tkjagadheesh@hotmail.com

Sunday, August 28, 2011

JavaScript to Select / UnSelect Multiple Checkbox in ASP.Net

// java script toggles the state of checkbox between selected and not selected.

function toggleCheckBoxes(e)

{

var isChecked = e.checked;

var grid = document.getElementById('gridOpportunityDetails');

var inputElements = grid.getElementsByTagName("input");

for (i = 0; i <= inputElements.length - 1; i++)

{

if (inputElements[i].type == "checkbox")

inputElements[i].checked = isChecked;

}

</script>

Code On Asp.Net Page:

<asp:GridView ID="gridOpportunityDetails" DataSource='<%# GetOpportunityDetails() %>' >

<asp:TemplateField ItemStyle-CssClass="dataGridText" ItemStyle-VerticalAlign="top" HeaderStyle-Width="2%" HeaderStyle-BorderStyle="solid">

</asp:TemplateField>

</Columns>

</asp:GridView>

Sunday, June 19, 2011

How to Import Data into SQL Server From Access / Excel

Import data from Access / Excel into SQL Server using TSQL:

Use the following SQL statements to import the data

--FROM EXCEL

EXEC sp_addlinkedserver @server = N'XLSERVER', @srvproduct=N'ExcelData', @provider=N'Microsoft.Jet.OLEDB.4.0',

@datasrc=N'C:\Manipulation\DCNetworkVendorData.xls', @provstr=N'EXCEL 8.0' ;

SELECT * FROM XLSERVER...[TableName]

-- FROM XLSX

--Don't be supprised if this doesn't work for Excel/Access using the latest 'Microsoft.ACE.OLEDB.12.0' driver (which should be called version 14 for Office 2010).

EXEC sp_addlinkedserver @server = N'XLSERVER', @srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',

@datasrc=N'C:\Manipulation\DCNetworkVendorData.xlsx', @provstr=N'EXCEL 12.0' ;

SELECT * FROM XLSERVER...[TableName]

-- FROM MS ACCESS

EXEC sp_addlinkedserver @server = N'AccessServer', @provider = N'Microsoft.Jet.OLEDB.4.0',

@srvproduct = N'OLE DB Provider for Jet', @datasrc = N'C:\Manipulation\AccessDB.mdb';

SELECT * FROM AccessServer...[TableName]

Tuesday, March 29, 2011

WPF - Windows Presentation Foundation

WPF Overview:

The Windows Presentation Foundation is Microsoft's next generation UI framework to create applications with a rich user experience. It is part of the .NET framework 3.0 and higher.

WPF combines application UIs, 2D graphics, 3D graphics, documents and multimedia into one single framework. Its vector based rendering engine uses hardware acceleration of modern graphic cards. This makes the UI faster, scalable and resolution independent.

The followinig illustration gives you an overview of the main new features of WPF

Separation of Appearance and Behavior

WPF separates the appearance of an user interface from its behavior. The appearance is generally specified in the Extensible Application Markup Language (XAML), the behavior is implemented in a managed programming language like C# or Visual Basic. The two parts are tied together by databinding, events and commands. The separation of appearance and behavior brings the following benefits:

Appearance and behaviour are loosely coupled
Designers and developers can work on separate models.
Graphical design tools can work on simple XML documents instead of parsing code.

Resolution independence

All measures in WPF are logical units - not pixels. A logical unit is a 1/96 of an inch. If you increase the resolution of your screen, the user interface stays the same size - if just gets crispier. Since WPF builds on a vector based rendering engine it's incredibly easy to build scaleable user interfaces.

Built-In Support for Graphics and Animation :

WPF applications as being rendered within DirectX environment, it has major support of graphics and animation capabilities. A separate sets of classes are there which specifically deals with animation effects and graphics. The graphics that you draw over the screen is also Vector based and are object oriented. That means, when you draw a rectangle in WPF application, you can easily remove that from the screen as rectangle is actually an object which you always have hold on. On traditional Windows based application, once you draw a rectangle, you cant select that individually. Thus programming approach in case of WPF is completely different and more sophisticated than traditional graphics approach. We will discuss graphics and animation in more detail in later section of the article.

Redefine Styles and Control Templates :

In addition to graphics and animation capabilities, WPF also comes with a huge flexibility to define the styles and ControlTemplates. Style based technique as you might come across with CSS are a set of definitions which defines how the controls will look like when it is rendered on the screen. In case of traditional windows applications, styles are tightly coupled with each controls, so that you need to define color, style etc for each individual control to make it look differently. In case of WPF, Styles are completely separated from the UIElement. Once you define a style, you can change the look and feel of any control by just putting the style on the element.

Most of the UIElements that we generally deal with is actually made using more than one individual elements. WPF introduces a new concept of Templates, which you might use to redefine the whole control itself. Say for instance, you have a CheckBox, which has a Rectangle in it and a ContentPresenter (one where the caption of the TextBox appears). Thus you can redefine your checkbox and put a ToggleButton inside it, so that the check will appear on the ToggleButton rather than on the Rectangle. This is very interesting. We will look into more detail on Styles and ControlTemplates in later section of the article.

Advantages

1 WPF has the ability to separate UI from logic effectively.

2 WPF has an inbuilt storyboarding feature and animation models.

3 Data binding is very much better than with the WinForms application.

4 WPF allows you to handle large data sets because of it has a built-in 'user interface virtualisation' feature.

5 WPF offers data and control templates that provide flexible modelling of UI on data models.

6 WPF supports 3D graphics to make UIs look really special.

7 WPF supports various types of media such as video, 3D content and animations.

8 Even if Visual Studio designer is not available, you can code in XAML (Extensible Application Mark-up Language).

9 WPF has the ability to use business objects in UI declaratively. In other words, a developer can build and package the business objects in an assembly and then one can instantiate the instances of them in XAML and bind them to controls directly. Not a single line of code is required for this at UI.

10 WPF enables programmers to develop much of the application in a completely declarative model, using XAML.

11 WPF has far more effective control creation and reusability features that provide greater flexibility to developers in building UIs.

WPF Architecture

For every new technology, it is very essential to have clear idea about its architecture. So before beginning your application you must grab a few concepts. If you dont like to know WPF in detail, please skip this section. As mentioned earlier, WPF is actually a set of assemblies that build up the entire framework. These assemblies can be categorized as

Managed Layer
UnManaged Layer
Core API

Managed Layer : Managed layer of WPF is built using a number of assemblies. These assemblies build up the WPF framework, communicates with lower level unmanaged API to render its content. The few assemblies that comprise the WPF framework are :

PresentationFramework.dll : Creates the top level elements like layout panels, controls, windows, styles etc.
PresentationCore.dll : It holds base types such as UIElement, Visual from which all shapes and controls are Derived in PresentationFramework.dll.
WindowsBase.dll : They hold even more basic elements which are capable to be used outside the WPF environment like Dispatcher object, Dependency Objects. I will discuss each of them later.

Unmanaged Layer (milcore.dll): The unmanaged layer of WPF is called milcore or Media Integration Library Core. It basically translates the WPF higher level objects like layout panels, buttons, animation etc into textures that Direct3D expects. It is the main rendering engine of WPF.

WindowsCodecs.dll : This is another low level API which is used for imaging support in WPF applications. WindowsCodecs.dll comprises of a number of codecs which encodes / decodes images into vector graphics that would be rendered into WPF screen.

Direct3D : It is the low level API in which the graphics of WPF is rendered.

User32 : It is the primary core api which every program uses. It actually manages memory and process separation.

GDI & Device Drivers : GDI and Device Drivers are specific to the operating system which is also used from the application to access low level APIs.

WPF architecture

-- Jagadeesan Kandasamy