Sample Header Ad - 728x90

DAC silently swallows tokenization errors in sp_msforeachdb

2 votes
0 answers
44 views
This morning I hopped on the DAC to check something out. Oops, I made a typo!
1> exec sp_msforeachdb N'
2~ use [?]
3~ select N'?', count(*) from sys.sysrscols;
4~ '
5> go
1>
Can you spot my error? Pretty obvious when you see it, right? It's even more obvious if you type that command into SSMS. Frustratingly, it took me a _long time_ to figure it out. You will note that **no error message is returned** from the batch **1** (indicated by the 1> on the newline after go) Maybe errors get handle differently in the DAC? No... doesn't look like it...
1> select 1/0 -- simple error
2> go
Msg 8134, Level 16, State 1, Server VANDIVIER, Line 1
Divide by zero error encountered.
1> select ( -- syntax error
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1> exec (N'select ('); -- syntax error in deferred command
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1> exec sp_executesql N'select ('; -- sp_executesql
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1>
So clearly the DAC returns most errors to the client in just the way we expect. What the heckin' heck is happening here?! --- **1** You will also note that even though rowcount is set to on, no rowcount was returned to the client - which should have been a clue in hindsight.
Asked by Peter Vandivier (5678 rep)
Jun 17, 2019, 08:00 AM