Dynamic SQL, writing an UPDATE Statement

I came across a situation, where I had to write a different sort of update statement. Following was the user requirement.

UPDATE two columns A and B in a table, say MY_TABLE based on the following condition.

• Update column A of MY_TABLE if a certain set of conditions met else dont update it
• Update column B of MY_TABLE if a second set of certain conditions are met else dont update it

Following is the code that can be used to perform this task.

DECLARE @UPDATEQUERY VARCHAR(1000) ;
DECLARE @COMMA VARCHAR(3) ;


SET @UPDATEQUERY = 'UPDATE MY_TABLE SET'
SET   @COMMA = '   '  
IF (SET OF FIRST CONDITIONS )
BEGIN
SET @UPDATEQUERY = @UPDATEQUERY + ' A =  '+ @var1 --COLUMN A IS ASSIGNED VALUE OF VARIABLE @VAR1
SET @COMMA = ' , ';
END

 
IF (SET OF SECOND CONDITIONS)

BEGIN
SET @UPDATEQUERY =@UPDATEQUERY+@COMMA
SET @UPDATEQUERY = @UPDATEQUERY + ' B= ''Y'' '--ASSIGNING COLUMN B A VALUE 'Y'
END

SET @UPDATEQUERY = @UPDATEQUERY + ' WHERE C = '+ @var2


--CHECKING IF ANY OF THE TWO COLUMNS IS BEING UPDATE THEN EXEC THE DYNAMIC QUERY.
IF(CHARINDEX('A',@UPDATEQUERY,1 )<>0 AND CHARINDEX('B',@UPDATEQUERY,1 )<>0 )
EXEC(@UPDATEQUERY)

ELSE

SET @UPDATEQUERY = NULL;

Read More

How to Comma Separate characters/words in a String

If you have a string of characters/words separated by comma and you want to comma separate each character/word in the string then you can use the following function.
Most often you come across this situation when you are passing an argurment to a Stored Procedure and the agruement being passed is a string of lets say ID_NO seperated by comma. In the Stored procedure you want to evaluate against each ID_NO in the string then you can use this function to comma separate each ID_NO

CREATE FUNCTION [dbo].[fn_CommaSeparatedValues]
(
@String VARCHAR(500)
) 

RETURNS @Seperated TABLE
 (
Id  INT IDENTITY(1, 1) NOT NULL,
Value VARCHAR(200)
 )

AS

BEGIN


DECLARE @Value VARCHAR(200)
DECLARE @StartPos bigint
DECLARE @EndPos bigint
DECLARE @LengthOfString int
DECLARE @ReachedEnd Char(1)
DECLARE @IndefOfComma INT
SET @StartPos=1
SET @EndPos=0
SET @LengthOfString=LEN(@String)
SET @ReachedEnd='N'

WHILE @ReachedEnd<>'Y'
BEGIN
SET @EndPos=CHARINDEX(',',@String,@StartPos)

 IF @EndPos>0

BEGIN
SET @Value = SUBSTRING(@String, @StartPos,@EndPos-@StartPos)
SET @StartPos=@EndPos+1
END

ELSE

BEGIN
SEt @ReachedEnd='Y'
SET @Value = SUBSTRING(@String, @StartPos,@LengthOfString-(@StartPos-1))
END

INSERT INTO @Seperated(Value) VALUES(@Value)

END
RETURN
END

Read More

Function to Remove Non Numeric Characters from String/Column

In order to remove non Numeric values from a String, you can use the following function. This Function uses two important String functions PATINDEX and STUFF.  The first Argument of Patindex explains a pattern, ^ sign show NOT and 0-9 showing range. So  this function will look for all those characters in the string passed which are not in the range 0-9.
STUFF will later replace the non Numeric character with a blank space.

CREATE FUNCTION dbo.RemoveNonNumeric(@String varchar(1000))
RETURNS VARCHAR(50)
BEGIN
DECLARE @Cur_Index INT
SET @Cur_Index = PATINDEX('%[^0-9]%',@String)

WHILE @Cur_Index > 0
BEGIN
SET @String = STUFF(@String,@Cur_Index,1,'')
SET @Cur_Index = PATINDEX('%[^0-9]%',@String)

END

RETURN @String

END
GO

Read More

FTP Task in SSIS Package failed when the package is executed via job

I have an SSIS package which was supposed to FTP the files to a specific server as a last task of the package. When I executed the package on BIDS, it worked perfectly however when I run it via job it failed. Each time I get the following error message:
"
Executed as user: mercury/admin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:27:13 PM Error: 2012-02-27 22:32:16.15 Code: 0xC001602A Source: Package1 Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: 425 Can't open data connection. 421 Connection timed out. . End Error Error: 2012-02-27 22:32:16.17 Code: 0xC002918B Source: FTP Task FTP Task Description: Unable to send files using "FTP Connection Manager".
"
I searched and found that I had to run the job using a proxy account having the same credentials used to create the package. I created a proxy account and under the JOB step section I selected the proxy account to run job as:


However doing all this didn't solve my problem until I did the following:

Turned Off the firewall on my machine and Boooomm the job perfectly executed the package including the FTP task.

Read More