Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.








Sponsored Links:

Related Messages:
Sp_send_dbmail: Need Output To A Query Without The Messages.
I am writing a stored procedure which finally has to send mail to the customer with a list of phones that have been added to his account. I am getting these phones from a SQL Table in the form of a query, and including the output of the query(list of phones),in the body of the mail.

There are two issues that I am unable to resolve,

1. I just want the output with no messages.



eg. 8887775567

not


8887777767 ( 1 row(s) effected)



2. One phone number per row.



8887775567
8009978776
6679800077

NOT


8887775567 8009978776 6679800077


Thanks in advance.






View Replies !   View Related
Formatting Output
Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----------
SET NOCOUNT ON
DECLARE @errorCount INT
SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5
IF @errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
-------------------------
The Results in the bottom pane looks like this below
------------------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
-------------------
But, when I do a select, they are like this below
----

NODETABLE0There are 2 duplicate subclass name: Specification
Propertytable0at Parentid = 2000000859 and propertyid = 721
Propertytable0at Parentid = 10122 and propertyid = 9

View Replies !   View Related
BCP -- Formatting Output
Hi,

I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?

DECLARE @FileName varchar(50),
@FileName1 varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:GPPD_db_stats.XLS'
SET @FileName1 = 'E:GPPD_file_stats.XLS'
print @FileName
SET @bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @FileName + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand

SET @bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @FileName1 + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
exec master.dbo.xp_stopmail
set @bcpCommand = ' ' + @FileName + '; ' + @FileName1 + ''
DECLARE @body VARCHAR(1024)
SET @body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ....'


EXEC master..xp_sendmail
@recipients='alla.levit@amex.com',
@message = @body,
@subject = 'Database Weekly Statistics Report',
@attachments = @bcpCommand

Thanks in advance!
-Alla

View Replies !   View Related
Formatting SQL Email Output
Hi,

i am using xp_sendmail to notify system users when certain actions happen in the database but cannot control the format of the output. I am building a string for the message in the a trigger and then use
exec xp_sendmail @recipients='email address', @Subject='subject',@message=@msg

however in the @msg variable it would make a lot more sense if i could include linebreaks.

I have tried using html tags and setting the outlook installation on the server to send in html format but that doesnt work, the html tags are output as text in the message.

i have tried putting CHAR(13) in the @msg variable as i build it up but that doesnt work either.

does anyone have any ideas please?

View Replies !   View Related
Formatting Query Output (with Text!)
I have a query that outputs a single field from a table. The field is a VARCHAR(8000) and contains text with line feed and carriage returns (CHAR(10) + CHAR(13)).
When I use Query Analyzer to run te query I get the output formatted as desired with the CHAR(10) and CHAR(13)s causing carriage returns and when printed this produces the correct report format.
However, when using ISQL or OSQL and feeding the output to a text file, the file when printed does not trigger the line feeds and carriage returns but "unprintable" character boxes appear and each record just runs across a line, wrapping until complete.
How can I get the output from isql or osql to mirror that from Query Analyzer so that the file can be printed correctly?
Philippe

View Replies !   View Related
Export To Excel - Formatting Number Output
Hi all,

Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).

I
have a relatively simple task - create an excel spreadsheet with 3
columns of data - Id, Description and Sales. ID and Description are
text, sales is int.

So my SP aggregates and creates my resultset
in my OLE DB Source in the Data Flow. It proceeds to the Excel
destination, and that all seems fine. My issue is that the data is
being written as text. Looking at the excel destination in Advanced
editor:
the Excel Destination Input, Input columns are formatted as
I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and
DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.

The
target excel spreadsheet is being created from a template file. That
template file has header columns. The target column for the Sales has
the entire column formatted to NUMBER (0 decimals). Yet to now avail.

When
I check the spreadsheet, the column has retained the cell formatting,
and I have a 'I' pop-up to inform me that 'someone' has inserted text
data into the number column (even though the data IS number).

Since
the SP spits out INT, it isn't a case of receiving a text value, imho.
While trying to change the external column data type in the advanced
editor, SSIS is quite happy to let me change the value for the Sales
output to DT_I4, apply, and ok. Then, when I open it immedaitely
aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't
handle it, at least tell me when I try and change it. don't let me
change it, and then revert back without telling me! Grumble grumble...

So - anyone know a way around this?

