Debugging a Complex T-SQL Issue

Ever wonder how someone else does it? Watch as Brent Ozar debugs a query error that seems simple on the surface, gets progressively harder to nail down, and then finally solves it.

[@superbaran9042]

This is what I'm watching during my an hour long work meeting....

[@aler.4108]

Right choice, bro ;)

[@VladDBA]

It's so awesome that you've recorded this! I kept coming back to the issue trying to reproduce it with different instance configurations, but still no go.

[@mattcargile]

Holy smokes! An hour of Brent O!🎉

[@Leggolem_]

I’m over here screaming in my head watching this but only half way through. I know exactly why this is happening, because I ran into something like this recently. Hoping you figure it out before then end, if not, I’ll make sure to share the solution and reason. Excited to see you work through this! Edit: Yay! You got it fixed. Check reply for details on why I think this is happening.

[@Leggolem_]

Here’s why this is happening. Inside a stored procedure, things run within the context of the database the stored procedure is in, not the database you are calling from. So when the stored procedure has SELECT * FROM dbo.Log, you can be sure it will select from the Log table in the same database as the stored procedure. Think about how messed up things could get if this didn’t happen! How could you ever be sure your stored procedure will run as you intended? You can’t control what database people call your procedure from. Now things get complicated with the execution of dynamic sql. Looks like that happens in the context of the database you are calling from, instead of the stored procedures’s database. This is super important to know! If you depend on dynamic SQL running within the context of your stored procedure’s database, you may need to prefix the sp_executesql with your stored procedure’s database name. I’d have to think out how to work around that in this situation. Your solution seems to be a good one here. Anyway, figured I would share the main reason. Hope it helps!

[@Leggolem_]

I wonder if sp_executesql would be any different than EXEC() inside a stored procedure. Do both use the caller’s db? Does one use the stored procedure’s db? I’ll have to test this later to put my mind at ease, lol. Gotta solve the mystery!

[@Leggolem_]

Thinking about this more, if sp_executesql runs within the context of the caller’s db, then you might not even need to prefix the callers db into your synonym. Just running it inside the dynamic SQL might be enough. I’ll have to test this too!

[@romeo2473]

I have no idea what you guys are doing here, but I'm just commenting my way through. Don't mind me, carry on. Dog bless

[@eustacelufgren]

Last 12 years I've been at a place with (mostly) binary collation and the case sensitivity issues with Deadlock and DeadLock keeps jumping out at me (granted, most people aren't in binary collation)

[@joshash5944]

dynamic sql... synonyms... what could go wrong😃

[@scbtripwire]

I'm here because of the division by zero in the thumbnail's SQL. Wtf.

[@wfvdijk1]

to proof: use master go create procedure sp_testme as begin select * from sys.tables; end; go use sandbox -- change me go create table doesitshowup(id int); go exec sp_testme go

[@CaribouDataScience]

Just ask ChatGPT 😮

[@chebrubin]

Don't they have a CoPilot plugin for MS SQL Server Studio 2025?