If you have space on prod, restore it to  second DB on prod server, remove unneeded tables/content, then back that up and restore to dev.

Thank you all.

This was a purely educational matter.

I share your concerns re: cursors and dynamic SQL, but sometimes there is no other way.

At any rate, this report will likely run...

I could have been clearer: the logical reads are on the target of the INSERT.

It's an INSERT #Action/EXECUTE dbo.sp_executeSQL, inside a cursor. Could it be because it's dynamic SQL?

I SET...

• This reply was modified 4 years, 2 months ago by schleep.

I need to call the sprocs with named parameters, so I ended up just building a command in the cursor declaration.

I think there was always going to be some hard-code,...

So I'm running a bunch of reports through a cursor. Different DBs, different subsets of the same list of reports.

A couple of the reports have additional parameters. I was hoping...

Thanks for the succinct reply 🙂

For that matter, is there a way to even list the variables in the current scope?

Yup, that does it. I'd distilled Johan's solution down to yours. Perf not completely horrible in real life, and I only need to run this once a month.

Thank you both.

Hmmm... my OP had appeared to time-out without posting, which is why I reposted the question.

The solution was to restart the SQL Server service.

...and then restart the server. D'oh!!

Skipped right over that line in BOL

The .Net directory searcher and PoSh is the way to go. The AD cmdlets and the QAD cmdlets (Qwest AD -  or OneIdentity now, as mentioned in one of the...

Since all operations inside the loop are in a single DB, USE @db_name makes the code a lot easier on the eyes.

SET NOCOUNT ON, only to print out a custom...

scdecade,

Trust me. this works to eliminate the DBs where the table to be updated doesn't exist from the cursor that is supplying the @db_name.

SELECT Name FROM sys.databases

WHERE OBJECT_ID(Name + '.dbo.<tablename>')...

• This reply was modified 5 years, 2 months ago by schleep. Reason: Edited to add missing '.' before 'dbo.'

Declare @msg varchar(100)

DECLARE db_cursor CURSOR FOR

SELECT Name FROM Alldatabases

WHERE OBJECT_ID(Name + 'dbo.<tablename>') IS NOT NULL

ORDER BY Name

OPEN wh_cursor

In your post, you had 2 tables, Settings and Employee, so maybe you...

Wow. So simple now.

Still labouring in 2008, our next version is supposed to be 2016, with no set date for that.

I figure I'll be able to start using STRING_AGG sometime...

You say "occasional" errors. For me, this would go into a separate table with a FK to the main table, the error, and the emailed status (and date?)