none
BCP utility using remote server

    Question

  • We have a requirement to delete rows from table based on specific condition on remote server.
    Let's assume we have two server A and B.

    1) Delete rows from table on Server B.
    2) Export file using bcp from Server A to Server B through SQL Agent job.

    Can anyone please help me with Step 2? How do i setup SQL Agent job using bcp?
     
    Monday, February 11, 2019 6:42 PM

Answers

  • Do you know how to use bcp on your local server?

    You can use SSIS package to import data without too much coding and set  a job to run on your schedule.

    • Marked as answer by Chickoo79 Tuesday, February 12, 2019 2:04 AM
    Monday, February 11, 2019 7:17 PM
    Moderator
  • Try using the Object Explorer -> RIght Click on DB -> Tasks -> Import or Export Wizard. I have not done it, but you should be able to copy data from one sql server to another. At the end of the Wizard it gives you the option to save it as an SSIS package. You can then execute this dtsx package from a SQL Agent Job. 
    • Marked as answer by Chickoo79 Tuesday, February 12, 2019 2:04 AM
    Monday, February 11, 2019 7:57 PM
  • The job step would be a CmdExec job.

    Assuming that you want to copy entire table, the job would have these commands:

    cd \windows\temp
    bcp somedb.dbo.tbl format nul -n -f tbl.fmt -T -S ServerA
    bcp somedb.dbo.tbl out tbl.bcp -f tbl.fmt -T -S ServerA
    bcp theotherdb.dbo.tbl intbl.bcp -f tbl.fmt -T -S ServerB

    replace somedb, tbl and the otherdb with your actual names. As for the CD on top you can use something else, but just run the commands where you stand, as you may litter some system folder.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Chickoo79 Tuesday, February 12, 2019 2:04 AM
    Monday, February 11, 2019 11:15 PM

All replies

  • The bcp utility is used to bulk load your data, not deleting rows.

    Do you need to compare source and target to decide deleting rows? 

    If you want to delete all rows, you can issue a truncate command to empty your target table 

    Monday, February 11, 2019 6:51 PM
    Moderator
  • Yes i understand.

    First job will run on Server B first to delete the rows that we dont need.

    i.e delete from tabl1 where id=20

    Second job: Run bcp utility from Server A (query) and import the data to table1 on server B.

    Monday, February 11, 2019 7:01 PM
  • Simply add one step for your job to execute the delete query.
    Monday, February 11, 2019 7:03 PM
    Moderator
  • My questions is how can i setup BCP job on server B?

    Can you please help with script for Step B?

    Monday, February 11, 2019 7:12 PM
  • Do you know how to use bcp on your local server?

    You can use SSIS package to import data without too much coding and set  a job to run on your schedule.

    • Marked as answer by Chickoo79 Tuesday, February 12, 2019 2:04 AM
    Monday, February 11, 2019 7:17 PM
    Moderator
  • Try using the Object Explorer -> RIght Click on DB -> Tasks -> Import or Export Wizard. I have not done it, but you should be able to copy data from one sql server to another. At the end of the Wizard it gives you the option to save it as an SSIS package. You can then execute this dtsx package from a SQL Agent Job. 
    • Marked as answer by Chickoo79 Tuesday, February 12, 2019 2:04 AM
    Monday, February 11, 2019 7:57 PM
  • The job step would be a CmdExec job.

    Assuming that you want to copy entire table, the job would have these commands:

    cd \windows\temp
    bcp somedb.dbo.tbl format nul -n -f tbl.fmt -T -S ServerA
    bcp somedb.dbo.tbl out tbl.bcp -f tbl.fmt -T -S ServerA
    bcp theotherdb.dbo.tbl intbl.bcp -f tbl.fmt -T -S ServerB

    replace somedb, tbl and the otherdb with your actual names. As for the CD on top you can use something else, but just run the commands where you stand, as you may litter some system folder.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Chickoo79 Tuesday, February 12, 2019 2:04 AM
    Monday, February 11, 2019 11:15 PM