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.
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.
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!
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!
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!
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)
lol, this works because it is in an EXISTS clause, which ignores/skips the SELECT part of the query. It is only worried about whether rows exist. I tend to do "SELECT 1" for exists, but I think "SELECT 1/0" is hilarious so maybe I'll start sneaking that in occasionally.
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
This is what I'm watching during my an hour long work meeting....
Right choice, bro ;)
about the TSQL bug your experiencing lol
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.
Holy smokes! An hour of Brent O!🎉
When the word "synonym" loses meaning... fantastic video, Brent!
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
Wait, what caused the issue in the first place?
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.
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!
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!
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!
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)
We called them Heisenbugs.😄
Heh heh heh!
dynamic sql... synonyms... what could go wrong😃
I'm here because of the division by zero in the thumbnail's SQL. Wtf.
lol, this works because it is in an EXISTS clause, which ignores/skips the SELECT part of the query. It is only worried about whether rows exist. I tend to do "SELECT 1" for exists, but I think "SELECT 1/0" is hilarious so maybe I'll start sneaking that in occasionally.
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
Just ask ChatGPT 😮
Don't they have a CoPilot plugin for MS SQL Server Studio 2025?
I think mssql server 2025 is not still available. Azure might be better than on premises sql