locked
Unable to tranfer jobs from sql2000 to sql2005 RRS feed

  • Question

  •  

    Hi

     

    Im in the process of upgrading my sqlserver 2000 to 2005.I want to migrate all the jobs from sql2000 to 2005.

    I scripted the job (right click job--->all tasks--->generate sqlscript).

    I ran the same script on sql2005,i was getting an error like this

     

    "Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137

    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."

     

    What does the error mean? cant i run the same script generated in sql2000  on sql2005.

    Wht is the approach to transfer the jobs from sql2000 to sql2005 ?

    I know ther is one more way i can transfer.It is through sqlserver business intelligence development studio and using transfer job task.But i  dont want to use that.I want to script the job and execute it on sql2005.

     

    Regards

    Arvind

    Tuesday, November 20, 2007 12:57 PM

Answers

  • Open the job script file , search for @owner_login_name =  and check what is the login name mentioned there. And check whether this login is created in the new Server and added to MSDB with enough permission

     

    Madhu

     

    Tuesday, November 20, 2007 4:34 PM

All replies

  • Hi

     

    Im in the process of upgrading my sqlserver 2000 to 2005.I want to migrate all the jobs from sql2000 to 2005.

    I scripted the job (right click job--->all tasks--->generate sqlscript).

    I ran the same script on sql2005,i was getting an error like this

     

    "Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137

    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."

     

    What does the error mean? cant i run the same script generated in sql2000  on sql2005.

    Wht is the approach to transfer the jobs from sql2000 to sql2005 ?

    I know ther is one more way i can transfer.It is through sqlserver business intelligence development studio and using transfer job task.But i  dont want to use that.I want to script the job and execute it on sql2005.

     

    Regards

    Arvind

     

    Tuesday, November 20, 2007 12:53 PM
  • Who is the owner of these jobs ?. Have you created same login and user in target server and database?

     

    Madhu

     

    Tuesday, November 20, 2007 1:30 PM
  • yes i have created same login and user in target server.Login is using an windows authentication and sqlserver service is a domain user.

    Why im i getting this error?

     

    Tuesday, November 20, 2007 4:22 PM
  • Open the job script file , search for @owner_login_name =  and check what is the login name mentioned there. And check whether this login is created in the new Server and added to MSDB with enough permission

     

    Madhu

     

    Tuesday, November 20, 2007 4:34 PM
  •  

    The owner of the job on old server was 'india\gm123' and the new server didnt have that user name in MSDB with enough permissions.when i created the same user in MSDB i was able to execute the script successfully. 

    Thank you everyone. 

    Saturday, November 24, 2007 5:04 AM
  •  

    Hi, I have the same problem,

     

    i get the same error inspite of the login having full user rights on all the databases, please help me

    Thursday, November 13, 2008 10:59 PM
  • Great solution. It solved my problem.

    In my case (as Madhu said), checking/modifying the variable @owner_login_name. The script created was making a reference to the old server, changing the name of the server and the user, the scrip was able to run without problems.

    Thank you!!!

    Monday, December 14, 2009 9:06 PM
  • Worked for me too, many thanks!
    Thursday, August 5, 2010 3:43 PM
  • Hi ,

     

    I got Solution, Thanks for your help.

    Tuesday, September 14, 2010 8:12 PM
  • Hi ,

     

    I got Solution, Thanks for your help.


    Tuesday, September 14, 2010 9:17 PM
  • Dear Mr.Madhu,

    I changed the name for @owner_login_name and it works fine and the issue is resolved. Thank you very much.

    Bye,TakeCare, Kannan Perumal....

    Thursday, November 18, 2010 11:15 AM
  • Thanks a lot for this solution. it worked for me as well.

     

    Thanks,

    Venkataramana M

    Wednesday, April 13, 2011 1:42 PM
  • Thanks Madhu, I was wondering for a fix. It also Worked for me. Thanks a lot
    Wednesday, May 11, 2011 12:26 PM