Executing a Stored Proc. from a job

[ruioliveira]

Hi,

I have done a stored procedure that runs ok is I star it from Query Analyzer. The problem is that I need to run it during night so I created a job schedule to run it and when it starts this way generates an error after 18 minutes running.

Any help would be appreciated.
Thanks
Rui Oliveira

 

[JohnDTampaBay]

What is the error message that is being generated?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLSister]

What are you doing in this stored procedure that it takes so long to run? You didn't use a cursor did you?

I can suggest several possibiltes - you could have a permission problem with the SQL Server Agent account.

You could have a locking problem due to some other process which runs at night only.

You could be running into some type of timing out issue.

If it always locks up at exactly 18 minutes, I would check to see if anything else is scheduled to start at that time. If so, I would bet the culprit is the interaction between the two processes.


Or try scheduling it for a differnt time, does the thing still lock up?

Or run profiler one night to see exactly what is happening.



Questions about posting. See faq183-874

 

[ruioliveira]

I am getting the following error message:

Executed as user: NT AUTHORITY\SYSTEM. Cursorfetch: The number of variables declared in the INTO list must match that of selected columns. [SQLSTATE 42000] (Error 16924). The step failed.

This stored procedure is importing data from a Progress DB into SQL, during this import is transforming some data also. The number of record is also big, if you have any sugestion that may help would be appreciate

Thanks
Rui Oliveira

 

[SQLSister]

My first suggestion would be to eliminate the cursor, they are totally unneeded for inserts and updates and are extremely slow and wasteful of network and server resources.

For more specific advice you'd need to post the SP.

Questions about posting. See faq183-874