I'm trying to figure out how the SQL Agent sends email through jobs using the notification option for operators. I have had little success using SQL mail so I want to use an alternate such as blat. I have not been able to find where the SQL jobs are actually sending the mail. Can someone help please. OI have checked the system stored procedures without suscess. I'm using SQL2000.
Thanks,
Steve
Hi,
I know that is possible to programmatically send an e-mail with the command xp_sendmail, but i want to know if it's possible to programmatically send a Net Send message.
Martin
Do you know a stored procedure ( or a query) to send a message to a SQLServer user ?
Something like NET SEND but with a spid user
Thanks
I am having a problem getting informational type messages to display for my end users.
I have a SQL2000 db with a Delfi front end. When I issue either the raiserror or print nothing is displayed inside the app. With raiserror I can get it to display if I make the severity higher than 10. The problem with this is that it stops the app from processing the transaction. This is ok in one case but not with just informational type stuff. Any suggestions? Thanks.
Jim
Say i want to send a blob in a message from queue A to queue B. These locations can be geographically different. How can i implement this ?
Also anything which i want to send as a message should be stored in the database. and then I should store it in a variable. this can be used as the message body. am I right ?
Can i send a file as the body of @message
in the extended sp xp_sendmail?
Pls help! thanks for any help.
I understand that SQL Service Broker will RECEIVE messages in the same order of SEND, so long as the messages are on the same conversation.
I would like to accomplish queue-level ordering instead of (or in addition to) conversation-level ordering.
There is a significant business case for this level of ordering. Consider an order processing system which is specified to fulfill orders in the sequence they are received. The reason for the ordering is as follows. Suppose the process(es) that RECEIVEs from the queue is down for several hours and the messages back up in the queue. Various customers place orders throughout this period of time. If more orders are placed than there are quantity for an item, the customers who placed their orders earliest in the day ought to be the ones that receive the merchandise and the later orders should be placed on backorder.
Limited experimentation showed that SQL Server totally disregards the order in which the messages were sent (on different conversations to the same queue).
The potential solution to use the same conversation has some drawbacks:
1. Difficult to do error handling because of the way error handling works in conversations.
2. It is not possible to RECEIVE using multiple threads. (Yes, RECEIVEing on multiple threads also would reorder the messages, but the reorderings would be localized in time so this would be tolerable by a lot of applications. In other words, orders from 1:00 AM would not be mixed with orders from 9:00 AM.)
Can anyone see a good solution to this in the current version?
If there are no good solutions, it would be great if Microsoft considers adding a feature where you can declare an ordering requirement when you CREATE QUEUE. Even support for an "approximate" ordering (messages can rearrange +/- several seconds) would be helpful, but the current way which seems to totally randomize the message order is not good for certain kinds of significant applications.
Hi All
I am stuck in this strange issue since the last two days. I have followed the tutorial to configure the 2 databases to send and receive messages which works. I added my own objects in the DB's also but when I try to send a message to the service I added I get a message:
"Dialog security is unavailable for this conversation because there is no security certificate bound to the database principal (Id: 1). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation."
Security is setup in the same way for my objects as the security specified in the tutorial. The service binding on the initiator and target use the same account as specified in the tutorial. Please help.
Thanks.
I used onError event to send email in case ssis pckage fails
but it send multiple email with errordescription. for ex below are the errordescription of four diferent emails i received.
Thread "WorkThread0" has exited with error code 0xC0047039.
An error occurred with the following error message: "The connection "{01AF859A-CF97-4F6C-9C78-1AA4B1C9C27B}" is not found. This error is thrown by Connections collection when the specific connection element is not found.".
Thread "SourceThread0" has exited with error code 0xC0047038.
The PrimeOutput method on component "Flat File Source - Read from source file" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Can anyone suggest if we can combine all this error description and send this as one email.
I 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.
Hi All,
I want to send notification to SSRS on change in database for that I am using SSB to send message to SQL Server Reporting Services.
Can I use SSB and if yes, how? Please guide me I am new to this.
Thanks,
Omkar.
Hi Guys,
I have already spent so many days in trying to find a solution to this problem but ..all in vain....
so please help me.
I have to send message to my running .Net [Asp.Net with C#] application from sql server. How can i do it?
We should not use notification services.
Please give me some examples / links.
Thanks in advance!
i have developed a pakage which populates a two different tables with reference to the xml files added to a folder which is watched by a security WMI task.it is governed by a sequence container which contains three for each loop container for working on the different files.i have different event handlers set up inside for each loop container tasks which contains , data flow task, execute sql task, and moving the processed file to the desired destination.i want to set up a send mail task on the package level using event handler on error, where i have set up a task for looging the error to the error table , i have tried to collect all the error messages in a array list variable . and trying to use that variable a s a message source. i could not under stand if i set the propogation variable in the sequence container as false than will the onpost execute event will fire the onpostexecute event handler in the package level.if show how can i send only one email for all the errors of package with error looging.
View 6 Replies View RelatedHello,
Is it possible to send message from SQL Server Express to Applications using it on different machines ?
For example When an important record is inserted or updated, a message appears on Application user's screen.
If yes then how ?
Thanks
hi
I am trying something very simple but cannot see why it is failing.
I am trying to add a filename to the message source of a mail task from a variable.
I have Variable 1 User::CurrentFilePath, Scope = ForEachLoop, Data Type = String
and Variable 2 User::mailSource, Scope = ForEachLoop, Data Type = String
Variable 2 EvaluateAsExpression is set to True and the Expression for now is simply "Processing of File " + [User::CurrentFilePath] + "has Begun"
However when i enter this i get
The expression for variable "Test2" failed evaluation. There was an error in the expression.
In the Value and Value type boxes of the variable property.
Any advice greatly received
Regards
Scott
Hi all, i searched everywhere but couldn't find any info on the following error that i'm currently receiving:
"The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'DI'."
I understand that this is due to some problem in my send/receive protocol but how do i fix this problem so that i can continue with my testing? Right now i'm forced to drop my entire test database and reinstall everytime this message shows up because i can't send/receive any messages at that point. Is there anyway to get rid of it?
Thanks in advance. This is driving me nuts.
Hi when I am performing a merge replication between my PDA (SQL CE) and SQL I get the above message. There is no information about it anywhere.
Any ideas?
Thanks in advance.
Hi,
I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.
My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.
Are there any other idea's how can i solve this?
Best regards,
Thomas
Hi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
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]....
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
Is there anyway to send excel file from ssis using send mail task without saving the excel file locally. I need to automate the process which involves loading the excel file from the database and send it to some people.
View 6 Replies View RelatedHello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
I have attempted to report out errors at the end of an ETL process by alerting supporting DBAs of errors using the SSIS "Send Mail Task". Task completes along with the sequenced packages, but does not mail anything out. No logic at this time for trigger, just success from the previous task triggering the task to send mail. I also get no errors in the output, and I get no output indicating the send mail task fired, but it does go "green". Do I have to enable database mail and have privileges?
Component Configuration:
SMTP Connection Manager - SMTP Server: arsocex02
Send Mail Editor -
From: messerj@arsocdev.bdev.lab.mil
To: sanderss@arsocdev.bdev.lab.mil
MessageSourceType: Direct Input
Expression: MessageSource = "Package>>> " + @[System:ackageName] +" was executed at>>> " + (DT_WSTR, 40) @[System:tartTime] + " by user>>> " + @[System::UserName] + " on Machine>>> " + @[System::MachineName] + " Errors reported to ERRORS_COURSE_CLASS_STATUS_T: " + (DT_WSTR, 50) @[User::ErrorCourseClassStatus]
Thanks
Hello All,
Hopefully someone out there will have an idea as this isdriving me nuts
I have some sample problem.I want to send many email in one send mail task, how can i do it?
what is a signal for separate between email address in send mail task.
please tell me for this event.
any suggesstion appreciated
Thanks you very much.
P.Chonnathan
Hi,It may sound funny but it is true. I have 2 version of same procedure, 1 is named as "update_aggre" other named as "update_aggre_2" .if I run these procedures using sql jobs the 1st procedure takes 12 seconds and the 2nd takes 2 second. I am stil surprise why is that both of them have same code.Any ideas please?and also I need to have lock mechanism within a procedure so that if some one is calling a procedure which is already called by some one ans is stil working on it the second user shld not be able to overwrite the existing data untill the first one is finished.TIAAmit
View 3 Replies View RelatedWe're upgrading to Sql 7 from 6.5 but having problems running jobs with the following sql statement :
SQLMAINT.EXE -D master -BkUpDB e:dump -BkUpMedia DISK -DelBkUps 2 -Rpt d:salogmaster_dump.log
The error message is [Microsoft SQL-DMO (ODBC SQLState :28000}] Error 18456:[Microsoft]ODBC SQL Server Driver][SQL Server]Login failed for
user 'Domainuser'. Process Exit Code 1. The step failed.
The user is a domain account and already in local administrator group. It also starts up both Sql server & Sql server agent. It can run the same job on another server. I have compared the set up and can't find any differences.
Thanks in advance for your help.
If i've created myself a job on my SQL Server and then scripted it, how do i run this script and so doing create my job on another server?
View 2 Replies View RelatedHi, I have a DTS package that runs correctly when executed manually, but when I schedule a job via the package, the job begins executing and nevers seems to complete the run. The Package runs in less that 1 minute, but the job sticks on executing and does not complete. I have to cancel, any ideas?
Thanks
I have 12 scheduled daily jobs, all the jobs are depending on one job.I want to run rest all the jobs if and only if the Job#1 gets success.Now all are running independently.Is there a way to force the sequence by using SQL.
Thanks!