The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Today when I was removing (deleting) some of the users from one of the SQL Server Database , got below error

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Fig 1: Drop User from SQL Server Database Error

From the error message we can clearly tell that the user is the owner of schema. To take a detail look double click on the User in the database as shown below and see which schema is owned by this user.

Fig 2: Check The Owner Of Schema In SQL Server Database.

As we can see that mytestschema is the schema that is owned by user Aamir. To drop user 'Aamir' , we need to change the ownership of mytestschema to some other user. We can transfer the ownership to dbo. If you try to un-check the box for mytestschema , it will not work. 

use this script to change the ownership of schema.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 

For our case, it will be 

use TestDB
go
ALTER AUTHORIZATION ON SCHEMA::mytestschema TO dbo

To drop the user , you can right click on user and Delete it or you can use below script to drop it.

USE DBName
go
Drop user UserName

DBA Posts - How to monitor SQL Server Cluster failover as well as current active node?

Cluster Failover Notification Process

This process explains step by step process of monitoring cluster failover of SQL Server.

Basic Idea behind this process is to find active node (where sql server resources are running currently)

High Level steps:

1- create a table to keep track of current active node

2- create a sql agent job that will read data from above table

3- send an email notification as soon as the value in tracking table changes

Detail Process Steps:

create table ActiveNodeTracker

(
PriorActiveNode varchar(50)
)

insert into ActiveNodeTracker

values('ActiveNodeName')

Declare @Nodevar1 varchar(50)

SELECT @Nodevar1= PriorActiveNode FROM ActiveNodeTracker

CREATE TABLE HOSTNAME
(
VALUE VARCHAR(50),
PresentActiveNode VARCHAR(50)

)

INSERT INTO HOSTNAME
EXEC master..xp_regread 'HKEY_LOCAL_Machine',

'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\','PhysicalName'

declare @nodevar2 varchar(50)
SELECT @Nodevar2=PresentActiveNode FROM HOSTNAME

IF @Nodevar1<>@Nodevar2
Begin
EXEC msdb..sp_send_dbmail @profile_name='DBAdmin',

@recipients='email@orgnization.com',--Please replace with your org domain emails

@subject='Failover Alert',
@body='Cluster Failover Notification, please see Prior and present Active Nodes',
@QUERY='SET NOCOUNT ON;
SELECT PriorActiveNode FROM ActiveNodeTracker;
SELECT PresentActiveNode FROM HOSTNAME;
SET NOCOUNT OFF'
UPDATE ActiveNodeTracker SET PriorActiveNode=@nodevar2
END
DROP TABLE HOSTNAME

DBA Posts - How to find SQL Server Port number using T-SQL?

How to find SQL Server Port Number from Management Studio?


Sometimes it is easier to run a query to find SQL Server Port number than having to remote into the server - You can find SQL Server Port number using below T-SQL query.