View Replies !   View Related
Need Help W/ Sp_send_dbmail !
SQL Server 2005Help. I have a problem:On a 2000 box, I am calling the sp_send_dbmail stored procedure on a2005 box (they are linked servers).This is how I'm calling the proc (from the 2000 box), which usessp_send_dbmail to send the mail.EXEC My2005Server.DatabaseName.dbo.SendNotification@recipients = 'me@yahoo.com',@subject = 'SENDING @TOTAL,@body = @text1 + @text2 + @text3 + @text4 + @text5@text1 - @text5 are varchar(8000).As you can see I'm trying to take advantage of the varchar(max) that@body allows.However, I get this error:Server: Msg 170, Level 15, State 1, Line 22Line 22: Incorrect syntax near '+'.I need to be able to concatenate these somehow... as I cannot put theminto one large variable from the 2000 server side.Can you see the problem here?Help.Thanks

View Replies !   View Related
Sp_send_dbmail
When using "sp_send_dbmail", messages are queued through Service Broker. Other than the system views, "dbo.sysmail_...", in MSDB, is there another way to know if an email was sent successfully?

Also, in 2000 you did not need to supply the whole email address in order for xp_sendmail to work. For example, I could use my name, "RGioia" as the recipient and it would send email to rgioia@<my current domain>. Does anyone know of a work-around for this in 2005 or do you just have to ensure that full email addresses are used?

Thanks in advance!!

View Replies !   View Related
SP_SEND_DBMAIL
I wrote a trigger as follows:

CREATE TRIGGER TR_ABC
ON TABLE_A
AFTER INSERT
AS
DECLARE @E_MAIL VARCHAR(255),
@MESSAGE VARCHAR(255)
SET @MESSAGE = 'A new call request has been logged'

SELECT @E_MAIL = E_MAIL
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SCINFO',
@recipients = @E_MAIL,
@body = @MESSAGE,
@subject = 'SCI Service Request';


The problem is everytime I insert data into the table I get an error stating that-:
'At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Mail queued.'

Please help.

View Replies !   View Related
Sp_send_dbmail
Hello all,

I'm new to this forum and have the same question on SQLTeam.com, where I'm a frequent forum user.

I'm trying to send the results of a table to a group of people by email, see code used below:

exec msdb.dbo.sp_send_dbmail @profile_name = 'DT', @recipients = 'isantos@foo.net;', @subject = 'QA Results', @body = 'Some Text', @query = 'select * from [db].[dbo].[table]', @execute_query_database = 'db', @exclude_query_output = 1, @append_query_error = 1;


Error message I get by email:

Msg 15404, Level 16, State 19, Server TAKKARA, Line 1 Could not obtain information about Windows NT group/user 'WECLICKisantos', error code 0x5.

If I remove the @query, @exclude_query_output and @append_query_error options, I can send the email without a problem, just whenever I try to add a query to it that it gives me this error message.

I'm a sysadmin, I can run that query, I have access to the DatabaseMailUserRole under msdb and the profile that I'm using is public... I'm kind of stuck, let me know if you can help.

View Replies !   View Related
Sp_send_dbmail Question
I'm running the following test

declare @dbccdate varchar(60)
set @dbccDate='dbcc east' + convert(varchar(60),getdate(),10)
exec msdb.dbo.sp_send_dbmail
@recipeints='human@gmail.com',
@body = 'This is a test'
@subject= @dbccdate ,
@query='dbcc checkdb (msdb)',
@attach_query_result_as_file=1,
@query_attachment_filename='Dbcc_MSDB_Results.txt'

This works fine but, I'd really like the subject to contain the line
"Checkdb found 0 errors..." So mgmt doesn't have to open the txt file to view the results.

I've tried dumping the results to a txt file first. Then trying to read the text file for the line inquestion. I wrote a for loop to parse the results command line, but can't figure how to add that value to the subject.

The script is this:

for /f %i in ('findstr /B /I "CHECKDB" y:dbcclogmsdblog.txt') do echo %i

Is there anyway to do this?

View Replies !   View Related
Sp_send_dbmail Problem
I am trying to use the sp_send_dbmail sproc but I get the following error:

Msg 22051, Level 16, State 1, Line 0
Attachment file C: est.txt is invalid.

My code:


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Server Mail Profile blah blah',
@recipients = 'myEmail@somewhere.com,
@body = 'Hello World',
@subject = 'Hello World',
@file_attachments = N'C: est.txt'

why would that be? The test.txt file does exist on my drive!

Please advise.

View Replies !   View Related
Possible Issues With SP2 And Sp_send_dbmail?
Have a funny one that I've not yet resolved, and was wondering if I am the only one.

SQL2005 SP1 server, Ent Ed, 64 bit, clustered machine.
SP2a installed over Easter weekend.

about 12 days after apparently succesful upgrade, the cluster was failed over. After that fail-over, an existing job started failing, with the following message:
quote:Error executing extended stored procedure: Invalid Parameter [SQLSTATE 42000] (Error 22050). The step failed.

All the job does is build a SQL query, then supply the query to sp_send_dbmail for execution:

DECLARE
@RetCodebit,
@Tovarchar(200),
@Subjectvarchar(100),
@CCvarchar(200),
@Queryvarchar(max),
@Debugsmallint

--cut section that has the query , but these are straight SQL
--queries - no SP or XP usage whatsoever

EXEC@RetCode = msdb.dbo.sp_send_dbmail
@profile_name= 'Email',
@recipients= @To,
@subject= @Subject,
@copy_recipients= @CC,
@query_result_header=0,
@Query=@Query

One 'funny' I noticed is that up until the failover, SSMS seemed to erport the version of the server as still the SP1 (2157). After failover, it now lists (3042). This may simply be a symptom of SSMS not auto-refreshing though. We have (I believe) failed back again since then, still no joy.

Any Ideas?

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

View Replies !   View Related
Sp_send_dbmail &&amp; COM Errors.
I just started getting these errors on a few (not all) servers the other day. These servers have been running fine for a few months now...



Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: Error initializing COM

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.



I can only find one thread regarding this error, specifically with dbmail and I am doing the same thing (executing a query and attaching the results).

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=248650

This thread points to a KB article http://support.microsoft.com/kb/910416which is close except for the fact that these were not upgrades, but clean installs.I'm leary of applying the hotfix to my servers.

Is this a known issue internal to MS and is the referenced hotfix appropriate to install?

View Replies !   View Related
Problem With Sp_send_dbmail


Hello,


I have a problem when send a mail with sp_send_dbmail.
the mail server adds a legal warning to the sent message but this he visualizes with strange characters
because can be?

Thanks.

View Replies !   View Related
Sp_send_dbmail Without Using A Cursor?
I have a table which contains a list of addresses to send an email to. I'd like to be able to do something like:


update emaillist

set sendcount = SendMyMessage( emailaddress )
where sendcount = 0


In this case SendMyMessage is a user defined function which calls the sp_send_dbmail stored procedure.

However, I am receiving an error saying "Only functions and extended stored procedures can be executed from within a function"


From what I've seen, it sounds like I am forced to use a cursor to go through my email list table. Is that the only way?

Under SQL 2000, using a user defined function to call CDONTS was a no brainer....



Thanks,
Chris.

View Replies !   View Related
Sp_send_dbmail Possible Security Issue
I have a stored procedure that sends an mail using sp_send_dbmail. My problem is, if I execute the stored procedure via my ASP.NET 2005 application, the email will not send. I know the stored procedure is being called and works because:
1. The stored procedure does 2 things, sets a status, then sends the email. The status is being changed, but the email is not being sent.
2. When I investigate with Sql Server Profiler, I see the stored proc being called and the values of the parameters are set as they should be.
What I don't understand is, I can copy the call in Sql Server Profler, and paste it in a Query Analyzer window and execute that same call, and everything works fine, status is changed, and the email is sent (to myself for testing and I receive it just fine).
Is there a reason why the email won't send via code? Am I suppose to setup some security that will allow my application to send emails? Any info would be GREATLY appreciated.

View Replies !   View Related
Sql 2005, Sp_send_dbmail, @profile_name
Hi everyone,
I'm trying to usie "sp_send_dbmail" and i'm required to provide value to: "@profile_name" argument. The trouble is i have no idea what it means..
How do i get to know what is my "profile_name" ?
Thanks.

View Replies !   View Related
Problem With Sp_send_dbmail With Attachment
Hi,

We have a DTS package in the old SS2000 that we are still using in SS2005. We change the old xp_sendmail to use the new sp_send_dbmail. This runs fine without attachment. But with attachment, I'm encountering an error when running the package.

The task reported failure on execution.
The client connection security context could not be impersonated. Attaching file requires an integrated client login.
Syntax error or access violation.

I cannot find much topic on the net about this error.

