Post reply

SQL Jobs

  • July 16, 2009 at 9:28 am

    #207082

    Hi,

    I have two SQL server 2005 database servers on the same network and really need to every night push data from one table in one database on one server to an identical table on the other database server.

    Can i use SQL jobs to do this? If so when I create a job I can target remote servers and an see where to write the command but I can't figure out where to select the remote server database.

    Any help / thoughts would be great.

    Thanks in advance.

  • July 16, 2009 at 5:43 pm

    #1025965

    I do something similar.

    I don't use the steps in the job to move the data, I execute a SSIS package from the job's first step. Is this a possibility?

  • July 16, 2009 at 9:39 pm

    #1025985

    I have to recommend the Integration services method. It is the fastest and most reliable..

    CEWII

  • July 17, 2009 at 2:26 am

    #1026056

    Thanks for your replies. I should explain that I am ok with adding SQL queries to my code but not so good with the inn’s and out's of setting this stuff up like this in management studio.

    The data that is being sent from the master server db to the other needs to be sent once a day and the table that is receiving can be emptied before the new data arrives. So like a delete from table x then a select from the other database z and insert into x

    Hope that make sense. Do you still think what you recommend is the best way? And is there anything specific I should Google?

    Thanks again

  • July 17, 2009 at 9:02 am

    #1026236

    Yes, that's exactly what I do with multiple tables. SSIS makes this very easy to see each step and troubleshoot if there is a problem.

    One other really nice thing is that once you have set the job up, all you have to do is add things to the SSIS package to have them run each day. Makes things really nice.

    My SSIS package is not deployed to a server, but is saved to a network share. That's where it runs from. You would set the job up with a step that was an SSIS Package step, choose File System and browse out to the package you created. It's actually pretty simple.

  • July 17, 2009 at 9:05 am

    #1026239

    Have you considered Sanpshot Replication?

  • July 17, 2009 at 9:36 am

    #1026264

    Also a good recommendation, the only reason I caution against is experience, replication is not for the faint of heart and this gentleman has expressed limited experience.. I think KISS comes into play.

    Also, the gent referenced DELETE from the table on the receiving side, I would do a TRUNCATE TABLE instead, you would waste a LOT of time, and the time would grow as more rows were added. The truncate operation is VERY fast and very cheap IO wise.

    Otherwise he was right on, truncate the receiver, and copy the data, no problem..

    CEWII

  • July 17, 2009 at 9:45 am

    #1026275

    Hello and thanks for all of your input, the more i learn the more i enjoy workign with SQL. Old hand i am going to look at what you mentioned as i tihnk i can find a use for that so thanks.

    Wesley Norton, great advice and i got it working throguh this. Really needed to get it sorted today and i have just fallen in love with SSIS Packages, never seen them before today.

    For anyone else that has this issue and wants a great method for transfering data and a whole lot more read up on this, it walked me through it with easy logical steps.

    http://it.toolbox.com/blogs/coding-dotnet/creating-a-ssis-package-24699

    Thanks again, i have lot's more SQL questions so i will be back shortly 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply