SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[13.0.53 Beta] - Query Error resulting in SA Error

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[13.0.53 Beta] - Query Error resulting in SA Error
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2151

Post [13.0.53 Beta] - Query Error resulting in SA Error Reply with quote
I tried executing a stored procedure in SQL Server that moves around some rows in some tables. There was a runtime error in it
Quote:

Msg 4448, Level 16, State 17, Proc "usp_blabla_2_another_blabla", Line 252, Cannot INSERT into partitioned view 'blablabla' because values were not supplied for all columns.: .NET Exception

but instead of displaying it in the Messages pane, it simply threw an error/exception in SA/SE. Also, it somehow caused the server to abort and skip the CATCH block and all the error handling in it completely, even when executing it from SQL Server Management Studio, so there's definitely something fishy happening here. Yet, when run from SSMS, the error message gets to the Messages pane instead of SSMS throwing an exception.


I've sent the error details from Error dialog (issue tracking number 44643).
Thu Jul 25, 2024 8:42 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7924

Post Reply with quote
Thank you so much, the database error triggered an exception in SQLClient client libraries, which for unclear reason raised it as a .NET Exception, and the .NET Exception was treated as a run time error, instead of a database error. Here is the logged error message including .NET Exception reference.

Msg 4448, Level 16, State 17, Proc "usp_datablocks_2_datahouse", Line 255, Cannot INSERT into partitioned view 'DATAHOUSE.dbo.vp_adatblokk_dt' because values were not supplied for all columns.: .NET Exception

We are looking into that issue.
Fri Jul 26, 2024 8:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7924

Post Reply with quote
The .NET Exception in the client is unexpected. It's treated as non database exception, for which a an error dialog is shown. That part has worked as designed.
Interesting to note that the SQL Server error stack does *not* contain the offending INSERT statement, which is what I believe is expected in such cases, it contains top few lines of the procedure code.
Sat Jul 27, 2024 10:15 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 838
Country: United States

Post Reply with quote
I'm getting net exception messages on what seems like simple parsing.


Issue tracking number: 44668
Mon Aug 05, 2024 2:06 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7924

Post Reply with quote
Could you please check SQLAssist.log file in %app_data%\SQL Assistant \13 folder? Does the log contain additional errors or related messages at the time of the errors?
Mon Aug 05, 2024 4:04 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 838
Country: United States

Post Reply with quote
SysOp wrote:
Could you please check SQLAssist.log file in %app_data%\SQL Assistant \13 folder? Does the log contain additional errors or related messages at the time of the errors?


It appears to be the same messages (repeating more than once) that ends up in the exception popup.


Code:

8/6/2024 6:53:27 AM   Ssms.exe   Error: Msg 102, Level 15, State 1,    Incorrect syntax near '.'.
Msg 102, Level 15, State 1,    Incorrect syntax near '.'.
Msg 102, Level 15, State 1,    Incorrect syntax near '.'.
Msg 102, Level 15, State 1,    Incorrect syntax near '.'.: .NET Exception

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(SCHEMA_NAME(), ''dbo'')')
else
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(USER_NAME(), ''dbo'')')
Msg 102, Level 15, State 1,    Incorrect syntax near '.'.: .NET Exception

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(SCHEMA_NAME(), ''dbo'')')
else
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(USER_NAME(), ''dbo'')')
Msg 102, Level 15, State 1,    Incorrect syntax near '.'.
Msg 156, Level 15, State 1,    Incorrect syntax near the keyword 'GROUP'.: .NET Exception

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(SCHEMA_NAME(), ''dbo'')')
else
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(USER_NAME(), ''dbo'')')
Msg 102, Level 15, State 1,    Incorrect syntax near '.'.
Msg 156, Level 15, State 1,    Incorrect syntax near the keyword 'GROUP'.: .NET Exception

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(SCHEMA_NAME(), ''dbo'')')
else
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(USER_NAME(), ''dbo'')')

Tue Aug 06, 2024 7:54 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7924

Post Reply with quote
It looks like a collision with the background syntax check. Is the current setting set to defaul, check after 9 seconds of idle?
Tue Aug 06, 2024 8:08 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 838
Country: United States

Post Reply with quote
SysOp wrote:
It looks like a collision with the background syntax check. Is the current setting set to defaul, check after 9 seconds of idle?


It was set to 7 seconds after idle.

I don't know that it's the background syntax check doing it or not, because what triggers it is me executing a bad script in SSMS, then going to fix the offending problem (missing comma, in my example) and it IMMEDIATELY triggers once I press ANY keystroke (not necessarily comma) and not just at another execution or interval.

edit: Changing the Syntax Check interval to 9 seconds seems to have silenced the error for now. Even setting it back to 7 seconds I haven't been able to reproduce the thrown exception.
Tue Aug 06, 2024 8:13 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7924

Post Reply with quote
That's a good sign, but let's see if that holds.
Maybe something else in the settings got upset after upgrading to v13, and saving settings somehow helped with that.
Tue Aug 06, 2024 8:29 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 838
Country: United States

Post Reply with quote
This crept back in. I sent the error report

Issue tracking number: 44698
Mon Aug 26, 2024 10:24 am View user's profile Send private message
Vitaliy



Joined: 13 May 2015
Posts: 24
Country: Ukraine

Post Reply with quote
I have the same issue.
An error goes to the Windows' notification center which irritates.
Windows 10,
SSMS 19.1
Sun Sep 29, 2024 3:58 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7924

Post Reply with quote
You can disable the automatic syntax checker by setting the option to Off value. Syntax check if needed can be triggered manually using menus or hot keys.
Mon Sep 30, 2024 7:02 pm View user's profile Send private message
Vitaliy



Joined: 13 May 2015
Posts: 24
Country: Ukraine

Post Reply with quote
I disabled Automatic Syntax Checking in the DB Options for SQL Server.
Still, I get errors in the notification area



I believe the only way to go for now is to disable notifications in Common -> Error Handling -> If an error occurs. Am I right?
Sat Nov 09, 2024 1:40 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.