Test doesnt get executed properly

[Matthias Melzner]

Hi,

I tried to run a small test in the Statement1 tab of the SQL workbench:

DROP PROCEDURE IF EXISTS stp_TestFM;

-- Stored Procedure in MS SQL

CREATE PROCEDURE stp_TestFM

AS SET NOCOUNT ON;

BEGIN

   PRINT 'I am a test: abc'

END;

 

EXECUTE stp_TestFM

When I execute my test the workbench does't print the expected message in the Messages tab. 

Thank you for the help in advance!

[Thomas Kellerer]

There is no API (neither "official" nor "unoffical") to retrieve those messages through JDBC (at least I am not aware of anything).

SQL Server used to return these on the Statement object as warnings, but apparently this doesn't happen anymore.
See SqlCommand.appendWarnings() and SqlUtils.getWarnings()

Oracle has a defined API through dbms_output to retrieve message like that and Postgres does return those messages as SQLWarnings on the Statement.

If SQL Server stopped doing that, I have no idea why and how to re-activate the old behaviour.

Regards
Thomas

[Franz Mayer]

Hi Thomas,

I just retried it. And the problem apparently seems to be the semi-colon after the SET command.

When I execute it in Workbench as following it works like a charm:

CREATE PROCEDURE stp_TestFM

AS SET NOCOUNT ON

BEGIN

   PRINT 'Ich bin ein Test: abc'

   RAISERROR (N'Ich bin eine Warnung', 9, 1)

END;

In MS SQL Server Management Studio however, it doesn't makes a difference, if you a semi-colon in between; that means the Statement Matthias posted works in SSMS also fine.

Is there maybe any line / statement delimiter setting to be adjusted?

Thanks in advance and beste regards,

Franz

[Thomas Kellerer]

If you want to embed semicolons inside a block, you will have to use an alternate delimiter, otherwise the block would be split at the semicolon.

In SQL Server it's common to use GO as this delimiter:

When I run this:

CREATE PROCEDURE test_print

AS
SET NOCOUNT ON;
BEGIN
PRINT 'I am a test: abc';
END

GO

EXECUTE test_print
go

I do see the output "I am a test: abc" in the message pane.

Wether or not the message is displayed seems to depend on a combination of SQL Server and the driver version.

Regards
Thomas

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/af75f3bb-f12a-4ad2-a444-c4af2a29032en%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/af75f3bb-f12a-4ad2-a444-c4af2a29032en%40googlegroups.com?utm_medium=email&utm_source=footer>.