Thanks very much for any help.

Regards

View Replies !   View Related
Trigger Problem With Sp_send_dbmail
Hallo

I want to write a trigger that sends me an email, when new records are added into a table.

For testing I have created one table called location1, which has simple data from AdventureWorks.Production.Location. And another table is called new_location, which stores the newly added records.

Here is the trigger:********************************
use [AdventureWorks]
if object_id ('location1') is not null
drop table location1
go
create table location1(LocationID int, LName varchar(50))

if object_id ('new_location') is not null
drop table new_location
go
create table new_location
(LocationID int, LName varchar(50))

if object_id ('change_ID', 'TR') is not null
drop trigger change_ID
go
create trigger change_ID on location1
for insert
as
delete new_location
insert into new_location
select * from inserted

exec sp_send_dbmail
@profile_name = 'Profile',
@recipients = 'email',
@subject = 'New Record(s) added.',
@query = 'select * from new_location order by LocationID';
go
********************************

After I insert statement, Management Studio runs the executing throughout.
insert into location1
select LocationID, Name
from Production.Location
order by LocationID

Can somebody help?











View Replies !   View Related
Msdb.dbo.sp_send_dbmail Error
I make PROCEDURE to send email useing db msdb and this PROCEDURE dbo.sp_send_dbmail like this

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'saly',

@recipients = @email,

@subject = @subject,

@body = @body;

Go

And when EXEC it gives me mail qeue

and mail don't arrive where it goes i don't know please tell me what error

thankx very much

View Replies !   View Related
Sp_send_dbmail Erroring Out On Line 476?!


Hello all -

I'm using SQL Server 2005. I'm trying to send an email with query results attached.
I've enabled database mail (surface config, config wizard, profile set, accounts loaded).
I know database mail itself works as I'm able to send plain text messages as well as messages with HTML embedded.
However, when I try to format a query to send attachments with mail, as shown in BOL; I always get this error message -
<snip>

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: Error initializing COM

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.
<snip>

Here is a sample of the query...

DECLARE @WHO VARCHAR(255)

SET @WHO = '<name would go here>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'sqlmail'

,@recipients = 'my.name@wouldgohere.com'

,@subject = 'testing attachment'

,@query = N'SELECT email_body

FROM WFS_non_stock_email_sendit_history

WHERE who_entered = @WHO

ORDER BY date_created DESC

OPTION(MAXDOP 1)'

,@execute_query_database = 'whaley'

,@attach_query_result_as_file = 1

,@query_attachment_filename = 'TESTATTACH.txt'

,@query_result_separator = ';'

If I were to run this query outside of this dbmail code, the query works fine.

If I can believe the go to line editor, line 476 in the sp_send_dbmail sproc is...

DELETE sysmail_attachments_transfer WHERE uid = @temp_table_uid


My problem here is this is a system sproc that MSFT built.I don't understand why I would get this error message instead of something more definitive and indicative of the problem.

Has anyone else seen this problem? I really need to be able to send attachments.

Thanks

Randyvol

View Replies !   View Related
Sp_send_dbmail. How Can I Use A Query For @recipients
Hi everyone,
i'd like to assign "@recipients" for sp_send_dbmail derived from a query as follows:



Code SnippetDECLARE @mailist VARCHAR(2000)
SET @mailist=
'select email
from
server.db.dbo.table
where
lastname=' +''''+'aaa'+''''
exec msdb.dbo.sp_send_dbmail
@profile_name='myprofile',
@recipients=@mailist,
@subject='email address through a query',
@...



when i run it i dont get any error message but mails to dot arrive.
any suggestion why ?
Thanks a lot

View Replies !   View Related
Sp_send_dbmail Doesn't Work
hi all,
i made a stored procedure that uses the sp_send_dbmail to send mails. SQL server dislays the message "mail queued" but nothing is recieved

here is the code of the stored procedure i made
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Exams',
@recipients = 'me@domain.com',
@Body_format = 'HTML' ,
@subject = 'Room Preparation' ,
@body='hi there';
so can anyone help with this issue
thanks in advance


View Replies !   View Related
Sp_send_dbmail Result Header
I am trying to use sp_send_dbmail to execstored procedure results to an email as an attachement. This works very well, except for one problem, it is not giving the result headers for the queries in the stored procedure even with the

@query_result_header = 1



I have also tried this withaVERY simple query, just incase it was the multiple result sets causing the problem, also not putting the results as an attachment and just straight into the body.



Does anybody know how to solve this problem?



Thanks in advance,



Chris



View Replies !   View Related
Sp_send_dbmail Query Option
Hello,
I'm using sp_send_dbmail with query option. Is there a way to not have "1 rows returned" in the email?
Thanks!

View Replies !   View Related
Sql Server 2005 How Do I Turn On Sp_send_dbmail
Hi everyone.
I've just installed sql server 2005 (evaluation) and i need to turn the option of using sp_send_dbmail on. I tried books on line but didnt manage to understand how it is done.
Thanks

View Replies !   View Related
Sp_send_dbmail-Mail Not Sent - Remote Certificate
When i use sp_send_dbmail stored procedure inside a Execute SQL task,the task is executed successfully.

But when i look in to 'Database Mail Log', it says

"The mail could not be sent to the recepient because of the mail server failure. Exception message: cannot send mails to mail server(The remote certificate is invalid according to the validation procedure)

Please let me know what can be the issue.

View Replies !   View Related
Attach Query Results As Xml With SP_SEND_DBMAIL
I'm trying to get sp_send_dbmail to attach the @query result set as an xml file.

The email is being sent and the attachment is present, but rather than ascii xml, it contains a chunk of nonsense like this
0x440352004F005700440470006B00650079004409660069007200730074006E0061006D00650044086C006100730074006E0061006D0065004410700068

I've tested the query with the first bit of code and sent the email with the second bit. I think I can jimmyjack it to write the xml to a temp file on the server and send that file using the @file_attachments parameter, but I'm hoping someone out there knows how to get this one to work.

Thanks in advance for any help you can give me.

Here's my code:


-- test that xml output is being generated
DECLARE @OUTPUT xml
SET @OUTPUT = (SELECT * FROM database..table FOR XML PATH('ROW'), root('ROOT'))
SELECT @OUTPUT

-- run the email proc
EXEC msdb.dbo.sp_send_dbmail
@QUERY= 'SET NOCOUNT ON SELECT * FROM database..table FOR XML PATH(''ROW''), root(''ROOT'')'
,@RECIPIENTS= 'userid@companyname.COM'
,@subject= 'Subject'
,@attach_query_result_as_file= 1
,@query_attachment_filename= 'ATTACHMENT.xml'
,@query_result_width= 32767
,@query_no_truncate= 1
,@append_query_error= 1
,@query_result_header= 1

View Replies !   View Related
Account Lockout Problem With Sp_send_dbmail
I am running this code in a job:

EXEC msdb.[dbo].sp_send_dbmail
@profile_name = 'Blah Blah',
@recipients = 'blah@blah.com',
@subject = 'Server Report 1',
@body = '',
@execute_query_database = 'DBInfo',
@query = 'EXEC dbo.usp_SvrRpt1;',
@query_result_header = 0,
@query_result_width = 300,
--@attach_query_result_as_file = 1,
@query_result_separator = '';
GO

Because I am creating a total of 6 server reports, I alter the subject and query like so:

@subject = 'Server Report X' --whereX is replaced withvalues 1 to 6
@query = 'EXEC dbo.usp_SvrRptX' --where X is replaced with values 1 to 6

I can run 4 reports without any problems. If I run more than 4 reports in the same batch,my network account under Windows 2000 Activity Directoryis locked out as if I had erroneously mistyped my password the number of time to meet the lockout requirement - in this case, the number is 3 login tries.

Is this a known problem with Database Mail? If so, is there a fix or workaround?

Thanks,

Neal



View Replies !   View Related
Sp_send_dbmail Trigger On Multiple Row Insert
Hi,


I have a SQL Agent Job that selects recordsthat are of a particular age (from Table1) and inserts them into another table (Table 2) - multiple records get inserted as asingle INSERT step. On Table 2 I then have a trigger set FOR INSERT which I was hoping would send an email using the fields copied from the batch job, one of which being an email address.


The trigger is: -




Code Snippet
CREATE TRIGGER trgSendMail ON Table2 FOR INSERT AS
DECLARE@myEmailVarChar(50)
SELECT@myEmail= strEmail FROM Inserted
EXEC sp_send_dbmail
@Profile_Name= '{MailProfile}',
@Recipients= @myEmail,
@Subject= '{MailTitle}',
@Body= '{MailBody}'






Thing is, the agent works fine but the trigger only sends an email to the first row inserted.
Any ideas on how to get around this so that if the batch job inserts 10 rows into Table2 then 10 emails are sent out?

Shaun.


View Replies !   View Related
Sp_send_dbmail @query Results Truncated


Hi,sending amail usingsp_send_dbmail (as below)with attachment works. However the characters in the attacheted file gets truncated to 256. The file is populated with the results from the@query string.


EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'SQLAdmin'

,@recipients = 'a@a.com'

,@subject = 'Test'

,@body = 'Test'

,@body_format = 'TEXT'

,@query = 'SELECTdistinct column FROM Table'

,@execute_query_database = 'PULSE'

,@attach_query_result_as_file = 1

,@query_result_header = 0

,@query_result_width = 32767

,@query_attachment_filename = 'a.pdf'

,@exclude_query_output = 1

,@query_no_truncate = 1

The value of MaxFileSize is 1 Mb when executing sysmail_help_configure_sp. It seems as if the @query_no_truncate value is being ignored by sp_send_dbmail procedure.

Any help would be appreciated.

View Replies !   View Related
Email Body Was Truncated In Tcp Layer In Sp_send_dbmail


I sent email from sp_send_dbmail in html format.
when i checked it in outlook, each piece was corrupted at 1000 bytes, the totalbytes in my email is 2600 bytes,it was corrupted twice. it looks like below:
<table>
<tr><td>Customer</
John Smith<td></tr>
</table>

I believe Sql server SMTP mail server sent the email body in 1000 bytes each TCP conversion.

Is any way I can change the TCP setting for SQL server SMTP mail engine.

Thanks,



View Replies !   View Related
Using Quotation Marks In A Msdb.dbo.sp_send_dbmail Query
Hi There,

I'm having a little trouble with a large query I have written to be sent as an email.

I want to send it as a csv so I've added commas in between each value in the query.

This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'

,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '

View Replies !   View Related
Database Mail Sends Test Mails Successfully, But Sp_send_dbmail Fails


Hi all,

I am having a few problems with Database Mail and wondered if anyone could assist. It sends test mails fine, but when I run the following script:

(I've changed my email address in this post to test@test.com they are accurate in the scripts.)


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Test Mail',

@recipients = 'test@test.com',

@body = 'Sent by sp_send_dbmail',

@Subject = 'SQL Server Email Test Email';


It gives the following errors.

(from the above script)


mailitem_id = 16
profile_id = 2
recipients = test@test.com
copy_recipients = NULL
blind_copy_recipients = NULL
subject = SQL Server Email Test Email
body = Sent by sp_send_dbmail
body_format = TEXT
importance = NORMAL
sensitivity = NORMAL
file_attachments = NULL
attachment_encoding = MIME
query = NULL
execute_query_database = NULL
attach_query_result_as_file = 0
query_result_header =1
query_result_width = 256
query_result_separator =
exclude_query_output = 0
append_query_error = 0
send_request_date = 2008-04-15 10:50:03.827
send_request_user= COMPANYTest.Test
sent_account_id = NULL
sent_status = failed
sent_date = 2008-04-15 10:50:04.000
last_mod_date = 2008-04-15 10:50:04.513
last_mod_user = sa


(from the test mail)


mailitem_id = 11
profile_id = 2
recipients = test@test.com
copy_recipients = NULL
blind_copy_recipients = NULL
subject = Database Mail Test
body = This is a test e-mail sent from Database Mail on UKDEVSQL1
body_format = TEXT
importance = NORMAL
sensitivity = NORMAL
file_attachments = NULL
attachment_encoding = MIME
query = NULL
execute_query_database = NULL
attach_query_result_as_file = 0
query_result_header =1
query_result_width = 256
query_result_separator =
exclude_query_output = 0
append_query_error = 0
send_request_date = 2008-04-15 09:51:46.530
send_request_user= COMPANYTest.Test
sent_account_id = 4
sent_status = sent
sent_date = 2008-04-15 09:51:46.000
last_mod_date = 2008-04-15 09:51:46.610
last_mod_user = sa


sysmail_event_log gives this error for mail item 16 (the scripted email):

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2008-04-15T10:11:41). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )

View Replies !   View Related
Output And Error Output Write The Same Table At The Same Time, Stall The Process.
Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View Replies !   View Related
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Commandas the Error flow ofthe Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the keyof the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to setthe output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command,2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistanceyou can provide.

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved