SQL 2012 :: Possible To Send Pivot Query Results As Automated Database Email?
Nov 26, 2014possible to send Pivot query results as automated database email ?
View 3 Replies
ADVERTISEMENT
How To Automated Send Email To Inform The Status Number Of Data In Database Using Stored Procedures?
Feb 23, 2008Hi ,
I'm just new in this SQL 2005, and I do not reallly sure the subject is right or not but as example in this link below
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008
Date To: 24/02/2008
Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name
-------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John
2 2/22/2008 4:16:45 PM Dean
3 2/23/2008 4:16:45 PM Rick
4 2/24/2008 4:16:45 PM Van
thanks to all of your corcern to help me
Regards;
Send Query Results Via Email
Sep 27, 2007is it possible to use send mail task to send results of a query via email? if it can be sent then what would the result look like? will it be like excel type format or fixed length? or comma delimited?
and obviously -- how can I accomlish it?
thanks
MS SQL 6.5 Procedure To Send Query Results Via Email
Jul 20, 2005Hi,I'm not sure if this is possible as i've googled everywhere, but i have aselect query that returns a customer record with their associated salesorders. I would like to automate a process which sends an email reminder toeach customer in the database, that has outstanding orders. This emailreminder should have the results of the query regarding their account.The table structure are as follows.---------------------Customer_tbl---------------------CustomerIDAccountNoNameEmailAddress---------------------Order_tbl---------------------OrderIDCustomerIDReferenceAmountDateOutstanding_flgCan anyone help?Sen.
View 2 Replies View RelatedTransact SQL :: Send Email Only If Query Contains Results
Aug 8, 2015I’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.
I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.
My code less the personal information
EXEC msdb.dbo.sp_send_dbmail
@profile_name
= '',
@recipients
= '',
@subject
= 'Database Integrity - Import Errors',
[Code] ....
SQL 2012 :: Send Emails With Query Results As Attachment?
Jul 16, 2014What is the best way to send emails with query results as attachment?
There is a stored proc sp_send_dbmail but the formatting is not very good.
SQL 2012 :: Database Mail - Test Email Works But Jobs Won't Send Out Notifications
Oct 19, 2015I am trying to send out notifications when jobs complete (fail or succeed). I have database mail working fine on my DEV server, but I am having issues with it on my PROD server. I am currently having people look into if McAfee may be blocking it.
I am able to send out a test email from SSMS>Management>Database Mail, but when I set a Notification for a job, the job will complete and in the history, it will say "NOTE: Failed to notify 'User' via email."
I have created an Operator and set up Profiles and Accounts, just as I did on my DEV server.
Send Per Email SELECT Results With More Than 1 Row
Nov 20, 2014"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"..It is about a Stored Procedure, which not only should output the number of opportunities with status "Pending" or "Accepted" but also the name of the opportunities with these statuses. Following points:
1. The SELECT that outputs the names of the opportunities may in some cases return more than one value. I created then the CURSOR CUR_TEST.
2. The code should return the names of Opportunities, namely after "The opportunities are:" and these must be sent to the recipients of an email. How can I do this, so the Names of the Opportunities and the rest of the email can be sent by email?. I mean, the concatenation with SET or SELECT doesn't work. I get as email only the output of the last row of the SELECT with cursor CUR_TEST.
3. I add an example how the output should be.
The following is the code:
BEGIN
declare
@V_USER VARCHAR(20),
@V_NAME VARCHAR(100),
@V_QUANTITY INT,
@V_EMAIL VARCHAR(60),
@V_BODY VARCHAR(MAX),
@V_QUANTITY_ACTIVE int,
[code]...
An example how the Output in the email body should be:
Dear MyName,
You have 47 Opportunities with status Pending.
The Opportunities are:
Name of Opportunity 1
Name of Opportunity 2
Name of Opportunity 3
SQL 2012 :: Send Email In HTML Format
Apr 27, 2015I wanted to implement the feature to send the email in HTML format from SQL. Which option will be more easy and less resource consumption? Like
sp_send_dbmail or sp_OAMethod or anything else?
SQL 2012 :: Send Binary File Stored In Server As Email Attachment?
Apr 26, 2014Is there a way to send binary file stored in SQL Server as email attachment without downloading it to the file system?
View 1 Replies View RelatedSQL 2012 :: Script To Find Out Blocked Sessions And Send Email Whenever It Finds Blocking
Feb 20, 2014I want to implement a script which will run continuously to find out blocked sessions and send an email when ever it finds the blocking.
View 5 Replies View RelatedHow To Send An Email If A Database Is Locked
May 3, 2008Hi all
I have heared that there is way to use SQL SMO (SQL Management Objects), or other third party tools running on a management server to send out an email or some kind of notifications if a database is locked.
Anyone knows how to do that in SQL Managment 2005.
Thanks
-Sarah
SQL 2012 :: Display Results As Vertical String - Use PIVOT?
Jun 9, 2014I have a single column returned from a select statement. How can I have this returned as a vertical string? I looked into using PIVOT but my scenario seems too simple to use Pivot. I'm not requiring any aggregate functions or anything.
So, I want to turn this:
SELECT CountyNames + ',' FROM Counties
---------------------
County1 ,
County2 ,
County3 ,
..etc
TO:
County1, County2, County3...etc
Please note that the list of rows is unknown. Could be 1. Could be 50.
Database Mail - Send Test Email
Nov 23, 2006i've got a brand new server and just installed SQL 2005.
when i try to send a test email, i get the following error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2006-11-23T11:49:34). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it). )
I have checked all items from troubleshoot and eveything is ok...any help ?
thanks
SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns
Mar 5, 2015I have a lookup table, as below. Each triggercode can have several service codes.
TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC
I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.
AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')
[Code] ....
Send Query Results Using SQl Mail
Sep 3, 2007Hi,
Does anyone have an example of how to send the results of a query using sql mail? I m using sql server 2000 and have never used sql mail before.
Hope someone can help
Thanks
Email The Results Of The Query
Apr 17, 2007Hello,
I want to email the results of the query. The query output can be email either as the HTML body of the email to be sent or as a excel attachement.
I am wondering as to what would be the best way to achive this.
a) I could have a email client module in C# or VB.NET 2.0, which would fetch the query resultset and store it in a dataset. I format it as HTML body and send the email using System.NET. For excel, can use XSLT
Please suggest if there is any other approach that would be more suitable.
Thanks.
How To Send Query Results To A Flat File?
Jul 26, 2001SQL Server is new to me.
I am using SQL Server 7.0 Query analyzer. I need to send my query results to a flat file. My select statement is 'SELECT * from table_a'.
Can someone help?
Send Results Of SELECT FOR XML Query To A File
Feb 9, 2008
I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful
Code Snippet
'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater).
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Xml
Partial Public Class outputxml
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString)
'Create the variables to hold the values that are supplied by the parameters
'input by the stored procedure
Dim xmlDoc As New XmlDocument()
Dim output As SqlPipe = SqlContext.Pipe()
Try
'Load the result set into the XmlDoc Variable and then save the results in the
'path provided by the stored procedure. The values are provided by the
'input parameters of the stored procedure
xmlDoc.LoadXml(XmlData.Value)
xmlDoc.Save(Filename.Value)
Catch ex As Exception
'If an error occurs catch the message and pipe it back to SQL
output.Send(ex.Message.ToString)
End Try
End Sub
End Class
T-SQL CODE BEGINS HERE
/*Alter the database to set trustworthy on in order
to allow the ability to set the assembly to external
access*/
ALTER DATABASE AdventureWorks SET trustworthy ON
--Import the assembly into SQL
CREATE ASSEMBLY outputxml
from 'C:outputxml.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
-- Create the proc from the imported dll
CREATE PROCEDURE output
@xmldata XML,
@filename nvarchar(1024)
AS
EXTERNAL NAME outputxml.[outputxml.outputxml]
.outputxml
-- Test managed stored procedure
DECLARE @output xml
SET @output = (SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO, ROOT('Catalog'), TYPE)
EXEC dbo.outputxml @output, 'c:Output.xml'
GO
How Do I Attach Query Results To Send Mail
Jun 21, 2006I have several tasks that I don't want to use conditional failure on. I have a ON ERROR send mail task right now that works. I want to have that send mail task include query results from a separate query. How do I do that?
Peter Cwik
Automatically Email Query Results
Aug 14, 2007
I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron??). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?
Thank you for any guidance.
Automated Birthday Email
Apr 13, 2006I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...How can I automate this?
View 5 Replies View RelatedRunning Automated Procedure To Email
Jan 30, 2012I have an EmployeeReviews table...and basically..when the date is 14 days before their review date..I want to have an automated email sent out to the Reviewer.
View 1 Replies View RelatedSQL Server 2008 :: Send Emails To Individuals In Query Results And Extra?
Jul 15, 2015I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.
I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.
Schedule And E-mail Query Results In HTML Formated Email
Aug 14, 2004Could anyone walk me through how to do the following:
Schedule a query to run at a designated time
Embed the resulting table in a HTML email
Email the HTML formated results someone automatically
Can this be done with SQL Server only or is there a third party app?
I know its alot but I'm having a hard time finding resourses.
Export Query Results To Excel File And Add As Attachment In Email
Jan 2, 2014Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
View 3 Replies View RelatedTransact SQL :: Create A Temp Table On Results Of A Pivot Query?
Jun 17, 2015I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update
May 27, 2008hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
SQL Server 2012 :: Pivot Rows And Columns In The Same Query?
Mar 26, 2015I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
SQL 2012 :: Using Database Mail To Send Emails Based On Certain Actions
Jun 12, 2014I have been using database mail for quite a few years now. I started using it with SQL Server 2005 and then it worked fine. Then we went over to SQL 2012 and the problems started. At first I could not get any e-mails out and read a post by Microsoft that a patch needs to come out but not available yet. That was in 2012. Presumably the patch came out with windows updates and my DB Mail started working. Now, however, I have the problem that some e-mails do get sent but the majority failed.
I use this so that whenever a user captures certain data to update a person's status on the database then an automatic e-mail will be sent to that person to inform them of their status at this company.
Send Email Through SQL
Sep 11, 2006Hi all, can i send an email through SQL? i don't want to use third party software. Also, i can't configure the customer's db server. It is possible to send an email without much configuration. If configuration needed, is it possible to configure through SQL script? thx
View 8 Replies View RelatedHow To Send Email
Mar 27, 2008Hi,
I need to select a table from my database and send the table as an email message to a person every 10am because the table changes every day. How to it?
Send Email
Jan 30, 2008
Hi all,
actually i m working on a store procedure in which end of the procedure when task complete it's send email to specific person, but i m having a problem using this function.
i am using
EXEC master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
it's work fine when the outlook is configure on the server, but is there any way to send email rather then configure outlook on the server just chk internet is working send email on behalf of server,
i have more then 2 servers and outlook is configure only one server and i don't want to configure outlook on other server due to work load on server.
Thanks and looking forward.
-MALIK