Cumulative Values in SQL Server 2005, 2008, 2012

If wishes to find the Cumulative Values in SQL Server 2005, 2008, 2012 then make a self join of the table and achieve it. See the below example :

 CREATE TABLE UserTable (UserID VARCHAR(20), Amount DECIMAL(18,2), CreatedDate DATE)

 INSERT INTO UserTable (UserID , Amount , CreatedDate )
 SELECT 'U001', 20.00, '2016-08-01'
 UNION ALL
 SELECT 'U001', 40.00, '2016-08-02'
 UNION ALL
 SELECT 'U001', 30.00, '2016-08-03'
 UNION ALL
 SELECT 'U002', 10.00, '2016-08-01'
 UNION ALL
 SELECT 'U002', 20.00, '2016-08-02'


 SELECT * FROM UserTable

 SELECT T1.UserID, MAX(T1.Amount) Amount, SUM(T2.Amount) CummAmount, T1.CreatedDate
 FROM UserTable T1 (NOLOCK)
 ,UserTable T2 (NOLOCK)
 WHERE  T1.CreatedDate >= T2.CreatedDate
 AND T1.UserID = T2.UserID
 GROUP BY  T1.UserID,T1.CreatedDate
 ORDER BY T1.UserID, T1.CreatedDate
    
 

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Rename Column Name in SQL Server 2005, 2008, 2012

In SQL Server 2005, 2008, 2012 to rename a column in a table we can call an SP  sp_RENAME as below :

    

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

    
 

Alternately, open the table in Object Explorer and then right click on the desired column and select RENAME. Give a new column name here.



Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Creating DropdownList as Parameter in SSRS

In SQL Server 2005, 2008, 2012 SSRS Reports, to create a dropdownlist as input parameter use below simple steps :

1) Right click the Parameters >> Add Parameters.

2) Give an Appropriate Name, Type.

3) Select "Available Values" section.

4) Click the "Get values from a query" Option.

5) Select the appropriate Dataset and Fields.

--------------------------------

For Multiple select values in a parameter :

1) Follow the above process. And additionally,

2) Check the "Allow Multiple Values"

3) Double click the Dataset which is calling the Parameterized SP, and using the above Parameter.

4) Go to "Parameters".

5) Open Expression for Multi Valued Parameter.

6) Append Join (eg,  =Join(Parameters!ProgramName.Value,",")) .


    
 

 Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Upload a File to SFTP Using SSIS Package

 I needed SFTP Task, as SSIS has a built in FTP task, but this works only for the FTP protocol. I needed to upload a file to SFTP.
For this purpose worked and found that it can be achieved with the help of some third party tools like Putty and WinSCP. I preferred here WinSCP for no reason.

It requires two things to be done :

1) Download and install WinSCP, you can get from the below link

http://winscp.net/eng/download.php

Generally the file installed resided at C:\Program Files (x86)\WinSCP.

2) Download and install the winSCP .NET assembly/COM library. below is the link from where you can get it :

http://winscp.net/eng/docs/library_install

This download has one one winscpnet.dll and some other files like readme.txt and others, out of which winscpnet.dll is usefull for us. WinSCP .NET assembly should be installed to GAC to be accessible.

-----------------------------------------------------------------------------

Now to setup the SSIS Package, we need a sshKey, and to get this follow the below steps :

1) Run the WinSCP.exe from C:\Program Files (x86)\WinSCP(path may be different in your case).
2) Log in to the SFTP server.
3) Go to the Menu and under "Session" click on "Server and protocol information"
4) In the middle get the text below “server host key fingerprint”

    IT will be looking like “ssh-rsa 2048 ff:38:aa:cd:12:22:46:78:ab:cd:df:hk:12:43:34:4a:05”
Keep this with you.

Now Setup the SSIS Package :

1) In the ssis package drag a new "script task".
2) Create some variable so that any modifications can be done easily.

To create the variables, right click on the package pane and click variable.

Below variables can be used :

winSCPPath    = "C:\Program Files (x86)\WinSCP\winscp.exe"
SFTPUserName  = "{username}"
SFTPPassword  = "{password}"
SFTPSite      = "{ftp.sitename.com or 188.206.123.675}"
LocalPath     = "{Local Path from where File to be uploaded}"
RemoteSFTPDirectory = "/home/folderWhereTheFileTobeUploaded"
sshKey        = “ssh-rsa 2048 ff:38:aa:cd:12:22:46:78:ab:cd:df:hk:12:43:34:4a:05”;
winSCPLog     = true/false
winSCPLogPath = "{path to local folder to save the log file}"

    
 

C# Script :

Now double click the "script task" and open the task

Here against ReadWriteVariable, click on the ellipse and check the user variables you created above.
so that these variables can be used in the script.

Now click on "Edit Script", here you will get a window for C# code.
Put your code here.
Note : If you get any error related to session or SessionOptions, do add WinSCP namespace and also add winscpnet.dll from the add "reference"

below is the C# code : 
--------------------------------------------------------------------------------------------------

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.AddIn;
using WinSCP;


namespace ST_58a3c528d50348fdad80a80a904f1e20.csproj
{
 
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
  The execution engine calls this method when the task executes.
  To access the object model, use the Dts property. Connections, variables, events,
  and logging features are available as members of the Dts property as shown in the following examples.

  To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
  To post a log entry, call Dts.Log("This is my log text", 999, null);
  To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

  To use the connections collection use something like the following:
  ConnectionManager cm = Dts.Connections.Add("OLEDB");
  cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

  Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
  
  To open Help, press F1.
 */



