How to Export Result Set to Excell file

Question

How to Export Result Set to Excell file

shamshudheen

SSCrazy

Points: 2390
More actions
April 21, 2008 at 12:59 am

#71557

Hi Gurus,
I have one stored procedure which will give me Execution time taken of all of the views as a list, i want this result set in excell sheet
any help is highly appreciated
Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matija Lah SSCertifiable Points: 6429 More actions · Answer 1

In addition to using DTS you should look up OPENROWSET in Books Online.

However, if the Excel destination is used frequently, you should consider adding a permanent connection to it using linked servers (look up sp_addlinkedserver in Books Online).

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Javier-691283 SSC Enthusiast Points: 170 More actions · Answer 2

You can try to export using bcp...

We use this sproc, don't know where i saw first

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72841

http://www.eggheadcafe.com/software/aspnet/31906881/importing-saltable-to-exc.aspx

DECLARE (We made some modifications to use it 'cause having rare columns name):

ALTER PROC [dbo].[uSp_ExportData]

(

@dbName varchar(100) = 'master',

@sql varchar(5000) = '',

@fullFileName varchar(100) = ''

)

AS

BEGIN

IF @sql = '' or @fullFileName = '' BEGIN

SELECT 0 AS ReturnValue -- failure

RETURN

END

-- if DB isn't passed in set it to master

SELECT @dbName = 'use ' + @dbName + ';'

IF OBJECT_ID('##TempExportData') IS NOT NULL

DROP TABLE ##TempExportData

IF OBJECT_ID('##TempExportData2') IS NOT NULL

DROP TABLE ##TempExportData2

-- insert data into a global temp table

DECLARE @columnNames VARCHAR(8000), @columnConvert VARCHAR(8000), @tempSQL VARCHAR(8000)

SELECT @tempSQL = LEFT(@sql, CHARINDEX('FROM', @sql) - 1) + ' INTO ##TempExportData ' + SUBSTRING(@sql, CHARINDEX('FROM', @sql) - 1, LEN(@sql))

EXEC(@dbName + @tempSQL)

IF @@ERROR > 0

BEGIN

SELECT 0 AS ReturnValue -- failure

RETURN

END

-- build 2 lists

-- 1. column names

-- 2. columns converted to nvarchar

SELECT

@columnNames = COALESCE( @columnNames + ',', '') + '['+ column_name + ']',

@columnConvert = COALESCE( @columnConvert + ',', '') + 'CONVERT(NVARCHAR(4000),'

+ '[' + column_name + ']'

+ CASE

WHEN data_type IN ('datetime', 'smalldatetime') THEN ',121'

-- REMOVE NUMERIC DATA TYPE, WEIRD RESULTS...

--WHEN data_type in ('numeric', 'decimal') THEN ',128'

--WHEN data_type in ('float', 'real', 'money', 'smallmoney') THEN ',2'

WHEN data_type in ('datetime', 'smalldatetime') THEN ',120'

ELSE ''

END + ') AS ' + '[' + column_name + ']'

FROM tempdb.INFORMATION_SCHEMA.Columns

WHERE table_name = '##TempExportData'

ORDER BY tempdb.INFORMATION_SCHEMA.Columns.ordinal_position

-- execute select query to insert data and column names into new temp table

SELECT @sql = 'SELECT ' + @columnNames + ' INTO ##TempExportData2 FROM (SELECT ' + @columnConvert + ', ''2'' AS [temp##SortID]

FROM ##TempExportData UNION ALL SELECT ''' + REPLACE( REPLACE( REPLACE(@columnNames, ',', ''', '''), '[', ''), ']', '' ) + ''', ''1'') t ORDER BY [temp##SortID] ASC'

EXEC(@sql)

-- build full BCP query

SELECT @sql = 'bcp "' + @dbName + ' SELECT * FROM ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'

-- execute BCP

EXEC master..xp_cmdshell @sql, NO_OUTPUT

IF @@ERROR > 0 BEGIN

SELECT 0 AS ReturnValue -- failure

RETURN

END

DROP TABLE ##TempExportData

DROP TABLE ##TempExportData2

SELECT 1 AS ReturnValue -- success

END

GO

In @sql parameter you can send single or complex query or send a sproc.

EXECUTE:

Exec Employees..spExportData 'Employees','select * from EmpDetails','d:

\test5.xls'

declare @sql varchar(6800), @dbName varchar(100), @fullFileName

varchar(100)

select @dbName = 'Employees', @sql= 'select * from EmpDetails',

@fullFileName = 'd:\test5.xls'

exec Employees..spExportData @dbName, @sql, @fullFileName

Hope this helps.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

You may query SQL server directly from Excel.

Of course you need to have access to that instance of SQL Server from the machine there you open Excel.

_____________
Code for TallyGenerator

shamshudheen SSCrazy Points: 2390 More actions · Answer 4

Javier (4/24/2008)
You can try to export using bcp...
We use this sproc, don't know where i saw first
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72841
http://www.eggheadcafe.com/software/aspnet/31906881/importing-saltable-to-exc.aspx
DECLARE (We made some modifications to use it 'cause having rare columns name):
ALTER PROC [dbo].[uSp_ExportData]
(
@dbName varchar(100) = 'master',
@sql varchar(5000) = '',
@fullFileName varchar(100) = ''
)
AS
BEGIN
IF @sql = '' or @fullFileName = '' BEGIN
SELECT 0 AS ReturnValue -- failure
RETURN
END
-- if DB isn't passed in set it to master
SELECT @dbName = 'use ' + @dbName + ';'
IF OBJECT_ID('##TempExportData') IS NOT NULL
DROP TABLE ##TempExportData
IF OBJECT_ID('##TempExportData2') IS NOT NULL
DROP TABLE ##TempExportData2
-- insert data into a global temp table
DECLARE @columnNames VARCHAR(8000), @columnConvert VARCHAR(8000), @tempSQL VARCHAR(8000)
SELECT @tempSQL = LEFT(@sql, CHARINDEX('FROM', @sql) - 1) + ' INTO ##TempExportData ' + SUBSTRING(@sql, CHARINDEX('FROM', @sql) - 1, LEN(@sql))
EXEC(@dbName + @tempSQL)
IF @@ERROR > 0
BEGIN
SELECT 0 AS ReturnValue -- failure
RETURN
END
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT
@columnNames = COALESCE( @columnNames + ',', '') + '['+ column_name + ']',
@columnConvert = COALESCE( @columnConvert + ',', '') + 'CONVERT(NVARCHAR(4000),'
+ '[' + column_name + ']'
+ CASE
WHEN data_type IN ('datetime', 'smalldatetime') THEN ',121'
-- REMOVE NUMERIC DATA TYPE, WEIRD RESULTS...
--WHEN data_type in ('numeric', 'decimal') THEN ',128'
--WHEN data_type in ('float', 'real', 'money', 'smallmoney') THEN ',2'
WHEN data_type in ('datetime', 'smalldatetime') THEN ',120'
ELSE ''
END + ') AS ' + '[' + column_name + ']'
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
ORDER BY tempdb.INFORMATION_SCHEMA.Columns.ordinal_position
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'SELECT ' + @columnNames + ' INTO ##TempExportData2 FROM (SELECT ' + @columnConvert + ', ''2'' AS [temp##SortID]
FROM ##TempExportData UNION ALL SELECT ''' + REPLACE( REPLACE( REPLACE(@columnNames, ',', ''', '''), '[', ''), ']', '' ) + ''', ''1'') t ORDER BY [temp##SortID] ASC'
EXEC(@sql)
-- build full BCP query
SELECT @sql = 'bcp "' + @dbName + ' SELECT * FROM ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
EXEC master..xp_cmdshell @sql, NO_OUTPUT
IF @@ERROR > 0 BEGIN
SELECT 0 AS ReturnValue -- failure
RETURN
END
DROP TABLE ##TempExportData
DROP TABLE ##TempExportData2
SELECT 1 AS ReturnValue -- success
END
GO
In @sql parameter you can send single or complex query or send a sproc.
EXECUTE:
Exec Employees..spExportData 'Employees','select * from EmpDetails','d:
\test5.xls'
declare @sql varchar(6800), @dbName varchar(100), @fullFileName
varchar(100)
select @dbName = 'Employees', @sql= 'select * from EmpDetails',
@fullFileName = 'd:\test5.xls'
exec Employees..spExportData @dbName, @sql, @fullFileName
Hope this helps.

Thanks for your response

I know the bcp method but i am trying any other way

shamshudheen SSCrazy Points: 2390 More actions · Answer 5

Sergiy (4/24/2008)
You may query SQL server directly from Excel.
Of course you need to have access to that instance of SQL Server from the machine there you open Excel.

nice..can you explain in detail

Matija Lah SSCertifiable Points: 6429 More actions · Answer 6

What version of Excel are you using?

For instance, in Excel 2007 in the Data ribbon in the Get External Data section using the "From Other Sources" tool you can create a connection to a SQL Server instance and then either select a database object or use a query to retrieve data from the selected data source.

In Excel 2003 in the Data menu under Import External Data you have the "New Database Query..." option where you can define the connection to the external data source, select object and/or build queries using Microsoft Query.

But be very careful, as all changes to the data in the Excel worksheet will be reflected back to the source unless you disconnect after the data has been initially retrieved (Excel 2007) or if data editing has been disabled (Excel 2003).

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

shamshudheen SSCrazy Points: 2390 More actions · Answer 7

Thanks

i can't try it now, since my excell feature is not yet installed. i will try later. but i understand the other way from bcp

Robert Bedick Old Hand Points: 310 More actions · Answer 8

You might want to try QueryToDoc (http://www.schematodoc.com). It can export resultsets to Word, Excel, or HTML. With the command-line driven version, you can schedule a query, export the results to Excel, and then mail the Excel file as an e-mail attachment.