Hi,
i have a service broker application that i send message to the queue and a specific SP reads the message and process it, part of this processing is sending the information found on that message to a specific email accounts. this means i have two service broker applicatins, one for my business (sending messages to queue to be processed and the secound one is the SendEmail service)
when i send email using the SP "msdb.dbo.sp_send_dbmail" the email is recieved 100% true, but when i come to send the email through the SP which recieve the messages from the SB queue, the email is not sent and i dont know where it goes.
this means that i cant read from a queue and send to another one at the same time... can you help me in this!
The project I'm currently working on has to be relatively simple for the users to install, so I had the idea of using service broker to "set itself up". The idea is that there will be a server with a service and suchlike already set up. Somehow the clients will get the information required to create a remote service binding, and once that has been accomplished will send a registration message to the server.
What I'm wondering is can I somehow create and send a certificate using service broker via t-sql, and also is this approach reasonable or is it a bad idea. The reason I had thought of something like this is because the people setting up the client sites may (and probably will) have very little knowledge (probably no sql server knowledge) so we need the installation to basically be a click and install.
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.
I have a table at my initiator, i have a copy of this table at my target, every morning i want to refresh the table at the target with the data from the initiator via service broker.
What would be the best way to do this?
One major consideration would be that this table would be very large +- 1.6 million rows.
I was thinking of exporting the table to file like a csv, compressing the file and then sending it via service broker. Then at the target i would uncompress and bulk copy the data into the table at the target.
But can this be done?
How would one go about sending a file via service broker?
For example if i wanted to send a .csv or something via service broker how would i do it?
I know i can send binary data, but i would have to equate the msg to a file on disk, and how exactly would a recieve this file on the other side and make sure it went to disk at the target, i am guessing it is a mixture of xp_cmdshell and service broker.
Can this work?
Or is there a much simpler way? Any thoughts would be appreciated.
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.
I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....
CREATE PROCEDURE [usp_ProcessMessage]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body AS VARBINARY(MAX)
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP (1)
@conversation_handle = conversation_handle,
@message_body = message_body
FROM [tcp://mydomain.com/INITIATE/MyQueue]
), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
END CONVERSATION @conversation_handle
IF @message_body IS NOT NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]
WITH ENCRYPTION = ON, LIFETIME = 600;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]
(@message_body);
END
COMMIT;
END
END
GO
My endpoints are created like so:
CREATE ENDPOINT MyEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)
GO
GRANT CONNECT TO CertOwner
GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner
GO
And my routes like so:
GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner
GO
CREATE REMOTE SERVICE BINDING [MyCertificateBinding]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
Hi 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
Hi, 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?
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.
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.
I am doing some research to see if the Service Broker technology would help my company with our Enterprise application. Here is our scenario: We have a 3 tier system. The first tier needs to contact the second tier asynchronously. Hence, using queues is a good option. However, the process that needs to happen on the second tier is mostly process intensive with little database updates. Is it still worth our time to use Service Broker?
I like the concept of Activation that Service Broker provides. But, from what I am reading most of the documentation describes activation as a way to call another stored proc. I definitely dont' want to do any process intensive work on the SQL server. So here comes my question...
How would I use a windows service to listen to the activation event from the Service Broker. I could have multiple windows services watching the same queue (scalable). Would I have to handle collisions myself? If so, I think I would rather keep it simple, and just use a simple table as my queue.
This was a P A I N to get working. Maybe someone else here is sending email and could use it.
A UNICODE Send Mail using CDOSYS with ReadReceipt and Importance...
Sub SendMail (sFromAddress, sToAddress, sCcAddress, sBccAddress, sSubject, sBody, boolReadReceipt, intImportance ) 'on error resume next Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to" Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to" dim cdoMessage, cdoConfiguration
Set cdoConfiguration = Server.CreateObject ("CDO.Configuration") ' Outgoing SMTP server With cdoConfiguration .Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost" .Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 .Fields.Update End With
Set cdoMessage = CreateObject("CDO.Message") With cdoMessage ' Update the CDOSYS Configuration SET .Configuration = cdoConfiguration .BodyPart.charset = "unicode-1-1-utf-8"
IF boolReadReceipt Then .Fields(cdoDispositionNotificationTo)= sFromAddress .Fields(cdoReturnReceiptTo)= sFromAddress End If
' Set the Importance: 0:Low, 1:Normal, 2:High .Fields("urn:schemas:httpmail:importance").Value= intImportance .Fields.Update .From= sFromAddress .ReplyTo= sFromAddress .To= sToAddress .Cc= sCcAddress .Bcc= sBccAddress .Subject= sSubject .Textbody= sBody .Send End With
Set cdoMessage = Nothing Set cdoConfiguration = Nothing End Sub
I'm trying to implement DTS and FTP. So that when a company sends or FTP infomation to our server, it runs a script and checks to see if the infomation is correct and sends and email saying successful or unsuccessful.
1. I'm trying to get the notifications working on the scheduled jobs.I want to email me when the job fails. I created an operator(dmalhotr) and given me as the email address.Here is the error what I'm getting when I'm pressing the test buttonto email myself in properties dmalhotr operator (hope that makessense).Error 22022: sqlserveragent error: the sqlserveragent mail session isnot running; check the mail profile and/or the sqlserveragent servicestartup account in the sqlserveragent properties dialog.I cancel out of it. Then I go to support services --> sql mail -->right-click properties (WE HAVE LOTUS NOT EXCHANGE HERE- dont knowwhether that makes a difference).It says profile name Outlook and I click test. It says Successfullystarted (and stopped) a MAPI session with this profile.I go back to the operator and then click test to send email and I getthe same error. I started and stopped sql server agent and still getthe same error.Not sure how this works, please send me an email as to what I'm doingwrongThanks:DHRUV
It seems that using the Send Mail Task, there is no option for me to format my email as HTML. I have all my HTML code in a string and need to format my email to send the string as an HTML email. What would be the best way to approach this?
I am generating the text file on run time using flat file destination. The text file is generated on the location C: SSIS or D:SSIS based on the location specified in the configuration file.
The text file contains the non matching rows during lookup transform task.
I want to send this dynamically generated text file using the send email task.
But while doing this, I receive an error during package validation:
Package validation error:
Error at send email task [ send email task ] : either the file C:SSISErroroutput.txt does not exists or you do not have permission to access the file.
Error at send email task ; There were error during task validation.
Please suggest as I need to generate the text file only in case of lookup failure.
I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage(); But VS2005 doesn't seem to want me touching System.Web.Mail. Any ideas? Thanks, Allen
Does anyone know if this can be done? I've looked everywhere. This is how my info is appearing in an email... These are the files I requested and this is how it's being displayed. Any suggestions? Requestor Requested Shipped Due ------------------------------------------------------------------------------ ----------------------------------------------------------------------- ----------------------------------------------------------------------- ----------------------------------- ----------------------------------------------------------------------- ---- ------------------------------ ------------------------------ ------------------------------ Approaching the Corporate Heart
klm@yahoo.com 02/07/2008 02/07/2008 02/06/2008 PASSION FOR CUSTOMERS 3 copies
Hi 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
When a new record is inserted into the database. I want this to trigger an email that will be sent to all users who requested one when a new record was added. I can't figure the best way to set this up. All help gratefully received. Thanks JB
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
I’d like to update rows matching a certain criteria each night automatically and then send an email.
I have a table with a Status column and an Expire Date column. I want to update rows where the Status = Open and the Expire Date < The current Date by changing the Status to Closed then send an HTML email. Do I have to write a program and schedule it to run or is there a way to do this in MS SQL? I looked at using a stored procedure but I don’t see how to schedule one to run (other than at startup) and I’m not sure if the GETDATE function can be called.
I have a basic trigger that populated an audit table, but I want to add logic to that to send an email everytime the trigger is called,Is there a easy way to add code to my basic trigger to send an email to me everytime the data changes.Thanks
I have a datetime field in a databse for when a tender closes. More often then not its 4pm - for what its worth. And I want to send an email informing the manager 6hrs, 30mins prior and when the tender closes. So I need to somehow check that field either once a day and store those times somewhere or quite regularly ... like every hour.
I would be interested to hear how people would approach such a concept. I havn't got a great deal of experience in triggers or SQL mail.
Hi guys,I hope somebody can help as I am trying to write a procedure in SQL that will be an email reminder sent to users on every 5 th or the month if they don't submit data before.
I am quite new to sql and procedures in particular so I was hoping somebody can help.
I need to: 1.Check the day of the month, if not 5th do nothing
2.Get the date of the prevoious month (get current date - 1 month,set day to 1)this is the funny bit. I have a field DateEntered,but users only select the month and the year on the acctual page,but when submitted it gets written as a full date and defaults to the 1st of the month.
3.Get the list of hospitals that haven't got data for the previous month
Dear all, i am using sql server 2005. i am facing a scenario where i have to send mail to single or multiple user depending on the query. i tried this stmt to send mail to multiple receipints EXEC msdb.dbo.sp_send_dbmail @profile_name = 'imran' , @recipients= 'imran.khan@bbraun.com;amol.puranik@bbraun.com', @subject = 'Test mail' it succesfully sent mail.
Now i want to send mail depending on query. with single employee it is no issue. but if it is more then one employee how can i mail to multiple recipient.
That is my question is in this sp to send multiple recepients one has to separates addresses with ; semi colon how can i arreange recipient so that ; comes in between.
