Distributed App, Technology Question

Hi Everyone.

I am trying to get ideas for a possible technology we could use for a certain scenario.

In a nutshell we want to run a distributed application at hundred of remote stores, anything that happens at the store must be propagated to a central sort of hub server.

Now i have already thought about replication, but maintaining replcation across hundreds or even a thousand remote instances would be a nightmare.Also the App must work offline so if no link exists the app must still work and sort of reliably queue the transactions.

So i been experimenting with service broker, to write transactions, that then queue on to service broker to the central hub, therefore enabling remote stores to continue to work even if no comms exist between the remote app and the hub.

But also maintaining service broker at hundred of remote sites would be an administrative nightmare.

So basically i am trying to pick your brains out there, can anyone reccomend a possible technology out there worth looking at for this solution , the simpler the better, even if it simply pushes database somewhere , must it must be reliable and queue like in case comms are down.

Also perhaps there is some sort of .NET technology i am not aware of, that can run the .NET app at the store and the app will try propagate the transaction up to the hub, and will automatically queue them. I know am i grabbing at straws , but perhaps there is some sort of technology that i dont know of as a dba that could be great for this scenario ?

Any reccomendations just to look into would be greatly appreciated.

Thanx

Since this is an archtiecture question here is my "2"...

You could in theory leverage .net to stream records in a distributed transaction (via DTC) but I dont really see how this would be any simpler in terms of administration. Replication & service broker are actually excellent candidates for this solution and would be the two I would look at the most. The other issue here is latency, how long can it take for the records to be merged together? Service Broker I know is a very "fast" technology. I am not a BizTalk expert but you may want to consider it as well.

Derek

|||

You could also look at MSMQ, which in .Net is implemented in the System.Messaging namespace. What follows is a short description of the namespace via Object Explorer in VS2005:

The System.Messaging namespace provides classes that allow you to connect to, monitor, and administer message queues on the network and send, receive, or peek messages.

|||

Thanx Guys

As far as i know MSMQ is kinda being replaced by Service Broker, but that may purely be from a DB perspective not .NET.

Looks like Service Broker is the better option then, replication is not an option due to the offline capabilities required by this app, service broker ensures that all transaction can be queue and deleivered later, replication cannot garantee this as a connection the the distributor and susncibers is obviously required.

However i am concerned about the amount of setup and adminsitration that would be required at the hundred of remote instances to enable and maintain service broker.

Thanx

|||

Service Broker does not replace MSMQ, it is as you said it is merely a similiar option now available in the database. MSMQ and Service Broker are not the same in terms of their pros/cons. I built a real-time ETL system based upon Service Broker about 18 months ago now. Yukon had just entered Beta 2 whenever that was...Anyway, I ended up making friends with Roger Wolter, Service Broker PM and he kinda coached me though my leading the engieering effort with the new technology. http://blogs.msdn.com/rogerwolterblog/archive/2006/02/28/540803.aspx go there to read Roger's quick comparisons between the technologies.

As far as minimizing dev/admin time, you could build everything the same way as I did with the real time ETL system. For example, we always had an "in" queue, "out" queue, "failed" queue, the activation procs had the same structure etc...This way only the logic specific to the individual service is unique.

HTH,

Derek

|||

Hi Derek

Thank you for the feedback.

I would be very interested to discuss and idea with you if you have the time. Sounds like you know a lot about service broker, not many do. I have been experimenting with service broker in an idea based on a realtime tranactional retail system using service broker, primarily so that if comms went down the store could continue to transact even though communication the central loan system was not possible therefore the transaction would be delivered by service broker as soon as the comms were up again. Service broker would always be used to send the transactions realtime.

My biggest concern is the initial setup of hundreds of remote sites with endpoints to a single central hub. I am concerned about the robustness and high avalibilty of servcie broker, by that i mean i would hate to be faced everyday with the prospect of having to manually check x amount of remote sites and troubleshoot service broker issues at each remote instance. Also i am not sure if service broker architecture, in terms of scalabiltiy, is more of a db server to db server design, or like i said would a central hub single endpoint with hundreds of remote site initiator endpoints, be perfectly feasable?

In a nutshell i am going to try do data replication from remote sites via service broker to a central loan environment.

However if i could give you my email address or vica versa i would love to shoot the idea past you, basically i would just like a yes this could work or no this is not going to work for you viewpoint, it has been very difficult to find someone who knows enough about servoce broker to really comment.

Thanx

|||

sure, just go to my consulting site (i wont charge u lol) and select contact. www.sqlserverdbas.com. We can compare contrast and I may try to enlist Roger's input if he has the time.

|||Please mark a reply as an answer too. Service Broker is in my opinion the best new feature of sql 2005 for devs, SQLCLR or not.|||

Sounds like you have the basic issues identified. One that you may not have considered is the network connection from the remote sites. Service Broker, distributed transactions both require a TCP/IP connection which may or may not be possible with your network configuration. MSMQ normally uses TCP/IP also but it has an optional SOAP protocol. Replication can use HTTP in some circumstances also.

I agree the Service Broker is probably your best alternative if you can get it to work with your network configuration. You will probably want to look at using forwarding to set up some concentrator machines so you don't have the overhead of hundreds or thousands of TCP/IP connections into your central server. You also want to be sure to test with the predicted volume of replication messages to ensure your server can handle the load. Dialog lifetime management can also be an issue. Most of the samples floating around use one dialog for each message. While this makes for a simple sample, it may not be the most efficient way to handle things. You may want each remote system to open a limited number of permanent dialogs and reuse them instead of opening and closing dialogs continuously. There's a discussion of this issue here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

For more information, try these blogs:

http://blogs.msdn.com/remusrusanu/

http://rushi.desai.name/Blog/tabid/54/BlogID/1/Default.aspx

http://blogs.msdn.com/rogerwolterblog/default.aspx

And this book:

http://www.amazon.com/gp/product/1932577270/sr=8-1/qid=1151000196/ref=pd_bbs_1/002-4995511-1360840?redirect=true&%5Fencoding=UTF8

|||

There you go dude, straight from the man himself :) If you still want to discuss more thats cool, just contact me. And by the way, if do decide to implement SB, buy that book. BOL is good too, but Roger's book is "it".

"D"

|||

Thanx Roger

I have already ordered that book, just waiting for it to arrive, yes the network is a major concern for me, however that is also why i am gonna try a few thigns out with SB, as i am not very familiar with messenging and queueing etc (I am more of a Database engine person) i am have ALOT of proof of concept to do. However my general understanding is that when the network is down , the initiator endpoints can still put things on the queue , i hope the central endpoint being "up" is not a must ?

Thanx again, after i have played around with some idea i may post back.

Thanx

|||Yes, when the connection is not available, sent messages will accumulate in the sys.transmission_queue until the connection is available again. Be sure to set your lifetime in the BEGIN DIALOG statement to a long enough value to last through network outages or just don't specify a lifetime and dialogs will last forever.

DISTINCT only certain columns in on table and pull their IDs

Please help.? (I am using ASP, VB, SQL)

I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

So for example I might have?

ID 1 - Office 1 - Chicago

ID 2 - Building 2 - Chicago

So I want to show

Office 1 - Chicago (my link will contain the ID in it)

So far I have

SELECT DISTINCT City , State FROM dbo.Offices WHERE (City IS NOT NULL) AND name like 'office%' ORDER BY City ASC

This works as far as pulling unique cities and names but now I need to get those City's ID. Can I use DISTICT for certain columns? Or how do I do it so that when it does pull out the unique cities it pulls their IDs as well?

?? Select ID and (DISTINCT City , State) FROM dbo.Offices ??

Thank you.

sscoder,

are you saying that the cities id is in another table. And are you just looking for the set of distinct city, state, id 's? is so just add id into your select statement

select distinct ID, City, State FROM Offices

if this is not what you are asking, can you include a bit more info...-- jp

|||City, State, ID are in the same table.

I want to find unqiue cities and then their IDs.?I'm using the ID in the hyperlink for a string.?

If I add ID to the distinct query?it will take priority and it will not list out unique cities. So instead of displaying

Chicago
Florida
Las Vegas

It will display this if I add ID to distinct
Chicago
Chicago
Chicago
Florida
Florida
Las Vegas

I need unique cities and then their ids.? Is there a way?

Thank you.

|||i see what you are saying but it you want the distinct city and a city may have more than 1 id, then how do you know which id you want to be returned?|||would only one of the city records be of type 'office'?|||there are many cities but each office has a different name. The every main office name start with 'Office' so I only want to pull up unique cities with name starting with 'Office%'. So there will never be two same cities. Only one office per city. That's where I put "WHERE name like 'office%' "

So the list will be:

Chicago - Office 1

Florida - Office 2

Las Vegas - Office 3

The query does this already. I just can't get the ID for that one row. That's what I need help on. If that "Office 1" in "Chicago" has an ID of 2. I want to get that. How??

Displaying only a fixed number of Columns in Matrix

Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).

In other words.....

I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.

Thanks.

I did something like this. If you know that 6 columns will fill the width then you can return the (total amount of known columns / 6) as a page count.

Use the page count variable to hide/unhide a set of labels on top of the report. When a page label is clicked call the same report with the selected page as a parameter.

This will work if you can segment the result set based on a your page range in the database.

|||

Hmmm... That may not be exactly what I am looking for. Ideally, it would be nice if one can go into Group->Edit Column Group and set some property such as "# of Columns to Display for this Column Group" and behind the scenes it didn't care whether or not you bring in 6 columns from the DB or 600.

I hope there is a simpler solution or workaround to this.

|||Any body plz.. any ideas how to achieve this either in sql or through matrix formatting?

Displaying only a fixed number for Columns in Matrix

Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).

In other words.....

I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.

Thanks.

I did something like this. If you know that 6 columns will fill the width then you can return the (total amount of known columns / 6) as a page count.

Use the page count variable to hide/unhide a set of labels on top of the report. When a page label is clicked call the same report with the selected page as a parameter.

This will work if you can segment the result set based on a your page range in the database.

|||

Hmmm... That may not be exactly what I am looking for. Ideally, it would be nice if one can go into Group->Edit Column Group and set some property such as "# of Columns to Display for this Column Group" and behind the scenes it didn't care whether or not you bring in 6 columns from the DB or 600.

I hope there is a simpler solution or workaround to this.

|||Any body plz.. any ideas how to achieve this either in sql or through matrix formatting?

Displaying data from a certain date range

Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??

My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0|||Okay, now whats up with this DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

What am I doing with my Date1.text and Date2.text input boxes.. sorry, I'm having trouble converting the code some.|||That's TSQL

The DECALRE and SELECT is to mimic the values in your text box...

Do you have sql server client tools installed?

Query Analyzer?

Your best bet would be to call a stored procedure...|||Yes, I have those tools installed, but I really don't know how to use them to my benefit.... :(|||Puttin this code in the Query analyzer shows the DATEDIFF in pink, and some of the code after that grey... I'm guessing something else needs to be used here?|||Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0

Brett, I am having a similar problem...can't seem to use datetime parameters with default values in a stored procedure.

This is the code I used. Basically, trying to default dates on the Begin and End Dates for the query...but when I run it, always get this error
'Syntax error converting datetime from character string.'

Is the problem in the default values or the datediff functions? Then is the solution to format the dates differently or use a cast/convert function?

Thanks for the help

Alex

CREATE PROCEDURE dbo.usp_TempTest
@.BeginDate DateTime = '11/1/2004',
@.EndDate DateTime = getdate

AS

SELECT dbo.vw_BasicAuditDetails_Complete.*
FROM dbo.vw_BasicAuditDetails_Complete
WHERE (DATEDIFF(d, @.BeginDate, Audit_TM) > 0)
AND (DATEDIFF(d, Audit_TM, @.EndDate) > 0)
GO|||Has anyone cut and pasted my code in to query analyzer and executed it?|||That's what I was trying to do earlier.... :confused: I'm not a pro with the analyzer though..|||Well, if you highlighted the text, and pasted it in to a QA (Query Analyzer) window, and the typed [CTRL]+E, the code would execut and give you this

OrderDate
---------------- ---- ----
1996-09-02 00:00:00.000 1 28
1996-09-03 00:00:00.000 2 27
1996-09-04 00:00:00.000 3 26
1996-09-05 00:00:00.000 4 25
1996-09-06 00:00:00.000 5 24
1996-09-09 00:00:00.000 8 21
1996-09-09 00:00:00.000 8 21
1996-09-10 00:00:00.000 9 20
1996-09-11 00:00:00.000 10 19
1996-09-12 00:00:00.000 11 18
1996-09-13 00:00:00.000 12 17
1996-09-16 00:00:00.000 15 14
1996-09-17 00:00:00.000 16 13
1996-09-18 00:00:00.000 17 12
1996-09-19 00:00:00.000 18 11
1996-09-20 00:00:00.000 19 10
1996-09-20 00:00:00.000 19 10
1996-09-23 00:00:00.000 22 7
1996-09-24 00:00:00.000 23 6
1996-09-25 00:00:00.000 24 5
1996-09-26 00:00:00.000 25 4
1996-09-27 00:00:00.000 26 3
1996-09-30 00:00:00.000 29 0

Which is the range of data from the sample database Northwinds Tables Orders...The USE Statement should have brought you there.

Isn't that what you want?|||OK Ok, thanks! It is working now in Northwind... :)

Some suggested I use a Stored Procedure for doing this...

""You'll want to create a Stored Procedure in the SQL Server and then pass it's result into a DataSet to use a the Source for your DataGrid.

Try a Stored Procedure like this, Changing table and field names appropriately:
Code:
------------------------

CREATE PROCEDURE sp_BetweenDates ( @.StartDate DATETIME, @.EndDate DATETIME ) AS SELECT * FROM YOURTABLENAMEHERE WHERE [YOURDATEFIELDNAMEHERE] Between @.StartDate And @.EndDate;GO
------------------------

Then you would just pass the two dates as parameters to the Stored Procedure.""

What do you think of something like this?|||Okay, i have contrusted this query that gives me what I want...

USE billing1SQL2
GO

SELECT Hours.Employ#, Hours.Purchord, Hours.Datewrk, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, Employee.Lastname, Employee.Firstname, Employee.[Employ#] AS Expr1, Hours.[Ticket#], PurchaseOrder.Purchord AS Expr2 FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.[Employ#] = Employee.[Employ#] WHERE Hours.Datewrk between '6/15/2004' And '6/28/2004' ORDER BY Hours.Datewrk, Employee.Lastname

I just need to figure out how to use it with VB.Net the best way. Suggestions?|||Long answer:
I would start from:
System.Data.SqlClient Namespace (help is available with VB.Net)
Then I would take a class or read a book about T-SQL
Same thing for SQL server.

Short answer:
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, Customer FROM Orders"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Try
While myReader.Read()
Console.WriteLine((myReader.GetInt32(0).ToString & ", " & myReader.GetString(1)))
End While
Finally
' always call Close when done reading.
myReader.Close()
' always call Close when done reading.
myConnection.Close()
End Try
End Sub 'ReadMyData
(source: VB.Net help)

good luck!|||In your VB code, you can create your query like this

dim str1 as string

str1 = "exec sp_between_dates '" & Format(Text1.text, "mm/dd/yy") & _
"','" & Format(Text2.text, "mm/dd/yy") & "'"

Roshmi Choudhury|||Okay, thank you! soo I have this stored procedure:

CREATE PROCEDURE sp_BetweenDates (@.StartDate DATETIME, @.EndDate DATETIME)
AS
SELECT *
FROM Hours WHERE [DateWrk] Between @.StartDate And @.EndDate;
GO

..and this in VB.net...

Dim str1 As String

str1 = "exec sp_BetweenDates '" & Format(txtDate1.Text, "mm/dd/yy") & _
"','" & Format(txtDate2.Text, "mm/dd/yy") & "'"

Do I need to put my txtDates.text into a variable/string "StartDate" and "EndDate" and put that in my str call, like this... ??

str1 = "exec sp_BetweenDates '" & Format(StartDate, "mm/dd/yy")

I'm just unclear how the best way to get the actual date from my text box, to the stored procedure works...

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Display empty rows

I am creating some reports with crystal reports 8.5

I made crosstabs listing the sales of certain products by store and month.

The stores are listed in the rows, products and months are in the columns. In the complete yearly reports everything is fine, because there are no empty rows.

When i create subreports for just a certain month or product, there might be empty rows, like, there might have been no sales of that particular product, in that store in that month. Instead of just displaying a row with the store name and Zero value, it just doesn`t show the row at all.

I need those empty rows to show and display zero values. The option "suppress empty rows" is not checked.

Any ideas ?

Example
...............January 2007
...............Product 1
Store1......0 <- this row is not displayed
Store2......6
Store3......24You'll need an outer join somewhere, rather than an inner join - read the help on join types, or describe your current tables and joins.|||I am just beginning in crystal but I did have the same issue but my training yesterday was about table joints.
You have to have your primary table as your "source" table where you have most of your data. When you link your secondary table, go to link options and you need to have the linking as "left join or outter join ...not Equal". Even though you have no value on that one record, it will show it with an empty cell or box but you will see everything else from that record. I still wouldn't know how to display the zero.