Flow Of Events While Sending Message From One Serivce Two Another
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
ADVERTISEMENT
Nov 14, 2007
Does anyone know how to hook up to data flow pipeline events via custom solution (C#)? I am trying to write code to log start and end times of components(lookup,merge joins etc) in a data flow task. I tried with a class inheriting from the EventsProvider class but it didn't work as this is only for container tasks. Any ideas will be greatly appreciated.
View 3 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
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
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
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
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 20, 2007
Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.
View 1 Replies
View Related
Jan 28, 2006
I have a start date, end date for each event.
I want to list all events between the start and end date comes in Saturday or Sunday.
in SQL server 2005 TSQL statement.
any insights ?
View 1 Replies
View Related
Feb 3, 2014
I am using this code for LongRunning Queries.
CREATE EVENT SESSION LongRunningQuery
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 60000
[Code] ...
Here Instead of writing to XML file how can send an EMAIL if a query runs more than 1 minute in my server ?
View 2 Replies
View Related
Dec 14, 2007
Hi there,
I have a Data Flow task which uses an XML File Source with six parellell Outputs, each going firstly to a Data Conversion Task, then the results of each end in a SQL Server Destination Object. (All using the SQL Native Client)
To eplain this further, the Xml file contains 6 different types of elements, the Dataflow splits out each type of element and processes them into different tables. The Data Transformation object exists only because the XML fields are Uni-code and the table fields are VarChar not nVarChar.
Initially using this setup I found that the Connection would timeout using the SQL Native Client so I changed the Timout on the Destination Objects to 0. This fixed the problem to some degree, however at present I can run the Pakage using the Visual Studio enviroment and everything works fine, no problem. When I run the Dtsx file using the SQL Server Agent, I end up getting the error below...
Error: 2007-12-14 14:33:19.16 Code: 0xC0202009 Source: Import XML File to SQL SQL - CP [2746] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from
I understand that this error is somewhat of a 'catch all' and that the way the Native SQL Server Connection object works makes Error Capturing difficult. I have tried a few things which I will list as I'm sure they will be suggested...
I have played around with the 'MaxInsertCommitSize' property of the SQL Server Destination Objects to no avail (IE, changing to 50000, 10000, 1000 all of which resulted in the same problem)
I am running the ssis pakage from the server which is the destination
As mentioned above the Timeout on the SQL Server Destination Objects is set to 0
What I have already mentioned and still don'tt quite understand is that I can run the job successfully from the Visual Studio enviroment but as a job run off the SQL Server it fails...
Can
View 8 Replies
View Related
Feb 14, 2006
Hi, All,
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
Thanks in Advance
View 2 Replies
View Related
Mar 9, 2007
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Thanks,
Steve
View 17 Replies
View Related
Jan 17, 2008
Dear All!
My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database.
Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any
Thanks
View 3 Replies
View Related
May 17, 2007
Hi everyone,
Primary platform is 64 bit cluster.
How to move information allocated in SSIS variables from Data Flow to Control Flow layers??
We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????
Thanks in advance and regards,
View 4 Replies
View Related
Jan 12, 2006
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
View 2 Replies
View Related
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
Jul 22, 2007
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:
A small control flow, with large data flow tasks
A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated.
Thanks,
Ricardo
View 7 Replies
View Related
Jul 23, 2007
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
View 3 Replies
View Related
Aug 29, 2007
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
View 15 Replies
View Related
Aug 8, 2007
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?
View 5 Replies
View Related
Aug 8, 2007
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?
View 1 Replies
View Related
Nov 17, 2006
Hello,
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...
View 1 Replies
View Related
May 29, 1999
What we are trying to do is watch a table in one database for an insert. If something is inserted into a unique ID column, we want to go to another database, on the same server, and look for that same number and place a value in another column for that record. Any ideas?
View 1 Replies
View Related
May 16, 2008
as asap pl give the detailed and clear description reg differences between delegates and events
vedavathi zend
View 3 Replies
View Related
Dec 16, 2005
I'd like to build an application that will react to specific changes todata in a set of tables in a database. The application would replicatethese data changes to another database. The target database won't beSQLServer. Neither is this simple replication, at times the applicationwill need to get extra data from the source database before the targetis updated.In other DBMS systems I am involved with the DBMS has the facilty towrite to an application message queue so that the monitoring applicationonly has to monitor the queue rather than a database. What I'd like todo is something like this:1. Some application changes data in a table.2. A trigger reacts to the change and writes a message to an applicationqueue.3. A windows service/process monitors the queue and picks up themessage. It then carries out whatever replication/DB actions are necessary.This would mean defining a number of new triggers on existing tables anddeveloping the windows service/process. Existing applications and theexisting tables in the database would remain unchanged. I'm not awindows programmer but I have someone in my team who is and who willbuild the windows service/process.The bit I'm unsure about is how a trigger can write to an applicationqueue or communicate with the windows service/process. I may be usingthe wrong terminology as I have more knowledge of Unix than Windows.Could anyone help with how I can do this or suggest any alternativestrategies.Thanks In Advance.Laurence
View 3 Replies
View Related