Caught errors on linked server

Question

Caught errors on linked server

p.zvonarev

Grasshopper

Points: 23
More actions
October 2, 2017 at 8:44 am

#323910
Hello!
I observe unexpected SQL Server behavior which associated with errors handling
- I have local SQL Server
- I have linked SQL Server ( [WIN2012\MSSQL2012] )
- I don't have UnknownTable on Linked Server
- Versions of these servers don't matter
First query:
begin try
exec [WIN2012\MSSQL2012].master.dbo.sp_executesql N'select * from UnknownTable '
end try
begin catch
print error_message();
end catch
Result: Error haven't been caught
Second query:
begin try
exec ( N'select * from UnknownTable' ) at [WIN2012\MSSQL2012]
end try
begin catch
print error_message();
end catch
Result: Error have been caught!
This behavior is repeated regardless of the type of error. It can be compilation error, statement terminating, batch aborting... doesn't matter
What's difference between exec_at and sp_executesql in this case ?
Thanks!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

I'm assuming you are executing these from the local server. That means that even though you are executing a remote stored procedure in the first case, you are executing that procedure on the local box, which is 1.) not a good idea for that specific sproc, and 2.) pointless for that specific stored procedure. With that specific sproc, I would NOT want to extrapolate that result to what would happen with a different stored procedure.

Additionally, as the first statement actually executed locally, and you expected it to run remotely, it's the how you went about executing it that gets you in some amount of trouble, because that effectively makes you trying to compare the results from running the same query both locally and remotely. As that "uknown table" may exist locally, but not remotely, that first query may well run successfully. As I can't see your environment, there's no way for me to know. Just understand that when you use EXEC with AT, you are executing the code specified on the remote server, where as in the first query, even though the stored procedure comes from the remote server, it's still going to be executed locally.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110651 More actions · Answer 2

Also, the server versions may well matter, as executing code with T-SQL feature such as LEAD or LAG on a server prior to version 2012 won't work.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

p.zvonarev Grasshopper Points: 23 More actions · Answer 3

sgmunson - Monday, October 2, 2017 8:57 AM
where as in the first query, even though the stored procedure comes from the remote server, it's still going to be executed locally.

Thanks for answer, but I don't understand what did you mean.
exec [WIN2012\MSSQL2012].master.dbo.sp_executesql N'select * from UnknownTable'
select * from UnknownTable This code will be executed on linked server and result will be returned to my query. Right?

sgmunson SSC Guru Points: 110651 More actions · Answer 4

p.zvonarev - Tuesday, October 3, 2017 6:24 AM
sgmunson - Monday, October 2, 2017 8:57 AM
where as in the first query, even though the stored procedure comes from the remote server, it's still going to be executed locally.
Thanks for answer, but I don't understand what did you mean.
exec [WIN2012\MSSQL2012].master.dbo.sp_executesql N'select * from UnknownTable'
select * from UnknownTable This code will be executed on linked server and result will be returned to my query. Right?

Nope. When you specify a stored procedure to be executed that happens to include a linked server as part of the name, that doesn't mean it executes ON the linked server. It will execute locally, meaning on the server your SSMS is connected to. You actually NEED the EXEC AT form of the command to get the procedure to execute somewhere other than locally.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110651 More actions · Answer 5

sgmunson - Tuesday, October 3, 2017 7:09 AM
p.zvonarev - Tuesday, October 3, 2017 6:24 AM
sgmunson - Monday, October 2, 2017 8:57 AM
where as in the first query, even though the stored procedure comes from the remote server, it's still going to be executed locally.
Thanks for answer, but I don't understand what did you mean.
exec [WIN2012\MSSQL2012].master.dbo.sp_executesql N'select * from UnknownTable'
select * from UnknownTable This code will be executed on linked server and result will be returned to my query. Right?
Nope. When you specify a stored procedure to be executed that happens to include a linked server as part of the name, that doesn't mean it executes ON the linked server. It will execute locally, meaning on the server your SSMS is connected to. You actually NEED the EXEC AT form of the command to get the procedure to execute somewhere other than locally.

In fact, once you use the EXEC AT form of it, you don't need the linked server name as part of the name of the stored procedure, because you will have provided that information after the keyword AT.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)