dopey beginner stuck with stored procedure and job creation 1

[petermeachem]

I'm sure this is going to be easy, but I've been trying to see what's wrong for ages now. Could some kind soul help me out.

I have some sql that consists of two INSERTS and 2 DELETES

i.e.

INSERT INTO ...
GO
DELETE FROM ...
GO
INSERT INTO ...
GO
DELETE FROM ...
GO

This works just fine in isql

Now, I want to run this weekly, so I put
CREATE PROCEDURE stuff AS

on the front and removed the GO's

Then I created a job consisting of
EXEC stuff

When I start the job, nothing appears to happen and certainly the tables are unaffected.

I have looked for a simple explanation, but, as I say, am well stuck here.

sql 7 by the way

http://www.therockalltimes.co.uk

http://www.ourhouseinportugal.co.uk/

 

[swampBoogie]

Add

SET NOCOUNT OFF

at the start of the procedure

 

[harebrain]

Use the Query Analyzer to

EXEC stuff

If that fails, the problem is with the sproc, otherwise you have a problem with the job you set up.

 

[SonOfEmidec1100]

Two things to check

Have you created the stored procedure?

Are you running the job against the right database ? (the default is master)

 

[petermeachem]

Runs ok from Query Analyzer. Perhaps my sql setup is faulty. Job history says there is no history. Job is against right database.

http://www.therockalltimes.co.uk

http://www.ourhouseinportugal.co.uk/

 

[pascalsql]

In query analyser type the following

drop procedure stuff
go
create procedure stuff 
as
begin
   insert your_table values (your_values)
   delete your_table where your_colomn = xxx
end
go

Execute it (press F5)
type:

exec stuff

Higlight this line and execute it (F5)
It should run and returns 'procedure executed succefully'
Then check the chandes are done

select * from your_table

Higlight this line and execute it (F5)

 

[SQLBill]

How did you create the job?

Did you set a schedule for the job?

-SQLBill

 

[petermeachem]

I don't have begin and end round the procedure.
I used the sql server agent new job wizardy thing. I didn't set a schedule. You should be able just to run a job by right clicking and selecting start shouldn't you?

http://www.therockalltimes.co.uk

http://www.ourhouseinportugal.co.uk/

 

[SQLBill]

Yes you should be able to start it that way. Go to the job, select properties, select the Steps tab, select the step and then click on Edit, and check these two things for us:

On the General tab, what is set for "Database:"

On the Advanced tab, what is set for "Run as user:"

Then go back and on the Notifications tab, is "Write to windows application log" checked or unchecked and what is in the text window for that line?

-SQLBill

 

[SQLBill]

One more thing, in the job listings, there are the columns "Enabled" and "Runnable", do both say YES for this job?

-SQLBill

 

[petermeachem]

On the General tab, what is set for "Database:"

The right one

On the Advanced tab, what is set for "Run as user:"

Self

Then go back and on the Notifications tab, is "Write to windows application log" checked or unchecked and what is in the text window for that line?

Write to nt application log is set, Whenever the Job Completes

Enabled and Runnable are both yes, Last Run Status is Unknown.

Do you have to have an operator to run a job? That's all blank as it is just my dev machine.

http://www.therockalltimes.co.uk

http://www.ourhouseinportugal.co.uk/

 

[SQLBill]

Try this:

On the Advanced tab, change Self to dbo.

-SQLBill

 

[petermeachem]

No difference. I've just looked on the event viewer and it informs me that I have run out of sql licenses. It's on small business server and it seems I only have one licence. Seems odd, but does a job need another licence?

http://www.therockalltimes.co.uk

http://www.ourhouseinportugal.co.uk/

 

[SQLBill]

It depends on how your licenses are set up. Are they by SEAT or by PROCESSOR? They appear to be by SEAT which is the same as saying by CONNECTION. That would limit how many connections you can have at one time to the server.

Let's say you login to the server - that's one connection
then you run a job - that's a second connection
then someone else logins in to the serve - that's connection three
then you run another job - that's a fourth connection

and if all are using the server at the same time and you are only allowed three connections, the last job won't work.

-SQLBill

 

[petermeachem]

Bill, that must be the problem. Thats fine as the customer has more than 1 licence!
Thank you so much for your help and patience.

http://www.therockalltimes.co.uk

http://www.ourhouseinportugal.co.uk/