Ccreate xls file based on the query passed

,

procedure will create xls spreassheet file based on the query passed and put it into the designated folder

/******************************************************************************************
'            		
' 	PROCEDURE:   	 	dbo.HPSP_UT_ExcelReport
'
' 	Author:   		Leah Kats
' 	Created Date:     	05/25/2005	
'				This utility stored 
'*******************************************************************************************/

ALTER    PROC HPSP_UT_ExcelReport
 @FileName	varchar(25)
,@SQL		varchar(8000)
,@WKS_Name 	varchar(100)
,@DDL		varchar(500)
,@FilePath	varchar(250)

	AS
	SET NOCOUNT ON

	DECLARE @Conn int -- ADO Connection object to create XLS
		, @hr int -- OLE return value
		, @src varchar(255) -- OLE Error Source
		, @desc varchar(255) -- OLE Error Description
		, @Path varchar(255) -- Drive or UNC path for XLS
		, @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
		, @WKS_Created bit -- Whether the XLS Worksheet exists	
		, @xlsDDL	varchar(1000)
		, @LocalDDL	varchar(1000)
		, @ServerName nvarchar(128) -- Linked Server name for XLS	
		, @Recs int -- Number of records added to XLS
		, @Log bit -- Whether to log process detail

-- Init variables
	SELECT @Recs = 0	
		, @Log = 1 
-------------------------------------------------------------------------------------------------------
--  assign a path and name for the XLS file
-------------------------------------------------------------------------------------------------------
	SET @Path = @FilePath + @FileName  + CONVERT(varchar(10),GETDATE(),112)+'.xls'
-------------------------------------------------------------------------------------------------------
-- assign the ADO connection string for the XLS creation
-------------------------------------------------------------------------------------------------------
	SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
-------------------------------------------------------------------------------------------------------
-- assign the Linked Server name for the XLS population
-------------------------------------------------------------------------------------------------------
	SET @ServerName = 'EXCEL_TEST'

	SET @xlsDDL = 'CREATE TABLE '+@WKS_Name+ ' (' +  @DDL + ')'

	SET @LocalDDL = REPLACE(@DDL, 'text', ' ')
	SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name +  ' (' +  @LocalDDL + ') ' +  @SQL

	IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
	-- Create the Conn object
	EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT

	IF @hr <> 0
	BEGIN
		-- Return OLE error
		EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
		SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
		EXEC @hr = sp_OADestroy @Conn
		RETURN
	END

	IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
	-- Set a the Conn object's ConnectionString property
	--   Work-around for error using a variable parameter on the Open method
	EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect

	IF @hr <> 0
	BEGIN
		-- Return OLE error
		EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
		SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc		
		RETURN
	END

	IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'
	
	-- Call the Open method to create the XLS if it does not exist, can't use parameters
	EXEC @hr = sp_OAMethod @Conn , 'Open'

	IF @hr <> 0
	BEGIN
		-- Return OLE error
		EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
		SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc		
		RETURN
	END
	
	EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @xlsDDL, NULL, 129 -- adCmdText + adExecuteNoRecords
	
	IF @hr = 0x80040E14 	
	BEGIN	
		PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
		SET @WKS_Created = 0		
	END
	BEGIN
		SET @hr = 0 -- ignore these errors		
	END
	
	IF @hr <> 0
	BEGIN
		-- Return OLE error
		EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
		SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
		RETURN
	END
	
	IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
	--!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!
	EXEC @hr = sp_OADestroy @Conn
	IF @hr <> 0
	BEGIN
		-- Return OLE error
		EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
		SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
		RETURN
	END
	
	IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
	BEGIN
		IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
		EXEC sp_addlinkedserver @server = @ServerName
	    		, @srvproduct = 'Microsoft Excel Workbook'
	    		, @provider = 'Microsoft.Jet.OLEDB.4.0'
	    		, @datasrc = @Path
	    		, @provstr = 'Excel 8.0' 
		
		EXEC sp_addlinkedsrvlogin @ServerName, 'false' 
	END
	
	EXEC (@SQL)
	PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'	
	
	IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
	BEGIN
		IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
		EXEC sp_dropserver @ServerName, 'droplogins'
	END

	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate