Capture File To Email

Apr 7, 1999

Does anybody know of a way to set up a task which will send an email with an attachment other than using sp_sendmail. Sp_SendMail will run a query and send the results as a text file. But I need to run a stored procedure which generates an Excel spreadsheet then have an email sent with the Excel file as an attachment. The sp_sendmail text file just doesn't work with the information I am generating so I need to find another solution.

Thanks for any help.

View 1 Replies


ADVERTISEMENT

Capture Error Message And Include It In The Email

Feb 15, 2008

Hello,

I have a SQL Task that executes some stored procedure. When a condition is met the stored procedure raises an error by calling RAISERROR (@ErrorMessage,16,1). Following the SQL Task I have Send Mail Task that sends an error email.

I would like to know how to include the @ErrorMessage in the error email.

Thank you,
-Oleg

View 3 Replies View Related

Load A Text File With Email Addresses And Compare Against A Database Table That Has Email Addresses And User_id

Jul 12, 2007

Hello ALL



what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.



I also want to update some users using a different text file.



Please help me with the best way to do this



Thanks in advance

View 6 Replies View Related

Capture Date And Timestamp Of A File?

Feb 21, 2007

Hi,

I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.

Any ideas?

Thanks in advance for your help.

$wapnil

View 7 Replies View Related

Capture Stored Proc Output To File

Jan 8, 2001

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

View 3 Replies View Related

Capture Line Of Flat File [Error]

Oct 16, 2006

Hi,



I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?????

View 4 Replies View Related

Capture Messages Window Into Log File Using Powershell Program

Jun 25, 2014

As we know there are two output windows in sql server management studio like 'Results' and 'Messages', I want to capture that 'Messages' window into a log file using powershell program . like into a log file it should display like :

ex:-
(1 row(s) affected)
(1 row(s) affected)

View 5 Replies View Related

Need To Capture Actual Data In Row On Error To Flat File

Sep 18, 2007

Let me preface by saying I am not very familiar with SSIS.

Ideally, since the Transfer SQL Server Objects task can do all tables, I would like to use it to copy only data from one server to a new server that has the tables pre-created. When I encounter any kind of error, in addition to the error information provided by SSIS, I also need the actual row data.

If using the Transfer Object task can't do that, how would I loop through all the tables on an OLEDB source and capture the same error information on the destination? I figured out how to do the Data Flow a table with a redirect error output but that does not give me the actual row data.

View 7 Replies View Related

Capture System.IO Rows From Incoming File And Insert Into Table

Mar 24, 2006

Ok, I'm not quite sure how to approach this one.  This is a VB.NET console app in which I want to capture each row and throw it into a table.  The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.
1) What should I use to do this, dataset?  I want to use stored procedures also, not inline SQL
Function here takes an incoming file, and splits it up into separate files.  I want to insert each row that is succesfully split
    Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)
        If sIncomingfile <> "" And sOutputDirectory <> "" Then
            Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None)            f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read
            Dim file As New IO.FileInfo(sIncomingfile)            Dim filefs As IO.FileStream = Nothing            If file.Exists Then                Try                    filefs = New IO.FileStream(file.FullName, IO.FileMode.Open)  'Place: 1                Catch ex As Exception                    SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1")                    Application.Exit()                End Try            End If
            Dim reader As New IO.StreamReader(filefs)            Dim counter As Integer = 0
            Dim CurrentFS As IO.FileStream            Dim CurrentWriter As IO.StreamWriter            Dim extension As String = IO.Path.GetExtension(file.FullName)
            If extension = ".mnt" Then                While Not reader.Peek < 0                    Dim Line As String = reader.ReadLine                    If IsNumeric(Line.Substring(0, 1)) Then                        Dim Parts() As String = Line.Split(" "c) ' split row into parts                        If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file                            counter += 1                            If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()                            CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)                            CurrentWriter = New IO.StreamWriter(CurrentFS)                        End If
                        If Not CurrentWriter Is Nothing Then                            CurrentWriter.WriteLine(Line)                        End If
                    End If                End While
                If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
                MoveFilesFTP(sOutputDirectory, "mnt")
            ElseIf extension = ".naf" Then                While Not reader.Peek < 0                    Dim Line As String = reader.ReadLine                    If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file                        counter += 1                        If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()                        CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)                        CurrentWriter = New IO.StreamWriter(CurrentFS)                    End If
                    If Not CurrentWriter Is Nothing Then                        CurrentWriter.WriteLine(Line)                    End If
                End While
                If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
                MoveFilesFTP(sOutputDirectory, "naf")            End If        Else            'input file not valid            SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1")        End If    End Sub

