I am trying to execute a stored procedure and it executes until a certain point and stops at a certain query.
(How we know it stucks at a certain query -- we have logging into a table after every query is executed in the stored proc)
When we run the query (which runs slow) by itself it completes in 38 secs.
You need to generate an access plan for the Stored procedure and make sure that it is what you are get for the standalone query. If they are different,
then maybe rebinding the package(s) of the SP will fix the issue.