This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'
/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
- all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'
*/
IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GO
CREATE PROCEDURE ExportToExcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@QueryText varchar(200) = null,
@filename varchar(200) = 'c:\ImportToExcel.xls'
)
AS
DECLARE @SQLServer int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(100),
@result_str varchar(255)
IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername
-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
