Post reply

Unable to Transfer Jobs from one instance to the other?????

  • December 3, 2008 at 5:18 pm

    #70961

    Hi,

    Iam trying move one instance of sql server 2000 SE (which is on a standalone machine) to another SQL server Devoloper edition(which is in a domain ABC\DBA1).

    Steps I followed:

    1.Backup databases in Server A

    2.Script the logins

    3.Script the jobs

    4.Install New Sql server 2000 dev edition in Server B and restore all the databases of Server A

    5.Run the login script

    6.Run the jobs script(Results in error)

    Do I need to change any thing in jobs script? because server B is in a domain ABC\DBA1, Server A is a standalone system

    I generated a script by right clicking on the job tree in

    Enterprise Manager. I then tried to run the script to

    create the job on another server and got the following

    error.

    Server: Msg 515, Level 16, State 2, Procedure sp_add_job,

    Line 105

    Cannot insert the value NULL into column 'owner_sid',

    table 'msdb.dbo.sysjobs'; column does not allow nulls.

    INSERT fails.

    The statement has been terminated.

    plz Advice me

    Thanks

  • December 4, 2008 at 4:32 pm

    #908012

    Madhu,

    It looks you have some job on source server which was owned by windows ID.

    Please check tht by running :

    select suser_sname(owner_sid)As JobOwner from sysjobs

    where suser_sname(owner_sid)<>'sa'

    and in the job script repalce the every occurence of olddomainname by the newone.

    HTH,

    MJ

  • December 5, 2008 at 11:46 am

    #908578

    Thanks Manu,

    After executing the commands, I got the below results

    select suser_sname(owner_sid)As JobOwner from sysjobs

    where suser_sname(owner_sid)<>'sa'

    Result:

    ABC\admin

    ABC\admin

    ABC\admin

    ABC\admin

    Here ABC is the Server name, from which Iam transfering the jobs to other server which is in a Domain

    called XYZ.

    So, In this case what are the steps to take inorder to transfer the jobs from Server A(Standalone) to Server B In Domain (XYZ).

    And Also How to transfer the Maintenance plans from Server A to Server B.?

    Thanks

  • December 6, 2008 at 4:58 am

    #908801

    For Transferring maintenance plans between servers:

    http://www.sqlservercentral.com/Forums/Topic614875-5-1.aspx

    -Hope is a heuristic search :smooooth: ~Hemanth

  • December 6, 2008 at 3:53 pm

    #908851

    Replace the occurences of ABC\ in job script to XYZ\ and execute the script. It shldn't gv any errors now.

    MJ

Viewing 5 posts - 1 through 4 (of 4 total)

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