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.
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
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.
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 ....
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.
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.
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?
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
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
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
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)
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.
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.
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)
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?
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?
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>
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?
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.
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
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
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.
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??
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.
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.
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..
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?
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.
