Data transformation services: web

Showing posts with label web. Show all posts

Tuesday, March 27, 2012

Data Source Provider Error: The event log file is full

I'm trying to process one of my cubes and am getting this error, see subject
line. Mt configuration is this: Development Web server Dul09 holds the cube
s. The data lives on a SQL server DryHou10. I have had my sysadmin check the
event logs on both serv
ers, neither of which is full. This setup has been working just fine for the
last few years. Other cubes on Dul09, using different SQL servers for data,
process just fine.
All servers are running SQL 2000TJSully wrote:
> *I'm trying to process one of my cubes and am getting this error, see
> subject line. Mt configuration is this: Development Web server Dul09
> holds the cubes. The data lives on a SQL server DryHou10. I have had
> my sysadmin check the event logs on both serv
> ers, neither of which is full. This setup has been working just fine
> for the last few years. Other cubes on Dul09, using different SQL
> servers for data, process just fine.
> All servers are running SQL 2000 *
annieckl
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message441896.html|||Are you sure that they are not full...I got the same error once and emptied
all my logs (you can put them into files if
you want to keep them) - it worked again after that.
--Michael
"annieckl" <annieckl.1dtcei@.mail.mcse.ms> skrev i en meddelelse
news:annieckl.1dtcei@.mail.mcse.ms...
> TJSully wrote:
>
> --
> annieckl
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message441896.html
>

Data Source Not Shared

How to create a Not Schared Data Source using Reporting Service Web Service?
ThanksWhat do you mean by "not shared"? A data source used by a report (as long
as it is not a shared data source) is a not shared Data Source. Are you
asking how to create it in the designer?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
news:B33C9A19-D026-481B-A669-24BF9EC8FBDF@.microsoft.com...
> How to create a Not Schared Data Source using Reporting Service Web
> Service?
> Thanks
>

Sunday, March 25, 2012

Data Source Credentials problem

Hi guys.

I've got an Xml DataSource in my report server, used to talk to a web service to retrieve it's data. Now, since this is an Xml DataSource that talks to a Web service, i shouldn't have to give it credentials to run with, or - since the web service runs on the machine right next to my report server - I could use Windows Integrated Auth.

However ... when I try to use Windows Integrated auth. I get back the error:

"This

data source is configured to use Windows integrated security. Windows integrated

security is either disabled for this report server or your report server is

using Trusted Account mode. "

And when I tried to use no credentials, before I can even see the report, I get the error:

"the user data source credentials that are required to execute this report are

not stored in the report server database." - where I just told it that it doesn't need credentials to run this datasource !!!

For the first error you got, there are two possible causes:

1. If you are running RS in sharepoint integrate mode, make sure the authentication mode you set in sharepoint central admin is Windows Integrated, not Trusted Account mode.

2. If you are running RS in non-sharepoint integrate mode, make sure this system properties is set to be true: "EnableIntegratedSecurity".

For the second error, if you set to be none, RS uses a fixed credential configured in Report Server config file to connect to your data source, you have to add it.

|||For the second issue make sure that unattended execution account is initialized. Open Reporting Services Config Manager and goto Execution Account tab|||No, I'm not running in Sharepoint Integrated mode, but I'll try the second resolution and let you guys know what happened, thanx :)|||

Well, here goes the whole story ...

First of all, we're using the Forms Authentication sample in our installation. Thought I should mention that in view of the error I'm about to write ...

Well, I've used the R. Services Configuration tool to set the execution account to a user in my domain. The config tool accepted it fine, only when I tried to run the report I got back:

|||

Finally, i got it to work !!! I created a new user account fot the execution account, one that had no xml special characters in it's uname/pwd and it worked fine.

Now, you guys should forward this to the implementing team because it's just rediculous ...

If the username / password of the Execution Account has any sort of "XML special characters" in it, the WMIProvider will fail to set the exec. account details. Although those credentials actually get written encrypted in the xml config file, so that doesn't seem really necessary.

Anyway, that WOULD be ok, u n l e s s ... if you try to type in the credentials, replacing those special characters with their &xxx; equivalents, the tool will succeed, and write out those details encrypted in the xml file. BUT !!! It won't replace the & to an & in the encrypted string it writes out, so when you try to run reports, login will fail, and you're left there wondering what went wrong.

Data source connection to SAS via SS05

Hi all, does anyone have a sample (or knows of one on the web, maybe screenshots) of how to connect via SQL Server 2005 to a SAS box? The SAS machine is Unix, I am able to connect via Enterprise Guide from my local machine - which is also runing SS05 Developer. I'm investingating the possibility of building a cube of off a SAS dataset.

Thanks in advance.

Mark

Two things to keep in mind when building a cube. First, you have to have a driver with which SSAS can connect to the data source and through which SSAS can build objects within the DSV. Next, you need to have a cartridge with which SSAS can construct query statements to pull data from the data source.

You might be able to present SAS objects through SQL Server views using the OPENROWSET function. (This may be where you are already headed.) So, is there a way to connect SQL Server Database Engine to SAS using OPENROWSET?

One other thing, SAS has their own OLAP solution. Is that an option for you?

|||

Ho Bryan,

I went in a different direction (FTP text files for awhile), but am re-visiting this now. We don't have the SAS OLAP Server in house, so unfortunately that isn't an option. However, is there a SAS/MSFT web-site where cartriges can be obtained from? I'm able to connect to the SAS box and can see the SAS datasets, but, I can't render them. Its literally the last step that's blowing up, and I believe it may be cartrige driven.

Thanks,

Mark

|||

Sorry to say I'm not aware of any cartridges other than those provided by default. Worst case, can you perform an OPENROWSET query through SQL Server? If so, you might be able to construct a query in SQL Server using OPENROWSET against SAS which would faciliate processing. Still, no guarantees that would work.

Data source connection to SAS via SS05

Thanks in advance.

Mark

One other thing, SAS has their own OLAP solution. Is that an option for you?

|||

Ho Bryan,

Thanks,

Mark

|||

Thursday, March 22, 2012

Data retrieving or querying problem

Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax forretrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this:

'Create a New Connection to our daabase

Dim testAs SqlDataSource =New SqlDataSource()

test.ConnectionString = ConfigurationManager.ConnectionStrings(

"Connectionstring1").ToString'This is the SQL Insert Command

test.InsertCommand =

"Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@.Initial,@.Correspondent_Name,@.Correspondent_No,@.Payment_Type)"'Each of this insert a value into the appropriate command

test.InsertParameters.Add(

"Initial", DropDownList4.SelectedValue)'This is the selected "Initial" DropDownList value

test.InsertParameters.Add(

"Correspondent_Name", TextBox10.Text)'This is the Correspondent_Name value

test.InsertParameters.Add(

"Correspondent_No", TextBox3.Text)'This is correspondent_no value

test.InsertParameters.Add(

"Payment_Type","Pay upon delivery")'This indicates that the payment is made with credit card

test.Insert()

But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!

Regards,

Ivan

Hi,

There are many ways to retrieve data from a database table into a form. It depends on what kind of controls would you like to bound with.

If you want to display the data in a table, the easist way is to use a GridView control and assign its DataSourceID with the SqlDataSource control's ID.

Another way is to use DataView to retrieve the set of data.eg:

DataSourceSelectArguments ar =new DataSourceSelectArguments();DataView dv = (DataView)SqlDataSource1.Select(ar);this.TextBox1.Text = dv.Table.Rows[0]["CategoryName"].ToString();

In this way you can retrieve the value of any row any column manually.

Thanks.

|||

One of the ways how can you select data from database is this:

Imports System.Data.SqlClient

Dim test As SqlConnection = New SqlConnection()
test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ConnectionString
Dim command As SqlCommand = test.CreateCommand()
command.CommandText = "SELECT * FROM Customer"
Dim GV1 As GridView = New GridView()
test.Open()
GV1.DataSource = command.ExecuteReader()
GV1.DataBind()
test.Close()
form1.Controls.Add(GV1)

Tuesday, March 20, 2012

Data Read / Write problem with concurrent users on with dll

Hi,

I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder.

We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack:

06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:\My Path\Validator\Validator.NET\Data\DatabaseEvents\DatabaseEventManager.cs:line 92
at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\CATCard.cs:line 94
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 215
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 147
at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 369
at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 353
at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\PrePrintedLetter.cs:line 282
at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:\My Path\Projects\Validator\Validator.NET\ValidationProcessor.cs:line 82
at clear_applications_scan_applications.ProcessValidation()

It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?

I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!!

Any ideas/suggestions would be greatly appreciated.

Thanks

Hi Assimalyst,

Based on the exception message, we can see that this is not because of reusing of the SqlDataReader, but reusing the SqlConnection.

Generally, on a single SqlConnection, there can only be 1 open SqlDataReader exist at the same time. If a using is trying to open another one, this exception is thrown.

In ADO.NET 2.0, a new concept is involved called Multiple Active Result Sets (MARS). This enables you open multiple DataReaders on a single connection. To achieve this, you only need to add "MultipleActiveResultSets=True" in your connection string. That will prevent the exception from being thrown.

