Help with excel output. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help with excel output.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-01-28 : 13:12:36

How can I get the out with Excel format. Here is the steps and output desire.
The trick is I want the excel out put with specific format date, I have tried using SSIS 2012 for several days and it did not work. The file must be in this format. I google and try several examples but it failed. Here is my codes below. Any help is greatly appreciate.
I have to schedule the job to run each night and output Excel file with date format below.
date format: DDMMYY append to the file.
Daily_Report_280115.csv or Daily_Report_280115.xlsx

----------------------------------------------------------------------

IF OBJECT_ID('dbo.usp_RptGetUserLogin', 'p') IS NOT NULL
DROP PROCedure dbo.usp_RptGetUserLogin
GO

CREATE PROCedure dbo.usp_RptGetUserLogin
(
@DBName VARCHAR(40)
)
AS
/*******************************************************************************
** Description:
**
**
**
** Written by:
** Written date: 01/23/2015
**
** Modifications:
** ------------+---------------+-------------------------------------------
** Date: | Author: | Reasons:
**
**
*******************************************************************************/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @SQLStr VARCHAR(8000)
,@crlf CHAR(1) = CHAR(13) + CHAR(10) -- carriage return, new line.

--INSERT dbo.#AuditUserLogin (LoginId, DBName, UserName, UserOrAlias)

--PRINT '-- go 2.'
SET @SQLStr = 'USE ' + @DBName + ';' + @crlf + 'SELECT CAST(p1.name AS VARCHAR(40)) AS [LoginName],
CAST(db_name() AS VARCHAR(40)) AS [DBName],
CAST(p.name AS VARCHAR(40)) AS [UserName],
CAST(CASE
WHEN (p.is_fixed_role = 1) THEN ''MemberOf'' -- 1 = in one of the DB role.
ELSE ''User''
END AS VARCHAR(20) ) AS [UserOrAlias]
FROM sys.database_role_members AS r
JOIN sys.database_principals AS p
ON (r.role_principal_id = p.principal_id)
JOIN sys.database_principals AS p1
ON (r.member_principal_id = p1.principal_id)
--WHERE (r.member_principal_id > 1) -- Exclusive dbo user.
ORDER BY p1.name ASC, p.name ASC;'

PRINT @SQLStr
EXECute (@SQLStr)
GO
------------------------------------------------------------------------

DECLARE @SQLString VARCHAR(5000)
,@sToday CHAR(6) = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '')
SELECT @sToday
SET @SQLString = 'OSQL /E -w2000 /d DBAMaintenance /S. ' + '/Q"EXECute dbo.usp_RptGetUserLogin @DBName = ''Dev''' + '" /o C:\ExcelOutPut\' + 'Daily_Report_' + RTRIM(@sToday) + '.csv'
SELECT @SQLString
--OSQL /E -w2000 /d DBAMaintenance /S. /Q"EXECute dbo.usp_RptGetUserLogin @DBName = 'Dev'" /o C:\ExcelOutPut\Daily_Report_280115.csv

--EXECute Master..xp_cmdshell @SQLString

-- Desired output in Excel in 4 columns:

LoginName DBName UserName UserOrAlias
dbo Dev db_owner MemberOf
MyDomain\QA_ReadOnly_DB Dev db_datareader MemberOf
MyDomain\Dev_ReadOnly_DB Dev db_datareader MemberOf
MyDomain\languyen Dev db_datareader MemberOf
MyDomain\languyen Dev db_datawriter MemberOf
Lam Dev db_backupoperator MemberOf
Lam Dev db_datareader MemberOf
Lam Dev db_datawriter MemberOf

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-29 : 09:15:10

Format the column in excel and keep as template. Every extract copy the template excel and extract the data to excel. Data will be populated as in your required format.

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources