Storing A List In A Database Table (messaging Queue?)
Feb 14, 2007
hey all you database guru's hopefully someone can lend some insight as to a little table design problem I have.
Basically I've got a system in place to authorize users to access a website typical username password stuff. The table contains a list of users and there passwords plus the auth level and a few other tid bits that aren't really important enough to into detail about them here. What I want to do is add a messaging system to this, I think I could probably figure out a way to do this half decent if I setup a seperate table for each user to add a row to the table for every message entry than in my asp.net code have it delete everything but the last 10 entries every time a user logs on. However I would much prefer a way that I didn't have to setup a whole new table for each user just for messaging purposes, maybe store something like a list in one of the database cell's kind of like .nets generic.list or better yet generic.queue, I would also like a way if it's possible without too much work to have the table automatically delete the oldest message every time a new message is received if there's already 10 messages existing for the user.
Anyways hopefully someone has some experience in setting up a system like this, I don't really require any code samples I can code it all myself (other than the database code to automatically remove entry's, I'm not a database guy) if someone could just explain a way to accomplish what I'm trying to do, or if someone has a different more convenient way of doing this I would be up for suggestions
Thanks in advance for any help offered, I do appreciate it
View 3 Replies
ADVERTISEMENT
Jul 21, 2015
I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.
I started to build the table which looked like this:
Id (PK) (Increments by 1)
ToUserId (FK) -- User who they're getting in contact with
FromUserId (FK) -- User who sent the message
Content (nvarchar(3000)) -- Message being send
Status (int) -- read / new / deleted / sent
EmailDate (datetime)
EmailDeleted (datetime)
But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control.
View 9 Replies
View Related
Jan 2, 2008
Hi,
I want to Export a List of the Public Queues using the command prompt. Can this be done. I don't want to always click the Export List button. I eventually want to automate the Export List so I can create an Environment HealthCheck.
Thanks
View 1 Replies
View Related
Dec 29, 2006
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View 6 Replies
View Related
Apr 11, 2012
How can I store multiple selected values (from a dropdown list) in mysql database?
PHP Code:
<form method="post" action="storedetails.php">    Research Interest:<br/>   Â
<select multiple="yes" size="6" name="interest[]">   Â
<option id="webt" value="webt">Advanced Web Tech1nologies</option>   Â
<option id="mobhum" value="mobhum">Mobile and Humanoid Robots</option>   Â
[code]...
View 5 Replies
View Related
Sep 10, 2007
Is there a system table that holds the table definitions, names, properties, etc. for tables that I have designed? I would like to write a transact SQL query to insert a field in every table that I have defined.
View 1 Replies
View Related
Apr 1, 2006
Please how can I get list of tables in a database? Thanks.
View 3 Replies
View Related
Dec 16, 2006
Hi,How to get list of all relations of certein database's table?
View 7 Replies
View Related
Aug 21, 2000
We are listing available SQL databases for selection in a VB list box, but need to be able to then elicit the tables in the database for another list. I am sure it is something simple, could someone help me out with this? Thanks!!
Laura
View 1 Replies
View Related
Jun 9, 2006
I use this macro to list in A column of sheet the name of sql server on
my lan.
Is possible to "scan server by server" and insert into column B the
name of database and related table?
Tks.
Sub test_sql()
Dim TEST As String
Dim RIGA As String
Set sqlApp = CreateObject("SQLDMO.Application")
Set serverList = sqlApp.ListAvailableSQLServers
numServers = serverList.Count
RIGA = 2
For I = 1 To numServers
TEST = serverList(I)
Range("A" + RIGA) = TEST
RIGA = RIGA + 1
Next
Set sqlApp = Nothing
End Sub
View 2 Replies
View Related
Aug 30, 2006
Is anyone know about waht type of table or database name
that not supported or will have error during execution of SQL statement
in SQL or MS Access database. For my, I know only two ,
i.) cannot put table name that same as the the database name
ii.) cannot put database name like "RETURN", "POSITION",etc.
Is there any reference site that show out a list of table about the
table or database name ??? Thank You ... :)
View 5 Replies
View Related
Aug 31, 2005
How to get the list of table names in a SQL2005 database?
View 6 Replies
View Related
Aug 3, 2007
Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?
Thanks
View 3 Replies
View Related
May 5, 2004
Hi there,
I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB
E.g
Table1 1111KB
Table2 123300MB
Table3 120448KB
etc for all the tables in a particukar DB
I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.
can anyone help please!!
From
NewToSQL
View 12 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
Nov 22, 2002
I have a table that I want to act as a queue.
It has no indexes and no key. Just one column.
Basically I want a stored procedure that will pull / return the first record off the queue (table) and delete it. I'd rather not use MSMQ for this.
There will be about 10 users trying to do this at the same time and will be trying to pull of about 15 times every second.
How can I do this and ensure that no two requests pull off the same row?
Thanks,
Kevin
View 1 Replies
View Related
Oct 23, 2000
Need Some Help. I'm new to SQL Server 7.0., and I'm wondering if the application can do what I need it to do. I've got a stand alone third party program that updates a SQL 7.0 DB, and what I need is: When an update happens, is there any way to 'let another program know' that an update has occured and what the data is? I would hate to have to write an SQL clause to constantly 'poll' the DB looking for an update, this seems silly. Simply I need some way to know if table X has been updated, and what was the update? Any help would be greatly apprec... KT
View 1 Replies
View Related
Oct 23, 2000
Trigger seems to work fine, however, the Online Books state you can find out what data was actually modified when the trigger fired, but I can't seem to find out how to do it. Any Ideas Apprec.. Thanks.. KT
View 1 Replies
View Related
Jul 20, 2005
I am trying to implement a very fast queue using SQL Server.The queue table will contain tens of millions of records.The problem I have is the more records completed, the the slower itgets. I don't want to remove data from the queue because I use thesame table to store results. The queue handles concurrent requests.The status field will contain the following values:0 = Waiting1 = Started2 = FinishedAny help would be greatly appreciated.Here is a simplified script to demonstrate what has been done.CREATE TABLE [dbo].[Queue] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[JobID] [int] NOT NULL ,[Status] [tinyint] NOT NULL) ON [PRIMARY]GOCREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]GOCREATE PROCEDURE dbo.NextItem@JobID integer,@ID integer outputASSELECT TOP 1 @ID = [ID]FROM Queue WITH (READPAST, XLOCK)WHERE (Status = 0) AND (JobID = @JobID)RETURNGO
View 6 Replies
View Related
Jul 26, 2006
I know we are not allowed to benchmark SQL Server but..... It would be nice to have material to present which demonstrates the performance gains using a queue compared to insert/delete in a SQL table.
Logically it seems faster to use a queue due to the conversation grouping locking and the service broker itself. But there seems to be some overhead involved just to manage these queues that the service broker has to perform.
I am sure we are not unique with the choice to figure out if we will get a boost in performance using SQL a queue between services rather than a table to queue data. What is available to help understand the performance gains of using a queue?
View 2 Replies
View Related
Jul 10, 2015
I'm trying to flesh out a good queue table design with our dev team.So here is a general overview of the scenario. First an application will hit a WebAPI and grab any updates to Content and store those ID's in SQL (queue table). Next is the fun part, different multi threaded apps will process ID's from the queue. One app will make updates to the data in a different SQL DB while the other will update an index (likely Elastic).
Obviously, we don't want multiple threads working on the same items. One strategy could be to use UPDLOCK & READPAST query hints. However, I'm not sure about the reliability or performance of this solution. I just started looking into setting up a service broker but that would be completely unfamiliar territory for me. Also I can see how a broker might work well within the instance but how would that work with the application making updates to Elastic?
View 5 Replies
View Related
Mar 3, 2005
Hello All,
Does anyone know if it is possible to send a text message to a phone number via a DTS package or job within SQL Server. For instance, if a job fails a text message is sent to a user.
Thanks, Gary
View 2 Replies
View Related
Dec 2, 2006
Hi All, i am trying to host the system.messaging.dll in SQL Server. I get the usual warnings about not being fully trusted. I am prepared for MS not supporting. But, when i execute my trigger (which writes to message queue), the trigger fails with an exception of 'That assembly does not allow partially trusted callers'. So. is there no way for me to write to MSMQ using triggers? Do i have to rethink my strategy?
imports System.Messaging
Dim msmq As MessageQueue = MessageQueue.Create(".TestQueue", False)
msmq.Send("Test")
Thanks for the help
View 5 Replies
View Related
Dec 22, 2000
Hi
I have Outlook 2000 and SQL 7 .. do I need Window NT Messaging and Exchange Server to configure a profile for the mail to work ???
thanks in advance for the answer
View 2 Replies
View Related
Jul 20, 2005
I need to develop an internal messaging sub-system that is similar toa web mail application but without SMTP support (e.g message routesare confined to the webapp domain). The requirements are rathersimple: Each user (e.g mailbox) can view incoming messages and hisoutgoing messages. Message quota is defined as the sum of all incomingand outgoing messages per userand tracked in the users' row (Users table – log_TotalMessages). Thequota is enforced by the business logic layer and not by the DB.I am considering the following data model for the storage component,and would appreciate community feedback:Table layout for incoming and outgoing messages************************************************CREATE TABLE [dbo].[Messages] ([MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID[RecipientID] [int] NOT NULL , // The userid ('Users'Table)[SenderID] [int] NOT NULL , // The userid ('Users'Table)[GroupID] [uniqueidentifier] NULL , // Only assigned if theuser "replyed" to an incoming message[SubmitDate] [smalldatetime] NOT NULL , // the date of themessage[DeleteBySender] [bit] NOT NULL , // Since I want to maintain onlyone copy of each message I mark a message "to be deleted" and deleteonly if both are true.[DeleteByRecipient] [bit] NOT NULL ,[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unreadmessages[Subject] [tinyint] NOT NULL , // Subject is derived from a fixedlist[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL) ON [PRIMARY]CREATE INDEX [Messages_RecipientID_IDX] ON[dbo].[Messages]([RecipientID]) ON [PRIMARY]CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])ON [PRIMARY]/* Send Message */CREATE PROCEDURE SendMessage (@IN_RecipientID int,@IN_SenderID int,@IN_GroupID uniqueidentifier,@IN_Subject tinyint,@IN_MessageText varchar(2000),@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION SendMessageTransINSERT INTO Messages(RecipientID,SenderID,GroupID,SubmitDate,Subject,MessageText)VALUES (@IN_RecipientID,@IN_SenderID,@IN_GroupID,GETDate(),@IN_Subject,@IN_MessageText)UPDATE UsersSET log_NumberOfNewMessages = log_NumberOfNewMessages + 1WHERE usr_AccountNo = @IN_RecipientIDUPDATE UsersSET log_TotalMessages = log_TotalMessages + 1WHERE usr_AccountNo = @IN_SenderIDSAVE TRANSACTION SendMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION SendMessageTransENDELSEBEGINCOMMIT TRANSACTION SendMessageTransEND/* ReadMessage */CREATE PROCEDURE ReadMessage (@IN_MessageID int,@IN_RecipientID int,@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION ReadMessageTransSELECT MessageText FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_NumberOfNewMessages =log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientIDSAVE TRANSACTION ReadMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION ReadMessageTransENDELSEBEGINCOMMIT TRANSACTION ReadMessageTransEND/* Delete Message */CREATE PROCEDURE DeleteMessage (@IN_MessageID int,@IN_DeleteIncomingMessage bit,@IN_DeleteOutgoingMessage bit,@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION DeleteMessageTransDECLARE @Recipient intDECLARE @Sender intSET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =@IN_MessageID)SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =@IN_MessageID)IF (@IN_DeleteIncomingMessage = 1)BEGINIF((SELECT DeleteBySender FROM Messages WHERE MessageID =@IN_MessageID) = 1)BEGINDELETE FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @RecipientENDELSEBEGINUPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @RecipientENDENDIF (@IN_DeleteOutgoingMessage = 1)BEGINIF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =@IN_MessageID) = 1)BEGINDELETE FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @SenderENDELSEBEGINUPDATE Messages SET DeleteBySender = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @SenderENDENDSAVE TRANSACTION DeleteMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION DeleteMessageTransENDELSEBEGINCOMMIT TRANSACTION DeleteMessageTransEND/* ListIncomingMessages */CREATE PROCEDURE ListIncomingMessages (@IN_RecipientID int)ASSELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID= @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC/* ListOutgoingMessages */CREATE PROCEDURE ListOutgoingMessages (@IN_SenderID int)ASSELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID= @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESCThanks in advance!-Itai
View 4 Replies
View Related
Apr 7, 2004
Hi all
We are writing a web-based multi-user call centre application application.
we are getting concurrency problems as you would expect with a multiuser application.
the application is made for callers who will bring up a different contact to call based on some predefined priority. now because the algorithm that prioritises the contacts takes a good 2 seconds to run, if 2 different caller request for the next prioritised contact, they will retrieve the same contact.
The only way that we think can resolve this problem is by building a queue. The queue would be implemented as a table, the particular implementation of this queue would be, when ever someone retrieves an entry from the queue, a background process will go on and generate a new queued item, i.e. in a FIFO manner. So that's how we think we should implement the queue.
Now come the question how to implement it. My idea is to have row level locking and a trigger to remove queue items from the queue. so that once one caller have looked at one of the item in the queue, another user can't look at the same item.
Any suggestions as to how i might be able to avoid concurrency problems?
What do you all think of my idea of implementing the FIFO queue?/
Is it possible to do row level locking in such a way that other users won't even be able to read the locked entry??
James :)
View 2 Replies
View Related
Jun 18, 2015
Trying to create a report... Report should show * documents on hold then depending on the "on-hold type" look in the corresponding table and SELECT a few fields. Here is what I have. Where do I SET the @profile variable to return the profile from my queue table?
DECLARe
@profilevarchar(256)
SELECT
q.[profile],q.on_hold,q.on_hold_message,q.dbc_stateÂ
FROM
QueueASq
[code]...
View 5 Replies
View Related
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
Feb 3, 2015
I had to restore a database in one of the staging servers the other day. However, upon completion, I found out that the service broker queue in the database is not working anymore. The service broker queue error was similiar to this error:
The activated proc dbo.procedure_name running on queue database_name.dbo.queue_name output the following: '<error message>.'
View 0 Replies
View Related
Jul 23, 2005
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies
View Related
Jul 20, 2012
best way to store questionnaire data in a database.Since different questionnairs have different questions and formats i.e dropdown, radio, checkboxes etc building such a database model becomes highly complex.
I've read that if data schema is complex and higly variable it may be better to use an xml document and store that in a databse. However I dont quite understand how you store xml to a database. Do you simply store the entire structure in something like a nvarchar column or is there some other way to store xml to a database.
If you store the entire structure to the databse then how do you query the content to generate reports.
example xml:
Code:
<survey>
<meta>
<id>sample</id>
</meta>
<questions>
<question id="1" type="singlechoice" page="1">
[code]...
View 3 Replies
View Related
May 26, 2008
Hi,
I have this page that upload's PFD's to a table. In principle this works fine.
Until I try to upload large files (3 to 4 MB)I need to even upload larger files than that. (Don't really know as of yet what users are going to come up with) I get TimeOut problems. Now some people say it is not possible to exceed a limit of about 4 MB. But that there is a workaround by changing something to the web.config file.Can somebody give me info about that, (I am quite a novice really)I tried to change it like this, but to no avail:
<system.web><httpRuntime maxRequestLength="102400"enable = "True"requestLengthDiskThreshold="102400" useFullyQualifiedRedirectUrl="True"executionTimeout="102400"/></system.web>
Thanks for any help!
View 2 Replies
View Related
Jul 16, 2002
Is it possible to store a .wav file in a SQL Server table? I have looked in books online and on the web... to no avail. Any info would help.
Thanks
Mike
View 1 Replies
View Related