Super User

Super User

Email: This email address is being protected from spambots. You need JavaScript enabled to view it.
Thursday, 25 September 2014 07:15

Repeat rows according to a value

Share this post

The case :

We have a table with this data :

Center Nb of letter
Center 1 6504
Center 2 12874
Center 3 1944
Center 4 3418
Center 5 6050
Center 6 1938
Center 7 1893
Center 8 2489
Center 9 1810









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)

  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

	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
		Insert into @Tbl (Centre, NbStart, NbEnd, Total, Remain)
				select @centre, @start, @end, @tot, @rest
	while @cpt > 0
			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 
				SET @end = @tot
				SET @rest = 0
				SET @rest = @tot-@end
fetch next from curseur into @centre, @nb


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
  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 :


Tuesday, 23 September 2014 07:57

Find all the dependencies for an Entity/column

Share this post

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

The source used is here :

-- =============================================
-- 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))
    ReferencingObject nvarchar(max) NULL,
    ReferencedObject nvarchar(max) NULL,
    ColumnName nvarchar(max) NULL,
    ReferencedObjectType nvarchar(10) NULL,
    ReferencingObjecType nvarchar(10) NULL
INSERT INTO @retTable(ReferencingObject, ReferencedObject, ColumnName, ReferencedObjectType, ReferencingObjecType) 
	SELECT	ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' +,
			ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name,
			ColumnName =,
			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 = @Column)
	ORDER  BY ReferencedObject, c.column_id



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

	<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" />


Wednesday, 05 February 2014 11:19

Use a specific port to send email in SSIS

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, ...


            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) = ";"

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

            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
            End If

            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
            End If

            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
            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

            '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())

            oMessage.Attachments.Add(New Attachment(strFilePath))

			'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


Wednesday, 05 February 2014 11:12

Get the error message in the script task

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.

        '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 

Wednesday, 05 February 2014 10:58

Convert a real datetime to Midas datetime

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

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



Wednesday, 05 February 2014 10:53

Convert a Midas datetime to SQL

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

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

Thursday, 30 January 2014 12:04

Opening of the new website

Share this post

The new website is open. You'll find now a support part in which there is various codes and assistances at your disposal

Good reading.

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 :


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 :

Thursday, 30 January 2014 12:04

Remove trailing zeros

Share this post

Remove Trailing Zeros

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

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

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

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

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

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

 --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
Page 1 of 3