TSQL - How to Execute Large SQL Script File In SQL Server

I got this big (4GB) SQL Script file(Schem +Data)  from one of our vendor that I needed to execute on one or our database.

Execute File by using SSMS:
First I tried to open that file with SSMS but received error as below

Error HRESULT E_FAIL has been returned from a call to a COM component. (mscorlib)

Fig 1: Error Message from SQL Server Management Studio

Seems like we can not open this big file with SQL Server Management studio and execute.

Execute Large SQL File By SSIS:
The next thing came in my mind, Why not to run this big file by using SSIS. Created quick SSIS Package with Execute SQL Task and provided the File path for Execute. When executed, received below error

Error: 0xC002F304 at Execute SQL Task, Execute SQL Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.".

Fig 2: Error Message from SSIS Package while execute Large SQL File.

Look like, SSIS also can not run this large SQL script file by using Execute SQL Task.

Execute Large SQL Script File by using SQLCmd:
Executed the SQL Script file by using SQLCmd and it worked :)
Go to Run, type CMD and then paste and hit Enter

sqlcmd -S [servername] -d [databasename] -i [scriptfilename]

e.g.
sqlcmd -S MyServerName\InstanceName -d MyDataBaseName-i c:\MySQLScriptFile.sql


Fig 3: Running SQL Script File by SQLCmd

SSRS -How To Get List of Folders,DataSources,Reports and Permissions on Each of Item in SQL Server Reporting Services

The below code can be used to get the list of Folders, Items in them such as reports, data sources and permission to each of these items in SQL Server Reporting Services. The code uses ReportServerDB tables to provide us all details.


USE ReportServer
GO
SELECT CASE
         WHEN C.Path = '' THEN 'Home'
         ELSE C.Path
       END    AS Path,
       C.Name AS ItemName,
       USR.UserName,
       RL.RoleName,
       CASE
         WHEN C.TYPE = 1 THEN 'Folder'
         WHEN C.TYPE = 2 THEN 'Report'
         WHEN C.TYPE = 3 THEN 'File'
         WHEN C.TYPE = 4 THEN 'LinkedReport'
         WHEN C.TYPE = 5 THEN 'DataSource'
         WHEN C.TYPE = 6 THEN 'Model'
         WHEN C.TYPE = 7 THEN 'ReportPart'
         WHEN C.TYPE = 8 THEN 'SharedDataset'
       END    AS ItemType FROM   Catalog C
       INNER JOIN Policies PL
               ON C.PolicyID = PL.PolicyID
       INNER JOIN PolicyUserRole PUR
               ON PUR.PolicyID = PL.PolicyID
       INNER JOIN Users USR
               ON PUR.UserID = USR.UserID
       INNER JOIN dbo.Roles RL
               ON RL.RoleID = PUR.RoleID
ORDER  BY C.Path 

DBA - How To Kill All Database Processes On SQL Server

The below code can be used to Kill all the processes to SQL Server Database. The code can be used in situations where changes to the database can not be done if any process is running on database such as renaming database can not be done if any process is running on that database.

USE MASTER
GO
DECLARE @DatabaseName AS VARCHAR(500)
-->Provide the DataBaseName for which want to Kill all processes.
SET @DatabaseName='YourDataBaseName'
DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
  SELECT spid
  FROM   sys.sysprocesses
  WHERE  DB_NAME(dbid) = @DatabaseName
OPEN KillProcessCur
FETCH Next FROM KillProcessCur INTO @Spid
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(500)=NULL
      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))
      EXEC (@SQL)
      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))
            + ' killed successfull'
      FETCH Next FROM KillProcessCur INTO @Spid
  END
CLOSE KillProcessCur
DEALLOCATE KillProcessCur

DBA - Generate Script To Detach All User Databases on SQL Server

Here is code that can be used to generate script to detach all the user databases on SQL Server. This can be helpful if during migration we need to detach all the SQL Server databases from one server and attach to other SQL Server.

After generating the script, Copy the output and execute in SSMS to detach all databases.

DECLARE @DatabaseName AS VARCHAR(500)
DECLARE DetachCur CURSOR FOR
  SELECT name
  FROM   MASTER.sys.databases
  WHERE  owner_sid > 1;
