Friday, May 10, 2013

Script to Find Foreign Key Columns Missing Index

The below query will retreive all foreign key columns which does not have an Index.
SELECT        s.Name + '.' + t.Name AS TableName,
            c.Name AS ColumnName,
            OBJECT_NAME(constraint_object_id) AS FKName
FROM        sys.foreign_key_columns fkc
LEFT JOIN    sys.index_columns ic
            ON    fkc.parent_object_id = ic.object_id
            AND    fkc.parent_column_id = ic.column_id
LEFT JOIN    sys.columns c
            ON fkc.parent_column_id = c.column_id
            AND fkc.parent_object_id = c.object_id
LEFT JOIN sys.tables t
            ON fkc.parent_object_id = t.object_id
LEFT JOIN sys.schemas s
            ON t.schema_id = s.schema_id
WHERE        ic.object_id IS NULL 
ORDER BY    TableName, ColumnName

Thursday, June 7, 2012

Disable Button While Processing a Request in Asp.Net

I have been looking into a problem of disabling a button when user clicked to prevent the multiple clicks. Finally I got a solution.

I tried to disable the button in client side by writing a piece of javascript code, but it doesnt work. If we want this in client side, we need to do all the validations and all other client side stuffs manually.

This is a tricky logic, if you have eny better idea please share.
Add the following html code.

<asp:Button ID="btnSearch" CssClass="button" runat="server" Text="Search" 

onclick="btnSearch_Click" />

<div style="display:none">

<asp:Button ID="btnSearch1" CssClass="button" runat="server" Text="Search" 

onclick="btnSearch1_Click" />


Add the following javascript in your aspx page
<script type= "text/javascript">

