SQL Server & Data Archives - Page 5 of 5

This is the function in SQL server to get the last day for each month until the day that you define in a variable which is in this case is “@genDate”.

I used this function to iterate some stored procedure every last day in the month

DECLARE @genDate datetime
SET @genDate = '1/31/2006'

WHILE @genDate <= getdate()
	BEGIN
		PRINT @genDate
		SET @genDate = DateAdd(month,1,@genDate)

		--this is used to get the last day for each month
		SET @genDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @genDate)+1, 0))
	END

NOTE: “-1” in this statement means that it’s minus one day from the beginning of the month which gives you the last day for each month
SET @genDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @genDate)+1, 0))

Failed to enable constraints, one or more rows contain values violating non null, unique or foreign-key constraints

By fransiscuss

On August 29, 2008

In C#, SQL Server & Data, VB.NET

I found this error on my project. Well i spent around one hour to figure out this problem. People might think that this is some silly error message.

The error message i got is “Failed to enable constraints, one or more rows contain values violating non null, unique or foreign-key constraints”.

this is caused by my stored procedure which is

SELECT e.eventid,e.event,e.eventdate,i.email,u.username
,u.firstname,u.surname,i.senttime,i.readtime,i.respond
FROM invitefriends i
inner join users u ON i.franchiseeid=u.userid
inner join events e ON i.eventid = e.eventid
WHERE i.franchiseeid is not null
    and ( (@EventID IS NULL) or (e.eventid=@EventID) )
ORDER BY e.eventdate DESC
GO

Since the query is returning multiple rows with the same eventid and the primary key in my datatable is eventid then it caused the error.

There are two workaround to this problem:

by using identity from your own table or you can generate it from your query and you need to regenerate your datatable and make sure check the primary key in datatable since it’s not automatically changed for you.

You can also relax the constraint by removing the Primary key on the DataTable

Cursor Tutorial in SQL Server

By fransiscuss

On August 29, 2008

In SQL Server & Data

This is the sample of cursor solutions, i will add the explanation a bit later

CREATE PROCEDURE AutoSumEventMembers
AS
SET NOCOUNT ON;

/*This stored procedure is used to update the number of players played on the particular event*/
/*Event selected only based on the completed one or the one where the result has been uploaded*/

/*Declare the variable  that is used to store the value coming from cursor*/
DECLARE @EventID int
DECLARE @TotalResult int

/*Declare the cursor for that particular select statement*/
/*You can fetch more that one column into cursor*/
/*You need to make sure that you have the same number of variables when you start fetching it*/
/*Make sure the variable has the same type as the column as well*/
DECLARE Cur_Events CURSOR
FOR
SELECT
EventID
FROM
Events
WHERE
Finalised = 1

/*Open the cursor*/
OPEN  Cur_Events

/*Start fetching from the cursor and dump it into the variable*/
FETCH     Cur_Events
INTO     @EventID

/*Iterating through the cursor until the end of the cursor*/
WHILE @@Fetch_Status = 0
BEGIN
SET @TotalResult = 0

/*Count how many results have been uploaded for particular event*/
SELECT @TotalResult = COUNT(*) FROM EventResults WHERE EventID = @EventID

/*Update the record*/
UPDATE Events SET NumberOfResults = @TotalResult WHERE EventID = @EventID

/*Fetch the next cursor*/
FETCH     Cur_Events
INTO     @EventID
END

/* Clean up - deallocate and close the cursors.*/
CLOSE     Cur_Events
DEALLOCATE Cur_Events

Technical articles on AI agents, Azure, .NET, architecture, and EV charging systems from Sydney.

Category: SQL Server & Data

Getting the last day for each month in SQL server

Failed to enable constraints, one or more rows contain values violating non null, unique or foreign-key constraints

Cursor Tutorial in SQL Server