OPEN DetachCur
FETCH Next FROM DetachCur INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'sp_detach_db ' + @DatabaseName + CHAR(10)
            + ' GO' + CHAR(10) + 'Print ''Detach of '
            + @DatabaseName
            + ' database completed successfully'''
            + CHAR(10) + ' GO'

      FETCH Next FROM DetachCur INTO @DatabaseName
  END

CLOSE DetachCur
DEALLOCATE DetachCur

DBA - How To Disable Transparent Data Encryption (TDE) On SQL Server Database

To disable Transparent Data Encryption (TDE) on SQL Server database, below code can be used.

USE MASTER
GO
ALTER DATABASE DatabaseName
SET ENCRYPTION OFF
GO
USE DatabaseName
GO
DROP DATABASE ENCRYPTION KEY
GO

SSIS - How To Validate Excel Header Against Definition Table

Scenario: 

We get Excel File on daily basis, we want to validate the Header against our definition table before start loading. If Header does not match with our definition , we will strop the execution of package and send an email. For File Name validation please check Link


Solution:

Step 1: Create Table In SQL Server Database
Let's create definition table in which we can insert entry for our Test_File.
CREATE TABLE [dbo].[FileValidation](
 [FileValidationID] [int] IDENTITY(1,1) NOT NULL,
 [FileName] [varchar](100) NULL,
 [Header] [varchar](4000) NULL,
 [FolderPath] [varchar](200) NULL,
 [FileExtension] CHAR(4)
)
INSERT INTO Dbo.FileValidation (FileName,Header,FolderPath,FileExtension)
 VALUES ('Test_File','Name,Address,phone,SSN','C:\SourceFile\','xlsx')

Step 2: Create Test Excel File 
Fig:1 Test_File for Header Validation in SSIS

Step 3: Create Variables for SSIS Package
create four variables as shown in Fig 2.

Fig 2: Create Variables in SSIS Package

DefHeader: We will read the value of Header from definition table for 'Test_File'
FileHeader: We will use this inside script task for comparison with DefHeader 
HeaderObj: Read the Excel Header Row and save in this variable and then use the values from this variable to build comma separated Header.
ValidationFlg: If Excel column header matches with definition , we will set ValidationFlg=1 else 0.

Step 4: Set DefHeader
Read the Definition value for 'Test_File' entry from SQL Server Table as shown below
Fig 3: Read Header value from definition by using Execute SQL Task

Fig 4: Map the value to variable in Execute SQL Task

Step 5: Read the Excel Header

Create Excel Connection, make sure not to check the box for First row has column names
Fig 5: Create Excel Connection that can be used in Execute SQL Task

Use the Execute SQL Task to read the Header Row of Excel File.  I used Select * from [Sheet1$A0:zz1] query to get header row by assuming that the max columns I will have can go to ZZ.


Fig 6: Read Header Row from Excel by using Execute SQL Task

Map the resultset to HeaderObj variable as shown below
Fig 7: Map HeaderObj Variable for Full Resultset

Step 6: Compare the Excel Header with Excel in Script Task 
We will use  DefHeader,HeaderObj,FileHeader and ValidationFlg in Script task. After comparing the values of DefHeader and FileHeader, we will set the value of ValidationFlg.
First of all we have to read the HeaderObj data and convert that to comma separated row so we can compare it with DefHeader.
Fig 8: Map the variables to Script Task 

Write the Highlighted code after Edit Script button
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
namespace ST_a044e9edd1104a458a7a88d86eb8f8e9.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()
        {
            OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::HeaderObj"].Value);
            int rowCount = 1;
            string colhdr;
            
            foreach (DataRow dr in dt.Rows)
            {
                if (rowCount == 1)
                {

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
           colhdr = dr[i].ToString();
Dts.Variables["User::FileHeader"].Value 
Dts.Variables["User::FileHeader"].Value + "," + colhdr;
                    }
                }
                rowCount += 1;

            }
if ((Dts.Variables["User::FileHeader"].Value.ToString().Remove(0, 1)).TrimEnd(',') ==
Dts.Variables["User::DefHeader"].Value.ToString())
            {
    Dts.Variables["User::ValidationFlg"].Value = 1;
    MessageBox.Show("Value matches");
            }
            else
            {
     Dts.Variables["User::ValidationFlg"].Value = 0;
     MessageBox.Show("Value Not matches");
            }
                
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}



