Automate deletion of SSIS packages from SSIS packagestore in SMDB

Once SSIS projects have been converted from Package Deployment to Project Deployment in Visual Studio, you may want to delete the deployed packages from the SSIS packagestore in MSDB on the SSIS servers. You can delete packages from the MSDB store one by one with SSMS. But it can be a tedious job if you have hundreds of packages on several servers (Development, Testing, Production environment). We can generate a script to delete the packages with the DTUTIL utility. 

Here is a very simple example

Before:

• SSIS project in Visual Studio in package deployment mode
• 2 packages PackageA & PackageB
• SSIS Servers (development and production)
• MSDB Package store

After:

• SSIS project in Visual Studio in project deployment mode
• 2 packages PackageA & PackageB

• SSIS Servers (development and production)
• SSISDB Integration Services catalog

Once the packages have been deployed in the SSISDB catalog, there is no need to keep them any longer in the MSDB Package store.

The following SQL script will generate a list of all packages in a given subdirectory and generate the SQL script to delete all of them. 

declare @SsisServers varchar(255);
declare @Parentfolder varchar(255);
declare @subfolders varchar(255);
declare @DeleteSubfolder bit;
SET @SsisServers = 'ServerDEV;ServerTST;ServerPRD';
set @Parentfolder = 'Genledger';
set @subfolders = 'Project1;Project2';
set @DeleteSubfolder = 1;
DECLARE @ts TABLE ( SsisServer varchar(255) );
insert into @ts SELECT * FROM STRING_SPLIT (@SsisServers, ';');
DECLARE @tf TABLE ( subfolder varchar(255) );
insert into @tf SELECT * FROM STRING_SPLIT (@subfolders, ';');
SELECT os.script
FROM @ts AS ts
OUTER APPLY (
SELECT  o.script
FROM    @tf AS t
        OUTER APPLY ( 
SELECT ('DTUTIL "/SQL ' +  @Parentfolder + '\' + t.subfolder + '\' + p.[name] + ' /DELETE /SOURCESERVER ' + ts.SsisServer +'"') AS script
FROM msdb.dbo.sysssispackages p
INNER JOIN msdb.dbo.sysssispackagefolders f
ON f.folderid = p.folderid
INNER JOIN msdb.dbo.sysssispackagefolders pf
ON f.parentfolderid = pf.folderid
WHERE pf.foldername =@Parentfolder
and f.foldername =t.subfolder
                    ) o
) os;
If @DeleteSubfolder <> 0
SELECT ('DTUTIL "/SOURCESERVER "' + ts.SsisServer + '" /FDELETE SQL;' +  @Parentfolder + ';' + t.subfolder) AS script
FROM @tf t
cross join @ts ts
go

The output of the script can then be copied into a Powerquery script:

DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerDEV"

DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerTST"

DTUTIL "/SQL GenLedger\Project1\PackageA /DELETE /SOURCESERVER ServerPRD"

DTUTIL "/SQL GenLedger\Project1\PackageB /DELETE /SOURCESERVER ServerPRD"

DTUTIL "/SQL GenLedger\Project2\PackageC /DELETE /SOURCESERVER ServerPRD"

DTUTIL "/SQL GenLedger\Project2\PackageD /DELETE /SOURCESERVER ServerPRD"

#delete subfolder

DTUTIL "/SOURCESERVER ServerDEV /FDELETE SQL;GenLedger;Project1"

DTUTIL "/SOURCESERVER ServerDEV /FDELETE SQL;GenLedger;Project2"

DTUTIL "/SOURCESERVER ServerTST /FDELETE SQL;GenLedger;Project1"

DTUTIL "/SOURCESERVER ServerTST /FDELETE SQL;GenLedger;Project2"

DTUTIL "/SOURCESERVER ServerPRD /FDELETE SQL;GenLedger;Project1"

DTUTIL "/SOURCESERVER ServerPRD /FDELETE SQL;GenLedger;Project2"

Run the script from the Powershell ISE

That's all folks!

Do not forget to enable Database Mail in SQL Server Agent too

After setting up Database Mail on a SQL Server, do not forget to enable the mail feature of SQL Server Agent.

SQL Server reduce the size of an overdimensioned transaction log file

use master
go

SELECT name, log_reuse_wait_desc FROM sys.databases

USE Mydatabase
go

CHECKPOINT

DBCC SHRINKFILE (N'MESTBIDW_DEV_log' , 48000)
GO

DBCC SQLPERF(LOGSPACE) --Optional -- just to see how much space
DBCC LOGINFO --Optional

Query to find specific job steps in SQL Server Agent jobs

To compare similar job steps on a server that ran several of Ola Hallengrens Maintenance scripts I came across this example to Get all job steps in SQL Server by Sufian Rashid.

I changed the WHERE condition to look for jobs names ending in ‘FULL’ and which executed a ‘sqlcmd’ statement:

SELECT JOB.NAME, STEP.STEP_ID, STEP.STEP_NAME, STEP.COMMAND

FROM Msdb.dbo.SysJobs JOB

    INNER JOIN Msdb.dbo.SysJobSteps STEP ON STEP.Job_Id = JOB.Job_Id

WHERE JOB.Enabled = 1

    AND (JOB.Name LIKE '%FULL' and STEP.COMMAND LIKE 'sqlcmd%')

ORDER BY JOB.NAME, STEP.STEP_ID

The query result:

This list allows me to easily compare the commands executed.

E.g. I saw that on line 3 there was as parameter with value 47, whereas on line 4 I had the same parameter with value 48.

The LogToTable parameter for the instance SQL1\STORE has value ‘N’, whereas this parameter has value ‘Y’ for the SQL1\PRD instance.

System backup software triggers unwanted SQL backups

Problem

Sometimes backup software that is used to backup the Windows servers on which SQL Server is running will trigger a SQL backup automatically. Although you may have setup a neat SQL backup schedule in SQL Server Agent with Full, Differential and transaction log backups, consequences are that whenever this Server backup software kicks in and triggers another full backup of your databases, your backup files may be the wrong ones in case you ever need to do a restore with your Full, differential and transaction log backups, because the order in which they are created by you is broken by the unwanted backup in the background.

You do not see the unwanted backups in the SQL server Agent log, but you will find them back in the standard SQL server logs. The SQL server log typically show lines like this:

• Database backed up. Database: master, creation date(time): 2015/02/19(16:52:39), pages dumped: 1356, first LSN: 388:415:117, last LSN: 388:465:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9603DF6C-C7E9-4A19-BAAF-C827EFE22023}7'}).
• I/O was resumed on database master. No user action is required.
• I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

The same messages are repeated for all the other system and user databases on that particular server.

Cause

The SQL server service called ‘SQL Server VSS Writer’ is used by the Windows backup software to trigger the SQL server backup. From SQL BOL:

When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.

• ‘Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.’

Solution

In the Services management console: Stop and disable the ‘SQL Server VSS Writer’ service.

Schedule to recycle the SQL Server Logs

A new SQL Server log file is created each time the SQL service is restarted. On a production server that is very sparingly restarted this can result in enormous log files.

Below is a script to schedule a weekly recycle of the SQL Server Logs. The number of log files is set to 12 before they are recycled. Thereby giving a 3 month long history of the logs.

Other combinations are possible, e.g. make a daily schedule and set the number of files to 30, this way the log files are shorter and the history is one month long.

---- set SQL server log recycle property to Recycle after 12 files

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12

GO

---- Create SQL Server Agent Job to do a weekly recycle (Saturday 12:00) of the SQL Server log

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC  msdb.dbo.sp_add_job @job_name=N'MSOL Recycle SQL Server Logs', 

        @enabled=1, 

        @notify_level_eventlog=0, 

        @notify_level_email=2, 

        @notify_level_netsend=2, 

        @notify_level_page=2, 

        @delete_level=0, 

        @category_name=N'Database Maintenance', 

        @owner_login_name=N'sa', 

        @notify_email_operator_name=N'ICT Helpdesk', @job_id = @jobId OUTPUT