function postSearchData() {

jQuery(document).ready(function () {





Add the Buttons event handlers
protected void btnSearch_Click(object sender, EventArgs e)


btnSearch.Enabled = false;

Page.ClientScript.RegisterClientScriptBlock(typeof(Label), "postback", "postSearchData();", true); 


protected void btnSearch1_Click(object sender, EventArgs e)


System.Threading.Thread.Sleep(5000);// remove this line
// do your operations here.
btnSearch.Enabled = true;


In the above example btnSearch is associateed with all validation controls and it does the validations. When user clicks on it it cause a postback and disable it, a piece of javascript code is written to the page after the postback to automatically fire the btnSearch1 button's click event. btnSearch1 will remain always hidden.

Hope this will help you, do not hesitate to share your comments.

Sunday, November 27, 2011

FILESTREAM in Sql Server 2008 and Example

FILESTREAM in SQL Server 2008

SQL Server 2008 introduces the new FILESTREAM attribute, which can be applied to the varbinary(max) data type. Using FILESTREAM, you can exceed the 2GB limit on stored values and take advantage of relational handling of files via SQL Server, while actually storing the files on the file system. BACKUP and RESTORE operations maintain both the database data as well as the files saved on the file system, thus handling end-to-end data recoverability for applications that store both structured and unstructured data. FILESTREAM marries the transactional consistency capabilities of SQL Server with the performance advantages of NT file system streaming.

SQL Server can manage the contents of the FILESTREAM containers on the file system for you and control access to the files, while the NT File System (NTFS) provides efficient file streaming and file system transaction support. This combination of SQL Server and NTFS functionality provides several advantages when dealing with LOB data, including increased efficiency, manageability, and concurrency.

Check whether the filestream property is enabled or not. To do this executes the following query.
SELECT SERVERPROPERTY('FilestreamShareName') ShareName,
SERVERPROPERTY('FilestreamEffectiveLevel') EffectiveLevel,
SERVERPROPERTY('FilestreamConfiguredLevel') ConfiguredLevel

If it is not enable use sp_configure procedure to enable it.
EXEC sp_ configure 'filestream access level', 2;

FILESTREAM Access Levels:

0. Disable

1. Access via T-SQL only

2. Access via T-SQL only and file system

Once you’ve enabled FILESTREAM support on your SQL Server instance, you have to create a SQL Server file group with the CONTAINS FILESTREAM option. This file group is where SQL Server will store FILESTREAM BLOB files.

To enable FileStream to an existing database execute the following query

-- to add filestream to an existing database
NAME = 'FileStreamdocuments'
FILENAME = 'E:\Jobin\sql\jobin_documents'
TO FILEGROUP DocumentFileStreamGroup;

FILESTREAM Enabled Tables

Once you’ve enabled FILESTREAM on the server instance and created a FILESTREAM filegroup, you’re ready to create FILESTREAM-enabled tables. FILESTREAM storage is accessed by creating a varbinary (max) column in a table with the FILESTREAM attribute. The FILESTREAM-enabled table must also have a uniqueidentifier column with a ROWGUIDCOL attribute.

Name VARCHAR(50),

Insert some records into the table.
INSERT INTO Employee VALUES(NEWID(),'Jobin',CAST('jobin john' AS VARBINARY(MAX)));
INSERT INTO Employee VALUES(NEWID(),'Ajith',CAST('Software Engineer' AS VARBINARY(MAX)));

Select the inserted values
SELECT * FROM Employee
SELECT EmpId,Name,CAST(Document AS VARCHAR) AS Document FROM Employee

Monday, October 24, 2011

Introducing Microsoft Visual Studio LightSwitch 2011

Microsoft Visual Studio LightSwitch 2011

Microsoft Visual Studio LightSwitch is a simplified self-service development tool that enables you to build business applications quickly and easily for the desktop and cloud or it is a development tool that helps you build business applications quickly. LightSwitch provides a simplified development environment that enables you to concentrate on the business logic instead of the application infrastructure.

LightSwitch heps us to develop business applications simply and fastly. LightSwitch is optimized around making data and screens, this would reduce the development effort.

Microsoft Visual Studio LightSwitch uses a model-centric architecture for defining, building, and executing a LightSwitch application.

Monday, October 17, 2011

Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property

Fix JSON serialization error by setting maxJsonLength.

This error is occured when serialize an object in JSON string by JavaScriptSerializer class, it will throw this exception if object contains too many records(data). We can fix this issue by setting maxJsonLength in web.config file.
Exception type: InvalidOperationException
Exception message: Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.

                <jsonSerialization maxJsonLength="50000000"/>
 </configuration> .

Tuesday, October 4, 2011

Software Development Tips and Tricks & Tutorials

Software Development is the process of creating or developing a quality application which will help the business to grow.

Software development process includes the following steps.
  • Understanding the business or Domain : Now you may have a question that "Why should I know the business/domain?. As a programmer my job is only writing codes and developing the application. then why?". The answer is very simple, if you are aware on the domain, I would say it is definitely an added advantage to the software development process.
  • Understanding requirements: Understanding client requirement is one of the important factor in software development.
  • Design: Once we completed the requirement analysis, next we need to design the application. Mostly its done by creating a high level design document and low level design document.
  • Development: Develop the application based on the design document and functional requirement document. While do coding ensure all business validations and requirements are under coverage.
  • Testing: Testing is an investigation conducted to provide the information about the quality of the product/application.
  • Implementation: In this step, we will be deploying the final solution/application which is tested and completed on to the server. While deploying/releasing we will also develop a release document which will help the end user to deploy the application in their enviornment.
  • Maintanance: Maintanance is the modification of a software product after delivery to correct faults or to improve performance.
I hope now you are clear on the Software Development Life Cycle (SDLC) mentioned above.

Here are some Tips and Tricks that might be improve the quality of software development
  • Love your profession
  • Simplify the complex logic by splitting.
  • Be an innovator and expert in technology
  • Be a good learner
  • Be aware of latest technologies
  • Learn the process
  • Minimum amount of sleep should be 6 hours
  • Be social
  • Enjoy holidays and free time
  • Involve in any games during work hours
  • Think positively.