View 2 Replies View Related

Capture Data From Excel File And Load Into Staging Table - Annual Count

Jul 31, 2013

I am working on an HR project and I have one final component that I am stuck on.

I have an Excel File that is loaded into a folder every month.

I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).

I then combine it with another table in a view.

I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it’s a table of all employees, positions, hire dates, term dates etc).

Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.

I have one final component that i cant seem to figure out. At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count.

So the data will look like this.

|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |

The data is in one table labeled [EEMaster]. To test the count I have the following.

SELECT COUNT([PersNo]) AS HistoricalHC
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'

this returns the HistoricalHC for 2013 as 418.

SELECT COUNT([PersNo]) AS NumbOfTermEE
FROM [dbo].[EEMaster]
WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'

This returns the Number of Termed employees for 2013 as 42.

I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into.

|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |

I need a script (or possibly a couple of scripts) that will add the numbers every year with the year that the data came from.

(so on Dec 31st this package will run and add |2013|418|42| to the next row, and so on.

View 20 Replies View Related

Email If File Not Found/exist

Jan 10, 2008

On Foreach File Enumerator, how can I send me email if no file found? Thanks.

View 2 Replies View Related

Send Email Only If There Is Data In The File

Mar 3, 2008

Hi i have a SSIS package, that looks at the @@rowcount in a table and if the rowcount is >= 1 then send the information on to a file.

Now i want to only email my self to say if the file has data in it.

Is there anyway of doing this on the send email task. Can i use an expression ?. I've check and i can't see a away of checking the file size etc.

View 1 Replies View Related

Extracting The Attached File From An Email

Feb 1, 2008

Hi there,

I need to extract a attached file which is sent via email. I believe that I can do this via SSIS... but I would like to know how...
Could you kindly send me any idea about how can I do it?




Thank you, so much.
Luis Antonio

View 15 Replies View Related

How To Set Up A Regular Job Which Will Put The Quesry Result Into File And Email It?

Oct 17, 2006

Dear forum people,

I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.

I am appreciate every answer.

Thank you.

Ann

View 3 Replies View Related

Data File Size Alert By Email

Sep 14, 2012

I've set up an alert to email me whenever the database is over a certain size. The amount is calculated by taking 80% of the total data file size. The problem I'm having is that it keeps generating a false positive alert because MS SQL seems to treat the currently used value as the total allocated space for the database data file. For example, the data file is 100MB, it's currently using 60MB, and if I enter 80MB in the alert, it generates an email alert claiming the current size is 100MB. Could it be because the data file size is set to 100MB (since autogrowth is disabled)?

View 4 Replies View Related

Email Task And File System Deployment

Oct 22, 2006

does email task only work with sql server deployment? I have an email task in my ssis package and i want go for file system deployment.

thanks,

kushpaw



View 1 Replies View Related

Sending File Contents In The Body Of The Email With Xp_sendmail

Jul 23, 2005

I would like to send the contents of a file using xp_sendmail howeverI do not want the file contents to be an attachment.I have no problem sending the file as an attachement.Can anybody give me an xp_sendmail example of how to do this.The results of a query can easily appear in the body of the email butall myattempts to include the contents of a file in the body of the emailhave not worked.TIA

View 1 Replies View Related

Sending Email / Paging In A Windows Batch File

Mar 8, 2008

Hi,

First, I would like to apologize if this is in anyway not related to Transact-SQL. And I would greatly appreciate your help for this is an urgent requirement. I just have a batch file which performs an ftp. Now, if the ftp process aborts, i would like to send out a mail and page the on-call person. I am not sure how to do this - I mean how to send email and send message through pager. My batch file is called by the Windows scheduler.


Thanks in advance.

View 7 Replies View Related

Export Query Results To Excel File And Add As Attachment In Email

Jan 2, 2014

Is 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 Related

SQL 2012 :: Send Binary File Stored In Server As Email Attachment?

Apr 26, 2014

Is 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 Related

Reporting Services :: Format File Name In EMAIL Data Driven Subscription

Mar 12, 2014

how can i dynamically specify the name of a report before sending in an email (example @reportName@datetime@parameter1@ServerName)

I have read most of the related articles and i am still curious about this topic - the technology is there so why wouldn't microsoft just add a REPORTNAME field to the email data driven subscription?

View 4 Replies View Related

Reporting Services :: Data Driven Subscription With Dynamic Email Attachment File Name

Feb 11, 2010

I have report which accepts a card number and fromdate and todate as parameters to the report. This report needs to be sent on a quarterly basis to each of the customer mail id to which their card number is linked. I am getting all this information from a database and sending as an attachment to the customer. Now I would need making these filename's which are attached to be dynamic based on the input parameters.

In datadriven subscriptions, the option of include report has only true or false values and another option was to take from a database. I tried putting the dynamic file name in the database and getting the value from the database but no success, the subscription itself is failing here. I guess I am doing something wrong here by binding the report name from the value which I am getting from DB to the actual report name.

If report name = Mytransactions, and the parameters passed are Card = 123,fromdate = 1/1/2010,todate = 31/3/2010.

Now in the attachment the file name should be something like "Mytransactions_123_January1st2010_March31st2010".
How to make the filename dynamic.

View 9 Replies View Related

Reporting Services :: Data Driven Email Subscription With Different Email Per Report Page

Jul 6, 2015

I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.

View 2 Replies View Related

Dbmail Doesn't Rely On IIS SMTP, How To Set Bounced Email Redirect Email Etc.? Thanks

May 4, 2007

Under IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.



Any idea?



thanks

View 2 Replies View Related

Help Split List Of Email Add Comma For Evry Email

May 12, 2008

need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this

emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm

need to do ilke this



Code Snippet
@list_email = (SELECT emall FROM tblLogin)

--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm

@recipients = @list_email










Code Snippet

IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )



BEGIN

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST(( SELECT

FirstName AS 'td','',

LastName AS 'td','' ,

Date_born AS 'td','' ,

Age AS 'td','' ,

BirthdayToday AS 'td','' ,

BirthdayThisWeek AS 'td'

FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>

<tr>

<td>name</td>

<td>fname</td>

<td>date</td>

<td>age</td>

<td>aaaaaaaaa</td>

<td>bbbbbbbbbbbbbbb</td>

</tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'rrr@iec.co.il',

@copy_recipients='rrrrr@iec.co.il',

@body = @body,

@body_format ='HTML',

@subject ='ggggggggggggggggggggg',

@profile_name ='ilan'

END

ELSE

print 'no email today'

View 1 Replies View Related

Capture Changes

May 23, 2002

Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??

View 3 Replies View Related

How To Capture Before And After Changes Of DDL

Aug 3, 2015

I have to create DDL trigger for audit to capture the object definition before and after the changes.

Like If any user running the Alter table Statement, i need to capture the Object definition before and after changes..

View 3 Replies View Related

Event Capture

Jul 27, 1998

Hi,
We would like to capture events in our system. There seem to be
three obvious capture points for us - application, triggers, transaction
log. The latter seems to be the most attractive, since we`re looking
for a solution with minimal performance impact. In general, our
problem is similar to populating data warehouses from on-line databases.
Can anyone proffer some advice? In particular, being quite new to
SQL Server, I am not sure how difficult/possible it is to read the
transaction log in order to cull events. Some direction here would
be greatly appreciated.
Thanks,
Karl

View 1 Replies View Related

Capture Error

May 2, 2002

Hi all,
Is there anyway to capture the SQL Server Error and act accordingly?
I donot want sql server to raise an error when a Primary key violation has occured. Instead i want to capture that error(number,description etc) and act
accordingly.
Whats happening is, from the application we are trapping this sql error
and raising it. Instead, if somebody inserts a record which already exists, then we want to trap that error from the sqlprocedure itself and then do an update to that record.

thanks for the help

View 1 Replies View Related

Capture A Sql Command

Aug 16, 2006

Hi All.

I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.

If anyone knows how to do this, please help.

Thanks,
ODaniels

View 5 Replies View Related

Text Capture

Jan 30, 2004

I was wondering how you go about capturing text as it is entere into a textarea? i want it captured exactly as it is typed, with carriage returns and everything. is there an easy way to do this?

View 14 Replies View Related

Capture NT User ID

Apr 5, 2007

Access front-end, SQL Server 2005 backend.I have users connected to SQL Server via a Microsoft Access user-interface.Connection is via NT login.I want to log users' activities to the database with their userid.How can I capture their NT User ID (via VBA in Access)?Thanks,Bubbles

View 1 Replies View Related

Capture I/O Per Database

Mar 29, 2007

Hi,



Please help me how can i capture I/O operation per database SQLServer2005.



Regards

Sufian

View 5 Replies View Related







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