Please check the following links for more information:

http://msdn2.microsoft.com/en-us/library/cfa084cz.aspx

HTH. If anything is unclear, please feel free to mark this post as Not Answered and post your reply. Thanks!

sql

Thursday, March 8, 2012

Data model for a web messaging application.

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 the
user "replyed" to an incoming message

[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message

[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.

[DeleteByRecipient] [bit] NOT NULL ,

[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages

[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list

[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT 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
)

AS
BEGIN TRANSACTION SendMessageTrans

INSERT INTO Messages

(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)

VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)

UPDATE Users

SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID

UPDATE Users

SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID

SAVE TRANSACTION SendMessageTrans

SET @.OUT_ERRCODE = @.@.error

IF (@.@.error <> 0)

BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END

ELSE

BEGIN
COMMIT TRANSACTION SendMessageTrans
END

/* ReadMessage */

CREATE PROCEDURE ReadMessage (

@.IN_MessageID int,
@.IN_RecipientID int,

@.OUT_ERRCODE tinyint OUTPUT
)

BEGIN TRANSACTION ReadMessageTrans

SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID

UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@.IN_MessageID

UPDATE Users SET log_NumberOfNewMessages =
log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID

SAVE TRANSACTION ReadMessageTrans

SET @.OUT_ERRCODE = @.@.error

IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END

ELSE

BEGIN
COMMIT TRANSACTION ReadMessageTrans
END

/* Delete Message */

CREATE PROCEDURE DeleteMessage (

@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,

@.OUT_ERRCODE tinyint OUTPUT
)

AS
BEGIN TRANSACTION DeleteMessageTrans

DECLARE @.Recipient int
DECLARE @.Sender int

SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@.IN_MessageID)

IF (@.IN_DeleteIncomingMessage = 1)

BEGIN

IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)

BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END

ELSE

BEGIN

UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END

IF (@.IN_DeleteOutgoingMessage = 1)

BEGIN

IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)

BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END

ELSE

BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END

SAVE TRANSACTION DeleteMessageTrans

SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END

/* ListIncomingMessages */

CREATE PROCEDURE ListIncomingMessages (

@.IN_RecipientID int

)

SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC

/* ListOutgoingMessages */

CREATE PROCEDURE ListOutgoingMessages (

@.IN_SenderID int

)

SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC

Thanks in advance!

-ItaiItai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?

"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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 the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT 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
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||Hi

I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.

In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...

> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.

"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.

Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...

I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...

Thanks again

-Itai

BTW does anyone know how to dump a table to a text file using command
line with arguments?|||Hi

I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google.com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further :)
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> > There is probably a
> > natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> > index may be useful, and a INT may not be sufficient for you messageid.
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.

> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?

BCP, DTS or even osql will do this, look at books online for more
information on these.

John

Data model for a web messaging application.

