SQL Server: How to modify existing table structure

Suppose you have a table in a production database and you need to modify table structures.

Let's say we have a table with following columns and properties

TableA

Columns:
ID int
FName varchar(50)
LName varchar(50)
version nvarchar(10)
file ntext
City char(10)


say we have primary key on ID


Now someone asked to change version nvarchar data type to varchar datatype and file ntext to text datatype. Also remember that we have data in this table.

So how would we modify the structure of table without change any values.

Here's How:

--Step 1. Drop any primary key if there is one.
--Step 2. Drop any index if it exists (may not have to do if step cover it).
--Step 3. Alter table--change column, insert or remove.
--step 4. Recreate primary key
--step 5. Recreate index.

--Step 1. Drop any primary key if there is one.

ALTER TABLE  [dbo].[TableA]

DROP CONSTRAINT constraintname_PK

GO

;

--Step 3. Alter table--change column, insert or remove.

ALTER TABLE [dbo].[TableA]

ALTER COLUMN [version ] [varchar](10) ;

when you try to convert ntext to text using above statement, it will give an error. To do that first convert to varchar(max) and then to text data type.

ALTER TABLE TableA

ALTER COLUMN  file VARCHAR(MAX)

ALTER TABLE TableA

ALTER COLUMN  file text

ALTER TABLE [dbo].[TableA] ADD  CONSTRAINT [constraintname_PK] PRIMARY KEY NONCLUSTERED 