        public void Main()
        {
            // TODO: Add your code here


            string winSCPPath = Dts.Variables["winSCPPath"].Value.ToString();
            string username = Dts.Variables["SFTPUserName"].Value.ToString();
            string password = Dts.Variables["SFTPPassword"].Value.ToString();
            string SFTPSite = Dts.Variables["SFTPSite"].Value.ToString();
            string LocalPath = Dts.Variables["RarFolder"].Value.ToString();
            string RemoteSFTPDirectory = Dts.Variables["RemoteSFTPDirectory"].Value.ToString();
            string sshKey = Dts.Variables["sshKey"].Value.ToString();
            Boolean winSCPLog = (Boolean)Dts.Variables["winSCPLog"].Value;
            string winSCPLogPath = Dts.Variables["winSCPLogPath"].Value.ToString();

try
{
    // Setup session options
    SessionOptions sessionOptions = new SessionOptions
    {
   

        Protocol = Protocol.Sftp,
        HostName = SFTPSite,
        UserName = username,
        Password = password,
        SshHostKeyFingerprint = sshKey
    };

    using (Session session = new Session())
    {

        // If you find that the assembly does not have the WinSCP.exe then you can assign here the path of exe.
        session.ExecutablePath = @"C:\Program Files (x86)\WinSCP\WinSCP.exe";

        // In case If the version in Assembly and that of the exe path given above mismatches then use disable version check.
        session.DisableVersionCheck = true;

        // Connect
        session.Open(sessionOptions);

        // Upload files
        TransferOptions transferOptions = new TransferOptions();
        transferOptions.TransferMode = TransferMode.Binary;

        TransferOperationResult transferResult;
        transferResult = session.PutFiles(LocalPath, RemoteSFTPDirectory, false, transferOptions);

        // Throw on any error
        transferResult.Check();

        // Print results
        bool fireAgain = false;
        foreach (TransferEventArgs transfer in transferResult.Transfers)
        {
            Dts.Events.FireInformation(0, null,
                string.Format("Upload of {0} succeeded", transfer.FileName),
                null, 0, ref fireAgain);
        }
    }
    Dts.TaskResult = (int)DTSExecResult.Success;
   // return 0;
}
catch (Exception e)
{
    Dts.Events.FireError(0, null,
        string.Format("Error when using WinSCP to upload files: {0}", e),
        null, 0);

    Dts.TaskResult = (int)DTSExecResult.Failure;
}


        }
    }
}

    
 

----------------------------------------------------------------------------------------------------------



Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Find Stored Procedures containing a text

Somewhere or Sometimes, we need to Access/Update/Delete all the Stored Procedures which contain some specific text in the name of DataObjects (like SP or Function) or in the definition of DataObjects. In such cases, first we need to get the list of all DataObjects containing the text. Now to get the list of such DataObjects which contain the text in their names, we will write the following query :

 

--#####################################################################################

SELECT      ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 
FROM        INFORMATION_SCHEMA.ROUTINES 
WHERE       ROUTINE_NAME LIKE '%NAME%'
            AND ROUTINE_TYPE ='PROCEDURE'
ORDER BY	CREATED

--#####################################################################################
    
 

Here in where clause "ROUTINE_TYPE ='PROCEDURE'" is given to search for Stored Procedures only. Now if you like to search a text 'NAME' in the definition of the DataObjects, then the query will be a bit changed as :

  

--#####################################################################################

SELECT      ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 
FROM        INFORMATION_SCHEMA.ROUTINES 
WHERE       ROUTINE_DEFINITION LIKE '%ghost%'
            AND ROUTINE_TYPE ='PROCEDURE'
ORDER BY	CREATED'

--#####################################################################################
    
 

Some other ways to find the same output is as below :

  

--#####################################################################################

SELECT	OBJECT_NAME(OBJECT_ID) AS ObjectName, Definition
FROM	SYS.SQL_MODULES
WHERE	OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1
		AND DEFINITION LIKE '%ghost%'
ORDER BY OBJECT_ID

SELECT	OBJECT_NAME(ID) AS ObjectName, Text
FROM	SYSCOMMENTS 
WHERE	[TEXT] LIKE '%ghost%' 
		AND OBJECTPROPERTY(ID, 'IsProcedure') = 1 
ORDER BY ID

--#####################################################################################
    
 

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Numeric Characters Only in SQL Server 2005, 2008, 2012

In SQL Server 2005, 2008, 2012 if you wish to find all the rows with a particular field having Numeric values only then give a simple check as below :

SELECT * FROM CustomerDetails (NOLOCK)
WHERE MobileNo NOT LIKE '%[^0-9]%'

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Remove Tab, Newline Character From Data In SQL Server

In SQL Server 2005, 2008, 2012, If you are facing some inconsistency with data while selecting and other operations and find that this is due to tab or newline characters, then just replace them with blank.

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

CHAR(9) - Tab
CHAR(10) – LineFeed
Char(13) - CarriageReturn

CR ("carrige return") is ASCII code 13, and means "go back to the beginning of the line". It tells the Teletype machine to bring the print head to the left.

LF ("Line Feed") is ASCII code 10, and tells the printer to move the paper up 1 line.


Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Email Validation in SQL In SQL Server 2005, 2008, 2012

In SQL Server 2005, 2008, 2012 for email validation I found that either you can write a plane query as below :

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

Or can write a function as below : "

CREATE FUNCTION dbo.ValidateEmail(@EMAIL VARCHAR(100))

RETURNS BIT AS
BEGIN     
  DECLARE @bitRetVal AS BIT
  IF (@EMAIL <> '' AND @EMAIL NOT LIKE '_%@__%.__%')
     SET @bitRetVal = 0  -- Invalid
  ELSE 
    SET @bitRetVal = 1   -- Valid
  RETURN @bitRetVal
END

Reference: Govind Badkur(http://sqlserver20.blogspot.com)