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)

Format money value as padded string

Ok my last formatting question.

How can I insert a money value as a padded string in another table?

example $1.25 gets inserted to another table as 00000125

I want 8 total characters and no decimal

another example would be 4,225.99 becomes 00422599

can this be done?

thank you!!declare @.m money
set @.m = $1.25

select @.m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@.m*100)),8)

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 10:15:08 -0800, paulmac106@.gmail.com wrote:

Quote:

Originally Posted by

>Ok my last formatting question.
>
>How can I insert a money value as a padded string in another table?
>
>example $1.25 gets inserted to another table as 00000125
>
>I want 8 total characters and no decimal
>
>another example would be 4,225.99 becomes 00422599
>
>can this be done?
>
>thank you!!

|||On Feb 22, 11:33 am, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

declare @.m money
set @.m = $1.25
>
select @.m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@.m*100)),8)
>
Roy Harvey
Beacon Falls, CT
>
On 22 Feb 2007 10:15:08 -0800, paulmac...@.gmail.com wrote:
>

Quote:

Originally Posted by

Ok my last formatting question.

>

Quote:

Originally Posted by

How can I insert a money value as a padded string in another table?

>

Quote:

Originally Posted by

example $1.25 gets inserted to another table as 00000125

>

Quote:

Originally Posted by

I want 8 total characters and no decimal

>

Quote:

Originally Posted by

another example would be 4,225.99 becomes 00422599

>

Quote:

Originally Posted by

can this be done?

>

Quote:

Originally Posted by

thank you!!

I had a similar project. I did this and it works great:

REPLACE(REPLACE(CONVERT(char(8), @.m), '.', ''), ' ', '0')

-Utah|||thanks that worked great.

any idea why this doesn't work:

REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')

it just won't seem to put the zero in...very strange

i get this for 15: '15 '|||(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

thanks that worked great.
>
any idea why this doesn't work:
>
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>
it just won't seem to put the zero in...very strange
>
i get this for 15: '15 '

Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.

It looks like a bug to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i got it to work using:

RIGHT('0000'+REPLACE(SUM(tblLines.fldUnits), ' ', '0'), 4)

results in 0015 where SUM(tblLines.fldUnits)=15|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

>thanks that worked great.
>>
>any idea why this doesn't work:
>>
>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>
>it just won't seem to put the zero in...very strange
>>
>i get this for 15: '15 '

>
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
>
It looks like a bug to me.

For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

I've submitted a workaround to this Feedback item. If you
concatenate the empty string '' to the CHAR value before
REPLACE is applied, the hidden conversion to VARCHAR
retains the trailing blanks.

declare @.t char(6)
set @.t = 'A'
select
replace(@.t,space(1),'*'),
replace(@.t+'',space(1),'*')

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- C70DF007-1034-489C-A71E-108FBC89D553

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:
>

Quote:

Originally Posted by

>(paulmac106@.gmail.com) writes:
>>

Quote:

Originally Posted by

>>>thanks that worked great.
>>>
>>>any idea why this doesn't work:
>>>
>>>REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
>>>
>>>it just won't seem to put the zero in...very strange
>>>
>>>i get this for 15: '15 '

>>
>>Good question. Seems like the reailing spaces are stripped when the
>>string is passed to replace(). Probably, because there is a conversion
>>to varchar, but trailing spaces should be retained, as long as the
>>setting ANSI_PADDING is in effect.
>>
>>It looks like a bug to me.

>
>
For what it's worth, I submitted
https://connect.microsoft.com/SQLSe...edbackID=259840
>
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
>
>

format data before being inserted into a column

I am trying to manipulate data before it is inserted into a column. For
instance
I am reading in a MAC addreess which looks like '00DE0B16AA99' and when I
do an insert into the column I want it to be '00:DE:0B:16:AA:99' In other
words I am trying to insert the semicolons. I thought I was on to something
with the sp_bindrule but have not had sucess with it yet. I am pretty sure
that you can set up some sort of formattting on a column maybe when creating
the table but have not figured it out. Any help is appreciated.
Thanks in Advance
DaveProbably you can create a User Defined Function (named MyFunction, that
takes a string as parameter and generates a string as result) in that SQL
database, in order to convert the string from
00DE0B16AA99
to
00:DE:0B:16:AA:99
I think that a SQL sentence like
Insert into TABLE values (... MyFUnction('00DE0B16AA99')
and MyFUnction should be like:
CREATE FUNCTION dbo.MyFunction (@.MAC varchar(12))
RETURNS varchar(17)
AS
BEGIN
declare @.NEWMAC varchar(17)
...
...your conversion code here
...
return (@.NEWMAC)
END
Hope it helps
Michael Prendergast
"deheinz1" <deheinz1@.discussions.microsoft.com> escribi en el mensaje
news:8F622015-658D-4C83-8F85-6B4797FB78AE@.microsoft.com...
>I am trying to manipulate data before it is inserted into a column. For
> instance
> I am reading in a MAC addreess which looks like '00DE0B16AA99' and when I
> do an insert into the column I want it to be '00:DE:0B:16:AA:99' In other
> words I am trying to insert the semicolons. I thought I was on to
> something
> with the sp_bindrule but have not had sucess with it yet. I am pretty
> sure
> that you can set up some sort of formattting on a column maybe when
> creating
> the table but have not figured it out. Any help is appreciated.
> Thanks in Advance
> Dave
>

format data before being inserted into a column

I am trying to manipulate data before it is inserted into a column. For
instance
I am reading in a MAC addreess which looks like '00DE0B16AA99' and when I
do an insert into the column I want it to be '00:DE:0B:16:AA:99' In other
words I am trying to insert the semicolons. I thought I was on to something
with the sp_bindrule but have not had sucess with it yet. I am pretty sure
that you can set up some sort of formattting on a column maybe when creating
the table but have not figured it out. Any help is appreciated.
Thanks in Advance
Dave
Probably you can create a User Defined Function (named MyFunction, that
takes a string as parameter and generates a string as result) in that SQL
database, in order to convert the string from
00DE0B16AA99
to
00:DE:0B:16:AA:99
I think that a SQL sentence like
Insert into TABLE values (... MyFUnction('00DE0B16AA99')
and MyFUnction should be like:
CREATE FUNCTION dbo.MyFunction (@.MAC varchar(12))
RETURNS varchar(17)
AS
BEGIN
declare @.NEWMAC varchar(17)
...
...your conversion code here
...
return (@.NEWMAC)
END
Hope it helps
Michael Prendergast
"deheinz1" <deheinz1@.discussions.microsoft.com> escribi en el mensaje
news:8F622015-658D-4C83-8F85-6B4797FB78AE@.microsoft.com...
>I am trying to manipulate data before it is inserted into a column. For
> instance
> I am reading in a MAC addreess which looks like '00DE0B16AA99' and when I
> do an insert into the column I want it to be '00:DE:0B:16:AA:99' In other
> words I am trying to insert the semicolons. I thought I was on to
> something
> with the sp_bindrule but have not had sucess with it yet. I am pretty
> sure
> that you can set up some sort of formattting on a column maybe when
> creating
> the table but have not figured it out. Any help is appreciated.
> Thanks in Advance
> Dave
>