Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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);
            context.SubmitChanges();
            var newProjects = context.Projects.Where(p => p.Flag == false);

            foreach (var project in newProjects)
            {
                SPWeb web = site.AllWebs.Add(
                    project.Id.ToString(),
                    project.ProjectName,
                    "Project Web",
                    1033,
                    site.GetWebTemplates(1033)[1],
                    false,
                    false);

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

                context.SubmitChanges();
            }
            base.Execute(targetInstanceId);
        }
    }

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);
connection.Open();
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;

command.ExecuteNonQuery();
connection.Dispose();

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!

share|improve this question
    
Are there any additional details in your 14\LOGS? –  Dave Wise Apr 11 '11 at 20:02
add comment

2 Answers

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.

share|improve this answer
    
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. –  Andrey Markeev Apr 11 '11 at 19:49
    
Run a SQL trace and see what is going on exactly. –  Muhimbi Apr 11 '11 at 20:45
add comment
up vote 0 down vote accepted

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 :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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