SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 T-sql email almost working.....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andyl9063
Starting Member

USA
19 Posts

Posted - 01/23/2013 :  10:21:04  Show Profile  Reply with Quote
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








Edited by - andyl9063 on 01/23/2013 14:04:40

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  10:56:10  Show Profile  Reply with Quote
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/

Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 01/23/2013 :  11:27:12  Show Profile  Reply with Quote
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.
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 01/23/2013 :  11:50:16  Show Profile  Reply with Quote
i edited the query. please take a look.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  13:54:53  Show Profile  Reply with Quote
where's cursor declaration part?

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

Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 01/23/2013 :  14:05:27  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000