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

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

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

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

Read More