select @jobId

GO

EXEC msdb.dbo.sp_add_jobserver @job_name=N'MSOL Recycle SQL Server Logs', @server_name = N'VMXYZ'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_jobstep @job_name=N'MSOL Recycle SQL Server Logs', @step_name=N'RecycleErrorLog', 

        @step_id=1, 

        @cmdexec_success_code=0, 

        @on_success_action=1, 

        @on_fail_action=2, 

        @retry_attempts=0, 

        @retry_interval=0, 

        @os_run_priority=0, @subsystem=N'TSQL', 

        @command=N'EXEC sp_cycle_errorlog', 

        @database_name=N'master', 

        @flags=0

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_update_job @job_name=N'MSOL Recycle SQL Server Logs', 

        @enabled=1, 

        @start_step_id=1, 

        @notify_level_eventlog=0, 

        @notify_level_email=2, 

        @notify_level_netsend=2, 

        @notify_level_page=2, 

        @delete_level=0, 

        @description=N'', 

        @category_name=N'Database Maintenance', 

        @owner_login_name=N'sa', 

        @notify_email_operator_name=N'ICT Helpdesk', 

        @notify_netsend_operator_name=N'', 

        @notify_page_operator_name=N''

GO

USE [msdb]

GO

DECLARE @schedule_id int

EXEC msdb.dbo.sp_add_jobschedule @job_name=N'MSOL Recycle SQL Server Logs', @name=N'Weekly Saturday 12:00', 

        @enabled=1, 

        @freq_type=8, 

        @freq_interval=64, 

        @freq_subday_type=1, 

        @freq_subday_interval=0, 

        @freq_relative_interval=0, 

        @freq_recurrence_factor=1, 

        @active_start_date=20150326, 

        @active_end_date=99991231, 

        @active_start_time=120000, 

        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

select @schedule_id

GO

On SQL Server 2012 and 2014 Use ALTER ROLE ADD MEMBER instead of sp_addrolemember

While creating some scripts in a test environment to create logins and add members I noticed that ALTER ROLE has been upgrade with the ADD MEMBER option. this option replaces the stored procedure sp_addrolemember used in SQL 2008 R2 and previous versions.

The syntax for ALTER ROLE has now been upgraded with the ADD MEMBER option:

ALTER ROLE role_name

{

      [ ADD MEMBER database_principal ]

    | [ DROP MEMBER database_principal ]

    | WITH NAME = new_name

}

For instance on a SQL Server 2008 R2 granting logins to a Database would be like this:

USE [AdventureWorks]

GO

CREATE USER [Mydomain\MyAccount] FOR LOGIN [Mydomain\MyAccount]

GO

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'Mydomain\MyAccount'

GO

With SQL 2012 or 2014 you can now create logins with the following script:

USE [AdventureWorks]

GO

CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]

GO

ALTER ROLE [db_datareader] ADD MEMBER [MyDomain\MyAccount]

GO

On the MSDN library site the SQL Server 2012 documentation for sp_addrolemember warns that this will be phased out in a future version

A quick way to convert integer to nchar right aligned with leading zeroes

For an existing database table I urgently needed to translate integer values into nchar with leading zeroes

e.g. for an nchar destination column of length 2:

• convert value 1 to ‘01’
• convert value 2 to ‘02’
• convert value 11 to ‘11’

The simplest solution that came to my mind at that moment was to add an integer value that was a multiple of base 10 with enough trailing zeroes to act as leading zeroes in the nchar field. In cas of the nchar(4) column I need 10000, in case of nchar(2) I need to add 100.

• convert value (100 + 1) to ‘101’
• convert value (100 + 2) to ‘102’
• convert value (100 + 11) to ‘111’

The result has one character too much, therefore I need to subtract this first character sith the SUBSTRING function:

• SUBSTRING(‘101’, 2, 2) to ‘01’
• SUBSTRING(‘102’, 2, 2) to ‘02’
• SUBSTRING(‘111’, 2, 2) to ‘11’

T-SQL example: I have a dimension table Dim_YearMonth that needs to be populated with data from a Dim_Year and Dim_Month table. The table Dim_YearMonth has one nchar(7) column calle [YearMonthName] that is used as display value in dropdown lists. this has to be populated with datalike: ‘2014-01’, ‘2014-02’.

I have a table Dim_Year with one smallint column YearId ranging from 2005 to 2025.

There is a second table Dim_Month with smallint column MonthId ranging from 1 to 12.

Part of the T-SQL statement to combine year and month into ‘YYYY-MM’ is:

, CAST(y.YearID as nchar(4))+ '-' + SUBSTRING( CAST( (100+m.MonthId) as NCHAR(3)),2,2) as YearMonthName

The T-SQL statement to select Year and month combined and insert into the table Dim_YearMonth:

select (y.YearID *100 + m.MonthId) as YearMonthId

    , CAST(y.YearID as nchar(4))+ '-' + SUBSTRING( CAST( (100+m.MonthId) as NCHAR(3)),2,2) as YearMonthName

    , YearID, MonthId

    from dbo.Dim_Year as y, dbo.Dim_Month as m

The result looks like this:

YearMonthId    YearMonthName    CurrentYear    CurrentMonth
201308         2013-08          2013           8
201309         2013-09          2013           9
201310         2013-10          2013           10
201311         2013-11          2013           11
201312         2013-12          2013           12
201401         2014-01          2014           1
201402         2014-02          2014           2
201403         2014-03          2014           3

Compare 2 SQL tables to find missing rows fix the difference.

Problem

I have 2 database tables in 2 different databases and they should contain the same data. In this particular case the data is inserted by an application that inserts the data row by row. The primary key is of type uniqueidentifier with default value newid(), so it will never be the same in both tables. The table should have the same number of rows and all columns should contain identical data.

Solution

The T-SQL clause EXCEPT can help to verify if the tables contain the same rows.

From the SQL-BOL: EXCEPT returns any distinct values from the left query that are not also found on the right query.

Example

Compare all columns:

SELECT * FROM db1.dbo.Product
EXCEPT
SELECT * FROM db2.dbo.Product

This will return all rows in table of db1 that are not found or different from the table in db2.

But with the primary key column that is always different (because the guid is generated per table and per row) all rows will be returned, even when the number of rows is equal and all the other columns are equal.

To compare the columns that are different except the primary key column we slect the columns by name, except the primary key column:

SELECT [name], [price], [categoryid], [supplierid] FROM db1.dbo.Product
EXCEPT
SELECT [name], [price], [categoryid], [supplierid] FROM db2.dbo.Product

In this case all the rows that are in db1 but not in db2 will be show.

To add the missing rows to the db2 table (the primary key has default value newid()):

INSERT INTO db2.dbo.Product ([name], [price], [categoryid], [supplierid])
SELECT [name], [price], [categoryid], [supplierid] FROM db1.dbo.Product
EXCEPT
SELECT [name], [price], [categoryid], [supplierid] FROM db2.dbo.Product

SQL 2008 Create a unique index on field with NULL values

According to ANSI standards a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In SQL Server a single NULL is allowed but multiple NULL values are not.

In SQL Server 2008,  it is possible to define a unique filtered index based on a predicate that excludes NULLS:

CREATE UNIQUE NONCLUSTERED INDEX ix_Mycolumn_notnull
ON MyTable(Mycolumn)
WHERE Mycolumn IS NOT NULL;

Use of spt_values to generate a short series of integer consecutive values

WARNING

In SQL server instance spt_values is an undocumented table in the master database. Do not use this table if you are worried that Microsoft might decide to drop it in a future release of SQL server. As of now it exists in SQL 2008 R2 and it still exists in SQL 2012.

Problem

