Friday, March 23, 2012

formatting output

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----
SET NOCOUNT ON
DECLARE @.errorCount INT
SELECT @.errorCount = COUNT(*) FROM error_report WHERE id != - 2 AND id != - 5
IF @.errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
--------
The Results in the bottom pane looks like this below
------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
------
But, when I do a select, they are like this below
--

NODETABLE 0 There are 2 duplicate subclass name: Specification
Propertytable 0 at Parentid = 2000000859 and propertyid = 721
Propertytable 0 at Parentid = 10122 and propertyid = 9That sounds like a grid limitation to me. I'd suggest Ctrl-T to set text mode, then Ctrl-E to execute the query again.

-PatP|||Hi Pat,
It outputted in the text format but the results are the same. However when I do select statement, it is fine.|||You need to explicitly specify the size for VARCHAR fields.|||Hi LOOKING AT THE ABOVE CODE, CAN YOU TELL ME WHERE IT IS?|||For example, here:

'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR(8)) )

But you also don't need that CAST.

'The Report was generated on ' + CONVERT(VARCHAR(8), GETDATE(), 1)

No comments:

Post a Comment