I am trying to create a timer job that will read a list of new customers from a SQL server db, create a site for the customer and then update the table with the site's id. I have no trouble reading and creating the site but I can't seem to update the table. Am I missing something? Here is the code that I am using:

 public override void Execute(Guid targetInstanceId)

        SPWebApplication application = this.Parent as SPWebApplication;
        SPSite site = application.Sites[0];

        using (AccountPortalDataContext context = new AccountPortalDataContext(CONNECTION_STRING))
            context.UpdateProject(1, null, true);
            var newProjects = context.Projects.Where(p => p.Flag == false);

            foreach (var project in newProjects)
                SPWeb web = site.AllWebs.Add(
                    "Project Web",

                project.Flag = true;
                project.WebId = web.ID;


I have also tried creating a stored procedure and calling that with both linq:

context.UpdateProject(project.Id, project.WebId, project.Flag);

and SQLcommands:

SqlConnection connection = new SqlConnection(CONNECTION_STRING);
SqlCommand command = new SqlCommand("UpdateProject",connection);
command.Parameters.AddWithValue("@id", 1);
command.Parameters.AddWithValue("@webId", Guid.NewGuid());
command.Parameters.AddWithValue("@flag", true);
command.CommandType = CommandType.StoredProcedure;


When running the sqlcommand version I get the following error:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

I have also given the user sp_workerprocess db_owner access to the database to try and make sure that is not causing the issue. Thanks for any help!

  • Are there any additional details in your 14\LOGS?
    – Dave Wise
    Apr 11 '11 at 20:02

Unless specified otherwise in your connection string, your timer job will connect to the database using the credentials your Timer Service is running under, not the interactive user.

So make sure the Timer Service account has the appropriate privileges.

  • This is the connection sting that I am using: @"Data Source=localhost;Initial Catalog=[DB];Integrated Security=True" it looks like all other sql jobs executed by the timer are executed as sp_workerprocess so mine should use the same account correct?
    – omockler
    Apr 11 '11 at 18:09
  • I have checked, and the user that is executing these updates does in fact have db owner permission so there has to be another issue going on. Thanks for the suggestion tho :)
    – omockler
    Apr 11 '11 at 19:03
  • 1
    You need to check the account under which the SPTimerV4 windows service (or corresponding OWSTIMER.EXE process) is running. By default, it is NETWORK SERVICE account. Apr 11 '11 at 19:49
  • Run a SQL trace and see what is going on exactly. Apr 11 '11 at 20:45

I was eventually able to solve this problem with a series of solution deployments and server restarts. It seems that my features were not updating correctly. Now I just need to find out why visual studio isn't correctly deploying solutions :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.