Disable F1 key in Excel Sheet

Yesterday, I was working on excel sheet and I was so frustrated with the function key F1 Key. Generally I use F2 key to edit the contents of the Cell. F1 & F2 are so close together, so accidentally I pressed F1 instead of F2 to edit the cell. All most half of my time went off closing the help window. It is really frustrating.

As you know F1 is the key that would help you, if you are running into problems or need something. But in this case I am running into problems because of F1, Now who is going to help me??

So I decided, either I should remove the F1 key from keyboard or write a program to disable the F1 key. I am a programmer so I thought I should write a program to save my life.

I hope the below code would be useful to you guys. In just five minutes you can disable your F1 key.

1. Open Excel sheet
2. Can you see below icons in your excel tool bar? If not then right click on the tool bar and select Visual Basic 3. Click on Visual Basic Editor Icon
4. Under the Microsoft Excel Objects, double click on ThisWorkbook.
5. Add the below code
Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
End Sub
6. Save the excel sheet and save your life. That’s it.

Very Common SQL Server Errors And Resolutions Part 2

In this article I would like to list out most common errors and their solutions that occur in SQL Server. This article will also you tell how to reproduce them.

Error: 1
Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.
Or
Msg 536, Level 16, State 1, Line 2
Invalid length parameter passed to the left function.

Cause:
You will get this error when you try to select some string based on negative value of length parameter in LEFT or SUBSTRING functions.

How to re-produce:
DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut, CHARINDEX('Z', @inPut) - 1)
Or
DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut, -10)

Solution:
Make use SIGN user defined function (UDF) to convert the sign to suppress the error.

DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut,(case SIGN(CHARINDEX('Z', @inPut)) WHEN -1 THEN -1 else 1 end ) - 1)


Error:2
Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value ',' to data type int.

Cause:
You will get this error when you try to convert INT columns to string or VARCHAR.
Another reason could be doing some operations with INT column data type to look like string or VARCHAR.

How to re-produce:
SELECT 1 + ',' + 2 + ',' + 3 AS COL

Solution:

Use CAST or CONVERT function to convert INT data type values into VARCHAR

SELECT (CAST(1 AS VARCHAR(10)) + ',' + CAST(2 AS VARCHAR(10)) + ',' + CAST(3 AS VARCHAR(10))) as col


Error:3
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table Table_name. Cannot perform SET operation for table Table_Name.

Cause:
At any point in time you can have only one set whose IDENTITY_INSERT ON.
You will get this error when you try to set IDENTITY_INSERT ON for a second table when IDENITY_INSERT is already on for the First Table.

How to re-produce:
create table t1(T1_ID INT IDENTITY(1,1))
SET IDENTITY_INSERT T1 ON

create table t2(T2_ID INT IDENTITY(1,1))
SET IDENTITY_INSERT T2 ON


Solution:
When you are using IDENTITY_INSERT then always use this in batch mode.

SET IDENTITY_INSERT T1 ON
GO
-- do you work
GO
SET IDENTITY_INSERT T1 OFF
GO


Error:4
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'T1' that match the referencing column list in the foreign key 'FK_T2'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Cause:
You are trying to create a foreign key column and referencing a column in parent table where there is no primary key or unique key defined.

How to re-produce:
create table t1(T1_ID INT IDENTITY(1,1))

create table t2(T2_ID INT IDENTITY(1,1))

ALTER TABLE T2 ADD CONSTRAINT FK_T2 FOREIGN KEY(T2_ID) REFERENCES T1(T1_ID)

Solution:
Make sure you have Primary key or unique key defined on the column that you are going to reference in Foreign Key Constraint.

Error: 5
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'T2', because it does not exist or you do not have permission.

Cause:
You are trying to drop a table where you are not the owner of the table or table doesn’t exist in your schema or database.

How to re-produce:
DROP TABLE T2

Solution:
Before you perform any DDL operation make sure you have the permissions on the object.

Undocumented feature to concatenate strings from a column into a single row

There are several ways to create one column with comma separated values from multiple rows. Today, I would like to show you, how to achieve this with very simple query using XML features of SQL Server.
set nocount on
-- Decalare Table Variable
Declare @t Table (c1 int)
-- Insert data into Table
Insert @t
Select 10 Union All
Select 20 Union All
Select 30 Union All
Select 40 Union All
Select 50 Union All
Select 60
-- Select data from table (in Rows)
SELECT C1 AS input FROM @t
-- Select data from table using XML(As One single column)
SELECT (
SELECT CAST(C1 AS VARCHAR(20)) + ','
FROM @t
FOR XML PATH ('')
) as Comma_Separated_String
set nocount off

-- Query Output

input
-----------
10
20
30
40
50
60

Comma_Separated_String
-------------------------------
10,20,30,40,50,60,

60 Seconds with Sugeshkumar Rajendran

60 seconds with Sugeshkumar Rajendran:

Adding the TAG MVP to my name, distinguishes me from other technologists near me.

VJ caught up with Sugeshkumar Rajendran, MVP in SQL Server, to get his view on the Microsoft Most Valuable Professional Program(MVP).

VJ: Please tell us about yourself.
Sugesh: I am Sugeshkumar Rajendran, a SQL Server MVP. I have been in IT industry for almost 6 years now. After completing Bachelors in Engineering from one of the reputed institutions in Chennai, started my career in IT as a Junior Systems DBA working for one of the Largest ERP Company as a client. I have worked with various clients as PeopleSoft, Windows and SQL Server Administrator. Currently, I am working as a Full-Time SQL Server DBA for one of the largest financial service providers in the globe.

VJ: What motivated you to become an MVP
Sugesh: Becoming a MVP has always been a dream for me after taking role of a Senior DBA in an IT Services company in India. I started a Blog, website to provide useful information about SQL server and Administration to User community. MVPs are always treated with respect in the community for the knowledge they posses and give to others. I gain and share knowledge by being a MVP.

VJ: Please share with us your journey of becoming an MVP.
Sugesh: It’s a great pleasure being part of this great team of countable MVPs around the globe. It gives us chance to communicate with experts of technologies, answer to questions, and learn new things every day. Adding the TAG MVP to my name, distinguishes me from other technologists near me

VJ: Please tell us about your MVP Summit experience
Sugesh: Attending MVP Summit was a great experience to me. It gave me a chance to know what’s going in the Technology; meeting experts of different technologies, product groups, the session were very useful and informative.

VJ: Any message to the technologists

Sugesh: Innovation is the Key. Think beyond what is possible. Value your time. There is no use in building or enhancing a product or a tool that is already available. Develop something new. That’s where you can prove you are worth more than what others are.

Tuning Replication for High Performance from SQL Server MVP Hilary Cotter

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

SQL Server MVP Hilary Cotter will explain how to maximize your replication solution for throughput and minimal latency. This is part of SQL Server User Group in New York.

Please Join New York City Microsoft SQL Server User Group and attend the session.

Session Details:
Topic : Tuning Replication for High Performance
Date : 28-MAY-2009
Time : 6:00 PM EST - 8:00 PM EST.

How To: SQL Server Management Studio

Hi
This page contains links to various Tips and Tricks on working with SQL Server Management Studio.

How to Debug TSQL Programs in SQL Server
How to Find out the dependencies using SQL Server Management Studio
Generate DDL of a Table Using SQL Server Management Studio
Intellisense update/refresh on schema changes - SQL Server