Start a new topic

Messages are not shown if SELECT query occurs before error

[This topic is migrated from our old forums. The original author name has been removed]

Consider the following SQL script to run on SQL Server 2005: PRINT 'Message #1' SELECT 'This eliminates printed messages' PRINT 'Message #2' RAISERROR ('Error raised', 16, 1); When run, DbVis does not produce printouts in log and displays only error message: [i]13:03:24 [PRINT - 0 row(s), 0.000 secs] [Error Code: 50000, SQL State: S1000] Error raised ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors][/i]. If SELECT query is removed then DbVis displays printouts: [i] 13:05:55 [PRINT - 0 row(s), 0.000 secs] [Error Code: 50000, SQL State: S1000] Error raised Code: 0 --- Message #1 Code: 0 --- Message #2 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors][/i] This issue is similar to one reported here: http://www.dbvis.com/forum/thread.jspa?messageID=14600㤈 There explains why result from SELECT query is not displayed by DbVis, but I expect at least printouts to be shown in log. My environment: ------------------------- *Product Information* Product: DbVisualizer Pro 9.0.8 Build: #1940 (2013/07/16 15:16) Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_45 Java Vendor: Sun Microsystems Inc. OS Name: Windows 7 OS Arch: x86 OS Version: 6.1 ------------------------- *Connected Databases* Database Type: SQL Server Major Version: 9 Minor Version: 0 Profile: sqlserver Connection Message: Microsoft SQL Server 09.00.4035 jTDS Type 4 JDBC Driver for MS SQL Server and Sybase 1.2.7

[This reply is migrated from our old forums.]

Re: Messages are not shown if SELECT query occurs before error
Hi, You need to separate the statements with statement delimiters, e.g. semicolon or the word "go" on a separate line. When I run this: PRINT 'Message #1'; SELECT 'This eliminates printed messages'; PRINT 'Message #2'; RAISERROR ('Error raised', 16, 1); I get: 10:23:45 [PRINT - 0 row(s), 0.000 secs] Command processed. No rows were affected Code: 0 --- Message #1 10:23:45 [SELECT - 1 row(s), 0.009 secs] Result set fetched 10:23:45 [PRINT - 0 row(s), 0.002 secs] Command processed. No rows were affected Code: 0 --- Message #2 10:23:45 [RAISERROR - 0 row(s), 0.000 secs] [Error Code: 50000, SQL State: S1000] Error raised ... 4 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.011/0.000 sec [1 successful, 2 warnings, 1 errors] and a result set for the SELECT statement. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Messages are not shown if SELECT query occurs before error
Dear Hans, thank you for your reply. Unfortunately separating with GO means splitting script into separately executed batches. In that case I'm unable to use neither variables that are referenced in separately executed script parts, nor enclose whole script within transaction or TRY..CATCH block. Thus that solution is not quite suitable :( Edited by: abyss on 2013-09-07 11.48
[This reply is migrated from our old forums.]

Re: Messages are not shown if SELECT query occurs before error
Hi, I'll have another look at this, but I cannot guarantee that we can get it to work. How databases (and drivers) deal with statements like PRINT and RAISERROR through JDBC is not well defined, and it may not be possible to get hold of the output for all situations. In this case, when all statements are sent together to the database (i.e. without statement delimiters) the PRINT statements results in a SQLWarnings but the RAISERROR results in an SQLException when reading the result set for the SELECT statement, and it terminates all processing. So even if I try to get the warnings before processing the result set, I only get the result of the PRINT statement that comes before the SELECT. Is writing the code as a stored procedure an alternative for you? Not sure it would solve the problem, but maybe worth a try? Best Regards, Hans