SQL Server 2008 :: Send Emails To Individuals In Query Results And Extra?

Jul 15, 2015

I 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.

View 1 Replies


ADVERTISEMENT

SQL 2012 :: Send Emails With Query Results As Attachment?

Jul 16, 2014

What 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.

View 3 Replies View Related

SQL Server 2008 :: 2 Emails Being Send From Job When Only 1 Should Send?

Apr 18, 2012

I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'

[code]....

View 9 Replies View Related

SQL Server 2008 :: Remove Rows Affected From Query In Send Mail

May 6, 2015

I need to remove "rows affected" text from results as shown below from posted Sp. I am using set nocount on but its not working as expected.

Create Procedure DailyCheckList
As
SET NOCOUNT ON
Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)
set @dt= convert(varchar(20),GETDATE(),107)

[Code].....

View 1 Replies View Related

SQL Server 2008 :: Reminder Emails - Records Created With Status

Jun 29, 2015

We have a difficult requirement.

When records are initially created, they are given statuses of New, Pending, Closed

By default, the status is New.

If no action has been taken after 15 days since the records were created, users are sent a reminder.

I have this part working.

The problem is that as long as the status is not equal to closed, start sending reminders every 10 days.

Here is what I am working with:

SELECT [ID] , [Name], [Email],
Status
FROMmyTable
WHERE Status_Name != 'Closed' AND Status_Name IS NOT NULL and Status_Name = 'New' and Status='Pending'
AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)

View 2 Replies View Related

SQL Server 2008 :: Query Results Output Into CSV File To Use In VBA Macro

Jul 31, 2015

I have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Any better way of out putting the results into a .csv file with a common delimiter.

-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)

[Code] ...

Output from query (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):

557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS
(only 12 pks in stock that will ship today)
^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000
^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^

[Code] ....

View 1 Replies View Related

Send Query Results Using SQl Mail

Sep 3, 2007

Hi,
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

View 1 Replies View Related

Send Query Results Via Email

Sep 27, 2007

is 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

View 5 Replies View Related

SQL Server 2008 :: DBMail Output Query Results As CSV With Headers But No Separator

Aug 25, 2015

If I use msdb..sp_send_dbmail or save query results as text (using sqlcmd) and include the column headers I get the dashed separator line.

e.g.

custID, name
------, ------
1,bob
2,jamesI would like this
custID, name
1, bob
2 ,james

I found this method [URL] ....

::
sqlcmd -E -S (local) -d myDB -W -w 1024 -s "," -i "SELECT * FROM tblCust" | findstr /V /C:- /B > C: emp.csv

Can the same result be achieved sending as attachment with dbmail?

EXEC msdb..sp_send_dbmail @attach_query_result_as_file = 1I don't want to have to add column names as part of the query

Change the query to return column headers in resultset
SELECT 'CustID' as f1, 'name' as f2
UNION ALL
SELECT CAST(CustID as Varchar(10)), name FROM tblCustand set

msdb..sp_send_dbmail @query_result_header = 0

View 0 Replies View Related

How To Send Query Results To A Flat File?

Jul 26, 2001

SQL 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?

View 1 Replies View Related

MS SQL 6.5 Procedure To Send Query Results Via Email

Jul 20, 2005

Hi,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 Related

Transact SQL :: Send Email Only If Query Contains Results

Aug 8, 2015

I’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] ....

View 5 Replies View Related

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

View 2 Replies View Related

How Do I Attach Query Results To Send Mail

Jun 21, 2006

I 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

View 8 Replies View Related

SQL 2012 :: Reporting Services Would Not Send Subscription Emails

May 5, 2014

I am running RS 2012 installation and everything worked until few days ago. Now the subscriptions would not even run. When I configure new ones, after the time passes, there is not error message, not even a sign it ran.

View 2 Replies View Related

SQL 2012 :: Data Driven Subscriptions - Any Way To Send All Emails At Once?

Mar 18, 2015

We have a rather large query that takes time to run between each data-driven instance. Is there a way to only send out the first email after the last subscription has finished processing? Any advanced input on data-driven subscriptions .

View 0 Replies View Related

Passing Variables In SP - Send Out Emails Based On Certain Criteria

Feb 11, 2015

I was tasked with witing a stored procedure to send out emails based on certain criteria. One of our developers decided to take care of the criteria in another process and pass variables for me to use. I am not quite sure how to go about this, but below is what I started with. I declared his variables, and then set up a couple of my own for the email subject line and text.

create PROCEDURE [dbo].[SendWelcomeEmails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

Do I need to use a cursor or can I just specify using if, then else? Also not sure where to SET the individual subject lines.

View 2 Replies View Related

SQL Server 2008 :: Cleanup History Has Extra Output Message

Apr 16, 2015

Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015

Running Cleanuphistory Procedure On Server:

NO of records are qualifying for deletion:0

Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1

*** END OF PROCEDURE. ***

[Code] .....

View 6 Replies View Related

SQl Express 2005 Suppoerts For Send Emails Using Stored Porcedures

Jun 3, 2008

Hello to all,Can anybody please tell is SQl express 2005 suppoerts for send emails using stored porcedures?
 

View 1 Replies View Related

SQL 2012 :: Using Database Mail To Send Emails Based On Certain Actions

Jun 12, 2014

I 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.

View 0 Replies View Related

SQL 2012 :: Possible To Send Pivot Query Results As Automated Database Email?

Nov 26, 2014

possible to send Pivot query results as automated database email ?

View 3 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

PDF Export Results In Extra Pages.

Jul 20, 2007

I am experiencing some quirks when exporting to PDF.
I have read all the other posts about page sizes, margins, etc and my situation seems to be a bit different.

PageWidth = 21cm
PageHeight = 29.7cm
Margins = 1cm

Body.Width = 19cm

I have a report that contains a bar chart (Height = 15cm, Width = 19cm) and below that a rectangle that contains two text boxes with totals information (Left = 11.6cm, Width = 7.4cm, Height 0.5cm)

Now most of the time it exports to PDF perfectly - all on one page as would be expected

If however the chart has more than say 20 items on the y-axis then the rectangle gets pushed onto an new page.

For extra information, but not to confuse the issue I also have a page header and footer that have items lined up right to the edge (left + width = 19cm) and they all display fine on the one page. It is just this rectangle that is having the problem.

When I move the rectangle to the left a bit (11.4cm) so 0.2cm away from the right hand edge then it works fine.

It is just very strange because I cant see how it is getting shifted only when the chart contains a lot of items.

Any ideas?

View 1 Replies View Related

SQL Server 2008 :: Send Mail Task On Failure Not Triggered

Mar 26, 2015

I have multiple task flows with precedence constraints and i have changed the constraint as completion to execute the package even though there is failure in one of the tasks in the package but every time the send mail success is executed instead of Send mail Failure task.

View 0 Replies View Related

SQL Server 2008 :: Excel File Name In Send Mail Task Using SSIS

Jun 1, 2015

I have a package which is creating a excel file 'ExcelName.xls' and storing in 'E:ReportingDelivered_Reports'. Now I have to attach this report using send mail task and send it to given mail id. To achieve this I have configured the send mail task and in Expression Builder, I have selected the below expression:

"E:ReportingDelivered_ReportsExcelName_"+
((DT_WSTR,4)Year(@[System::StartTime]))+
RIGHT("0"+((DT_WSTR,2)Month(@[System::StartTime])),2)+
RIGHT("0"+((DT_WSTR,2)Day(@[System::StartTime])),2)+".xls"

I need file name should be 'ExcelName_20150601' where suffix is the current date. But I recieve file which name is 'ExcelName', which is the original file name.

View 7 Replies View Related

SQL Server 2008 :: Monitor Database Growth And If Any DB Grown By 20% Send Mail Alerts

Apr 30, 2015

Is there any automated script available to - "Monitor Database Growth and if any DB is grown by 20%, sending mail alerts"? If not, what is the approach to write the T SQL script ?

View 5 Replies View Related

SQL Server 2008 :: How To Get Rid Of NULL In Results From Case When

Sep 21, 2015

I'm trying to get a result set without the NULLs. Here is the code I'm using. I'd like the results to look like:

17285204 90471 090471
17285204 90715 090715
17285204 99396 099396
17285204 99420 099420
17285204 90471 090471
17285204 NULL G0444

create table #Test
(
AppNum varchar(10),
CPT varchar(10),
src char(1)
)
insert into #Test(AppNum, CPT, src) values('17285204','090471','b')

[Code] ...

View 5 Replies View Related

SQL Server 2008 :: How To Hide Criteria In Search Results

May 28, 2015

Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.

In Access you can easily just uncheck that field in design view, but how do I do it in sql?

View 4 Replies View Related

Transact SQL :: Why Does File Seem Displaced When Send Server Results To A File

Aug 24, 2015

I have the following SQL

IFOBJECT_ID('tempdb..#TempTable_Lockbox_File_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_File_Header_Record
;
IFOBJECT_ID('tempdb..#TempTable_Lockbox_Batch_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_Batch_Header_Record
;

[code]....

When I send my query results to a file in SQL Server Management Studio, how come I'm seeing the following in Notepad++? FH   TEST "FH" which I thought should be in a CHAR(2) data column is there but "TEST" seems to start in Column 6...not column 3 as I would have expected. I was expecting... FHTEST.

View 3 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

Apr 6, 2015

I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.

ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR

[Code] .....

View 2 Replies View Related

Best Way To Send DB Results To Variable

Feb 28, 2005

Hello all

I have a database query which will return a single value

SELECT COUNT(DISTINCT(Level_id)) FROM RoleSkill WHERE Route_ID = 1

obviously this will return only a single value and not an array of data like most database queries, therefore i would like it to be sent directly to an integer variable.

I tried doing this directly with a sqlcommand but get error that it cannot be converted to integer. Is there a good way of solving this problem?

thanks for your help

Tom

View 3 Replies View Related

Send Results From Sp_executeSQL To Another Sp.

Nov 9, 1999

I need to pipe the results of a dynamic query to another stored procedure.

How?

EXEC (here doesn't work) = sp_executeSQL
@Query

View 1 Replies View Related

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

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved