Messages are not shown if SELECT query occurs before error
a
anonymous
started a topic
over 11 years ago
[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
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
a
anonymous
said
over 11 years ago
[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
Hans Bergsten
said
about 11 years ago
[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
anonymous