Disable Triggers

Last time, I had some calculation problems in one of my tables, and I needed to make a simple batch Update on the table but the following error was loading…

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

After several trial and errors, I finally found the problem: it was that I had a trigger on the table and it didn’t let me batch Update. After googling i found that you can disable any trigger using the following code in you SQL Statement:

DISABLE TRIGGER triggername ON tablename

add batch update or batch insert command;

ENABLE TRIGGER triggername ON tablename

And now the batch Update worked flawlessly. Hope this works for you too.

(PS. It’s important to insert a semi-colon (;) after your update or insert command, as it would give an error and wouldn’t re-enable the Trigger.)

There is already an open DataReader…

Sometimes, when I try to get data from the SQL Server the following exception is loaded:

“There is already an open DataReader associated with this Command which must be closed first.”

This occurs when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don’t close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection).

This is due to a change in the default setting for MARs (Multiple Active Result Sets).  By default, It used to be set as True but it was changed and was set to False by default post RC1.

So to remove this exception, you just need to change the following:

Get Month Name

In Sql Server to find the month from a date you use the following command:

Month(DateTime)

This will give you the month number. To get the month name, you need to use the next command:

DateName(month, DateTime)

Happy Programming.

Leading Caps in Crystal Reports

When designing a report in Crystal Reports, a common problem is the text to retrieve from the database. Sometimes the text is all in Capital Letters, or Small Letters, and a few times it’s ok.

Crystal Reports can only transform the text either in Small Caps or All Caps. To have the text written in Leading Caps you have to do the following…

Count Working Days

Sometimes people need to count the difference between two dates. In Sql Server 2000 and 2005 it’s not a problem. To count the difference between the days you van use the following statement:

DATEDIFF(hh, Date 1, Date 2, ) / 24

The difficulty comes when you want to count only the working days of the dates. Maybe not many developers have this problem… but i couldn’t find a solution. After searching i found this function…


CREATE FUNCTION dbo.GetWorkingDays
(