Generate a series of ID badge numbers in the range between 1001 and 30000. The amount of ID badges that are ordered in one batch is between 20 and 100. Each time an order is created, the ID card records must be inserted automatically for each number in the range. E.g. when a new order is made for badges from 1501 to 1520, then 20 badge records must be created with these numbers. Instead of using a FOR loop in the SQL code or instead of generating my own table of consecutive numbers (which the customer did not prefer) I opted to use the numbers in the spt_values table.

Solution

Given the starting number and ending number, a T-sql statement must generate the range of numbers from start to end.

DECLARE @OrderSerialNrFrom int;

DECLARE @OrderSerialNrTo int;

SET @OrderSerialNrFrom = 1501;

SET @OrderSerialNrTo = 1520;

SELECT DISTINCT n = number + (@OrderSerialNrFrom - 1) 

        FROM master..[spt_values] WHERE number BETWEEN 1 AND (@OrderSerialNrTo - @OrderSerialNrFrom + 1)

GO

The result looks like this:

n
-----------
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520

(20 row(s) affected)

To insert new ID badge records with this range and data from the order, the T-SQL statement looks like this:

DECLARE @OrderSerialNrFrom smallint;

DECLARE @OrderSerialNrTo smallint;

DECLARE @OrderId int;

DECLARE @Comment nvarchar(100);

SET @OrderSerialNrFrom = 1501;

SET @OrderSerialNrTo = 1520;

SET @OrderId = 101;

SET @Comment = N'Expo 2014';

WITH a (SerialNr)

    AS (SELECT DISTINCT n = number + (@OrderSerialNrFrom - 1) 

        FROM master..[spt_values] WHERE number BETWEEN 1 AND (@OrderSerialNrTo - @OrderSerialNrFrom + 1)

        )

    INSERT INTO dbo.Badge (BadgeNr, IsActive, IsBlocked, Orderid, Comment)

    SELECT a.SerialNr, 0, 0, @OrderId, @Comment

        FROM a;

GO

Conclusion

With the aid of spt_vlaues I can easily create small ranges of records without adding complicated code or extra tables to the database.

In SQL 2012, there is a SEQUENCE object that could be used instead of spt_values for generating the number series.

SQL Server 2012 setup job notification by email

Step by step procedure

How to setup job notification by email on a newly installed SQL Server 2012.

• Setup Database Mail
• Create an operator for SQL server Agent
• Setup notification in the job properties

Setup Database Mail

First check with your mail administrator what the outgoing mail server is and if you need a real email account for the outgoing email or if a dummy email address will suffice.

Next screen click finish

Next screen: success message, click Close

Create an operator for SQL server Agent

Select properties

Use Windows account to grant SSIS packages R/W access to files on file share

Problem

To schedule a job that runs SSIS packages that need read/write access on folders and files on a file share, the windows account running the SSIS service needs access to the file share, or you can setup a proxy account with a dedicated windows account that has access to the file share and that can run the SSIS package. Packages are stored in the SSIS package store.

A solution is also explained in Running a SSIS Package from SQL Server Agent Using a Proxy Account

Solution

• Create a windows account that has sufficient rights on the file share.
  • e.g. MYDOMAIN\ssisfileuser with password xyz
• On the SQL server where the SQL Server Agent will be running the jobs, add the windows account as a new login.
  • In the user mapping of the Login properties, select the ‘msdb’ database and assign the ‘db_ssisoperator’ Database role membership for msdb to the login.
• To run the package from a SQL job, the SQL Agent has to use the windows account as a proxy, to define the proxy we have to define a credential, where the password of the windows user can be stored.
  • Under Security / Credentials add a new credential
    • Credential name: ssisfileusercred
    • Identity: MYDOMAIN\ssisfileuser
    • Password: xyz
  • Under SQL Server Agent / Proxies, add a new proxy
    • Proxy name: ssisfileuserproxy
    • Credential name: ssisfileusercred
    • Activate the following subsystems:
      • Operating system (cmdexec) (if you are still running on 64-bit version of SQL 2008, not on SQL 2008 R2)
      • SQL server Integration Services Package
