create proc p_bulkexport @server varchar(256) = null, /* Server name where data exists, defualts to the current server*/ @uname varchar(30), /* Login name that has select permission on the table being bcp'd */ @pwd varchar(30), /* Password for the login name specified */ @dbname varchar(128) = null, /* Database name, defaults to the current database */ @tbl_name varchar(255), /* Table name to export */ @output_path varchar(255), /* Ouput file name and path */ @cdelimiter char(1) = null, /* user defined column Delimiter, defaults to comma delimited */ @format_file varchar(255) = null, /* Optional format file */ @err_file varchar(255) = null, /* Optional error file path, defaults to C:\bcp_err.txt*/ @log_file varchar(255) = null, /* Optional log file path, defaults to C:\bcp_log.txt */ @max_errors int = 0 /* Maximum error allowed before BCP is aborted, defaults to never abort */ AS /*********************************************************************/ /** This script is designed for SQL Server 7.0 **/ /** Because of the limited access to xp_cmdshell (only members **/ /** of the sysadmin role have access to **/ /** xp_cmdshell using the SQL Server service account, all **/ /** non-sysadmins use the local account **/ /** SQLAgentCmdExec which has no access to domain resources), **/ /** the average end user cannot export data **/ /** to a UNC path. This procedure gives the end user that **/ /** capability without having to have access **/ /** to xp_cmdshell or being a member of the sysadmin role. **/ /** This procedure exports (BCPs) data from a **/ /** table using the OLE automation capabilities of SQL Server. **/ /** To run this stored procedure you will **/ /** need to create the two stored procedures listed in the BOL **/ /** under the topic "OLE Automation Return **/ /** Codes and Error Information". **/ /** **/ /** Create by: Buddy Ackerman (buddy__a@hotmail.com) **/ /** Created on: 5/4/99 **/ /** Modified on: 5/6/99 Added error and log file parameters **/ /** **/ /** Tables/Views Used: NONE **/ /** **/ /** Stored Procs Used: sp_OACreate **/ /** sp_OAMethod **/ /** sp_OASetProperty **/ /** sp_OAGetProperty **/ /** sp_displayoaerrorinfo **/ /** **/ /** **/ /** Local Variables: **/ /** @object SQL Server Object variable **/ /** @hr HRESULT from OLE stored procedures **/ /** @BCobject BulkCopy object variable **/ /** @Tobject Table object variable **/ /** @bcp_type Specifies the BCP file type **/ /** @rdelimiter Row deliniter **/ /** @property_string String used for creating the table object **/ /** **/ /** **/ /** **/ /** Returns: Number of row exported **/ /** **/ /** **/ /** **/ /*********************************************************************/ DECLARE @object int DECLARE @hr int DECLARE @BCobject int DECLARE @Tobject int DECLARE @bcp_type int DECLARE @rdelimiter char(2) DECLARE @property_string varchar(750) /* Sets the server to BCP from */ IF @server is NULL BEGIN SELECT @server = @@servername END /* Sets the database to BCP from */ IF @dbname is NULL BEGIN SELECT @dbname = db_name() END /* Create a SQL Server object */ EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr Return END /* Call Connect method */ EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server, @uname, @pwd IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Connect Error' Return END /*******************************************************************************************************/ /*************** Create a BulkCopy object and set some of the parameters *****************************/ /*******************************************************************************************************/ /* Create a Bulk Copy object */ EXEC @hr = sp_OACreate 'SQLDMO.BulkCopy', @BCobject OUT IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr Print 'Create BCP object error' Return END /* Set the bcp_type to be used when setting the DatFileType property of the BulkCopy object */ /* Set the @bcp_type to 2 initially to default to TAB delimited */ SELECT @bcp_type = 1 IF @cdelimiter is NOT NULL BEGIN SELECT @bcp_type = 3 /* User defined column delimiter */ END IF @format_file is NOT NULL BEGIN SELECT @bcp_type = 5 /* User defined format file */ END /* Set output file path parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'DataFilePath', @output_path IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set DataFilePath parameter error' Return END /* Set data file type parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'DataFileType', @bcp_type IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set DataFileType parameter error' Return END /* Set column delimiter and format file parameters if necessary */ IF @bcp_type = 3 BEGIN /* Set column delimiter parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'ColumnDelimiter', @cdelimiter IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set ColumnDelimiter parameter error' Return END /* Set row delimiter parameter */ SELECT @rdelimiter = CHAR(13) + CHAR(10) EXEC @hr = sp_OASetProperty @BCobject, 'RowDelimiter', @rdelimiter IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set ColumnDelimiter parameter error' Return END END ELSE BEGIN IF @bcp_type = 5 BEGIN /* Set format file path parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'FormatFilePath', @format_file IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set FormatFilePath parameter error' Return END END END /* Set max errors parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'MaximumErrorsBeforeAbort', @max_errors IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set DataFilePath parameter error' Return END IF @log_file = null SELECT @log_file = 'C:\bcp_err.txt' /* Set LogFilePath parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'LogFilePath', @log_file IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set LogFilePath parameter error' Return END IF @err_file = null SELECT @err_file = 'C:\bcp_err.txt' /* Set ErrorFilePath parameter */ EXEC @hr = sp_OASetProperty @BCobject, 'ErrorFilePath', @err_file IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Set LogFilePath parameter error' Return END /*****************************************************************************************************/ /***************** Create a table object pointing to table ************************************/ /***************** hm_parameters and execute it's export method ************************************/ /*****************************************************************************************************/ /* Create a Table object */ SELECT @property_string = 'Databases(' + @dbname + ').tables(' + @tbl_name + ')' EXEC @hr = sp_OAGetProperty @object, @property_string , @Tobject OUT IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Create Table object error' Return END /* Call table object's ExportData method and pass the BulkCopy object as the argument */ EXEC @hr = sp_OAMethod @Tobject, 'ExportData', NULL, @BCobject IF @hr <> 0 BEGIN Exec sp_displayoaerrorinfo @object, @hr PRINT 'Export data error' Return END RETURN