The problem was you had P.RowNum=1 as part of the left join.

Try this instead:

select * 
  from #tmpSTP P
  left join #tmpZATS Z
  ...

I would say that won't work as the SP is already creating a temp table using a insert into that can give Msg 8164, Level 16, State...

Why aren't you using the method Phil Parkin provided to insert the results from the SP into a table. Then you can do with it whatever you like?

i.e:

            
June 9, 2019 at 11:45 am
            
#3651545

            
            
                                                
        

There are third parties that have software to recover dropped tables.

ApexSQL https://solutioncenter.apexsql.com/4-techniques-for-recovering-lost-tables-due-to-drop-table-operation/

I've not tried it so I can't really comment any further.

I guess it's checking the publisher's certificate revocation on the internet but just times-out if there is no access to the website. If you stop it looking for it then...

SSMS was slow for me to start up on a production server that had no access to the internet (I'm not sure if this is your situation).

To fix this I...

You need to click the 'OK' button when you edit the joining line.

Then go into the destination and map the new column.

Have you tried right-clicking on the joining line and editing it, the missing column should be visible there.

Then just click 'OK' and you should see the column when you edit...

• This reply was modified 6 years, 3 months ago by Jonathan AC Roberts.

I'd urge you not to use a temp table for that.  If SQL goes down during the processing, you'll never be able to determine what the original key values...

You could backup the database then restore it to a different name.

then all you need is a script to drop the existing users and create new ones.

In fact you don't need any dynamic SQL.

This statement will do everything the dynamic SQL does:

SELECT icml.*
  FROM AMP.dbo.InterfaceConnectionMessageLog icml
 INNER JOIN AMP.dbo.InterfaceConnection ic 
 ...

If you create a temporary table by inserting the contents of table variable @messagesearchterms into it (say call the temporary table #messagesearchterms) you should be able to change the SQL...

I'm somewhat aware of this, but I don't have the precise answer. It's some kind of bug or glitch in the optimizer that gets a difference in row counts....

Maybe he's just saying that if you have an index that is never used, that could be a symptom of performance improvements to be made, by creating one that...

Here's a script to test it:

SET STATISTICS IO, TIME ON
GO
SELECT SalesOrderDetailId, OrderQty
  FROM Sales.SalesOrderDetail sod
 WHERE ProductId = (SELECT AVG(ProductId)
      ...