T-sql email almost working.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

T-sql email almost working.....

Author

Topic

andyl9063
Starting Member

19 Posts

Posted - 2013-01-23 : 10:21:04

Basically, I am trying to create a t-sql that send email alerts to me. I have attached the queries below. Basically, the query starts at SELECT and end at Tat 0 is what I want to execute and return those data via email. When I run that query alone, it returns it fine. I suspect its something inside this whole t-sql statement that is not correct. I'm kind of a db newb. Any clarification would be extremely helpful.

Declare @dict int
	,@tableHTML NVARCHAR(MAX) 
	,@studyDate varchar(25)
	,@patientID varchar(25)
	,@patient varchar(50)
	,@report varchar(max)
	,@subj varchar(100)
	,@dr varchar(50)
	,@approve varchar(25)
	,@inst varchar(50)
		

         
if @dict > 0
begin

declare	cursorName CURSOR for
	SELECT PATIENTS.FirstName, PATIENTS.Lastname, STUDIES.study_datetime, STUDY_LIST_VIEW.tat, PATIENTS.Patient_ID
	FROM opalrad.dbo.INSTITUTIONS INSTITUTIONS, opalrad.dbo.PATIENTS PATIENTS, opalrad.dbo.STATUSES STATUSES, opalrad.dbo.STUDIES STUDIES, opalrad.dbo.studies_calc studies_calc, opalrad.dbo.STUDY_LIST_VIEW STUDY_LIST_VIEW, opalrad.dbo.TRANSCRIPTION_TEXT TRANSCRIPTION_TEXT
	WHERE STUDIES.study_id = studies_calc.study_id AND STUDIES.institution_id = INSTITUTIONS.institution_id AND TRANSCRIPTION_TEXT.STUDY_ID = STUDIES.study_id AND TRANSCRIPTION_TEXT.STUDY_ID = studies_calc.study_id AND PATIENTS.Patient_ID = STUDIES.patient_id AND STUDY_LIST_VIEW.INSTITUTION_ID = INSTITUTIONS.institution_id AND STUDY_LIST_VIEW.PATIENT_ID = PATIENTS.Patient_ID AND STUDY_LIST_VIEW.study_id = STUDIES.study_id AND STUDY_LIST_VIEW.study_id = studies_calc.study_id AND ((INSTITUTIONS.institution_name='TEST') AND (STUDY_LIST_VIEW.tat=0))
	
OPEN cursorName

FETCH NEXT FROM cursorName
 INTO @studyDate, @patientID, @patient
 While @@FETCH_STATUS = 0
	Begin
	set @tableHTML = '<html><body><H2>PRI Tat 3</H2><p><b> Patient Name:       </b>'+ @patient + '</p><p><b>Patient ID:       </b>' + @patientID +
		'</p><p><b>Study Date:       </b>' + @studyDate
		
	set @subj = '**** PRI Tat 3 **** ' + @patient + ' ' + @studyDate
	
        EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'TRGEmail',
		@recipients = 'test@test.com',
		@body_format='HTML',
		@body = @tableHTML,
		@subject = @subj ;
	FETCH NEXT FROM cursorName
	INTO @studyDate, @patientID, @patient
	End
CLOSE cursorName
end
GO


Comparison QUERY THAT WORK

Declare @dict int
	,@tableHTML NVARCHAR(MAX) 
	,@studyDate varchar(25)
	,@patientID varchar(25)
	,@patient varchar(50)
	,@subj varchar(100)
	,@draftDate varchar(50)
	,@doctor varchar(50)
		
SELECT 
      @dict = count([status_id])   
  FROM [opalrad].[dbo].[STUDIES]
  WHERE status_id =1009
         
if @dict > 0
begin

declare	cursorName CURSOR for
	select Patient_id_dicom, 
PATIENTS.Lastname + ', ' + PATIENTS.FirstName as patient,
study_datetime,
USER_NAME,
Dt
from opalrad.dbo.STUDIES,
opalrad.dbo.PATIENTS,
opalrad.dbo.USERS,
opalrad.dbo.STUDY_STATUS_LOG
where STUDIES.status_id =1009 and 
STUDIES.study_id = STUDY_STATUS_LOG.STUDY_ID and
STUDIES.patient_id = PATIENTS.Patient_ID and
STUDY_STATUS_LOG.USER_ID = USERS.USER_ID and 
USERS.USER_ID in (23) and 
STUDY_STATUS_LOG.STATUS_ID = 1009
	
OPEN cursorName;


FETCH NEXT FROM cursorName
 INTO @patientID, @patient, @studyDate, @doctor, @draftDate
 While @@FETCH_STATUS = 0
	Begin
	set @tableHTML = '<html><body>Please Investigate DRAFT Studies<p><b>Patient: </b>' + @patient + '</p><p><b>Patient ID: </b>' + @patientID +
		'</p><p><b> Study Date: </b>' + @studyDate + '</p><p><b>Doctor: </b>' + @doctor + '</p><p><b>Draft Time: </b>' + @draftDate + '</p>'
	set @subj = 'DRAFT Studies Needs Investigating ' + @patient + ' ' + @studyDate
	
        EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'TRGEmail',
		@recipients = 'test@test.com',
		@body_format='HTML',
		@body = @tableHTML,
		@importance ='High',
		@subject = @subj ;
	FETCH NEXT FROM cursorName
	INTO @patientID, @studyDate, @patient, @doctor, @draftDate
	End
CLOSE cursorName;
end
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 10:56:10

you've not specified all tables relationships in below query

select Patient_id_dicom,
PATIENTS.Lastname + ', ' + PATIENTS.FirstName as patient,
study_datetime,
Dt
from opalrad.dbo.STUDIES,
opalrad.dbo.PATIENTS,
opalrad.dbo.USERS,
opalrad.dbo.STUDY_STATUS_LOG
where STUDIES.status_id =1009 and
STUDIES.study_id = STUDY_STATUS_LOG.STUDY_ID and
STUDIES.patient_id = PATIENTS.Patient_ID

it doesnt have any conditions linking between USERS to other tables. so it will end up repeating the entire result for each of the users

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andyl9063
Starting Member

19 Posts

Posted - 2013-01-23 : 11:27:12

do i have to specify those? can I get rid of those? I just want it to run that specific query and return the results via email text.

andyl9063
Starting Member

19 Posts

Posted - 2013-01-23 : 11:50:16

i edited the query. please take a look.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 13:54:53

where's cursor declaration part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andyl9063
Starting Member

19 Posts

Posted - 2013-01-23 : 14:05:27

I have edit query and put the top that I am trying to get working. Then the bottom is the comparison query that is working fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources