January 16, 2013 at 10:52 am
Hello Again...I am at my new intern place and the job my manager gave me is
I have to run a query for example "select * from organization" and then the results i get from the result tab should be send to an email id (want to try wid my own).
So I have reached a point where i can send emails from the database. I have created a new profile and account associated with that using database mail node. But I dont know what to do next.. Also the mails are too slow.. I haven't find any solution till now..Its been a couple days since I am trying this..Any help is appreciated. I am open to learn any new thing.
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 10:56 am
We would need more details. The info is pretty limited in order to provide a viable solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 16, 2013 at 11:02 am
So My data base is DB1
I have some 120 tables in that
I have one particular table which is oraganization
My job is to run a query "select * from organization"
and the results that i get, I have to send it to my email ID.. but not manually by exporting a file,
It should be automated. It’ll need to run every night automatically without anyone doing anything. Is that possible?
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 11:07 am
That could be a rather large email.
But yes you can do that.
start with a little reading on the procedure that sends emails.
http://msdn.microsoft.com/en-us/library/ms190307.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 16, 2013 at 11:47 am
USE msdb
GO
EXEC sp_send_dbmail @profile_name='prathibha',
@recipients='prathibha_aviator@abc.com',
@subject='Unbalanced Accounts',
@body='',
@query='select * from CNAccounting_Joliet_2013.dbo.employee',
@execute_query_database= 'CNAccounting_Joliet_2013',
@attach_query_result_as_file= 1,
@query_attachment_filename= 'Results'
Is this right??? My intention is just to give the query results as an attached file
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 12:04 pm
Looks correct to me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 16, 2013 at 12:09 pm
Now How do I do it automatically evrynight.. I dont have to run the query again and again
It should run by its self at the end of the day and send me an email with the query results... Please................????
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 12:11 pm
Add the query to a SQL Agent Job. Schedule the job to run nightly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 16, 2013 at 12:45 pm
I don't know if you care, but database mail sends outgoing mail only. The recipients need to understand that if they try to reply, it won't be deliverable unless you're sending it from a profile using an account that just happens to have the same email address as one on an Exchange (or other email) server. Either way, it won't go to the sender because there's nothing there to receive incoming email.
Also, be aware that sending the result of a query can get very big very quickly. Any mail rules the incoming mail server has in place will be applied and may prevent the email from even arriving.
January 16, 2013 at 12:54 pm
Thanks for the information. Yes the recipients are all aware, This is just to send them one or two column query results so that they can work on the effected parts. But Ya, The size can be big some times. I totally agree and will keep that in mind. I'll think of any other alternative later. But presently focusing on automation part, I mean "Adding the query to a SQL Agent Job"
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 2:04 pm
SQLRNNR (1/16/2013)
Add the query to a SQL Agent Job. Schedule the job to run nightly.
I couldn't figure out how do i add a query to sql server agent and schedule it 🙁
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 2:06 pm
who hired you as intern? ask him .. he will give all these info.
January 16, 2013 at 2:10 pm
Any better replies?????
--Pra:-):-)--------------------------------------------------------------------------------
January 16, 2013 at 2:26 pm
You want to add it to a scheduled job?
USE [msdb]
GO
/****** Object: Job [Automated Email] Script Date: 01/16/2013 15:25:10 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Automated Email')
EXEC msdb.dbo.sp_delete_job @job_id=N'c4af5bc9-47c9-474f-84c1-6731faf2c2a6', @delete_unused_schedule=1
GO
USE [msdb]
GO
/****** Object: Job [Automated Email] Script Date: 01/16/2013 15:25:10 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Data Collector] Script Date: 01/16/2013 15:25:10 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Automated Email',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Sends Unbalanced Accounts Information to specific recipients',
@category_name=N'Data Collector',
@owner_login_name=N'COMPANY\user',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Gather Results and attache to email] Script Date: 01/16/2013 15:25:11 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Gather Results and attach to email',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_send_dbmail @profile_name=''prathibha'',
@recipients=''prathibha_aviator@abc.com'',
@subject=''Unbalanced Accounts'',
@body='''',
@query=''select * from CNAccounting_Joliet_2013.dbo.employee'',
@execute_query_database= ''CNAccounting_Joliet_2013'',
@attach_query_result_as_file= 1,
@query_attachment_filename= ''Results''',
@database_name=N'msdb',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily @ 2am',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20130116,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959,
@schedule_uid=N'8abe66bf-4fdb-4cd1-a76e-4c70a3bd4e07'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 16, 2013 at 2:46 pm
Here is an article from microsoft on creating SQL Agent jobs
http://msdn.microsoft.com/en-us/library/ms135739(v=sql.105).aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply