Can Windows variables Be Used Within T-SQL

Question

Can Windows variables Be Used Within T-SQL

JC-3113

SSCrazy Eights

Points: 8371
More actions
September 15, 2010 at 10:25 am

#224068

Hi Folks
just want to know if there is a way to incorporate Windows Variable(s) in some T-SQL using DBMAIL
Here is what I have:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBCC Check Success',
@recipients = 'james.cox.ctr@losangeles.af.mil',
@body = 'Test DBMail from SQLCMDS prompt.',
@subject = ' Test - DBCC Check Success' ;
GO
I would like to add the COMPUTERNAME variable ==> %COMPUTERNAME% in @subject
Is that possible ?
Thanks
Jim

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Either Host_Name() or @@SERVERNAME should give you what you need. Host_Name will give you the name of the workstation that initiated the process, @@SERVERNAME will give you the name of the server that actually runs it.

Drew

NOTE: The "workstation" may also be a server. This happens when the process is initiated by a SQL job, for example.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

HI Drew

that will work

but how do i concantenate it to the string in @subject=

if my @@SERVERNAME is NSAB-SS73-ORA-N

then

I want it to look like this:

NSAB-SS73-ORA-N Test - DBCC Check Success

Jim

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

JC-3113 (9/15/2010)
HI Drew
that will work
but how do i concantenate it to the string in @subject=
if my @@SERVERNAME is NSAB-SS73-ORA-N
then
I want it to look like this:
NSAB-SS73-ORA-N Test - DBCC Check Success
Jim

You'll need to do the concatenation in a variable defined before the EXEC and then use the variable in the EXEC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)