(

[ID ] ASC,

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SSIS: Script task to copy files from one location to another location

Script task to copy files from one folder to another folder.

In SSIS, we can use file system task to copy and move files from one folder to another folder.

However you can accomplish same task using script task.  Here's how you can do.



/*
   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 System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_fbfcdb1278d0459f9a8c35e0b0ab16de.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

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

 // This is where my source files are located.
        string sourceDir = @"C:\Data\";
// This is where my target files will be copied
        string target = @"C:\Archive\";
     

        public void Main()
        {
            string[] sDirFiles = Directory.GetFiles(sourceDir);
            if (sDirFiles.Length > 0)
            {
                foreach (string file in sDirFiles)
                {
                    //string[] splitFile = file.Split('\\');
                    string copyFile = Path.GetFileName(file);
                    string source = sourceDir + "\\" + copyFile;
                    Copy(source, target + "\\" + copyFile);
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        public void Copy(string source, string target)
        {
            try
            {
                //If file exists at the target location, then delete it
                if (File.Exists(target))
                {
                    File.Delete(target);
                }
                //Otherwise copy it from source to target
                File.Copy(source, target);
            }
            catch (Exception ee)
            {

            }
        }
    }
}

Restoring Database from one environment to another environment in SQL Server

As a DBA, we are required to restored database from one environment to another environment all the time.

The following steps are involved in restoring database.

Step 1. Find the mdf and ldf files to restore
Step 2. Do Actual Restore of the database
Step 3 Find orphan object for user login and fix them. ( This is important as different environment have different users --Production version test or dev environment)

Step 1.

Find the files to restore .



USE MASTER
GO

DECLARE @Path VARCHAR(128)
       ,      @Backup VARCHAR(128)
       ,      @Filename VARCHAR(128)
       ,      @dbname VARCHAR(128)

-- Set Variables
SET @Path     = 'C\BackupFiles' + '\' ---Path of the file..Replace with your path
SET @Backup = 'Name of the database backup files' + '.bak' -- File Name --- Replace with your
SET @filename = @path + @Backup
SET @dbname = 'Development1'  ---Name of Database ---replace with your


-- Restore FileList
RESTORE FILELISTONLY
FROM DISK = @Filename

Step 2

In real environment, you may have replication going on the server where you want to restore. So we have to make sure that we kill replication.


:connect SQLTST
:setvar dbname "Development1"
:setvar delay "00:00:00.003"
:setvar max_retries "5"
:r "C\Scripts\UtilityScripts\Recovery\KillDB.sql"

USE MASTER
GO

DECLARE @Path VARCHAR(128)
       ,    @Backup VARCHAR(128)
       ,    @Filename VARCHAR(128)
       ,    @dbname VARCHAR(128)

   -- Set Variables
SET @Path     = 'C\Backup' + '\' ---Path of the file..Replace with your path
SET @Backup = 'Name of the database backup files' + '.bak' -- File Name --- Replace with your
SET @filename = @path + @Backup
SET @dbname = 'Development1' --Which Database to restore

RESTORE DATABASE @dbname
FROM   DISK = @Filename
WITH   MOVE 'Development1' TO 'C:\SQL\Development1_data.mdf' ---Replace with Phyiscal Name value which was given in first step.  If there are multiple mdf file
,      MOVE 'Development1_log       TO 'C:\SQL\Development1_log.ldf'
,      STATS = 5
,       replace -- May be needed if replacing existing database.


Step 3:

Fix orphan object

EXECUTE sp_change_users_login 'Report'


This will give a list of username where the objects are orphan. For example if a user 'Home' exist


Then run this

EXECUTE sys.sp_change_users_login 'Auto_Fix', 'Home'

SSIS: script task to write to file and send as email attachment

Problem:

Create a package which read a query and save the result in file and send that file as an attachment.

Solution:

The problem says that we do a select query on some database, save the result to a file and send them as attachment. Or you asked to write to file and leave it there. This file can be picked by any other process. In this case, I am going to send the file as an attachment through email.

Designing the package:

This package will consists of three task:
1. Execute SQL Task
2.  Script Task
3. Send Mail Task

Variables:

Lets create all the variables needed for this package.

1. varResultSet: object type. This variable will hold the result of our execute SQL task.
2. varNewFileDelimiter: This variable is used as file delimiter which will be used in script task.
3. varUploadDirectoryPath: This variable is used to save my file created and hold values where I will be saving my file.
4.varMyResultFileName: This variable hold file name. Set Expression value to true and under expression add following.
@[User::varUploadDirectoryPath]+ "\\"+"MyResult"  + "_" + Right("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + Right("0" + (DT_WSTR,2)DAY(GETDATE()),2)  + (DT_WSTR,4)YEAR(GETDATE()) + ".csv"

Here I am saving my file as csv file with date added to file name.

5. varExceptionCount: This variable hold any exception counts.

Most of these variable will be used in script task and send mail task.

So lets go and add Execute SQL task to our package.

1. Execute SQL Task

Added connection manager which will be used. Right down your query in your sql statement and set ResultSet to Full Result Set.

Under Result set add the variable
Result Name = 0
Variable =varResultSet

This is all we have to do in the first task. Now if you execute this task, the result of your query will be held in this variable.

Let's add script task to consume this result and write to file.


2.  Script Task

In our script task, let's chose C# language and Under Read Only add following variables.

User::varResultSet,User::varMyResultFileName,User::varNewFileDelimiter

And In ReadWriteVariable, add User::varExceptionCount

Open your script task and add following code. Make sure that if you are using different variable name, change them at your end.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;  
using System.IO;
using System.Reflection;

namespace ST_7cdb67cf97304adab75a69c0b1d4688e.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

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



        public void Main()
        {
            try
            {
                OleDbDataAdapter A = new OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable();
                A.Fill(dt, Dts.Variables["User::varResultSet"].Value);
                string filepath = Dts.Variables["User::varMyResultFileName"].Value.ToString();
               
                int i = 0;

                Dts.Variables["User::varExceptionCount"].Value = dt.Rows.Count;

                if (dt.Rows.Count > 0)

                {
                    StreamWriter sw = null;

                    sw = new StreamWriter(filepath, false);

                    for (i = 0; i <dt.Columns.Count; i++)
                        {
                   
                            if (i == dt.Columns.Count -1)
                        {
                            sw.Write(dt.Columns[i].ToString());
                        }
                    else
                        {
                            sw.Write(dt.Columns[i].ToString()+Dts.Variables["User::varNewFileDelimiter"].Value.ToString());
                        }
                    }
               sw.WriteLine();

                    foreach (DataRow row in dt.Rows)
                    {
                        object[] array = row.ItemArray;

                        for (i = 0; i < array.Length; i++)
                        {
                            if (i == array.Length - 1)
                            {
                                sw.Write(array[i].ToString());
                            }
                            else
                            {
                                sw.Write(array[i].ToString() + Dts.Variables["User::varNewFileDelimiter"].Value.ToString());
                            }
                        }
                        sw.WriteLine();
                    }
                    sw.Close();

            }
                    }
                        catch (Exception ex)
                        {
                         }
                 }
    }
}
       

Save this. Now if you run your package, you find your file in the folder.

3. Send Mail Task

Add a send mail task, add your connection manage, add email id where you want to send. In expression, select FileAttachment and set it your variable (in this case @[User::vvarMyResultFileName]

You are all set to send this to as attachment.


 

SSIS: File System Task to rename File

SSIS: File System Task to rename File

Assignment: You are give a task where you have some files--> *.csv, *.txt, or any other file. You are asked to rename the files and move to same folder or different folder.  How would you do this.

Lets look at what we are asked to do.

1. We have a Source Folder where we have files of a particular type. So we need to know the location of this folder. Lets create a variable called SourceFolder of string type and save location into this folder. let's assume that this is C:\temp\SourceFolder

2. We need to rename these files and save it in same folder location or different folder location and change name of these files. So let's create another variable and call them DestinationFolder of string type and save the location in value.

3. Now we need to pull each file in SourceFolder and put in them in Destination folder. Lets create a variable called FileName of string type and leave value blank.  Also create two more variables called FullSourceFolderFileName and FullDestinationFolderFileName. FullSourceFolderName value would be @SourceFolder + @FileName and FullDestinationFolderFileName value would be @DestinationFolder + @FileName. The reason for creating these two variable is to be used at run time to dynamically use them in Foreach Loop Container.

So far we have following variable in our package

VariableName             type              Value
1.FullName                   string            No value --- leave this blank
2.SourceFolder             string            C:\temp\SourceFolder
3.DestinationFolder       string            C:\temp\SourceFolder or C:\temp\DestinationFolder (you can dump renamed file in same source folder or dump into destination folder)
4. FullSourceFolderName string    set expression to true and in expression put @SourceFolder + @FileName
5. FullDestinationFolderFileName string   @DestinationFolder + @FileName same for this as you did for variable 4.

Now let's drop Foreach Loop in our package and configure it.

Go to variable Mapping and add FileName

Now drop file system task and configure as show in figure below.

Now you are good to run the package.

SSIS: Insert and Update data in Oracle table from SQL Server

Recently I was asked to develop a package which would pull data from SQL Server Database and insert and update data in Oracle database.

To begin with this, we have to keep few things in mind:

First make sure that you know what data type are in your source and destination table. For example say EmpID is a integer in SQL Server database but chances are that it is NUMBER in your Oracle table to make sure to do correct data conversion task.

Secondly, in SQL Server, commit is auto, so we don't have to issue any statement (chances are that database is setup like this) however in oracle database you have to issue commit statement after you make any change to data.

For my SSIS packages, I designed my task into following steps:

1. I created a temp table in oracle database to dump all my data from sql server (using right data conversion and data type.
2. I created a Merge Stored Procedure on Oracle side which I would execute inside my SSIS package to update and insert data.

And everytime, I use DML statement, I use SQL excute task to issue commit statement on Oracle Database.

Also make sure that you use ADO.Net connection Manager to communicate with your oracle database.

To call stored procedure inside Execute SQL task use something like this.

 CALL MY_Stored_Procedure ()

If you get this error message,

Warning: Multiple-step OLE DB operation generated errors. 
Check each OLE DB status value, if available. 
No work was done.

It means that you have missed commit statement.

How to upload documents in sql server table

How to upload documents in sql server table

Not the best solution but you still want to do it??

Lets do it.

Step 1: Create a table called documents with following columns: docID int IDENTITY, DocumentName varchar(100), Document VARBINARY(MAX), DateCreated Datetime default (getDate()));

Create table documents
(DocID int IDENTITY(1,1),
DocumentName varchar(100) NOT NULL,
Document VARBINARY(MAX),
DateCreated Datetime default (getDate())
);


Step 2:

Lets say we have a document tittle " A sql guide" and we want to store it into sql server so that incase we want to use it later for some reason.

Say this is located in C:\documents\sql\ A sql guide.doc

let's save this document in sql table:


Declare @doc As VARBINARY(MAX)

Select @doc = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET
(BULK 'C:\documents\sql\A sql guide.doc', Single_BLOB) AS x

INSERT INTO documents (DocumentName, Document) Values ('A sql guide', @doc)

And You are done!!