BCP with differing criteria

[Steve Anderson]

I am familiar and happy with using BCP to export from SQL Server to a flat file

.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?

.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?

Thanks in anticipation

[Paul A]

Here is a sample of how to batch and verify success..
Save the sample below as a .bat and create another .bat file that passes the

d:
cd dba ransfer_extract
set LOGFILE=.log ransfer_extract.log

ECHO "BCP OUT table data_blue_metrics in progress..." > %LOGFILE%
bcp %2..vw_data_blue_metrics out d:dba ransfer_extractdatadata_blue_metrics.dat -S%1 -T -f.fmtdata_blue_metrics.fmt -o.outdata_blue_metrics.out
ECHO "BCP OUT table data_product in progress..." >> %LOGFILE%
bcp %2..vw_data_product out d:dba ransfer_extractdatadata_product.dat -S%1 -T -f.fmtdata_product.fmt -o.outdata_product.out
ECHO "BCP OUT table data_portfolio in progress..." >> %LOGFILE%
bcp %2..vw_data_portfolio out d:dba ransfer_extractdatadata_portfolio.dat -S%1 -T -f.fmtdata_portfolio.fmt -o.outdata_portfolio.out
ECHO "BCP OUT table data_TPP_organization in progress..." >> %LOGFILE%
bcp %2..vw_data_TPP_ORGANIZATION out d:dba ransfer_extractdatadata_TPP_ORGANIZATION.dat -S%1 -T -f.fmtdata_TPP_organization.fmt -o.outdata_TPP_organization.out
ECHO "BCP OUT table data_TPP_market_segment in progress..." >> %LOGFILE%
bcp %2..vw_data_TPP_MARKET_SEGMENT out d:dba ransfer_extractdatadata_TPP_MARKET_SEGMENT.dat -S%1 -T -f.fmtdata_TPP_market_segment.fmt -o.outdata_TPP_market_segment.out
ECHO "BCP OUT table data_blue_client in progress..." >> %LOGFILE%
bcp %2..vw_data_blue_client out d:dba ransfer_extractdatadata_blue_client.dat -S%1 -T -f.fmtdata_blue_client.fmt -o.outdata_blue_client.out
ECHO "BCP OUT table data_employee_client in progress..." >> %LOGFILE%
bcp %2..vw_data_employee_client out d:dba ransfer_extractdatadata_employee_client.dat -S%1 -T -f.fmtdata_employee_client.fmt -o.outdata_employee_client.out
ECHO "BCP OUT table data_client_product in progress..." >> %LOGFILE%
bcp %2..vw_data_client_product out d:dba ransfer_extractdatadata_client_product.dat -S%1 -T -f.fmtdata_client_product.fmt -o.outdata_client_product.out
ECHO "BCP OUT table data_client_portfolio in progress..." >> %LOGFILE%
bcp %2..vw_data_client_portfolio out d:dba ransfer_extractdatadata_client_portfolio.dat -S%1 -T -f.fmtdata_client_portfolio.fmt -o.outdata_client_portfolio.out
ECHO "BCP OUT table data_priority in progress..." >> %LOGFILE%
bcp %2..vw_data_priority out d:dba ransfer_extractdatadata_priority.dat -S%1 -T -f.fmtdata_priority.fmt -o.outdata_priority.out

