Multiple Services On Same Queue Or Not ?
Oct 11, 2006
Hi There
I am guessing defining multiple services on the same queue is basically for providing different services to outside sources while using 1 queue, obviously as long as the contracts and activated sp logic applies to all services defined on that queue.
I am defining a queue per service, is this right or wrong or irrelevant ? It just works better for me in terms of manageability.
I just want to make sure there is no "best practice" reason for doing either or ? Is it just a matter of preference?
Any comments?
Thanx
View 3 Replies
ADVERTISEMENT
Apr 26, 2007
In a situation where messages are coming in faster than they can be processed, at what point will service broker start up another queue_reader? Also, how do you prevent table locking if part of the processing of that message involves inserting or updating data in a table? We are experiencing this problem because of the high number of messages coming through, and I'm not sure what the best solution is - does service broker have some built-in support for preventing contention on a table when multiple readers are running? Or maybe a pattern that can be used to get around it?
View 1 Replies
View Related
Aug 14, 2007
Hi,
I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().
I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.
My dequeue SP is as follows:
CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END
if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END
Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View 11 Replies
View Related
Jan 17, 2007
How To Use Message Queue Task In Integration Services
View 1 Replies
View Related
Jan 11, 2006
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
View 5 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Feb 13, 2006
Hello All,
After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues.
Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.
We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?
Thank you very much,
Lubomir
P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;
View 5 Replies
View Related
Aug 31, 2007
I have 2 systems that will send data to each other. Each system will originate a particular set of messages, there will be no overlap. This scenario has transaction data going to a reporting system and management operations going back to the transactional system. It is semi-related data.
I have two patterns in mind, a combined stream of all messages or 2 streams of segregated messages.
A. A single service on each instance would originate a set of messages, process the responses, and receive the other instance's messages. The responses and original message from the other system would mix on the same Q. The activation procs would have to handle all message types. The same infrastructure (message types, contracts, Qs, activation stored procs) would be created on both systems. Although, distinct service names and ports would be used on each instance.
B. Two services and two Qs. 1 service would originate a set of messages, process the responses. The other service would process messages from the other system. The responses and original message from the other system would be on the separate Qs. There would be 2 infrastructures created. There could be separate activation stored procs.
There is just one message type and message validation is only WELL_FORMED_XML.
Which is pattern is better for management and performance? Should I create 1 service or 2 on each instance? Either way should work about as well as the other? 2 services are twice as complex to set up. Separation is not necessary, but I like the idea. 1 service will send many more messages (>10x) than the other. Any thoughts?
View 1 Replies
View Related
Sep 6, 2007
Hi
I can add a single subreport to a report using the code
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SetSubDataSource
reportViewer1.LocalReport.Refresh()
End Sub
Public Sub SetSubDataSource(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
e.DataSources.Add(New ReportDataSource("DataSet1_tblReportExport", ObjectDataSource1))
End Sub
but i cant add anymore as when i try i get the error
Error: subreport cannot be shown.
Anyone knownhow i add another subreport?
Thanks a lot for your time
View 4 Replies
View Related
Mar 25, 2008
Hi
I want to show my reports in different language . For this i have added a parameter Language on the report that contain all the language . Now i want to change all the captions of Report and parameter list in selected language .
How i will do it ?
@mit
View 7 Replies
View Related
Sep 19, 2005
I have a group of reports that are the same for 3 different companies, the difference is the data connection. Is there a way I can change the data connection based on a variable passed in at the time of report execution?
View 12 Replies
View Related
Jun 19, 2008
I have an XML document that I am trying to use within a data flow (XML Source)--the problem is that I keep getting an error message stating that the XML document has multiple namespaces and therefore, SSIS will not create the XSD for me. If I take out the second namespace, I am able to successfully get the task to execute, but this XML is created with 2 namespaces and there is no way to get around this (I cannot get the report server to change these parameters)--my question is: know a way to get SSIS to handle multiple namespaces so that I can process this XML document and extract the necessary data elements from it.
View 11 Replies
View Related
May 8, 2007
Hi!
We have multiple databases (1 per client) and we want to use reporting services for ad-hoc reporting. Our authentication is done by using client's database so security is custom.
What is the best way to set this up? Is it possible to somehow allow users/admins to use only their database and never see any others? I know we can install a separate instance of RS and do that but I am wondering if there is a better way to run multiple clients doing different reports using exclusively their databases without having to create a new instance.
Thanks
View 13 Replies
View Related
Oct 31, 2007
Hi everyone, I'm very new to SQL and SQL Reporting Services.
However I've been given a task.
How do I save multiple reporting services reports onto a predetermined location on a users local pc? Are there tools/applications that can be used to push files onto the users local pc?
The application uses .NET but they've created reports using SQL Reporting Services. The user will select which report they want (out of 2). My task is to figure out how can we save those reports that they have selected onto their pc?
Thanks in advance
View 1 Replies
View Related
Jul 8, 2015
I have a table with the following fields and data:
Offense, Year, Month, Count, Amount
F 2007 January 11 $49,238.00
F 2007 February 12 $24,000.00
F 2007 March 31 $55,184.00
F 2007 April 19 $64,647.00
F 2007 May 33 $100,010.00
F 2007 June 16 $59,678.00
F 2007 July 22 $39,700.00
F 2007 August 3 $9,000.00
F 2007 September 4 $75,000.00
F 2007 October 8 $19,250.00
F 2007 November 50 $106,153.00
F 2007 December 26 $80,000.00
I have data for years 2007-2014. I am designing an SSRS report, and want to use IIF statement to return data. Using the following:
=IIF(Fields!Year.Value="2007" AND Fields!Month.Value="February", "return value of count field", "n/a")
The above does not work, it doesn't return the value of Count field, it does return the n/a.
View 14 Replies
View Related
Oct 26, 2015
1. SQL 2008 R2
2. Maintenance Plan -
a. Daily, Weekly, Monthly schedule
b. Full Back Up - All Database, To Disk, for every database and Disk:DirectorySubDirectory*.bak Compress backup
3. SQL Agent Job
a. Runs the Sub_Plan's at scheduled time I've allocated for each Maintenance Plan.
4. These creates Multiple .bak for all database's that list in the directory.
How can I zip them into a compressed Directory.
View 13 Replies
View Related
Oct 3, 2012
I'm building a report that has a column in the table called countries of origin, that column can contain data like "USA","China" ( normal countries) but some rows can also contain "USA, China, Australia" ( multiple counties in one row).
I'm trying to build a filter to filter my results :
-I have a data set created with a list of all world countries
-I've Build a Parameter containing a drop down list with all countries
--- My problem is when I want to create the filter in my main data set , I can so it , but when I search for example for "USA" , I don't get the lines where there are multiple countries and "USA" is one of them...
There must be an expression in the filter that will be able to search through all the values in the string and return me my correct results.
View 19 Replies
View Related
Jul 23, 2015
I'm using Reporting Services 2008 R2, version 10.50.2550.0, and Visual Studio 2008, version 9.0.30729.4462 QFE. Not sure if the SQL version matters, since I haven't gotten the report out of the development environment.I've read lots of messages, including URL.... that show you can have multiple data entries per row in a Range Bar Chart, simply by turning DrawSideBySide to False.My data set basically contains four pieces of data, LineNumber, Equipment, StartTime and EndTime. I have data like:
1, 'CLM1', 08:00, 08:30
1, 'CLM1', 09:00, 09:15
2, 'SS1', 08:05, 08:35
2, 'SS1', 09:05, 09:35
(I actually have a date with my datetime entries, but that should give you the idea.) My chart is set up with Values of EndTime and StartTime. If I set my chart up with only Equipment in the Category Groups entry, my chart will show CLM1 from 08:00-08:30, and SS1 from 08:05 to 08:35. No sign of the other entries per equipment, although I added a tablix to retrieve the same data and it all shows up.Changing DrawSideBySide between True, False and Auto has no effect on the data displayed on my chart. If I add StartTime in the Category Groups, I do show all the data, but one row for each piece of data, which isn't what the customer wants.From what I can tell from various sites, I'm set up right, but it just isn't working.
View 2 Replies
View Related
Mar 31, 2009
I have a situation where i need to unpivot multiple columns using ssis. The data looks like
Name Age products1 products2 orders1 orders2
abc 23 cycle radio 12 24
as
Name Age Products orders
abc 23 cycle 12
abc 23 radio 24
Is it possible to do this using the unpivot task in ssisMy actual data is has 18 columns which needed to be unpivoted into one and another 18 into another one.when using unpivot task it gives an error saying only one pivotvalue key is allowed.
View 6 Replies
View Related
Jul 16, 2015
I need to create area chart using SSAS cube which will be having :
sum(A) as per the Series group i.e Projects (expression used is Sum(Fields!Demand_FTE.Value))
Sum(B) as per the CategoryGroup1 =Sum(Fields!Demand_FTE.Value,"CategoryGroup1") where categoryGroup1 is calculated date
And Sum(C) = sum(Lookup(Fields!Time_FY.Value,Fields!Time_FY.Value,Fields!Capacity_FTE.Value, "DataSet2")).
I want Sum(C) to be calculated on Fields!Time_FY.Value (which is CategoryGroup2)
Sum(C) do not have any links with CategoryGroup1 and that is why my graph is synchronized.
Sum(A and B) belongs to Dataset1 and SUM(C) belongs to Dataset2
View 2 Replies
View Related
Sep 8, 2015
I have a SSRS report with a Parameter that lets you choose the name of an employee. The report lists their Name, Number, User ID etc.
When you select a single employee, the filter on the Dataset works perfectly. However once you select two or more employee names, it only returns the first result.
The parameter, Field, and Filter are text.
The Filter is EmployeeName IN @EmployeeName
View 8 Replies
View Related
Jul 18, 2014
SSRS report background :
Report generating 50 student details with 50 page (each student one page- group by student).
now data will export one pdf file with 50 page where each page having one student details.
Requirement :
instead of one single pdf with 50 page i want 50 pdf file so i can send each report to respective student.
View 8 Replies
View Related
Jul 5, 2007
Hi all,
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
Thank you,
View 2 Replies
View Related
May 20, 2015
I'm looking to build a report that is basically a series of line charts that is plotting a value over time. I can accomplish this one chart/dataset at a time, but I believe with 'List's this can be achieved in a more automated fashion.If I have some data similar to this:
Area_ID, Location_Name, DataValue, ReadingDate
1,Site1,100,1990-10-10
1,Site1,110,1990-11-11
1,Site2,105,1994-01-07
1,Site2,105,1994-02-07
2,Site3,113,1994-06-02
2,Site3,120,1994-07-30
2,Site4,120,1994-06-30
2,Site4,120,1994-07-30
I want the charts grouped by the Area_ID, and then each Location_Name is a Series in that chart. So in this example, I would end up with 2 charts (Areas 1 & 2), each have 2 series (Area 1: Site1, Site2 and Area 2: Site3, Site4)Is it possible to build this with a list? or do I need to structure the data differently?
View 6 Replies
View Related
Oct 14, 2008
I'm pretty new to SSIS but I've managed to cobble together a number of individual packages to refresh SQL tables from a 3rd-party database.
Now, what I'd like to do is have a single package that I can use to invoke each of the individual ones. Since it will run on a quad, I'd like to invoke them such they'll run in parallel.
View 2 Replies
View Related
Jun 1, 2015
I have one matrix with multiple row & column groups which is not looking nice.
See the below image. The reason for having a Matrix is they can create any no of Tests.
In the above image i have two row groups Test Type and Test Date and column groups as TestDesc and my required format is .
View 2 Replies
View Related
May 15, 2015
Using a single multi select parameter I want to show/hide 5 tablix's I have in my SSRS 2008 report. Inside the parameter I want to give each tablix a value a have user control which tablix he wants to see. If user selects all 5 he should be able to see all 5 or if he selects only 4 then display only the 4 tablix's user selected or select's only 3 then display only 3 so on so forth.
How to configure the parameter and hidden expression of the tablix.
View 3 Replies
View Related
Sep 21, 2015
I have a sequence container in my Package and this sequence has more than one control flow tasks.
Can I create the checkpoints such that only the failed component inside the sequence container runs again and not the other successful components/tasks in the sequence container?
View 3 Replies
View Related
Sep 3, 2015
Is there any possible way the i sum the "Grand TTL Cases" by month? means only 1 cell in each month for "Grand TTL Cases"(25.56 + 1334.22)
View 3 Replies
View Related
Dec 3, 2015
I have 5-6 table and wanna to make sure if I'm using any of them in my Packages and I do have 100 Packages.shortest way to search these tables in Packages.
View 2 Replies
View Related
