Sending The Same Message To Multiple Servers
Jul 19, 2007
Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?
Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.
The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.
Any advice is appreciated.
View 7 Replies
ADVERTISEMENT
Feb 14, 2006
Hi Remus
What if I need multiple clients to read (RECEIVE) the same message?
Would it be possible?
Thanks
View 8 Replies
View Related
Jul 20, 2005
I have everything set up with SQL Server 2000 and Outlook 2000 and theprocedure works fine but the message sits in my inbox. When it openthe e-mail it says this message has not been sent. I just click sendand the e-mail sends. Is there any reason I have to manually sent thee-mail after the xp_sendmail procedure works and should send thee-mail itself.ThanksJohn
View 1 Replies
View Related
Jun 18, 2007
Hi I have difficulty in sending message to different reciepients. I am using SSIS package to send in the parameter. If anybody could help to resolve will be great.
Thanks
declare @MailMsg varchar(1000)
select @MailMsg = 'Hi there,
Here are the Documents Nos. and details
Status.
Thank you.'
exec master.dbo.xp_sendmail
@recipients = ?,
@subject = 'Documents Status',
@message = @MailMsg,
@attachments = 'C:MyWorkDocument.XLS'
View 10 Replies
View Related
Apr 23, 2008
Hi,
I have define an "execute SQL task" followed by an "Send Email Task" :
http://img171.imageshack.us/img171/449/pic562qn9.gif
http://img171.imageshack.us/img171/1793/pic564jo2.gif
I want to include the package name that is being executed in the message body of the email, How can i do this ?
I have tried to set an output parameter with Variable Name "System:ackageName", but i get the message :
Variable "System:ackageName" cannot be used as an out parameter or return value in a parameter or return value in parameter ....
View 6 Replies
View Related
May 3, 2006
I have a need to asynchronously send a short message to an application over
tcp/ip after the user makes a selection from a web browser.
We are developing a reservation system where the availability to users in
close physical proximity is indicated by lights at a kiosk. We want the user
to be able to make a reservation from a web browser over the internet, (which
checks the sql server db for availibility), and when the reservation is made,
the appropriate lights at the kiosk indicate it is unavailable.
I can think of three choices, but am unsure which is best:
1. Poll the server to look for the event. I don't like this since the
application will be connected over a dial up internet connection, and I want
to keep traffic to a minimum, although this seems the easiest to build.
2. Have the users web browser be redirected to the application's ip address
directly. Here I am not sure which message protocol to use. The kiosk
application will be a .net 2.0 windows form app, but not running on a server
per se, but windows xp pro.
3. Have the database relay a message to the kiosk. I like this way the best,
since the user does not need to know the ip address of the kiosk, and the
kiosk does not need to be open from all ip addresses for security reasons. I
am not which way to do this either. I have read a little about hosting web
services using http.sys, but we probably want a shared hosting plan due to
costs at this point, and they are restrictive about turning on the clr inside
the db. Is there a different way to send a short message?
I apologize in advance if this is the wrong forum.
View 1 Replies
View Related
Apr 3, 2006
Hello,
I want to know the exact flow of events when I use this statement:
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]
Assuming that I have defined SERVICE1 for Queue1 in the initiator ,
and I have defined SERVICE2 for queue2 on the target.
Is this the flow:
1.Message first goes to the queue1
or
it directly goes to the SERVICE2 on target end point which in turn puts this message in Queue2 on the target?
2. target queue then activates the stored procedure which is connected to queue2 (let's say the procedure name is 'Processqueue2')
3.I noticed that even when none of the item comes in the queue, still when u use "ALTER QUEUE queue2 WITH STATUS = ON", that time also the 'processqueue2' is called.
4.'processqueue2' then fetch message from queue2 and process this. optionally it can send the acknowledgement message to the initiator or just send the end dialog message.
5.if i dont want 'processqueue2' to send the acknowledgement then
can I can directly send the End dialog from the inititor it self i.e. the end dialog just after sending the message.
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE SendMessageType ('hello from intiator')
END CONVERSATION @dialog
In this case why do i need queue1 at all?
my assumption here is that my communication is one way and i don't need the ACK from the target.
Thanks,
View 1 Replies
View Related
May 2, 2008
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 Related
Jan 29, 2008
I've got a situation where I want to put request message on a queue. Because starting a conversation is the only way to put messages on a queue I have to start a conversation with myself. So my Begin Dialog Statement looks something like this:
DECLARE @conversation_handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [ServiceName1]
TO SERVICE 'ServiceName1'
ON CONTRACT [ContractName1]
WITH ENCRYPTION = OFF;
SELECT @conversation_handle AS ConversationHandle
I haven't noticed any problems with doing this but I wanted to know if there was anything wrong with it. Does someone know what problems this might cause?
View 4 Replies
View Related
Mar 28, 2008
while i was trying to execute the code for Sending an "HTML Mail Message with the Script Task" given at the link below http://msdn2.microsoft.com/en-us/library/ms403365.aspx
following error was encountered.
DTS Script Task has encountered an exception in user code:
Project namecriptTask_098956444e9f4ae195c3565569c9444b
The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_098956444e9f4ae195c3565569c9444b.ScriptMain.Main() in dts://Scripts/ScriptTask_098956444e9f4ae195c3565569c9444b/ScriptMain:line 29
please help me
View 7 Replies
View Related
Oct 3, 2006
I set up the "Send Email Task" succesfully with "SMTP Connection to myExchangeSERVER" using "Windows Authentication"
However, as we all know - you can't have html format for the Send Mail Task. BUT this piece of code straight from MSDN doesnt work for me - each time it pops up this "Mail Sent Succesfully" - but I receive NO freaking EMAILs!!! Am I missing something or is it another one of those Microsoft "gotchas" ?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Public Class ScriptMain
Public Sub Main()
Dim htmlMessageTo As String = "me.here@mydomain.com"
Dim htmlMessageFrom As String = "SSIS@mydomain.com"
Dim htmlMessageSubject As String = "SSIS Success - My Package"
Dim htmlMessageBody As String = _
Dts.Variables("User::HTMLtemplateText").Value.ToString
Dim smtpServer As String = "myExchangeSERVER"
SendMailMessage( _
htmlMessageTo, htmlMessageFrom, _
htmlMessageSubject, htmlMessageBody, _
True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String, _
ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
htmlMessage = New MailMessage( SendTo, From, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.CredentialCache()
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
MsgBox("Mail sent")
End Sub
View 3 Replies
View Related
Jan 25, 2008
I have been using the ServiceBrokerInterface example for a project of mine and so far it has been very successfull. However , I recently attempted to use it to send a large-ish message (32K xml file from MS Word) to SQL. The message is recieved but the Body member of the message is null when I try to retrieve the data from within my stored procedure CLR method.
The only differences I can see between the message that does not work and those that do are that the one that doesn't is large and it is XML data that I read from a .xml file produced by MS Word as opposed to internally generated strings in the other working messages.
Does anybody have any suggestions as to why the body of the message is not being sent in this case?
Is there something about the example code that precludes sending this type of message?
This is the code that sends the message (extracted from Conversation.cs in ServiceBrokerExample)
string query = "SEND ON CONVERSATION @ch MESSAGE TYPE @mt ";
param = cmd.Parameters.Add("@ch", SqlDbType.UniqueIdentifier);
param.Value = m_handle;
param = cmd.Parameters.Add("@mt", SqlDbType.NVarChar, 255);
param.Value = message.Type;
if (message.Body != null)
{
query += " (@msg)";
param = cmd.Parameters.Add("@msg", SqlDbType.VarBinary);
param.Value = new SqlBytes(message.Body);
param.Size = -1;
}
cmd.CommandText = query;
cmd.ExecuteNonQuery();
This is the code that reads the message from the SQL queue (extracted from Message.cs in ServiceBrokerExample)
m_reader = reader;
m_convGroupId = reader.GetGuid(0);
m_conv = new Conversation(service, reader.GetGuid(1));
m_sequenceNumber = reader.GetInt64(2);
m_serviceName = reader.GetString(3);
m_contractName = reader.GetString(4);
m_type = reader.GetString(5);
m_validation = reader.GetString(6);
if (!reader.IsDBNull(7))
{
SqlBytes sb = reader.GetSqlBytes(7);
Body = sb.Stream;
}
else
Body = null;
This is the MESSAGE TYPE used by this message.
CREATE MESSAGE TYPE CreateReport_Command VALIDATION = None;
Here is the code where msgReceived.Body == null in the SQL CLR method invoked by this message.
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:ValidateArgumentsOfPublicMethods"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic"), BrokerMethod("AssessmentContract", "CreateReport_Command")]
public void CreateReport_Command(
Message msgReceived,
SqlConnection connection,
SqlTransaction transaction)
{
StreamReader reader = null;
try
{
reader = new StreamReader(msgReceived.Body);
And finally here is the code snippet that creates the stream that gets loaded into the message to be sent.
if (Report != null && ReportName != null)
{
MemoryStream ReportBody = new MemoryStream();
if (ReportID == null)
{
ReportID = Guid.NewGuid().ToString();
}
ReportBody.Write(Encoding.ASCII.GetBytes(ReportID), 0, ReportID.Length);
ReportBody.Write(Encoding.ASCII.GetBytes("|".ToCharArray()), 0, 1);
ReportBody.Write(Encoding.ASCII.GetBytes(ReportName), 0, ReportName.Length);
ReportBody.Write(Encoding.ASCII.GetBytes("|".ToCharArray()), 0, 1);
ReportBody.Write(Encoding.ASCII.GetBytes(Report), 0, Report.Length);
Microsoft.Samples.SqlServer.Message request = new Microsoft.Samples.SqlServer.Message("CreateAssessmentReport_Command", ReportBody);
// Send the message to the service
dialog.Send(request, conn, tran);
View 1 Replies
View Related
Jun 6, 2007
If i want to exchange message between two different instances (or) databases in two different servers, then I have to enable the transport protocol which wil be disabled by default in service broker.I read in some article that some registry settings has to be changed.So can someone suggest on the same.
"Connection attempt failed with error: '10061(No connection could be made because the target machine actively refused it.)'." Im getting this error in the initiating database.The message that i sent still exists in thr Transmission queue fo initiating database.
It will be better if someone sends a sample or the steps to follow to communicate between databases in two different servers.So that i can cross check with mine.
View 1 Replies
View Related
Apr 5, 2008
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.
View 4 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Oct 9, 2014
I need to send multiple emails to recipients one at a time what is the best way to do this?for example i do a select statement to get 5 email addresses, now i need to be able to fire off an email with verbiage to each one of them separately. The reason they have to be separate is in the verbiage i need to have there name in the 'Dear Jondoe1,' Here is my second dilemma. How would you insert a name in the verbiage for each email?
jondoe1@gmail.com
jondoe2@gmail.com
jondoe3@gmail.com
jondoe4@gmail.com
jondoe5@gmail.com
View 2 Replies
View Related
Apr 19, 2008
We're looking to send messages to an SSB Queue (our 'Notification' queue) that contains multiple values.. like Message #1: 20,1 to indicate Record #20, send Notification #1.. the reason for this is we have lots of different types of records in our database, and based on certain conditions, we would like to send different notices regarding these various record types.
What is the best format to send mesages like this? We thought about sending TABLE vars.. what other options are there?
View 3 Replies
View Related
Dec 15, 2005
Any ideas on how I can send multiple values from a listbox to a stored procedure? right now I have a ListBox Control called lbCategory, and I want to pass multiple selected items to a stored procedure.
<asp:SqlDataSource ID="dsFS" runat="server" ConnectionString="someConnectionString" SelectCommand="usp_FS" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lbCategory" DefaultValue="%" Name="category" PropertyName="SelectedValue" type="String" />
</SelectParameters>
</asp:SqlDataSource>
View 5 Replies
View Related
Dec 15, 2007
If I send multiple sql's with ado.net in one statement (one executeSql separated by semilcolons), and the second one fails, will the first one be rolled back? or do I need to put it all in a transaction?
View 1 Replies
View Related
Sep 18, 2014
I am tring to send an email reminder to multiple users using a cursor.
declare emailCursor cursor for
select name,info,default_email, @MyMessage
from My_Temp
open emailCursor
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
while(@@FETCH_STATUS = 0)
begin
-- select name,info,ins_default_email,@MyMessage
--from temp
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Hello',
@recipients = @Recipients,
@from_address = '<address.com>',
@Body = @MyMessage,
@subject = 'Subject'
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
end
close emailCursor
deallocate emailCursor
Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.
View 2 Replies
View Related
Nov 15, 2007
Hi All,I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?ThanksBryan
View 3 Replies
View Related
Mar 2, 2015
What's the easiest way to run the same db across multiple servers?
View 4 Replies
View Related
Jun 3, 2008
hi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)
View 2 Replies
View Related
Jul 12, 2000
suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you
View 2 Replies
View Related
Feb 6, 2001
Question:
How to prevent a user/admin on one SQL 7 server, who knows the system admin password that is common to both servers, from accessing a second SQL 7 server via the SQL Server Group setup process.
Is there a way from shutting off remote access to a SQL server without limiting RPC?
All reading points towards 1) Remote Access Options, 2) Store User Independent, 3) Control Store as options to locking down a server. Would like to hear from someone that ran into this type of issue. Third party software available?
I've tried the following without any success: 1) I have deleted the second SQL server registration from the first SQL server group - but this is too easy to delete and recreate, 2) edit the SQL server registration for the remote server and force authentication - almost there if SQL prevented deletion of the registration without verifying the logon/password of the person who created it (again able to delete and recreate), 3) played around with the client utility to remove the TCP/IP pipe entry.
Anyone else pulling their hair out on this?
TIA!
View 1 Replies
View Related
Nov 12, 1999
Please excuse my niavety on this subject, But I have a simple question. When you have more than one SQl server 7 installation on the same network, each using different machines, does one automatically have the ability/authority to be able to stop the other server at will??
Thanks in advance for any help
Simon
View 2 Replies
View Related
Mar 12, 2007
Joe writes "I am trying to find whether I can have multiple SQL 2005 servers but having same security profiles and maintain them grom one place only. I prefer not to have windows authentication.
Thanks a lot"
View 1 Replies
View Related
Apr 13, 2007
I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.
Any help on this would certainly be appreciated.
Thanks in advance.
View 2 Replies
View Related
Nov 15, 2006
Hi,
I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...
Is there a way to do that...
I need to connect to two servers from the same procedure to get data..
Please get back
Thanks,
View 3 Replies
View Related
Nov 27, 2007
Hi,
I want to perform a join on two tables from two separate database servers.
Code Block
SELECT *
FROM tbl1 AS t1
INNER JOIN
"SERVERASQL2000".Production.tbl2 AS t2
ON t2.UserID = t1.UserID
Why do I get this error...
Invalid object name 'SERVERASQL2000.Production.tbl2'
thanks.
View 10 Replies
View Related
Aug 24, 2000
I have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?
Thanks!
Lia
View 1 Replies
View Related
May 3, 2007
We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.
We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.
View 4 Replies
View Related