SET NOCOUNT ON
DECLARE @port varchar(30), @key varchar(150)
IF charindex('\',@@servername,0) <>0
BEGINSET @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'
+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGINSET @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'
ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,@value_name='Tcpport',@value=@port OUTPUT

SELECT 'SQL Server Name: '+@@servername + ' Port # '+convert(varchar(20),@port)

SSIS - How To Load Files in Different Tables According To The Name Of File

Scenario:

We receive different text files in our source directly. For each of the file we have created the table. By anytime we get a file. We want to execute our SSIS package and load that file to related table. New files can be included moving forward. Whenever a new file will be added to the process, Related required table will be created ahead of time. We want to create an SSIS Package that can load the file according to the file name and we don't have to make any modification in SSIS Package if we want to include new file in process.

Sample Source Files With Different Columns

Solution:

We will be using Script Task to perform this task. 

Step1 :

Create a variable VarFolderPath that will hold your folder path for source files.

Fig 1: Create Variable for Source File Directory

Step 2:

Create ADO.NET Connection Manager to the database in which your tables exist.

Fig 2: Create ADO.NET Connection Manager in SSIS Package

After creating the connection, I have renamed it to ADO_TestDB as shown in Fig 2.

Step 3:

Bring the Script Task and map the VarFolderPath variable as shown below.

Fig 3: Map Variable To Script Task 

Once done, Click on Edit Script and use below code. I have bold the code that I included in Script Task.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;#endregion
namespace ST_abbc60b70dbb46c295e5cfad132a08e3
{
    /// 

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
      
        public void Main()
        {
            // TODO: Add your code here

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADO_TestDB"].AcquireConnection(Dts.Transaction) as SqlConnection);
           // MessageBox.Show(myADONETConnection.ConnectionString, "ADO_TestDB");

                      
            //Reading file names one by one
            string SourceDirectory =Dts.Variables["User::VarFolderPath"].Value.ToString();
            // TODO: Add your code here
            string[] fileEntries = Directory.GetFiles(SourceDirectory);
            foreach (string fileName in fileEntries)
            {
                // do something with fileName
               // MessageBox.Show(fileName);
                string columname = "";


                //Reading first line of each file and assign to variable
                System.IO.StreamReader file2 =
                new System.IO.StreamReader(fileName);

                string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\\", ""));

                file2.Close();

                //Writing Data of File Into Table
                int counter = 0;
                string line;

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {

                    if (counter == 0)
                    {
                        columname = line.ToString();
                       
                    }

                    else
                    {
                      
                        string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(",", "','") + "')";
                        //MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                        myCommand1.ExecuteNonQuery();
                    }
                    counter++;
                    }
                 SourceFile.Close();
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

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

    }
}

Let's run our SSIS Package and see if the Tables are loaded.

Fig 4: Load Data to Tables from Flat File According to Name of File

As we can see that the data is loaded to each table from required file.

SSIS - How to Write Case Statement In SSIS Package

Scenario:

We have received a text source file that has the abbreviation for Region Code. We want to convert these region codes to region name such as

If RegionCode is AS then RegionName="ASIA'

If RegionCode is NA then RegionName="NORTH AMERICA'

If RegionCode is EU then RegionName="EUROPE'

If non of above them "UNKNOWN".

Fig 1: Text Source File with ClientRegion

Solution: 

If this data would be in table we could have write a query with case statement like below

Select id,Name,ClientRegion, 
Case 
When ClientRegion='AS' THEN 'ASIA'
WHEN ClientRegion='NA' THEN 'NORTH AMERICA'
WHEN ClientRegion='EU' THEN 'EUROPE'
ELSE 'UNKNOWN' END AS RegionName
from dbo.Client

As we are reading the data from text file we can't write the TSQL statement. We can load this data to some table and then write the statement but we don't want to do that. We will use Derived Column Transformation to write expression to get our Region Name.

Step 1: 

Create your SSIS Package. Inside the SSIS Package, Bring Data Flow Task. As we need to read the data from text file, Bring Flat File Source and create connection to source file.

Once the connection is created, Bring Derived Column Transformation and connect to Flat File Source and write expressions as given below. These expressions will give us the results like our Case statement. 

ClientRegion == "AS" ? "ASIA" : ClientRegion == "NA" ? "NORTH AMERICA" : ClientRegion == "EU" ? "EUROPE" : "UNKNOWN"

Fig 2: Write Case Statement in Derived Column Transformation in SSIS Package

Step 2: 

Bring Multicast Transformation as test destination and then connect Derived Column Transformation to it. Add the Data Viewer so we can see the output. Run the SSIS Package and see the output.

Fig 3:Write Case Statement in SSIS Package By Using Derived Column Transformation

As we can see that the expression worked as expected.

SSIS - How To Create Folder with Date in SSIS Package

Scenario:

We load data from different type of files on daily basis. We want to archive files after load. To manage processed files by date, we need to create a folder with Date every day and move the files to it once processed.

Solution:

We will be creating the Folder with Date if not exists. We will be using File System Task and expressions to achieve this.

Step 1:

Create your new SSIS Package and then create three variables as shown below.

Fig 1: Create Variables for Archive Folder

I created the variables so you can change the value of these according to the environments in which your package is going to run by using SSIS Configuration.

VarFolderPath: It is path to the main folder where we want to create Archive Folder.

VarFolderName : This will hold the value of folder name that we want to create.

VarFolderFullPath: This will combine Folder path, folder name and date added to the end. We will write expression on this variable as shown below.

Step 2: 

Select VarFolderFullPath and then hit F4 to go to properties of variable.

Fig 2: Write Expression on Variable in SSIS Package

Set EvaluateAsExpression=True and then Click on the button right front of Expression. Under Expression , write the below expressions as shown below

@[User::VarFolderPath]+ @[User::VarFolderName]+"_"+REPLACE(SUBSTRING((DT_STR,50,1252)GETDATE(),1,10),"-","_")

Fig 3: Build Expression in SSIS Package for Folder with Date

Step 3: 

Bring File System Task to the Control Flow Pane and then configure as shown below.

Fig 4: Create Directory With Date By Using File System Task In SSIS Package

Let's run our SSIS Package and see if the directory is created in required folder.

Fig 5: Folder Created with Date by Using SSIS Package

Now you have the folder ready to move your processed files to it.

Video for this post

SQL DBA - How To Enable/Disable Database User in SQL Server

If you see a red down arrow on User name under Users Tab in Database what does that mean? Simple and short answer can be , the user is disable.

Fig 1: User is Disabled in Database

As long as we don't need this user, we are fine to let the user in disable state. But if we have to enable this user, what do we need to do?

If we Deny on Connect permission then the user get disable and by granting Connect permission will enable the user.

Right Click on User and go to properties

Fig 2: Go to User Properties by Right Clicking on User Name

Click on Securables and Add the Database

Fig 3: Connect Permission on Database To User

As we can see in Fig 3, the Connect permission is denied to database user. Remove the check box under Deny and Click under Grant to Enable the Database user.

Fig 4: Enable Database User In SQL Server on Database Level

The user is enabled now. Click on Users Tab and then see if Aamir ( Databsae user) is enabled.

Fig 5: Enable/Disable SQL Server Database User 

If you don't want to do this by using GUI, you can use below script to enable and disable the Database user.

--Enable DataBase User( Provide Connect Permission on Database)
use [YourDataBaseName]
GO
GRANT CONNECT TO [YourDatabaseUserName]
GO


--Disable DataBase User( Deny Connect Permission on Database)
use [YourDataBaseName]
GO
REVOKE CONNECT TO [YourDatabaseUserName]
GO

SSIS - How To Build SQL Query By Using Expressions in SSIS Package

Scenario:

A friend of mine who is working as ETL Developer. He wrote Merge Statement for Upsert. When he was deploying his package to QA,UAT and Production, He noticed that the Source and Destination Database names are different in each of environment.
He does not want to maintain different copies of SSIS Package. What other option he has?

Solution:

One way to get this done is create variables for source and destination databases and pass the values of these variable by using SSIS Configuration according to the environments. 

Let's create three variables for this as shown below.

Fig 1 : Variables Required To Generate SQL Query Dynamically in SSIS Package

We can write expression in SSIS 2005 and all latest versions. The same expression can be build by using Expression Task in SSIS 2012 and SSIS 2014. I have example on using Expression Task if you like to use for this example.

Here is my Merge Statement

MERGE [Test1].[dim].[MyTable_SCD] AS DST
      USING [Test1].[dbo].[MyTable] AS SRC
      ON ( SRC.ID = DST.DimID )
      WHEN NOT MATCHED THEN
        INSERT (DimID,
                DimName)
        VALUES (SRC.ID,
                SRC.Name)
      WHEN MATCHED AND (SRC.Name <> DST.DimName) THEN
        UPDATE SET DST.DimName = SRC.Name;

If you see that I am reading from the same database but that is not going to be true. So I am going to use the variables to change the database name.

Click on VarSQL Variable and then hit F4 to go to properties. Click on Expressions as shown below. I used below script in expressions. You can change the query according to your requirement.

"MERGE "+ @[User::VarDestinationDB]  +" .[dim].[MyTable_SCD] AS DST
      USING "+ @[User::VarSourceDB] +".[dbo].[MyTable] AS SRC
      ON ( SRC.ID = DST.DimID )
      WHEN NOT MATCHED THEN
        INSERT (DimID,
                DimName)
        VALUES (SRC.ID,
                SRC.Name)
      WHEN MATCHED AND (SRC.Name <> DST.DimName) THEN
        UPDATE SET DST.DimName = SRC.Name;"

Fig 1: Build SQL Query by Using Variable Values In SSIS Package

The next step is to run this SQL Statement that is saved in our VarSQL variable. We can use the Execute SQL Task to do that.

Bring the Execute SQL Task to Control Flow Pane and configure as shown below.

Fig 3: Execute Query from Variable in Execute SQL Task in SSIS Package

Now you are good to go and create configuration. You can create any type of configuration such as XML, SQL Server etc. whatever you are using in your company.  Include VarSourceDB and VarDestinationDB in configuration and change the database names according to the environment. Your Merge query is going to be build by using Source and Destination Database name from variables and then run by Execute SQL Task.

SSIS - How to Disable/Enable Task/s In SSIS Package

Scenario:

One of our SSIS Package failed in Production today. By looking at the log information we got to know that the one of the Data Flow Task failed. We want to debug this package in Development environment but Package has other Tasks in it. How Can I only run the failed Data Flow Task?

Solution:

In SSIS Package you can disable the Tasks. Any Task or Container that you place in Control Flow Pane can be disabled and you can leave the Task or Tasks enable which one you want to run. This feature is very helpful while debugging package without deleting the Tasks.  Once you are done with debugging , you can enable the Tasks.

Tasks/Containers can be disabled. You can not go inside the Data Flow Task and disable a transformation or more than one Transformations.

Fig 1: SSIS Package with Different Tasks

Let's say we want to debug above SSIS Package but we don't want to execute Foreach Loop Container and "Data Flow Task_Load data Into Staging Tables" Data Flow Task. 

We can simply Right Click on any of the Tasks/Containers and then click Disable.That will do the magic for us and disable the script.

Fig 2: How To Disable Task/Container with Tasks in SSIS Package

Fig 3: Disable/Enable Tasks/Containers in SSIS Package

If you execute SSIS Package, the disabled Tasks will not run. To enable Task/ Container, Right Click on it and click Enable.

Fig 4: Enable Disabled Tasks in SSIS Package

SQL Server Error Messages

1.  /REPORTING E " has mismatched quotes.  The command line parameters are invalid.  The step failed.
2. Length of LOB data to be replicated exceeds configured maximum 65536
3. The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
4. Msg 15404, Level 16, State 19, Procedure sp_replcmds, Line 1 
5. Replication Error, can't connect to actual server, @@servername returns Null (Video Answer)

SQL DBA - How to Grant Execute Permission To User on msdb.dbo.sp_send_dbmail in SQL Server

The below script can be used step by step to create login, user and finally provide execute permission to user on sp_send_dbmail stored procedure in SQL server so the user can send emails.

--Create Login for Windows account.You can create SQL Server Login if you want
USE master
go
CREATE LOGIN [DomainName\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

--Create User in MSDB Databse
USE msdb
go
CREATE USER [DomainName\UserName] FOR LOGIN [DomainName\UserName]
GO

--Get the Profile Name that we need to use 
select * from msdb..sysmail_profile



--Assign the DatabaseMailUserRole to user
USE [msdb]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'DomainName\UserName'
GO

--Grand Execute permission on sp_send_dbmail procedure
Grant EXEC on sp_send_dbmail to [DomainName\UserName]



--Grants permission for a database user or role to use a Database Mail profile.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MailProfileName',
    @principal_name = 'DomainName\UserName',
    @is_default = 1 ;
    
  
--Test if all working   
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name ='MailProfileName',
    @recipients = 'aamir_warraich@hotmail.com',
    @body = 'Test Body',
    @subject = 'Test Sub';

Length of LOB data to be replicated exceeds configured maximum 65536

I have a table in which one column datatype is ntext. The replication was created while back. Yesterday when I was deploying some DML scripts , the script failed with below error.


Msg 7139, Level 16, State 1, Line 186
Length of LOB data (71308) to be replicated exceeds configured maximum 65536.
The statement has been terminated.

The error itself tells us that the size of the text is greater than configured size. So where do we need to make change to make this work?

By default the maximum size is set to 65536 bytes as we can see in below fig for SQL Server property. Right Click on SQL Server Instance and go to properties to see below window.

Fig 1: Max Text Replication Size SQL Server Property

Set the Max Text Replication Size property to -1 that means that there is no size limit.

Change the value from 65536 to -1 and then hit Ok as shown below.

Fig 2: How to set Max Text Replication Size in SQL Server

Once the change is done, you are good to go with your insert or update statements.

/REPORTING E " has mismatched quotes. The command line parameters are invalid. The step failed.

I created the SQL Server Agent job to run an SSIS Package. The Package needed to read the file from input folder. As the Package is not created with SSIS Configuration, I had to pass the folder path to variable in SQL Server Agent job.



I provided the value to the variable as shown below and executed the job.

Fig 1: How to pass value to Variable in SQL Server Agent Job for SSIS Package

The job failed with below error

Message
Executed as user: Domain\svsSSISAccount. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    The argument "\Package.Variables[User::FolderPath].Value;\\ServerName\MainFolder\InputFiles" /REPORTING E " has mismatched quotes.  The command line parameters are invalid.  The step failed.

Solution:

Adding "\" did the trick so the value of variable looks like \\ServerName\\MainFolder\\InputFiles

Fig 2: How to set variables in SQL Server Agent Job for SSIS Package