if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_exportData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_exportData]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE sp_exportData
@Login VARCHAR(25)= 'sa',
@Password  VARCHAR(25)= 'sa',
@Query VARCHAR(255) = 'SELECT * FROM Customers',
@ExportFile VARCHAR(25) = 'ExportData.txt',
@DatabaseName  VARCHAR(25) = 'Northwind'

 AS


/************************Variables required to create  the com object******************
**********************************************************************************************/
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)


/************************Other Variables******************
**********************************************************************************************/
DECLARE @Path VARCHAR(100)


SELECT @Path = "d:\Data"
SELECT @ExportFile  =@Path  +"\" + @ExportFile 

/*************************Create the com object ******************************************
**********************************************************************************************/
EXEC @hr = sp_OACreate 'ExportData.ExportClass', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

/*************************Set the properties for the object ******************************
**********************************************************************************************/
--set The Server Name to be the name of the Database Server 
EXEC @hr = sp_OASetProperty @object, 'server', @@ServerName
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END



--set The Login Name
EXEC @hr = sp_OASetProperty @object, 'Login', @Login
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END


--set The Password
EXEC @hr = sp_OASetProperty @object, 'Password', @Password
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END



EXEC @hr = sp_OASetProperty @object, 'DatabaseName', @DatabaseName
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END



EXEC @hr = sp_OASetProperty @object, 'FileName', @ExportFile
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END



EXEC @hr = sp_OASetProperty @object, 'SQL', @Query
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END



EXEC @hr = sp_OAMethod @object, 'MakeConnection'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END


EXEC @hr = sp_OAMethod @object, 'sqlToText '
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

/************************Destroy  the Object ******************************
**********************************************************************************************/
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END















GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO