Find the “Unaccounted-For” Memory Using RAMMap

As a DBA, have you ever experienced or witnessed the “unaccounted-for” memory issue where memory utilization is at 100% or close to 100% and SQL Server is using only the Max Server Memory configured and the remaining majority of the memory is being used by other process or processes not showing up in the Task Manager processes tab? First of all, let me give you a warm welcome to the DBA world of “guilty until proven innocent!” Second, you need to get into the habit of being able to defend yourself from these wrongful, hurtful, and plainly ignorant accusations. These accusation will always end with “…It’s the DBAs issue. They need to fix it!” Third, be a bigger man or a more civilized woman, don’t argue with the folks doing the blame or else you will be in the same class as they are. But, share with them your technical findings with supportive evidences to prove the root causes of the issues. In this case, use RAMMap to identify or explain SQL Server is not using all the memory. It is VMWare Tools or other apps that are hogging all the memory.

You can read more details about RAMMap and download it on https://technet.microsoft.com/en-us/Library/Ff700229.aspx. Yes. It is free from Microsoft. One “red tape” removed here. But, you may have other bureaucratic policies that may prevent you from installing it. Similar to other advices that I had shared with you in the past years, watch some political debates or watch the Sunday news programs to see how the professionals, the politicians, deploy diplomacy and how to get it done!

Here’s an example of “Driver Locked” using 21,811,372 KB of memory and it does not show up in Task Manager. As you click through the different tabs in RAMMap, you will be able to identify the app that’s using all these memory.
RAMMap

How to drop subscriptions in Transactional Replication?

Scenario: You are working on refreshing the staging environment by using the database backups from the Production environment. Prior to the database restores, you had removed the subscriptions and publications and then performed the database restores. You had then re-created the publications and tried to subscribe to them. You are greeted by the following error message:

SQL Server could not create a subscription for Subscriber “Subscription_InstanceName”
Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information. Changed database context to ‘MCFIData’. (Microsoft SQL Server, Error: 14058)”

Replication Error

How can you resolve this issue? First of all, you already know that prior to the refresh, replication worked fine. Installing new patches or .NET Framework 4.5 or new or any other software will not solve your problem. Don’t mislead by others. Trust yourself. You will just need to find a way to remove the subscription that you thought you had removed prior to the refresh. Here’s the code that I had used to resolve this issue:

-- Run this on the Publisher
-- Replace the Publishing_DB, Publication_Name, Subscriber_InstanceName, Subscriber_DB accordingly
use Publishing_DB 
exec sp_dropsubscription @publication =N'Publication_Name', @subscriber = N'Subscriber_InstanceName', @article = N'all', @destination_db = N'Subscriber_DB'
GO

Make sure you monitor your publishing instance for blocking while this code to remove the subscription is running and make assessments and kill the blockers if needed or permitted.

How to List the Login’s Databases Access and Database Role(s) on One Row?

Challenge: A SQL Server login has access to 3 databases. The database users will be the same as the login. You have been requested to list the login’s database access on the instance and the role(s) the login’s database user belongs to in each of the databases like the screen-shot blow:
Logins and DB Access and Roles

Here’s the solution. It is based on the blog, http://www.handsonsqlserver.com/how-to-find-all-database-users-and-their-role-membership/ in addition to utilizing the correlated query plus the STUFF() function:

-- find all logins and their database access and role memberships in one row
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


--SELECT dsr.dbname AS [Database Name],
--	dsr.dbuser AS [Database User],
--	dsr.dbrole AS [Database Role],
--	dsr.account_type AS [Account Type],
--	dsr.create_date,
--	dsr.modify_date
--FROM #dbs_users_roles AS dsr;


IF OBJECT_ID('tempdb..#dbs_users') IS NOT NULL
	DROP TABLE #dbs_users;

SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date,
	STUFF((SELECT '; ' + dr.dbrole 
			FROM #dbs_users_roles AS dr
			WHERE dr.dbname = dsr.dbname AND dr.dbuser = dsr.dbuser
			FOR XML PATH('')),1,1,''
	) AS [DB Roles]
INTO #dbs_users 
FROM #dbs_users_roles AS dsr
GROUP BY dsr.dbname, dsr.dbuser, dsr.account_type, dsr.create_date, dsr.modify_date


SELECT u.[Database User],
	u.[Account Type],
	STUFF((SELECT ' ' +'%[DB]: ' +urs.[Database Name]+ ' [DB Role(s)]:' +urs.[DB Roles]
		FROM #dbs_users urs
		WHERE urs.[Database User] = u.[Database User]
		FOR XML PATH('')),1,1,''
	) [DB Access and Roles]
