Why would you want to do it without a union and in...

It already is one query.

I'm not sure if this will fix it but the first thing I would try is to make sure the statistics have been updated:

UPDATE STATISTICS...

I'm not sure if this will fix it but the first thing I would try is to make sure the statistics have been updated:

UPDATE STATISTICS dbo.mytable WITH...

Is it just one query that is...

It's deferred name resolution.

You can create a procedure if the table does not exist but not if the table exists and the columns do not exist.

don't know what tool you are using to execute the above...

don't know what tool you are using to execute the above - but...

IF @DB1 = 'TestDB' USE TestDB
ELSE IF @DB1 = 'ProdDB'  USE ProdDB

To stop the table locking you could use a rowlock hint:

-- Ensure we are using row-level locking
SELECT @intID = (idPool + 1)
  FROM IdConfiguration...

Does your dynamic SQL get executed as one string?

e.g.:

SELECT @ENV = 'USE ' + @DB1
SET @ENV = @ENV + CHAR(13) + CHAR(10) + 'SELECT * FROM myTable;'
EXEC...

To stop the table locking you could use a rowlock hint:

-- Ensure we are using row-level locking
SELECT @intID = (idPool + 1)
  FROM IdConfiguration WITH (ROWLOCK,...

select @uuuImportID,
       1 as hdrid,
       line.value('(abcdefIndex)[1]', 'int') as [Index],
      ...

Deleted duplicate

• This reply was modified 5 months, 3 weeks ago by  Jonathan AC Roberts. Reason: Deleted duplicate

Here's just one of the many proofs... Both the direct conversion from DATETIME2 to DATETIME and the faulty answer above produce the "silent failures" that I'm talking about". ...