I need to develop an internal messaging sub-system that is similar to
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table ? log_TotalMessages). The
quota 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 the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT 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
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID = @.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID = @.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID = @.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID = @.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-ItaiItai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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 the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT 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
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages => log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID => @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID => @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
>I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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:
>
See changes inline. . .
David
--Use Declarative referential integrity and cascading deletes
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[RecipientID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[SenderID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[GroupID] [uniqueidentifier] NULL ,
[SubmitDate] [smalldatetime] NOT NULL ,
[DeleteBySender] [bit] NOT NULL ,
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL ,
[Subject] [tinyint] NOT NULL ,
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT 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
change the error handling.
there was a mix of "nested transactions" and savepoints
which wouldn't really work.
Slso get rid of the @.out_errorcode parameter.
Any error will go to the client in a message anyway.
If you really need the error value to be returned to another
stored procedure, just return it as the return value of the
stored procedure.
*/
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
IF (@.@.error <> 0) goto eh
COMMIT TRANSACTION
RETURN 0
eh:
ROLLBACK TRANSACTION SendMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ReadMessage
Single value results should be returned in
output parameters instead of resultsets.
And only decrement log_NumberOfNewMessages the first time
*/
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_MessageText out varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION ReadMessageTrans
declare @.AlreadySeen bit
SELECT
@.OUT_MessageText = MessageText
@.AlreadySeen = SeenByRecipient
FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.AlreadySeen = 0)
BEGIN
UPDATE Messages SET
SeenByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION ReadMessageTrans
COMMIT TRANSACTION
RETURN 1
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
DECLARE @.DeleteBySender bit
DECLARE @.DeleteByRecipient bit
--do this in one query
SET
@.Recipient = RecipientID,
@.Sender = SenderID,
@.DeleteBySender = DeleteBySender
@.DeleteByRecipient = DeleteByRecipient
FROM Messages (updlock, holdlock)
WHERE MessageID = @.IN_MessageID
IF @.IN_DeleteOutgoingMessage = 1
BEGIN
if (@.DeleteByRecipient = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
IF (@.@.error <> 0) GOTO EH
END
ELSE --@.IN_IncomingMessage
BEGIN
if (@.DeleteBySender = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION DeleteMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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 the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT 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
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages => log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID => @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID => @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||Uri, John and especially David! Thanks for the code review; I am now
one step further :)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google.com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further :)
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> > There is probably a
> > natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> > index may be useful, and a INT may not be sufficient for you messageid.
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John

Data model for a web messaging application.

I need to develop an internal messaging sub-system that is similar to
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table log_TotalMessages). The
quota 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 the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT 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
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages =
log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-Itai
Itai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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 the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT 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
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai
|||1. MessageID serves as the PK
2. I am concerned with the overall design approach
tnx
-Itai
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Itai
I looked at your stored procedures
In the SendMessage stored procedure instead of update these tables I'd use
ON DELETE CASCADE ON UPDATE CASCADE when you design relationship between
users and messages tables
CREATE TABLE Messages_Users
(
[ID] INT NOT NULL PRIMARY KEY,
MessageId INT NOT NULL FOREIGN KEY REFERENCES Messages([MessageId ])ON
DELETE CASCADE ON UPDATE CASCADE,
UserId INT NOT NULL FOREIGN KEY REFERENCES Users([UserId ])ON DELETE
CASCADE ON UPDATE CASCADE,
......
......
)
"Itai Itai" <itaitai2003@.yahoo.com> wrote in message
news:OFNXqCUqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> 1. MessageID serves as the PK
> 2. I am concerned with the overall design approach
> tnx
> -Itai
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
>I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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:
>
See changes inline. . .
David
--Use Declarative referential integrity and cascading deletes
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[RecipientID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[SenderID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[GroupID] [uniqueidentifier] NULL ,
[SubmitDate] [smalldatetime] NOT NULL ,
[DeleteBySender] [bit] NOT NULL ,
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL ,
[Subject] [tinyint] NOT NULL ,
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT 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
change the error handling.
there was a mix of "nested transactions" and savepoints
which wouldn't really work.
Slso get rid of the @.out_errorcode parameter.
Any error will go to the client in a message anyway.
If you really need the error value to be returned to another
stored procedure, just return it as the return value of the
stored procedure.
*/
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
IF (@.@.error <> 0) goto eh
COMMIT TRANSACTION
RETURN 0
eh:
ROLLBACK TRANSACTION SendMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ReadMessage
Single value results should be returned in
output parameters instead of resultsets.
And only decrement log_NumberOfNewMessages the first time
*/
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_MessageText out varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION ReadMessageTrans
declare @.AlreadySeen bit
SELECT
@.OUT_MessageText = MessageText
@.AlreadySeen = SeenByRecipient
FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.AlreadySeen = 0)
BEGIN
UPDATE Messages SET
SeenByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION ReadMessageTrans
COMMIT TRANSACTION
RETURN 1
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
DECLARE @.DeleteBySender bit
DECLARE @.DeleteByRecipient bit
--do this in one query
SET
@.Recipient = RecipientID,
@.Sender = SenderID,
@.DeleteBySender = DeleteBySender
@.DeleteByRecipient = DeleteByRecipient
FROM Messages (updlock, holdlock)
WHERE MessageID = @.IN_MessageID
IF @.IN_DeleteOutgoingMessage = 1
BEGIN
if (@.DeleteByRecipient = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
IF (@.@.error <> 0) GOTO EH
END
ELSE --@.IN_IncomingMessage
BEGIN
if (@.DeleteBySender = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION DeleteMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota 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 the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT 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
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai
|||Uri, John and especially David! Thanks for the code review; I am now
one step further

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk >...

> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?
|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google. com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further

> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk >...
>
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.

> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John

Tuesday, March 27, 2012

Data Source Provider Error: The event log file is full

Data Source Not Shared

Sunday, March 25, 2012

Data Source Credentials problem

Data source connection to SAS via SS05

Data source connection to SAS via SS05

Thursday, March 22, 2012

Data retrieving or querying problem

Tuesday, March 20, 2012

Data Read / Write problem with concurrent users on with dll

Thursday, March 8, 2012

Data model for a web messaging application.

Data model for a web messaging application.

Data model for a web messaging application.

Data transformation services

Blog Archive

About Me