Export SQL Server results as Excel files

I'm sure you'll often encounter problems exporting SQL Server query results to Excel. If you export fewer times, direct "Save Results as ..." Yes, but when it comes to sampling each table separately, it's pretty messy. Today to provide you with a detached Office components can be exported statements results to Excel process, I hope it will help!

---Export to excel

---use instructions:

--1. The server to which you are connecting at execution time determines which server the file resides on

--2. In the remote query statement, add the database name

ALTER PROC Exportfile

@QuerySql VARCHAR (max)

, @Server VARCHAR (20)

, @User VARCHAR (20)

, @Password VARCHAR (20)

, @FilePath NVARCHAR = ' C:exportfile.xls '

As

DECLARE @tmp VARCHAR = ' [# #Table ' + CONVERT (VARCHAR), NEWID ()) + '] '

BEGIN TRY

DECLARE @Sql VARCHAR (max), @DataSource VARCHAR (max) = ';

--Determine if the remote server

IF @Server <> '. ' and @Server <> ' 127.0.0.1 '

SET @DataSource = ' OpenDataSource (' SQLOLEDB ', ' ' Data source= ' + @Server + '; User id= ' + @User + '; Password= ' + @Password + ']. '

--Export the result set to the specified database

SET @Sql = REPLACE (@QuerySql, ' from ', ' in ' + @tmp + ' from ' + @DataSource)

PRINT @Sql

EXEC (@Sql)

DECLARE @Columns VARCHAR (max) = ', @Data NVARCHAR (max) = '

SELECT @Columns = @Columns + ', ' + name + ' '--Get column name (xp_cmdshell export file has no column name)

, @Data = @Data + ', Convert (nvarchar,[' + name + ']) '--Update the field that contains the result set to Nvarchar (avoid type conflicts when column names and data Union)

From tempdb.sys.columns WHERE object_id = object_id (' tempdb.. ') + @tmp)

Select @Data = ' SELECT ' + SUBSTRING (@Data, 2,len (@Data)) + ' from ' + @tmp

Select @Columns = ' SELECT ' + SUBSTRING (@Columns, 2,len (@Columns))

--Use the xp_cmdshell bcp command to export data

EXEC sp_configure ' xp_cmdshell ', 1

Reconfigure

DECLARE @cmd NVARCHAR (4000) = ' bcp ' + @Columns + ' Union all ' + @Data + ' "queryout ' + @FilePath + '-c-t '

PRINT @cmd

EXEC Sys.xp_cmdshell @cmd

EXEC sp_configure ' xp_cmdshell ', 0

Reconfigure

EXEC (' DROP TABLE ' + @tmp)

End TRY

BEGIN CATCH

--Handling exceptions

IF object_id (' tempdb ... ') + @tmp) is not NULL

EXEC (' DROP TABLE ' + @tmp)

EXEC sp_configure ' xp_cmdshell ', 0

Reconfigure

SELECT Error_message ()

End CATCH