Archive

Archive for the ‘SQL Programming’ Category

Importing Excel to SQL: The value violated the integrity constraints

December 21, 2010 Comments off

Another interesting thing happened today when i tried to import the  data from Excel to SQL Database i got an error that the value violated the integrity constraints. The error message as like below.

Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Unspecified error”.
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column “Showtext” (52) on input “Destination Input” (42). The column status returned was: “The value violated the integrity constraints for the column.”.
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The “input “Destination Input” (42)” failed because error code 0xC020907D occurred, and the error row disposition on “input “Destination Input” (42)” specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Problem Cause

When you try to import the excel to sql using ssms ,ssms create the automatic sql script to create a table. in which few of the data types (bit,etc) will not allow nullable value into the column that is the reason it is causing the above error.

Solution 

In my case from my excel data there are many null values or the empty values are there so i need to change the column to allow null values. after that i try to import the data it worked fine for me.

Advertisements
Categories: SQL Programming

Saving Changes is not permitted,when Modified the table using SSMS

December 20, 2010 Comments off

When i tried to modified the table structure using SSMS in SQL 2008 SSMS is not allowing me to modify the table i got the warring message that,Saving Changes is not permitted.

 

This is the good thing SSMS  has the facility to prevent the changes on the table design. because using query analyzer in all the version if you want to modify the table structure  it will drop and recreate the table, if you have very huge table and you want to modify the table structure using the GUI it will take huge time to recreate the table. this will create a production outage too.

How to resolve this . Go to > Tools >Options>table and database designer > uncheck the prevent and saving table recreation.

The following  screenshot will give the clear idea.

Note :  By Default this option will be enabled so user will not be able to modify the table .Please do not uncheck this option on production environment  use script to modify them.

Categories: SQL Programming

How to Use Try.. Catch ?

August 25, 2010 Comments off

This post will help you to write Error Handling in T-SQL using Try..Catch block. earlier version we do not have Try..Catch Error handling mechanism where as we normally captured it from the error number function.

Let us see how we are ging to implement Try..Catch in T-SQL. we can use Try..catch in any of the T-SQL block.

Example 1 :
This is simple example of try..Catch block
BEGIN TRY 
SELECT 100/0
END TRY
BEGIN 
CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber,  ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
END CATCH

This will give you the Error number and the Line Number where the error occurred and the error message. apart from the above function few have few more functions as well . Please find the Details 

  • Error_Number ()   –  Will give you the Error Number
  • Error_Line()  – Will give you the Line number of the Error occurred
  • Error_Message()  – Will give the Exact error message
  • Error_Severity() – Will give the Severity
  • Error_State() –  will give the error state number
  • Error_Procedure –  will give the name of the procedure or trigger where the error occurred.

Now will see bit more detail of Try..Catch Block

Example 2

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 
TABLE [dbo].[product]([product] [int] NOT NULL, 
CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED (
[product]
ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]

GO
insert into product
select 1

Open new query Window and type the following T-SQL you can see the Primary key violation

BEGIN  TRANSACTION

BEGIN  TRY 
insert into Product
select 1
END TRY
BEGIN
CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity, 
ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure, 
ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

So we can now implement the Try.Catch to any of your T-SQL.

Categories: SQL Programming
%d bloggers like this: