Share this post

The case :

We have a table with this data :

Now, we want to have packages of 100 letters. So for example, for the Center 1, we want 65 packages of 100 letters and one package of 4 letters.

The table with the data is TempData

1. The first solution is to use a cursor :

declare @mod int
Set @mod = 100 --The number of letters per package

declare @nb int,
	@start int,
	@end int,
	@tot int,
	@rest int,
	@cpt int,
	@centre nvarchar(200)

DECLARE @Tbl TABLE
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  Centre nvarchar(200),
  NbStart int,
  NbEnd int,
  Total int,
  Remain int
)

declare curseur cursor for select Centre, NbTotal from TempData 
open curseur

fetch next from curseur into @centre, @nb
while @@fetch_status = 0
begin

	SET @tot = @nb
	SET @start = 1
	SET @end = case when @mod < @tot then @mod else @tot end
	SET @rest = case when @mod < @tot then @tot-@end else 0 end
	set @cpt = @rest % @mod
	
	if @cpt = 0
	begin
		Insert into @Tbl (Centre, NbStart, NbEnd, Total, Remain)
				select @centre, @start, @end, @tot, @rest
	end
	else
	begin
	while @cpt > 0
		BEGIN
		
			Insert into @Tbl (Centre, NbStart, NbEnd, Total, Remain)
				select @centre, @start, @end, @tot, @rest
			
			set @cpt = @rest % @mod
				
			SET @start = @end + 1
			SET @end = @end + @mod
			
			if @end > @tot 
			BEGIN
				SET @end = @tot
				SET @rest = 0
			END
			ELSE
				SET @rest = @tot-@end
		END
	end
	
fetch next from curseur into @centre, @nb

end

close curseur
deallocate curseur
	
select * from @Tbl

2. The most simple solution is to use the system table master..spt_values :

declare @mod int
Set @mod = 100 --The number of letters per package

if OBJECT_ID('tempdb..#temp') is not null drop table #temp

SELECT Centre, Nb, NbPage = Nb/@mod + case when Nb % @mod > 0 THEN 1 ELSE 0 END 
into #temp
from TempData  

SELECT  T.Centre, T.Nb, 
	ROW_NUMBER() over (PARTITION BY Centre ORDER BY Centre, NbPage) AS ParcelNumber, 
	NbPerParcel = CASE WHEN ROW_NUMBER() over (PARTITION BY Centre ORDER BY Centre, NbPage) * @mod > Nb THEN Nb % @mod ELSE @mod END
FROM    #temp t
CROSS APPLY
( SELECT  *
  FROM    master..spt_values m
  WHERE m.number <= T.NbPage AND m.number > 0 and m.Type = 'P'
) x

if OBJECT_ID('tempdb..#temp') is not null drop table #temp

The source used for this solution was there : http://www.sqlservercentral.com/Forums/Topic1040609-338-1.aspx

Share this post

This table function return all the dependencies for a table/column.

The source used is here : http://www.sqlusa.com/bestpractices2008/object-dependency/

-- =============================================
-- Description:	Find all the dependencies for an Entity/column
--		Parameter Entity : [Optional] the name of the table or view
--								the name must be passed like this : dbo.TableName
--		Parameter Column : [Optional] the name of the column
-- =============================================
CREATE Function [dbo].[udf_Dependecies] (@Entity varchar(max), @Column varchar(max))
RETURNS @retTable TABLE 
(
    ReferencingObject nvarchar(max) NULL,
    ReferencedObject nvarchar(max) NULL,
    ColumnName nvarchar(max) NULL,
    ReferencedObjectType nvarchar(10) NULL,
    ReferencingObjecType nvarchar(10) NULL
)
AS
BEGIN
INSERT INTO @retTable(ReferencingObject, ReferencedObject, ColumnName, ReferencedObjectType, ReferencingObjecType) 
	SELECT	ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
			ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name,
			ColumnName = c.name,
			ReferencedObjectType = o2.type,
			ReferencingObjecType = o1.type
	FROM   sys.sql_expression_dependencies ed
	INNER JOIN sys.objects o1 ON ed.referencing_id = o1.object_id
	INNER JOIN sys.objects o2 ON ed.referenced_id = o2.object_id
	INNER JOIN sys.sql_dependencies d ON ed.referencing_id = d.object_id AND d.referenced_major_id = ed.referenced_id 
	INNER JOIN sys.columns c ON c.object_id = ed.referenced_id AND d.referenced_minor_id = c.column_id
	WHERE	(IsNull(@Entity,'') = '' OR SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name = @Entity)
		AND	(IsNull(@Column,'') = '' OR c.name = @Column)
	ORDER  BY ReferencedObject, c.column_id

RETURN
END

Share this post

With a NumericUpDown control in silverlight, if we clear the value, the string empty is not recognized and the control keep the last value.

Here is a simple solution to solve this problem :

Make a custom control :

	using System;
	using System.Net;
	using System.Windows;
	using System.Windows.Controls;	

	namespace MySolution.Controls
	{
	    public class BetterNumericUpDown : NumericUpDown
	    {

	        public BetterNumericUpDown()
	            : base() { }

	        protected override double ParseValue(string text)
	        {
	            return base.ParseValue(string.IsNullOrEmpty(text) || string.IsNullOrEmpty(text) ? this.Minimum.ToString() : text);
	        }
	    }
	}

In your XAML file

	xmlns:customControls="clr-namespace:MySolution.Controls"

	<customControls:BetterNumericUpDown Grid.Row="0" Grid.Column="0" TabIndex="1" Height="24" Value="{Binding Path=MyValue, Mode=TwoWay}" DecimalPlaces="2" Maximum="999999999" Minimum="0" Width="225" HorizontalAlignment="Left" />

Share this post

When you send an email with the Mail Task in a SSIS, you cannot specify the port for the connection.

The solution is to use a script task and set the specific port. Here is an example to send an email in HTML and Text/plain with an attachment. I've used variables for the recipient, cc, subject, port, ...

    Try

            Dim oViewPlain As AlternateView
            Dim oViewHtml As AlternateView
            Dim oMessage As New MailMessage()

            oMessage.From = New MailAddress(Dts.Variables("User::varFromMailFile").Value.ToString())

            'Separator for mail adresses
            Dim sSep As String()
            ReDim sSep(0)
            sSep(0) = ";"

            'Subject
            oMessage.Subject = Dts.Variables("User::varSubjectMailFileAssogestione").Value.ToString()

            'Recipients
            If Not Dts.Variables("User::varToMailFile").Value.ToString().Equals("") Then
                Dim strTO As String = Dts.Variables("User::varToMailFile").Value.ToString()
                Dim oTO As String() = strTO.Split(sSep, StringSplitOptions.RemoveEmptyEntries)
                For Each oTO_MailAdress As String In oTO
                    oMessage.To.Add(oTO_MailAdress)
                Next
            End If

            'CC
            If Not Dts.Variables("User::varCCMailFile").Value.ToString().Equals("") Then
                Dim strCC As String = Dts.Variables("User::varCCMailFile").Value.ToString()
                Dim oCC As String() = strCC.Split(sSep, StringSplitOptions.RemoveEmptyEntries)
                For Each oCC_MailAdress As String In oCC
                    oMessage.CC.Add(oCC_MailAdress)
                Next
            End If

            'BCc
            If Not Dts.Variables("User::varBCCMailFile").Value.ToString().Equals("") Then
                Dim strBCC As String = Dts.Variables("User::varBCCMailFile").Value.ToString()
                Dim oBCC As String() = strBCC.Split(sSep, StringSplitOptions.RemoveEmptyEntries)
                For Each oBCC_MailAdress As String In oBCC
                    oMessage.Bcc.Add(oBCC_MailAdress)
                Next
            End If
			
            'Create an alternate view for your mail
            'Message type plain/text
            oViewPlain = AlternateView.CreateAlternateViewFromString("Hello," _
                            & Chr(10) & Chr(10) & "Here is the file " & strFile & "." _
                            & Chr(10) & Chr(10) & "------------------------------------------------------------------------" _
                            & Chr(10) & Chr(10) & "Yours sincerely." _
                            & Chr(10) & Chr(10) _
                            , Nothing, "text/plain")
			'Message in HTML
            oViewHtml = AlternateView.CreateAlternateViewFromString("" _
                            & "<p style=""font-family:Arial;font-size:12px;"">" _
                            & "Hello," _
                            & "<br /><br />Here is the file " & strFile & "." _
                            & "<br /><br />Yours sincerely.." _
                            & "</p><DIV>&nbsp;</DIV><BR>" _
                            , Nothing, "text/html")

            'Add the views to the message
            oMessage.AlternateViews.Add(oViewPlain)
            oMessage.AlternateViews.Add(oViewHtml)

            'Message priority : Hight = 2, Loaw = 1, Normal = 0
            oMessage.Priority = MailPriority.Normal
            oMessage.IsBodyHtml = True

            'SMTP Server
            Dim smtpServer As String = Dts.Connections("SMTP Connection Manager").ConnectionString.Replace("SmtpServer=", "").Substring(0, Dts.Connections("SMTP Connection Manager").ConnectionString.Replace("SmtpServer=", "").IndexOf(";"))
            Dim oSmtpMail As New SmtpClient(smtpServer)
			
			'Exchange Port
            oSmtpMail.Port = CInt(Dts.Variables("User::varExchangePort").Value.ToString())

			'Attachment
            oMessage.Attachments.Add(New Attachment(strFilePath))
            oSmtpMail.Send(oMessage)

			'Succes Result
            Dts.TaskResult = Dts.Results.Success

        Catch ex As Exception
            ' post the error message we got back.  This is the old error catch code that might work better with SSIS.
            Dts.Events.FireError(0, "Script Task Error - SendMail", ex.Message.ToString(), String.Empty, 0)
			
			'Failure result
            Dts.TaskResult = Dts.Results.Failure
        End Try
    End Sub

Share this post

When you execute a SSIS and a script task failed, you have only the message indicating the script task failed.

To retrieve the error message, here is the solution. Use a try catch bloc and write the error in the log.

Try 
        'Write your code here 
	
	'Return the succes result 
	
	Dts.TaskResult = Dts.Results.Success 
Catch ex As Exception 
	' post the error message we got back. This is the old error catch code that might work better with SSIS. 
	Dts.Events.FireError(0, "Script Task Error", ex.Message.ToString(), String.Empty, 0) 
	
	'Return the Failure result 
	Dts.TaskResult = Dts.Results.Failure 
End Try 

Share this post

This Scalar-valued function return a Midas datetime from a real datetime. In midas, the date are the number of day since 1971-12-31.

ALTER FUNCTION [dbo].[udf_DateSqlToMidas]
(
	@Date		datetime
)
RETURNS int
AS
BEGIN

RETURN datediff(day, '1971-12-31', @Date)

END

Share this post

This Scalar-valued function return a real datetime from a Midas datetime. In midas, the date is the number of day since 1971-12-31.

ALTER FUNCTION [dbo].[udf_DateMidasToSql]
(
	@Days		int
)
RETURNS datetime
AS
BEGIN

RETURN dateadd(day, @Days, '1971-12-31')

END

Share this post

Graphic issues in WPF applications can include any of the following symptoms:

·         A WPF window that fails to refresh.
·         A WPF window that contains distorted and corrupted visuals.
·         On Windows Vista, the screen flickers.
·         On Windows XP, a blue screen crash sometimes occurs.
·         The occurrence of a Timeout Detection and Recovery (TDR).

As reported (see here), depending on the machine configuration and the application, software-based rendering is sometimes faster than hardware.

A new API is now available to allow developers to force software rendering in his/her application (per window) instead of using the GPU.

Solution :

In the main window, set the event :

Loaded="OnLoaded"

In the code behind :

private void Window_Loaded(object sender, RoutedEventArgs e)
{
    if (ForceSoftware)
    {
        HwndSource hwndSource = PresentationSource.FromVisual(this) as HwndSource;
        HwndTarget hwndTarget = hwndSource.CompositionTarget;

        // this is the new WPF API to force render mode.
        hwndTarget.RenderMode = RenderMode.SoftwareOnly;
    }
}

Source information :

http://blogs.msdn.com/jgoldb/archive/2007/10/10/performance-improvements-in-wpf-in-net-3-5-3-0-sp1.aspx

Share this post

Remove Trailing Zeros

CREATE FUNCTION [dbo].[udf_RemoveTrailingZeros]
(
	@strValue nvarchar(50),
	@KeepComma bit = 0,
	@RemoveBefore bit = 0
)
RETURNS nvarchar(50)
BEGIN

DECLARE @strValueR nvarchar(50)
DECLARE @strValueL nvarchar(50)
DECLARE @charInd int
DECLARE @intCount int

SET @charInd = CHARINDEX('.',@strValue)

IF @charInd = 0
 SET @strValueL = @strValue
ELSE
BEGIN
 SET @strValueR = RIGHT(@strValue, LEN(@strValue)-@charInd)
 SET @strValueL = LEFT(@strValue, @charInd-1)

 SET @intCount = LEN(@strValueR)+1
 WHILE @intCount > 0
 BEGIN
  SET @intCount = @intCount - 1
  IF SUBSTRING(@strValueR, @intCount, 1) NOT LIKE '0'
   BREAK
 END
 SELECT @strValueR = LEFT(@strValueR, @intCount)
END

SELECT @strValue = CASE WHEN @RemoveBefore = 1 THEN Cast(Cast(@strValueL as int) as varchar) ELSE @strValueL END +
 CASE WHEN (LEN(@strValueR) > 0) THEN
  '.' + @strValueR
 ELSE
  ''
 END

 --Replace point decimal by comma
 set @strValue = replace(@strValue,'.',',')

 IF @KeepComma = 1 AND SUBSTRING(@strValue, len(@strValue),1) <> ',' AND CHARINDEX(',',@strValue) <= 0
	SET @strValue = @strValue + ','

RETURN @strValue
END