Adding a line return to an SQL messge

HI all,
I'm adding a custom SQL message. How do I insert a new line, line feed. So
that the message is split between a few lines
Thanks
RobertRobert
SELECT 'HELLO'+ CHAR(13)+'World'
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||select 'error' + char(13) + 'message'
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Robert Bravery" wrote:

> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>
>|||A "proper" CR+LF pair is:
SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar';
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||A "proper" CR+LF pair is:
SELECT 'foo' + CHAR(13) + CHAR(10) + 'bar';
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||Another method:
EXEC sp_addmessage 60000, 10, 'This
is
a
multi-line
message'
RAISERROR(60000, 10, 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||Thanks
Robert
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHwpxnflGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Another method:
> EXEC sp_addmessage 60000, 10, 'This
> is
> a
> multi-line
> message'
> RAISERROR(60000, 10, 1)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert Bravery" <me@.u.com> wrote in message
> news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
So
>|||This begs the question about why format output from SQL Server instead of
the client application...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Robert Bravery" <me@.u.com> wrote in message
news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I'm adding a custom SQL message. How do I insert a new line, line feed. So
> that the message is split between a few lines
> Thanks
> Robert
>|||Sometimes it's just a heck of a lot easier to do in SS than a given client.
A good example is a spreadsheet I created for one of my coworkers that draws
data from some SS queries. Word wrap wasn't working the way he wanted on
some column headers and adding the CR between 2 key parts of the field alias
solved the problem.
Randall Arnold
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:upvDfGhlGHA.836@.TK2MSFTNGP02.phx.gbl...
> This begs the question about why format output from SQL Server instead of
> the client application...
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:ukG8HCflGHA.4772@.TK2MSFTNGP03.phx.gbl...
>|||>> I'm adding a custom SQL message. How do I insert a new line, line feed. S
o that the message is split between a few lines <<
Why are you doing display in the database and not the front end? The
basic principle of a tiered architecture is that display is done in the
front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.

add cariage return

I'm trying to add a carriage return in the below function, but nothing is
happening.
The end result is to cut-n-paste into notepad with carriage returns.
What am I doing wrong?
thanks!
CREATE FUNCTION dbo.fctConcatTitles
(
@.O VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
FROM Titles
WHERE OrderNo = @.O
RETURN @.r
ENDshank wrote on Wed, 11 Jan 2006 05:15:01 -0500:

> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
Try CHAR(13) + CHAR(10). Windows uses a Carriage Return + Line Feed for end
of line termination, not just a CR.
Dan|||The above code works and an optional solution is manually adding a
linefeed,
like this:
SELECT @.r = ISNULL(@.r + '
' , '') + Title + ' - ' + Type
FROM Titles
It's not pretty - but efficient...
/ola|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
> thanks!
> CREATE FUNCTION dbo.fctConcatTitles
> (
> @.O VARCHAR(32)
> )
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> DECLARE @.r VARCHAR(8000)
> SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
> FROM Titles
> WHERE OrderNo = @.O
> RETURN @.r
> END
Beside the CR issue, note that this piece of code relies on undefined
behaviour, so there is no guarantee that you will get the result you are
looking for.
In SQL 2000, the only guaranteed way is to run a cursor. And most
probably you want an ORDER BY as well, so that you don't the data
in some funny order.
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've tried this...
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
Then this...
SELECT @.r = ISNULL(@.r+ '
', '') + Title + ' - ' + Artist
...without any luck.
To be clear, I'm expecting to see results in QA.
I'm also cut-n-pasting from QA into notepad and there's no carriage returns.
Can you explain more about the cursor?
I don't believe I've ever used code that uses the cursor.
thanks to all!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974881545AC39Yazorman@.127.0.0.1...
> shank (shank@.tampabay.rr.com) writes:
> Beside the CR issue, note that this piece of code relies on undefined
> behaviour, so there is no guarantee that you will get the result you are
> looking for.
> In SQL 2000, the only guaranteed way is to run a cursor. And most
> probably you want an ORDER BY as well, so that you don't the data
> in some funny order.
> --
> 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|||Use the "Results to Text" option.
ML
http://milambda.blogspot.com/|||shank (shank@.tampabay.rr.com) writes:
> I've tried this...
> SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
> Then this...
> SELECT @.r = ISNULL(@.r+ '
> ', '') + Title + ' - ' + Artist
> ...without any luck.
> To be clear, I'm expecting to see results in QA.
> I'm also cut-n-pasting from QA into notepad and there's no carriage
> returns.
To echo ML's post: you are using text more, aren't you? In grid mode
you will not see any CRs.

> Can you explain more about the cursor?
> I don't believe I've ever used code that uses the cursor.
That's good! Too many inexperienced programmers use cursors when they
shouldn't, so I almost feel bad for showing you, but since this only
works with cursors (in SQL 2000), there is not much choice:
DECLARE @.r varchar(8000),
@.item varchar(50)
DECLARE thiscur CURSOR LOCAL FAST_FORWARD FOR
SELECT title + '-' + artist
FROM Titles
WHERE OrderNo = @.OrderNo
ORDER BY title, artist
OPEN thiscur
WHILE 1 = 1
BEGIN
FETCH thiscur INTO @.item
IF @.@.fetch_status <> 0
BREAK
SELECT @.r = CASE WHEN @.r IS NULL THEN ''
ELSE @.r + char(10) + char(13)
END + @.item
END
DEALLOCATE thiscur
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 Sommarskog:
> To echo ML's post: you are using text mode, aren't you? In grid mode
> you will not see any CRs
Well that's not true is it? The CRs is shown as "squares" in grid mode
and when c-n-p
from QA to notepad they tag along and do result in CRs.
shank:
I cannot understand why you don't get the expected result - can you try
the
following code (NB you have to have example database "pubs" installed)
-- code start
use pubs
declare @.r varchar(8000)
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + title + ' - ' + type
>From titles
Select @.r
-- code end
/o

add another column to sproc output

Say i have sproc that return rows with x columns.
Say I now want to add another column to it.
For eg: Say I am running sp_who2
But now Say i want to add a getdate column to it and i want to do something
like
Select getdate() + exec sp_who2There *might* be *better* ways to do this, but my general purpose sp_who2
script might help you with this specific requirement..
go
if OBJECT_ID('tempdb..#spwho') > 0 drop table #spwho
go
create table #spwho (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID2 int not null
)
go
insert #spwho
exec sp_who2
go
select getdate(), *
from #spwho
--where SPID > 50 and login != SUSER_SNAME()
order by SPID --LastBatch desc
go
if OBJECT_ID('tempdb..#spwho') > 0 drop table #spwho
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eH74sHz6FHA.472@.TK2MSFTNGP15.phx.gbl...
> Say i have sproc that return rows with x columns.
> Say I now want to add another column to it.
> For eg: Say I am running sp_who2
> But now Say i want to add a getdate column to it and i want to do
> something like
> Select getdate() + exec sp_who2
>
>|||Hassan,
This technique might be more generally useful to you:
SELECT getDate() as CurrentTime, *
FROM OPENROWSET ('SQLOLEDB',
'Server=(local);Database=master;Trusted_
Connection=yes', 'SET FMTONLY OFF;
exec sp_who2');
You may have to change the connection string if you are not using Windows
authentication for some reason.
Cheers,
Chris
"Hassan" wrote:

> Say i have sproc that return rows with x columns.
> Say I now want to add another column to it.
> For eg: Say I am running sp_who2
> But now Say i want to add a getdate column to it and i want to do somethin
g
> like
> Select getdate() + exec sp_who2
>
>

Add a function a report

How i can add a function to a report and use to put in a textbox

Like

Public Function Area(radius)

return 3.14*radius*radius

End Function

Thanks

vgta,

from your report click on Report
then choose Report Properties...

From there you will see a CODE tab. Enter your function here.

The expression of your textbox will be Code.functionName
Using your example it would be Code.Area(radius)

Bret