Add this before the SQL statement to collect textual showplan:

Set showplan_text on

go

>>I took a look at the query execution plan and I found that they we're the exact same in both cases

When you "took a look", was this the graphical execution...

You can also make things easier on yourself (and anyone else reading the code) by using table aliases:

UPDATE  t1

SET [prv_location] = t2.[Location]

FROM [v2_beta_small].[dbo].[beta] As t1

INNER JOIN [v2_lookup].[dbo].[nsg06may] As t2

  ON...

DELETE RL

FROM ReleaseLanguage AS RL

WHERE NOT EXISTS

(

  SELECT *

  FROM VideoRelease As VR

  WHERE VR.MuzeId = RL.MuzeId

  AND     VR.RelNum = RL.RelNum

)

If a foreign key constraint exists, with...

Question:

What exactly is  [v2_lookup].[dbo].[nsg06may].[col016]  ?

It is in the part of the query where a table is expected, but the 4-part naming indicates either a linked server (in which case [dbo]...

A drastic change in execution time, but with the correct result still being generated does not indicate an error situation. It is more likely that a query execution plan has changed...

>>haven't tested for speed but seems that it would be faster than either the correlated subquery

I generally get the same execution plan using NOT EXISTS or LEFT JOIN check for...

Any time you use the LEFT()/RIGHT()/SUBSTRING() functions in conjuntion with CHARINDEX(), you need to ensure that CHARINDEX() is not spitting back zero as a result. In your code, you're subtracting...

Replace the table variable with a temp table.

Create the table *outside* of any transactions, and create it via CREATE TABLE, or via SELECT INTO ... WHERE 0 = 1 so...

For NOT IN with multiple join columns, you need a correlated sub-query, which means you need NOT EXISTS:

INSERT INTO table1

SELECT colA, colB, colC, colD, colE

FROM table2 As t2

WHERE NOT EXISTS...

>>The OLE DB provider 'SQLNCLI' indicates that the object has no columns.

That's your clue. A DELETE does not return a resultset. OPENQUERY() expects a resultset. You can't use something that...

Procedure definitions are in the syscomments table:

Select object_name(id) As Routine_Name, colid As Chunk, text 

from syscomments

For large procedures with more than 4000 bytes of text, the procedure definition spans over...

Subtract 6 months from the MAX() date in the table ?

SELECT *

FROM YourTable

WHERE YourDateColumn >=

  ( SELECT DATEADD(m, -6, MAX(YourDateColumn)) FROM YourTable)

You implement If .. Then .. Else type logic in T-SQL using CASE ... WHEN:

SELECT @c = CASE WHEN @a > @b-2 THEN @a ELSE @b-2 END

You need to be aware of what security context xp_cmdshell is actually executing under:

"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will...