REM document start time
ECHO " " >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
ECHO " transfer_extract INTERFACE BCP OUT UTILITY" >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
isql /S%1 /E /d%2 /n /Q"set nocount on select 'BCP Start Time: ' , getdate()" >> %LOGFILE%
ECHO " " >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
ECHO " transfer_extract INTERFACE BCP OUT UTILITY" >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
isql /S%1 /E /d%2 /n /Q"set nocount on select 'BCP End Time: ' , getdate()" >> %LOGFILE%
ECHO " " >> %LOGFILE%
ECHO "BCP PROCEDURE ENDED..." >> %LOGFILE%
REM document start time
ECHO " " >> %LOGFILE%
ECHO "Examine BCPs for errors..." >> %LOGFILE%
findstr /i /c:"rows copied" .outdata_blue_metrics.out && (ECHO "BCP data_blue_metrics OK..." >> %LOGFILE% && goto :next_bcp1)
(ECHO "BCP data_blue_metrics ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp1
findstr /i /c:"rows copied" .outdata_product.out && (ECHO "BCP data_product OK..." >> %LOGFILE% && goto :next_bcp2)
(ECHO "BCP data_product ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp2
findstr /i /c:"rows copied" .outdata_portfolio.out && (ECHO "BCP data_portfolio OK..." >> %LOGFILE% && goto :next_bcp3)
(ECHO "BCP data_portfolio ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp3
findstr /i /c:"rows copied" .outdata_TPP_organization.out && (ECHO "BCP data_TPP_organization OK..." >> %LOGFILE% && goto :next_bcp4)
(ECHO "BCP data_TPP_organization ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp4
findstr /i /c:"rows copied" .outdata_TPP_market_segment.out && (ECHO "BCP data_TPP_market_segment OK..." >> %LOGFILE% && goto :next_bcp5)
(ECHO "BCP data_TPP_market_segment ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp5
findstr /i /c:"rows copied" .outdata_blue_client.out && (ECHO "BCP data_blue_client OK..." >> %LOGFILE% && goto :next_bcp6)
(ECHO "BCP data_blue_client ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp6
findstr /i /c:"rows copied" .outdata_employee_client.out && (ECHO "BCP data_employee_client OK..." >> %LOGFILE% && goto :next_bcp7)
(ECHO "BCP data_employee_client ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp7
findstr /i /c:"rows copied" .outdata_client_product.out && (ECHO "BCP data_client_product OK..." >> %LOGFILE% && goto :next_bcp8)
(ECHO "BCP data_client_product ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp8
findstr /i /c:"rows copied" .outdata_client_portfolio.out && (ECHO "BCP data_client_portfolio OK..." >> %LOGFILE% && goto :next_bcp9)
(ECHO "BCP data_client_portfolio ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp9
findstr /i /c:"rows copied" .outdata_priority.out && (ECHO "BCP data_priority OK..." >> %LOGFILE% && goto :next_bcp10)
(ECHO "BCP data_priority ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp10
ECHO " " >> %LOGFILE%
:normal_exit
ECHO "BCP PROCESS ENDED..." >> %LOGFILE%
exit
:err_exit
ECHO "BCP PROCESS ABENDED..." >> %LOGFILE%
exit




------------
Steve Anderson at 7/12/2002 8:29:39 AM

I am familiar and happy with using BCP to export from SQL Server to a flat file

.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?

.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?

Thanks in anticipation

[Paul A]

Here is a sample of how to batch and verify success..
Save the sample below as a .bat and create another .bat file that passes the
database and server name.

As far as doing something dynamic there are many ways one of which is using the query option. Take a look at books online for BCP and you will see the query option. You can also create views of the tables and using the query option pass in parameters. Best bet though is really depending on what exactly you are doing and find out what best suits your needs.
d:
cd dba ransfer_extract
set LOGFILE=.log ransfer_extract.log

ECHO "BCP OUT table data_blue_metrics in progress..." > %LOGFILE%
bcp %2..vw_data_blue_metrics out d:dba ransfer_extractdatadata_blue_metrics.dat -S%1 -T -f.fmtdata_blue_metrics.fmt -o.outdata_blue_metrics.out
ECHO "BCP OUT table data_product in progress..." >> %LOGFILE%
bcp %2..vw_data_product out d:dba ransfer_extractdatadata_product.dat -S%1 -T -f.fmtdata_product.fmt -o.outdata_product.out
ECHO "BCP OUT table data_portfolio in progress..." >> %LOGFILE%
bcp %2..vw_data_portfolio out d:dba ransfer_extractdatadata_portfolio.dat -S%1 -T -f.fmtdata_portfolio.fmt -o.outdata_portfolio.out
ECHO "BCP OUT table data_TPP_organization in progress..." >> %LOGFILE%
bcp %2..vw_data_TPP_ORGANIZATION out d:dba ransfer_extractdatadata_TPP_ORGANIZATION.dat -S%1 -T -f.fmtdata_TPP_organization.fmt -o.outdata_TPP_organization.out
ECHO "BCP OUT table data_TPP_market_segment in progress..." >> %LOGFILE%
bcp %2..vw_data_TPP_MARKET_SEGMENT out d:dba ransfer_extractdatadata_TPP_MARKET_SEGMENT.dat -S%1 -T -f.fmtdata_TPP_market_segment.fmt -o.outdata_TPP_market_segment.out
ECHO "BCP OUT table data_blue_client in progress..." >> %LOGFILE%
bcp %2..vw_data_blue_client out d:dba ransfer_extractdatadata_blue_client.dat -S%1 -T -f.fmtdata_blue_client.fmt -o.outdata_blue_client.out
ECHO "BCP OUT table data_employee_client in progress..." >> %LOGFILE%
bcp %2..vw_data_employee_client out d:dba ransfer_extractdatadata_employee_client.dat -S%1 -T -f.fmtdata_employee_client.fmt -o.outdata_employee_client.out
ECHO "BCP OUT table data_client_product in progress..." >> %LOGFILE%
bcp %2..vw_data_client_product out d:dba ransfer_extractdatadata_client_product.dat -S%1 -T -f.fmtdata_client_product.fmt -o.outdata_client_product.out
ECHO "BCP OUT table data_client_portfolio in progress..." >> %LOGFILE%
bcp %2..vw_data_client_portfolio out d:dba ransfer_extractdatadata_client_portfolio.dat -S%1 -T -f.fmtdata_client_portfolio.fmt -o.outdata_client_portfolio.out
ECHO "BCP OUT table data_priority in progress..." >> %LOGFILE%
bcp %2..vw_data_priority out d:dba ransfer_extractdatadata_priority.dat -S%1 -T -f.fmtdata_priority.fmt -o.outdata_priority.out

REM document start time
ECHO " " >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
ECHO " transfer_extract INTERFACE BCP OUT UTILITY" >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
isql /S%1 /E /d%2 /n /Q"set nocount on select 'BCP Start Time: ' , getdate()" >> %LOGFILE%
ECHO " " >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
ECHO " transfer_extract INTERFACE BCP OUT UTILITY" >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
isql /S%1 /E /d%2 /n /Q"set nocount on select 'BCP End Time: ' , getdate()" >> %LOGFILE%
ECHO " " >> %LOGFILE%
ECHO "BCP PROCEDURE ENDED..." >> %LOGFILE%
REM document start time
ECHO " " >> %LOGFILE%
ECHO "Examine BCPs for errors..." >> %LOGFILE%
findstr /i /c:"rows copied" .outdata_blue_metrics.out && (ECHO "BCP data_blue_metrics OK..." >> %LOGFILE% && goto :next_bcp1)
(ECHO "BCP data_blue_metrics ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp1
findstr /i /c:"rows copied" .outdata_product.out && (ECHO "BCP data_product OK..." >> %LOGFILE% && goto :next_bcp2)
(ECHO "BCP data_product ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp2
findstr /i /c:"rows copied" .outdata_portfolio.out && (ECHO "BCP data_portfolio OK..." >> %LOGFILE% && goto :next_bcp3)
(ECHO "BCP data_portfolio ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp3
findstr /i /c:"rows copied" .outdata_TPP_organization.out && (ECHO "BCP data_TPP_organization OK..." >> %LOGFILE% && goto :next_bcp4)
(ECHO "BCP data_TPP_organization ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp4
findstr /i /c:"rows copied" .outdata_TPP_market_segment.out && (ECHO "BCP data_TPP_market_segment OK..." >> %LOGFILE% && goto :next_bcp5)
(ECHO "BCP data_TPP_market_segment ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp5
findstr /i /c:"rows copied" .outdata_blue_client.out && (ECHO "BCP data_blue_client OK..." >> %LOGFILE% && goto :next_bcp6)
(ECHO "BCP data_blue_client ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp6
findstr /i /c:"rows copied" .outdata_employee_client.out && (ECHO "BCP data_employee_client OK..." >> %LOGFILE% && goto :next_bcp7)
(ECHO "BCP data_employee_client ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp7
findstr /i /c:"rows copied" .outdata_client_product.out && (ECHO "BCP data_client_product OK..." >> %LOGFILE% && goto :next_bcp8)
(ECHO "BCP data_client_product ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp8
findstr /i /c:"rows copied" .outdata_client_portfolio.out && (ECHO "BCP data_client_portfolio OK..." >> %LOGFILE% && goto :next_bcp9)
(ECHO "BCP data_client_portfolio ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp9
findstr /i /c:"rows copied" .outdata_priority.out && (ECHO "BCP data_priority OK..." >> %LOGFILE% && goto :next_bcp10)
(ECHO "BCP data_priority ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp10
ECHO " " >> %LOGFILE%
:normal_exit
ECHO "BCP PROCESS ENDED..." >> %LOGFILE%
exit
:err_exit
ECHO "BCP PROCESS ABENDED..." >> %LOGFILE%
exit




------------
Steve Anderson at 7/12/2002 8:29:39 AM

I am familiar and happy with using BCP to export from SQL Server to a flat file

.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?

.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?

Thanks in anticipation

[Paul A]

Here is a sample of how to batch and verify success..
Save the sample below as a .bat and create another .bat file that passes the
database and server name.

As far as doing something dynamic there are many ways one of which is using the query option. Take a look at books online for BCP and you will see the query option. You can also create views of the tables and using the query option pass in parameters. Best bet though is really depending on what exactly you are doing and find out what best suits your needs.
d:
cd dba ransfer_extract
set LOGFILE=.log ransfer_extract.log

ECHO "BCP OUT table data_blue_metrics in progress..." > %LOGFILE%
bcp %2..vw_data_blue_metrics out d:dba ransfer_extractdatadata_blue_metrics.dat -S%1 -T -f.fmtdata_blue_metrics.fmt -o.outdata_blue_metrics.out
ECHO "BCP OUT table data_product in progress..." >> %LOGFILE%
bcp %2..vw_data_product out d:dba ransfer_extractdatadata_product.dat -S%1 -T -f.fmtdata_product.fmt -o.outdata_product.out
ECHO "BCP OUT table data_portfolio in progress..." >> %LOGFILE%
bcp %2..vw_data_portfolio out d:dba ransfer_extractdatadata_portfolio.dat -S%1 -T -f.fmtdata_portfolio.fmt -o.outdata_portfolio.out
ECHO "BCP OUT table data_TPP_organization in progress..." >> %LOGFILE%
bcp %2..vw_data_TPP_ORGANIZATION out d:dba ransfer_extractdatadata_TPP_ORGANIZATION.dat -S%1 -T -f.fmtdata_TPP_organization.fmt -o.outdata_TPP_organization.out
ECHO "BCP OUT table data_TPP_market_segment in progress..." >> %LOGFILE%
bcp %2..vw_data_TPP_MARKET_SEGMENT out d:dba ransfer_extractdatadata_TPP_MARKET_SEGMENT.dat -S%1 -T -f.fmtdata_TPP_market_segment.fmt -o.outdata_TPP_market_segment.out
ECHO "BCP OUT table data_blue_client in progress..." >> %LOGFILE%
bcp %2..vw_data_blue_client out d:dba ransfer_extractdatadata_blue_client.dat -S%1 -T -f.fmtdata_blue_client.fmt -o.outdata_blue_client.out
ECHO "BCP OUT table data_employee_client in progress..." >> %LOGFILE%
bcp %2..vw_data_employee_client out d:dba ransfer_extractdatadata_employee_client.dat -S%1 -T -f.fmtdata_employee_client.fmt -o.outdata_employee_client.out
ECHO "BCP OUT table data_client_product in progress..." >> %LOGFILE%
bcp %2..vw_data_client_product out d:dba ransfer_extractdatadata_client_product.dat -S%1 -T -f.fmtdata_client_product.fmt -o.outdata_client_product.out
ECHO "BCP OUT table data_client_portfolio in progress..." >> %LOGFILE%
bcp %2..vw_data_client_portfolio out d:dba ransfer_extractdatadata_client_portfolio.dat -S%1 -T -f.fmtdata_client_portfolio.fmt -o.outdata_client_portfolio.out
ECHO "BCP OUT table data_priority in progress..." >> %LOGFILE%
bcp %2..vw_data_priority out d:dba ransfer_extractdatadata_priority.dat -S%1 -T -f.fmtdata_priority.fmt -o.outdata_priority.out

REM document start time
ECHO " " >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
ECHO " transfer_extract INTERFACE BCP OUT UTILITY" >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
isql /S%1 /E /d%2 /n /Q"set nocount on select 'BCP Start Time: ' , getdate()" >> %LOGFILE%
ECHO " " >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
ECHO " transfer_extract INTERFACE BCP OUT UTILITY" >> %LOGFILE%
ECHO " ************************************************** *********************" >> %LOGFILE%
isql /S%1 /E /d%2 /n /Q"set nocount on select 'BCP End Time: ' , getdate()" >> %LOGFILE%
ECHO " " >> %LOGFILE%
ECHO "BCP PROCEDURE ENDED..." >> %LOGFILE%
REM document start time
ECHO " " >> %LOGFILE%
ECHO "Examine BCPs for errors..." >> %LOGFILE%
findstr /i /c:"rows copied" .outdata_blue_metrics.out && (ECHO "BCP data_blue_metrics OK..." >> %LOGFILE% && goto :next_bcp1)
(ECHO "BCP data_blue_metrics ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp1
findstr /i /c:"rows copied" .outdata_product.out && (ECHO "BCP data_product OK..." >> %LOGFILE% && goto :next_bcp2)
(ECHO "BCP data_product ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp2
findstr /i /c:"rows copied" .outdata_portfolio.out && (ECHO "BCP data_portfolio OK..." >> %LOGFILE% && goto :next_bcp3)
(ECHO "BCP data_portfolio ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp3
findstr /i /c:"rows copied" .outdata_TPP_organization.out && (ECHO "BCP data_TPP_organization OK..." >> %LOGFILE% && goto :next_bcp4)
(ECHO "BCP data_TPP_organization ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp4
findstr /i /c:"rows copied" .outdata_TPP_market_segment.out && (ECHO "BCP data_TPP_market_segment OK..." >> %LOGFILE% && goto :next_bcp5)
(ECHO "BCP data_TPP_market_segment ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp5
findstr /i /c:"rows copied" .outdata_blue_client.out && (ECHO "BCP data_blue_client OK..." >> %LOGFILE% && goto :next_bcp6)
(ECHO "BCP data_blue_client ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp6
findstr /i /c:"rows copied" .outdata_employee_client.out && (ECHO "BCP data_employee_client OK..." >> %LOGFILE% && goto :next_bcp7)
(ECHO "BCP data_employee_client ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp7
findstr /i /c:"rows copied" .outdata_client_product.out && (ECHO "BCP data_client_product OK..." >> %LOGFILE% && goto :next_bcp8)
(ECHO "BCP data_client_product ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp8
findstr /i /c:"rows copied" .outdata_client_portfolio.out && (ECHO "BCP data_client_portfolio OK..." >> %LOGFILE% && goto :next_bcp9)
(ECHO "BCP data_client_portfolio ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp9
findstr /i /c:"rows copied" .outdata_priority.out && (ECHO "BCP data_priority OK..." >> %LOGFILE% && goto :next_bcp10)
(ECHO "BCP data_priority ABENDED..." >> %LOGFILE% && goto :err_exit)
:next_bcp10
ECHO " " >> %LOGFILE%
:normal_exit
ECHO "BCP PROCESS ENDED..." >> %LOGFILE%
exit
:err_exit
ECHO "BCP PROCESS ABENDED..." >> %LOGFILE%
exit




------------
Steve Anderson at 7/12/2002 8:29:39 AM

I am familiar and happy with using BCP to export from SQL Server to a flat file

.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?

.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?

Thanks in anticipation