Skip to main content

Posts

Featured Post

SQL Query - Parsing comma separated string

In this article we will see how to separate data one by one from a comma separated string. First declare a variable to store articles in comma separated values. DECLARE @DataString VARCHAR(200) SET @DataString = 'ASP.NET, VB.NET, C#.NET, SQL, Javascript' Add comma at last to @DataString SET @DataString = @DataString + ',' Using while loop through all the topics in @DataString WHILE (CHARINDEX(',', @DataString) > 0) --  CHARINDEX  is a SQL inbuilt function which returns the integer value if charecter is present in a string. BEGIN         -- To hold each topic         DECLARE @Topics VARCHAR(30)         -- Get the topic one at a time                SET @Topics = SUBSTRING(@DataString,0, CHARINDEX(',', @DataString))         PRINT @Topics             -- Reset the @DataString         SET @DataString = SUBSTRING(@DataString, CHARINDEX(',', @DataString) + 1, LEN(@DataString)) END Here is the out put of the above query   ASP.NET  V
Recent posts

Upload image and render image through AWS Gateway proxy+ lambda

 I searched internet for handling binary data in AWS Gateway for more than a week but I could not find any working sample code. Every where there was a bits and pieces but no one explained or provided sample working code through which I can upload an image or render an image. So I am sharing below working sample code to handle binary data in AWS Gateway Proxy+ Below is the NodeJS example to upload and render image or binary data. By default AWS Gateway does not enable processing binary data. So we have to go to settings and enable binary data as shown below Copy this code in lambda index.js file and Deploy it. const https = require('https'); const AWS = require('aws-sdk'); exports.handler = async (event) => { let response = ''; let options = { method: event.httpMethod, hostname: ' ', path: ' ' }; let apiResponse = await proxyRequest(options, event.body, event.isBase64Encoded); // Handling ret

SSIS package - set oledb connections at runtime

SSIS oledb connection manager does't allow user to connect to different oledb servers. If we store oledb connection sting in Config file then it will accepts server name, InitialCatalog and username. But it wont accept password. The is because oledb connection manager has not exposed the oledb password property. To over come this problem we have to override the OLEDB connection manager itself. So that it accepts connections to different oledb servers. Public Sub Main() Dim vars As Variables Dim oledbConnectionManager As ConnectionManager oledbConnectionManager = Dts.Connections("<oledb connection name>") ' ADDED TO MAKE oledb CONNECTION STRING DYNAMIC Dim oServerName As String Dim oUserName As String Dim oInitialCatalog As String Dim oPassword As String Dts.VariableDispenser.LockOneForWrite("oServerName", vars) oServerName = vars("oServerName").Value.ToString().Trim() vars.Unlock() Dts.VariableDispenser.LockOneForWrite("oInitia

SQL query - Table rows as columns

-- Create a table variable to store user data DECLARE @myTable TABLE (    UserName VARCHAR(50),    ArticleName VARCHAR(50) ) -- Insert some data to table to work on that data INSERT INTO @myTable(UserName, ArticleName) VALUES ('Jack', 'ASP.NET') INSERT INTO @myTable(UserName, ArticleName) VALUES ('Jack', 'SQL Server') INSERT INTO @myTable(UserName, ArticleName) VALUES ('Jack', 'C#') INSERT INTO @myTable(UserName, ArticleName) VALUES ('Jack', 'VB.NET') INSERT INTO @myTable(UserName, ArticleName) VALUES ('David', 'Java') INSERT INTO @myTable(UserName, ArticleName) VALUES ('David', 'Java Beans') INSERT INTO @myTable(UserName, ArticleName) VALUES ('David', 'Java script') SELECT UserName, ArticleName FROM @myTable -- This is how the table looks after inserting the data Now I want all the articles related to Jack and David in a single column.

SQL query - Table sorting

In this article we will learn how to sort the table values with out using dynamic query to improve performance. CREATE PROCEDURE SortingExample ( @sortFiled INT, @sortType INT  -- 1 for ASC, 2 for DESC ) AS BEGIN -- Create a table variable to store user data DECLARE @myTable TABLE ( UserID INT IDENTITY(1,1), UserName VARCHAR(50), Password VARCHAR(50), Email VARCHAR(50) ) -- Insert some data to table to work on that data INSERT INTO @myTable(UserName, Password, Email) VALUES ('Jack', 'JackPwd', 'jack@gmail.com') INSERT INTO @myTable(UserName, Password, Email) VALUES ('Anand', 'AnandPwd', 'raj@gmail.com') INSERT INTO @myTable(UserName, Password, Email) VALUES ('smith', 'smithPwd', 'smith@gmail.com') INSERT INTO @myTable(UserName, Password, Email) VALUES ('Brandy', 'BrandyPwd', 'tom@gmail.com') -- If @sortType = 1 then sort the selected field in ASC ord

SQL query - Iterate through table records without using cursor

In this article we will see ow to loop through the rows of a table without using cursor In this article I will explain how to loop through each rows in a table and get the data one by one for processing. -- Create a table variable to store user data DECLARE @myTable TABLE (     UserID INT IDENTITY(1,1),     UserName VARCHAR(50),     Password VARCHAR(50),     Email VARCHAR(50) ) -- Insert some data to table to work on that data INSERT INTO @myTable(UserName, Password, Email) VALUES ('Jack', 'JackPwd', 'jack@gmail.com') INSERT INTO @myTable(UserName, Password, Email) VALUES ('Raj', 'RajPwd', 'raj@gmail.com') INSERT INTO @myTable(UserName, Password, Email) VALUES ('smith', 'smithPwd', 'smith@gmail.com') INSERT INTO @myTable(UserName, Password, Email) VALUES ('Tom', 'tomPwd', 'tom@gmail.com') -- Get the number of rows in the looping table DECLARE @RowCount I

SSIS - Error handling

In this article we will learn how to handle the errors in SSIS package. In SSIS when package throws an error then these error details is stored in following variables ErrorCode, ErrorDescription and SourceName. Below is the code to handle error in SSIS package. When a SSIS task encounters an error these error details are stored in three system variables. Dts.VariableDispenser.LockForRead("ErrorCode") Dts.VariableDispenser.LockForRead("ErrorDescription") Dts.VariableDispenser.LockForRead("SourceName") ErrorCode variables returns error code which will be numeric. ErrorDescription will give us a brief description of an error encountered by the SSIS task. SourceName give us the task name from which error occured. Private Sub GetErrorValues(ByRef errNumber As Integer, ByRef errDescription As String, ByRef errSource As String)                     Dts.VariableDispenser.LockForRead("ErrorCode")             Dts.VariableDispenser.L