• Define a new job to be run under the windows account
  • Under SQL Server Agent Jobs, add a new job
    • General
      • owner can be your own windows account name
      • category : I use ‘Data collector’ to put the job under this kind of category
    • Steps – add new step
      • Name: use a name of your choice, I use the same name as the package to run in this step.
      • Type: Sql Server Integration Services Package
      • Run as:ssisfileuserproxy
      • General
        • Package source: SSIS Package store
        • Server: my servername
        • Use windows Authentication is default and only setting
        • Package: select the package name to be run.
    • Schedules: add a schedule when to run the job.

With this approach you can schedule packages that need file access on a share to run from the SSIS package store.

How to report the space used for each table in a SQL server DB?

USE [MyDatabaseName]

GO

sp_msforeachtable 'exec sp_spaceused [?]'

GO

Data driven report subscription send one email to all subscribers in comma separated list

Question

With an existing data driven report solution, a scheduled report is sent to several subscribers by email individually. I;e. if there are 4 users that subscribe to the report in the subscription database then 4 emails will be sent by reporting services. The manager would like to have the report sent once with all the subscribers in the TO section in a comma separated list. that way he can see who else received the email.

Current situation

The subscription database basically looks like this

The Subscription table holds the report parameters needed to subscribe to a report

The UserData table holds general information about the Windows users that can be subscribers.

For the many to many relation between subscriptions and users, the relation has been defined in the table ‘SubscriptionUser’ where users can be assigned to one ore more subscriptions.

There is also a lookup table ‘ReportFormat’ that holds the name of various export formats available in SSRS.

The original statement used by the data driven subscription in reporting services was:

SELECT a.SubscriptionName, d.EmailAddress AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat

    , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody

FROM dbo.Subscription AS a

INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID

INNER JOIN dbo.SubscriptionUser AS c ON a.SubscriptionId = c.SubscriptionId

INNER JOIN dbo.UserData AS d ON c.UserId = d.UserId

WHERE a.SubscriptionId = 3 AND d.IsActive = 1

This statement will yield a number of lines, in this example 4 lines

jack@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
tom@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
pete@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
mike@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>

Solution

We need to have only one row returned from our data driven select statement. The Email addresses need to be concatenated in one semicolon (not comma!) separated string that will serve as the TO column.

• Select email addresses for the given report
  
  
  
  

  SELECT EmailAddress 

  FROM dbo.UserData AS dd

  INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId

  WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1

jack@abc.com
tom@abc.com

pete@abc.com

mike@abc.com




• This will yield 4 rows again, now these rows need to be concatenated into a single string. For this the T-SQL FOR XML PATH can be used
  
  SELECT  ';' + EmailAddress
  FROM
  (
      SELECT EmailAddress 
      FROM dbo.UserData AS dd
      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
  ) AS e FOR XML PATH('')
  
  
  ;jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com
  




• Now the first semicolon needs to be trimmed from this string, this can be achieved with the STUFF function in T-SQL. The complete select statement result needs to b used as argument to the STUFF function.
  SELECT STUFF(
              (
                  SELECT  ';' + EmailAddress
                  FROM
                  (
                      SELECT EmailAddress 
                      FROM dbo.UserData AS dd
                      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                  ) AS e FOR XML PATH('')
              ) ,1,1,'') AS EmailAddress
  
  
  
  jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com
  




• Finally the result has to be selected together with the other report subscripion parameters for the report with id 3.
  SELECT STUFF(
              (
                  SELECT  ', ' + EmailAddress
                  FROM
                  (
                      SELECT EmailAddress 
                      FROM dbo.UserData AS dd
                      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                  ) AS e FOR XML PATH('')
              ) ,1,1,'') AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat
      , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody
  FROM dbo.Subscription AS a
  INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID
  WHERE a.SubscriptionId = 3
  
  



• 
  

  The result is one line and a semicolon separated [TO] column

  jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com    true    Excel    Weekly FER report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly FER report. <br/><br/>Regards <br/>