One idea is to put your dataset queries into stored procs and call those from your report.

My preference is to keep complex business logic such as this within the database....

I had to look up "corpuscles", for a moment I thought it was a new drink at starbucks.

Thank you all for taking the time and giving me your take...

Jeff, that's nice code, well done!

I would define first week as any days of the month that land on the first week of the month.  i.e. if September 1 lands on Friday, then September...

• This reply was modified 2 years ago by Phil Parkin.
• This reply was modified 2 years ago by Phil Parkin.

If you want a load into a new table, then create the empty table AND create the Clustered Index.  Then, forget SSIS... use BULK INSERT with the TABLOCK...

I agree!...

SELECT STRING_AGG(CONCAT('''',td.name,''''),',')
FROM #tbl_db td

For finding out whether DBs are being used, there is also the (simple, but somewhat brutal) option of taking them offline and waiting for someone to complain.

why are you dropping and recreating the indexes? what are you trying to accomplish with it?

(for Phil - OP has DROP_EXISTING = ON so indexes are there already while...

Are you saying that this proc runs slower in SSIS than it would if you were running it from SSMS?

Have you determined which statement is the slow one (I imagine...

If that is the syntax that the app will use, and you cannot change it, won't it fail anyway, because of the 'CALL'?

Not sure I understand. Best (and possibly only) way in SQL is to use a series of INSERT queries. If that's not the answer you were looking for, please clarify.

There is a Postgres forum here. I am sure that there are others. Why not ask questions like this in a forum where the other people use the same...

Yeah, that makes sense. But if the code is all in a single DB at the moment, I can't see why a linked server would be needed. Why not a...

I can't think of a great way of handling this – sounds like you've considered the possibilities.

Presumably you've discounted the idea of keeping your custom code in a completely separate...