Final Output:
Let's use the ValidationFlag in Precedence Constraint to run the Data Flow When Value=1( Header Match) and stop if header does not match(ValidationFlg=0).




















Fig 8: Use Precedence Constraints


Execute your package couple of time by changing the header and see if all logic working fine. 
Fig 9: Header Match Execution

DBA- Generate Script To Bring All User Databases Online in SQL Server

The below query can be used to generate scripts to bring SQL Server Databases from Offline state to Online.

SELECT 'ALTER DATABASE ['+name+'] SET ONLINE'+CHAR(10)+' GO' 
FROM MASTER.sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
AND state_desc='OFFLINE'

DBA- Generate Script To Bring All User Databases Offline in SQL Server



Quickly generate script to bring all SQL Server Databases offline.

SELECT 'ALTER DATABASE ['+name+'] SET OFFLINE WITH NO_WAIT'+CHAR(10)+' GO' FROM 
MASTER.sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb','distribution')

DBA - How To Get The Size of All Databases on SQL Server

Here is code that can be used to get the size of Data File (MB), Log File (MB) and Total Size of Database in GB on SQL Server.

SELECT DBName,
       DataFile                        AS DataFileSizeInMB,
       LogFile                         AS LogFileInMB,
       ( DataFile + LogFile ) / 1024.0 AS DataBaseSizeInGB
FROM   (SELECT DB_NAME(Database_id) AS DBName,
               size * 8.0 / 1024    AS SizeInMB,
               CASE
                 WHEN TYPE = 0 THEN 'DataFile'
                 ELSE 'LogFile'
               END                  AS FileType
        FROM   sys.master_files) D
       PIVOT ( MAX(SizeInMB)
             FOR FileType IN (DataFile,
                              LogFile)) pvt

DBA - Backup All TDE Certificates For All Databases ON Sql Server

When we try to de-attach a database on which TDE is enabled  and try to restore, we get this error "Cannot Find Server Certificate with Thumbprint", We have to take the backup of certificate from source server and create on destination server and then we can attach database successfully.

The code can be used to generate backup certificates for all the databases on server.
USE MASTER
GO
DECLARE @CerName VARCHAR(500)
DECLARE @FolderLocation VARCHAR(100)
SET @FolderLocation='C:\Scripts\Keys\'
DECLARE CUR CURSOR FOR
  SELECT name
  FROM   sys.certificates
  WHERE  name NOT LIKE '##%'
OPEN CUR
FETCH NEXT FROM CUR INTO @CerName
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(MAX)

      SET @SQL=''
      SET @sql = 'BACKUP CERTIFICATE ' + @CerName
      SET @sql = @sql + ' TO FILE = ''' + @FolderLocation
                 + @CerName + '.cer''' + CHAR(10)
      SET @SQL=@SQL + 'WITH PRIVATE KEY ( FILE='''
               + @FolderLocation + @CerName + '.key'',' + CHAR(10)
               + 'ENCRYPTION BY PASSWORD='''
               + CAST(NEWID() AS VARCHAR(50)) + ''')'
               + CHAR(10) + 'GO'

      PRINT @sql

      FETCH NEXT FROM CUR INTO @CerName
  END

CLOSE CUR
DEALLOCATE CUR


CREATE Certificate from Backup Copy


CREATE CERTIFICATE CertificateName
FROM FILE='C:\Scripts\Keys\CertificateName_TDECert.cer'
WITH PRIVATE KEY(FILE='C:\Scripts\Keys\Certificate_TDEKey.key',
DECRYPTION BY PASSWORD='Password$') 

DBA - Permissions To Execute SQL Server Profiler

Below code can be used to provide/revoke Permissions to user to run SQL Server Profiler.

USE MASTER
GO
GRANT ALTER Trace TO [DomainName\UserName]
--Grant Permission To Aamir To Run Profiler
GRANT ALTER Trace TO [MyDomain\Aamir]

--Revoke Permission For Aamir
REVOKE ALTER Trace TO [DomainName\UserName]
REVOKE ALTER Trace TO [MyDomain\Aamir]