FROM #dbs_users u
GROUP BY u.[Database User], u.[Account Type]

How to List the Server Roles for the Logins in a Row Separated by a Delimiter?

You have met the challenge to create the report to show all the logins and their corresponding servers roles. Now, some very wise people with pay grades much higher than yours have requested that you need to format the report so that the logins belonging to multiple server roles will be listed in one row rather than separate rows for server roles. For example, for demonstration purposes, I have added the TestLogin to both the sysadmin and the setupadmin server roles. Without added formatting, these two server roles will show in individual rows for the TestLogin. Then, I formatted the report so that the TestLogin will have both the sysadmin and setupadmin server roles listed in one row rather than the previous individual row for each. Here’s the screen-shot of the explanation and end result:
Formatting the Server Roles into a Row

The correlated subquery and the STUFF() function did all the magic. Here we go with the script:

-- find all logins and their server role memberships
IF OBJECT_ID('tempdb..#Logins_SrvRoles') IS NOT NULL
	DROP TABLE #Logins_SrvRoles;	

SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
INTO #Logins_SrvRoles
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')

-- formatting the server roles for the logins into a row
SELECT L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database],
	STUFF((SELECT '; ' + LR.[Server Role]
		FROM #Logins_SrvRoles LR
		WHERE LR.[Login Name] = L.[Login Name]
		FOR XML PATH('')),1,1,''
	) [SRV_Roles]
FROM #Logins_SrvRoles L
GROUP BY L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database]

How to find all logins and their server role memberships?

Have you ever wished to know or have you ever been requested to create a report to show what logins belong to what server roles? While sys.server_role_members alone can produce the logins and server roles by supplementing it using the system function SUSER_NAME() as in:

SELECT SUSER_NAME(sr.role_principal_id) AS [Server Role]
		, SUSER_NAME(sr.member_principal_id) AS [Login Name]
	FROM sys.server_role_members sr

If you need to see other user data columns such as is_disabled, create_date, modify_date, and default_database_name of the logins, you will need to join sys.server_role_members to sys.server_principals. The following is one way that this can be done:

-- find all logins and their server role memberships
SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')

How to find all database users and their role membership?

You need to generate security reports to retrieve the database users and their role memberships for SOX or other auditing reports requirements or for security troubleshooting purposes. SQL Server has a number of system tables when joined together can help you produce the security report needed. These tables are:

  • sys.sysusers
  • sys.sysmembers
  • master.dbo.syslogins

While these system table are considered deprecated, they are still being supported on SQL Server 2012 for backward compatibility. I will be working on an updated version when time permits. In case you have your version of the script using the updated versions of the system tables and views, please share. Updated version is now available using the sys.database_role_members and sys.database_principals system tables at the bottom of this blog.

Note that I have specifically not using the undocumented and unsupported stored procedure SP_MSFOREACHDB as it misses databases. A simple WHILE loop can accomplish the same.
Here we go with the script:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	login_name nvarchar(123),
	default_db nvarchar(123)
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME() AS [Database Name],
				su.name AS [Database User],
				CASE 
					WHEN (ug.uid is null) THEN ''public''
					ELSE ug.name
				END AS [Database Role],
				CASE 
					WHEN su.isntuser=1 then ''Windows Domain Account''
					WHEN su.isntgroup = 1 then ''Windows Group''
					WHEN su.issqluser = 1 then ''SQL Account''
					WHEN su.issqlrole = 1 then ''SQL Role''
				END AS [Account Type],
				l.loginname AS [Login],
				l.dbname AS [Default DB]
			FROM
			   sys.sysusers su LEFT OUTER JOIN
			   (sys.sysmembers sm INNER JOIN sys.sysusers ug ON sm.groupuid = ug.uid) ON su.uid = sm.memberuid
				LEFT OUTER JOIN master.dbo.syslogins l on su.sid = l.sid
			WHERE
			   (su.islogin = 1 and su.hasdbaccess = 1) and
			   (ug.issqlrole = 1 or ug.uid is null)';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.login_name AS [Login Name],
	dsr.default_db AS [Deafult Database]
FROM #dbs_users_roles AS dsr;

Updated on October 05, 2014 to use the sys.database_role_members and sys.database_principals system tables:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date
FROM #dbs_users_roles AS dsr;

How to Search All Stored Procedures for a Specific Keyword?

You have been assigned to support a SQL Server instance that your team has been given ownership of through recent re-organizations within your company. You need to search for the stored procedures that reference a specific table or keyword. There are no documentations and you do not have intimate or domain knowledge about the databases as they were created by developers that are no longer with the company or third-party software vendors that are no longer in business. Yes. It is a very tough job. Let’s get it done.

To search all the stored procedures in a database for a specific keyword:

-- search all stored procs in a database for a specific keyword
SELECT DB_NAME() AS [Database Name],
	OBJECT_NAME(object_id) AS [Stored Proc Name], 
	OBJECT_DEFINITION(object_id) as [Stored Proc Definition]
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Keyword_Search%'

What if you need to search all the stored procedures in all databases in a SQL Server instance for a specific keyword? Use the following script instead:

-- search all stored procs in all databases for a specific keyword
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdSearch nvarchar(max)

IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
	DROP TABLE #db_list

CREATE TABLE #db_list
(
	id int identity (1,1)
	, dbname nvarchar(123)
);

IF OBJECT_ID('tempdb..#dbs_storedprocs') IS NOT NULL
	DROP TABLE #dbs_storedprocs

CREATE TABLE #dbs_storedprocs
(
	dbname nvarchar(123),
	storedproc_name nvarchar(250),
	storeproc_definition nvarchar(max)
);


INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

SELECT @id = 1, @max = max(id)
FROM #db_list

WHILE (@id <= @max)
BEGIN
	SELECT @dbname = dbname
	FROM #db_list
	WHERE id = @id;

	SET @cmdSearch = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
				OBJECT_NAME(object_id), 
				OBJECT_DEFINITION(object_id) 
		 FROM sys.procedures
		 WHERE OBJECT_DEFINITION(object_id) LIKE ''%Keyword_Search%''';

	INSERT INTO #dbs_storedprocs
	EXEC (@cmdSearch);

	SET @id = @id + 1;
END

SELECT dbname  AS [Database Name],
	storedproc_name  AS [Stored Proc Name],
	storeproc_definition AS [Stored Proc Definition]
FROM #dbs_storedprocs 

How to Search SQL Server Agent Jobs for a Specific Keyword?

Similar to being dropped off in the middle of a forest that you had never visited, you had been assigned to troubleshoot SQL Server Agent job failures on a new SQL Server environment that you had never worked on. You had asked for documentations and the server contacts. You have gotten laugther replies – the answer is no and the contacts are no longer with the employer. That shouldn’t be a surprise of the century for you. The requester acknowledges that while there are a high number of job failures throughout the network, the top priority is to find the job that execute the stored procedure that runs the “Flash Report” and it’s conveniently named “usp_RunDailyFlashReport” as the requester had learned from the former SQL Server DBAs. How can you find all the SQL Server Agent jobs that may run this stored proc in a New York minute or less? Let’s try the following script:

-- search the jobs for a specific text 
SELECT SERVERPROPERTY('SERVERNAME') as [InstanceName],
	j.job_id,
	j.name,
	js.step_id,
	js.command,
	j.enabled 
FROM	msdb.dbo.sysjobs j
JOIN	msdb.dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
WHERE	js.command LIKE '%KEYWORD%' -- replace keyword with the word or stored proc that you are searching for
GO

How to resolve the users “disabled” issue?

First of all, let’s clarify this up front! There’s no such thing as disabling and enabling users in SQL Server. If you keep on communicating to other professional SQL Server DBAs that you wish to disable or enable a user, we will put you in the “ESPN C’MON MAN!” segment because you had fumbled in the 1 yard line without anyone tackling you. Make no mistake about it. There’s disabling and enabling logins. But, there’s no such thing as disabling and enabling users! What if you see the user has the “Red Down” arrow? Do not Google too much on this topic or else some of the forums will tell you to remove the user and then create it again! Don’t do it. The following will describe the symptom and the remediation.

If you see the user with the “Red Down” arrow like the following screen-shot, the most likely cause is that someone had revoked the “CONNECT” privilege from the user or someone had just performed the following steps: 1) created a login from Windows, 2) created the user in the corresponding database, 3) then added the user to a database role. In both cases, the “CONNECT” privilege is missing.
User CONNECT Permission Revoked

To reproduce this, you can use the following steps:
1. Create the test login.

-- Create Login
USE [master];
CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; -- this is for demo only

2. Create the test user.

USE [AdventureWorks2012];
CREATE USER [Billy];

3. Add the user to a database role. Note: for SQL Server Logins, you will not experience the “CONNECT” not granted issue as in the case for Windows Logins.

USE [AdventureWorks2012];
EXEC SP_ADDROLEMEMBER 'db_datareader', 'Billy'

4. Revoke the “CONNECT” privilege from the user. Note: you need to use [] around the user as in [Billy]. You will then see the user “disabled” with the “Red Down” arrow next to it.

USE [AdventureWorks2012];
REVOKE CONNECT FROM [Billy];

Solution:
To resolve this issue, you will just need to grant the “CONNECT” privilege to the user. Yes. That’s all she wrote!

USE [AdventureWorks2012];
GRANT CONNECT TO [Billy];

To confirm the user has access:

-- To confirm the user has access to the database
USE [AdventureWorks2012];
SELECT name, 
	hasdbaccess
FROM sys.sysusers 
WHERE name = 'Billy'

How to Send the Output of a Stored Proc in CSV Format Using Database Mail?

You have been presented with a unique and interesting challenge. The company you work for wishes to see an automated report every Monday morning at 9am based on a query result in CSV format. It does not have Reporting Services installed. We all know that we can use Database Mail to send out html reports or send out alerts. Can we send out report in CSV format? Yes we can! Here’s what you need and how.

While there are no extra software programs or components required to accomplish this task, you do need to have Database Mail and xp_cmdshell enable. In addition, you need to create a folder as the repository for the CSV files to be generated. If you have not enabled Database Mail or do not have an email account handy, see my blog on “How to setup database mail using a gmail account?

Here’s an overview of the steps required:
1. Enable Database Mail if not already done.
2. Enable xp_cmdshell if not already enabled.
3. Create the stored proc for the report you wish to get automated and scheduled to run.
4. Create the folder as the repository for the CSV files to be generated.
5. If for whatever reason you are not able to use domain login to connect to the SQL Server instance, create the required login to use SQLCMD to connect to the corresponding instance with sufficient privileges to run the stored proc.
6. Create the TSQL script to run the steps to generate the CSV file and then send it out as an attachment.
7. Schedule a SQL Server Agent job to run the TSQL script you had created in step 6.

Implementation steps:
1. Enable Database Mail if not already done.
If not already enabled, then see my blog on “How to setup database mail using a gmail account?

2. Enable xp_cmdshell if not already enabled.

-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

3. Create the stored proc for the report you wish to get automated and scheduled to run. In this example, we will use the stored proc, [dbo].[uspGetBillOfMaterials] in AdventureWorks2012 as an example.
uspGetBillOfMaterials

4. Create the folder as the repository for the CSV files to be generated.
For this example, I have created the C:\BillOfMaterials on my laptop.

5. If for whatever reason you are not able to use domain login to connect to the SQL Server instance, create the required login to use SQLCMD to connect to the corresponding instance with sufficient privileges to run the stored proc.

USE [master]
GO

CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Billy] -- this is for testing purposes only
GO

6. Create the TSQL script to run the steps to generate the CSV file and then send it out as an attachment.

DECLARE @sqlCommand VARCHAR(999)
DECLARE @filePath VARCHAR(125)
DECLARE @fileName VARCHAR(125)
DECLARE	@fileAttachment VARCHAR(225)		
DECLARE @todaysDateFormatted VARCHAR(30)
DECLARE @emailSubject VARCHAR(50)
 
SET @filePath = 'C:\BillOfMaterials\' -- folder to store the csv files to be generated

-- generate the dynamic file name based on the date and time 
SELECT @todaysDateFormatted = CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_' +
		 CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR)

-- create the file name
SET    @fileName = 'BillOfMaterials_' + @todaysDateFormatted + '.csv'

-- create the report 
SET    @sqlCommand =
       'SQLCMD -S (local) -UBilly -PBob -d AdventureWorks2012 -Q "EXEC [dbo].[uspGetBillOfMaterials] 800, ''9/13/2014''" -s "," -o "' +
       @filePath + @fileName +
	   '" '
EXEC   master..xp_cmdshell @sqlCommand;

-- create the attachment
SET @fileAttachment = @filePath + @fileName;

SET @emailSubject = 'Bill of Materials Report ' + @todaysDateFormatted

EXEC msdb.dbo.sp_send_dbmail 
  @profile_name='SQLAlerts', -- change your database mail profile name here
  @recipients='jpchen@handsonsqlserver.com', -- change the recipient(s) here, separated by ;
  @subject=@emailSubject, -- email subject
  @body='Please see attachment for today''s report for Bill of Materials!', -- note to show up in the email body
  @file_attachments=@fileAttachment -- email attachment

Check your email to see the email and the report attached. Beautiful isn’t it?

CSVEmailAttachment

7. Schedule a SQL Server Agent job to run the TSQL script you had created in step 6.

REFERENCES:
sqlcmd Utility
http://msdn.microsoft.com/en-us/library/ms162773(v=sql.110).aspx

xp_cmdshell Server Configuration Option
http://msdn.microsoft.com/en-us/library/ms190693(v=sql.110).aspx