I recommend the following steps to change it into a remote query:

1) change the main query completely into a dynamic query so it will run when using EXECUTE(@sql) where

Where can I find the queries?

Since it'll break all of a sudden and then work as usual I'd add a TRY CATCH block to the sproc where the CATCH block would call a separate sproc...

Did you try what I proposed to find the table in question?

I don't think it will help at all to know the linked server that is referenced.

The OP should try a

SELECT TOP 1 AUFNR FROM afpo

SELECT TOP 1 AUFNR FROM...

Would you mind being a little more specific?

A SQL Server Agent job can be considered as "automation", for instance...

Seems like there are some views involved since there's no reference to Tbl1008 in the code you've posted.

Most probably the underlying data structure has changed without notice (as usual when...

All I can think of is to deny SELECT in general and only allow EXECUTE.

However, I don't know if this will also work for system variables.

I don't think it's an issue of the queries being "large".

More likely the two while loops *cough* generate a huge number of execution plans being generated and displayed leading to...

I'm sorry Lynn for not providing any addtl. information how I ended up with the solution I posted....

Here's the "story behind":

I looked at the result set based on the sample...

Try this link.

Lynn Pettis (5/18/2014)

Depending on how many rows and columns are in the lookup table, a table scan of the lookup table may not be an issue. If we are...

Here's a slightly different approach using CROSS APPLY:

;WITH cte as

(

SELECT id_resource, max(id_shift) as last_shift

FROM @mytable

WHERE actionKindCode='section_decoupling'

GROUP BY id_resource

)

SELECT x.id_action, x.id_resource, x.actionKindCode

FROM cte

CROSS APPLY

(

SELECT TOP 1 id_action, id_resource, actionKindCode

FROM...

This will force the query to perform a table scan since...

One reason could be the end of the Mainstream Support for 2008R2 (July 2014).

On the other hand you'll need to be aware of the change in terms of licensing cost...