Advice On The Best Way To Update A Database
Jun 17, 2007
Hi,
Apologies if this has already been asked, but I couldnt find a thread that asked exactly what I wanted.
Im making an administration panel for a site where you can change various settings, options, and categories that data can fall into. When editing, adding or deleting a record i COULD make a trip to the database every time, but this feels very inefficient as I understand that establishing the connection is usually the biggest performance hit when querying a database.
An alternative plan is for me to simply record the changes made in the panel and have a "save" button. When this is clicked, ONE database connection would be opened and all the data would be saved/updated/deleted as necessary. However, this would involve several "for" loops while the connection is open.
The question is; which method would you recommend and why? And does having several "for" loops while the connection is open nullify the advantage gained by only opening one connection?
Any advice would be very much appreciated.
Thank you
View 2 Replies
ADVERTISEMENT
Aug 28, 2006
I have a simple update statement that set sthe student's password to their id number:
update studemo set passwd = ident
ident is not part of the primary key but there is a unique, non-clustered index using that column
This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?
View 10 Replies
View Related
Feb 26, 2008
Hi - I'm in a situation with a very large table, and trying to run an update that, any way I've approached it so far, seems to be taking unnacceptably long to run. Table has about 20 million rows, looks something like this:
ID - int, identity
Type - varchar(50)
PurchaseNumber - varchar(50)
SalesAmount - Money
ID Type PurchaseNumber SalesAmount
1 A 3834AA38384 20.32$
2 B 3834AA38384 11837.32$
3 C 3834AA38384 666.32$
4 C 887DF88U01H 23423.32$
5 A 887DF88U01H 12.32$
6 B OI83999FH28 4747.1$
7 D 38438495985 9384.6
8 E 02939DDJJWI 22.22$
9 F 07939SDFDF2 33.33$
The goal of the update is to make the [Type] uniform across [PurchaseNumbers], according to the max sales amount. For each PurchaseNumber a, set the type = the type of the row that has the MAX salesAmount. If there is only one entry for PurchaseNumber, leave the type alone. Expected update after completion would look like this:
ID Type PurchaseNumber SalesAmount
1 B 3834AA38384 20.32$
2 B 3834AA38384 11837.32$
3 B 3834AA38384 666.32$
4 C 887DF88U01H 23423.32$
5 C 887DF88U01H 12.32$
6 B OI83999FH28 4747.1$
7 D 38438495985 9384.6
8 E 02939DDJJWI 22.22$
9 F 07939SDFDF2 33.33$
I got this out of a warehouse, and it definitely isn't normalized well. Was considering breaking down into a better model, but I'm not yet sure if that would make the update easier.
I've been approaching this with sub-queries (finding all the PurchaseNumbers with more then one entry, then the max sales purchase of that purchase Number, then the type of that purchase number and sales amount to update all of that purchase number) but this not only ends up a little messy, but also very slow.
The only other detail that may be important is that out of the 20 million total rows, about 19.5 million purchaseNumbers are unique. So, really, there are only about 500k rows I actually have to update.
I've thought of a few ways to make this work, but none of them seem fast and wanted to see if anyone had a pointer. Thanks!
View 5 Replies
View Related
Nov 12, 2006
Hi all,
I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.
I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...
Here it is:
-- Update a student, by ID.
DROP PROCEDURE p_UpdateStudent
CREATE PROCEDURE p_UpdateStudent
@ID INT,
@NewFName VARCHAR(25),
@NewOName VARCHAR(25),
@NewLName VARCHAR(25),
@NewDOB DATETIME,
@NewENumber VARCHAR(10),
@NewContactAID INT,
@NewContactBID INT
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE OLD VALUES
DECLARE @FName AS VARCHAR(25)
DECLARE @OName AS VARCHAR(25)
DECLARE @LName AS VARCHAR(25)
DECLARE @DOB AS DATETIME
DECLARE @ENumber AS VARCHAR(10)
DECLARE @ContactAID AS INT
DECLARE @ContactBID AS INT
-- Get all of the old values
SELECT @FName = FName FROM TBL_Student WHERE ID = 10000
SELECT @OName = OName FROM TBL_Student WHERE ID = 10000
SELECT @LName = LName FROM TBL_Student WHERE ID = 10000
SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000
SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000
SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000
SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000
-- USE ISNULL to set all of the new parameters to the provided values only if they are not null
-- Keep the old ones otherwise.
SET @NewFName = ISNULL(@NewFName, @FName)
SET @NewOName = ISNULL(@NewOName, @OName)
SET @NewLName = ISNULL(@NewLName, @LName)
SET @NewDOB = ISNULL(@NewDOB, @DOB)
SET @NewENumber = ISNULL(@NewENumber, @ENumber)
SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)
SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)
-- Do the update
UPDATE TBL_Student
SET FName = @NewFName,
OName = @NewOName,
LName = @NewLName,
DOB = @NewDOB,
ENumber = @NewENumber,
ContactAID = @NewContactAID,
ContactBID = @NewContactBID
WHERE
ID = @ID
END
GO
So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....
Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)
Thanks all.
Chris
View 7 Replies
View Related
Oct 27, 2006
Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
View 4 Replies
View Related
Dec 8, 2007
Hi,I recently contacted my hosting company's customer support about my databases not working - saying that I use sql express (which they support).The guy recommended:
"I would suggest you to upgrade the db's to use mssql 2005." "This
is because, sql express is built for development environment. When you
are in development environment, you are accessing everything with
administrator permission. However, in live hosting environment (when
there are differnet kind of permission restrictions), sql express often
failed on attaching database." Does anyone have any opinion on that? Would it be best to change db's to use mssql 2005? How complicated/time consuming will it be to upgrade?Thanks!Jon
View 1 Replies
View Related
Jun 4, 2007
I am thinking of doing a fake PC company site for my ASP project. so
what they will have is a chat, products with reviews, and users can
have "Buddies".So my DB so far (Tables):ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware
- ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5
stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, DateChat - ID, TopicID, Title, Content, DateUsers - ID, Username, Password,The problem, how do i connect Reviews to the products since they are from diff tables.How do i get the "buddy" system workingChat i think its not as simple as thatBut i just need a simple ASP project, so no need to get too complex, but i still hope to learn as much.
View 12 Replies
View Related
Dec 16, 2005
Hello,I am working on a web site which will use SQL 2005.I am planing my first SQL database and I am looking for advice.1. There will be two types of users: students and professors.2. Both users types will have login information. (Username, Password, AccessLevel) 3. The remaining information on students and professores is different. Student (Name, Email, Phone, ...) / Professor (Name, Email, Phone, Subjects, ...)4. Professors can publish documents. Each document has some info (Type, Title, Description, ...)My plan in this moment is to:A. Create the tables Students, Professors, Login and Documents.B. Students table would be connected to Login table. Professors table would be connected to Login table and Documents table.C. The field [Type] in documents table should include the type or should I create a table DocumentsTypes where I add codes for each type. I have seen this. What is the advantage?Can someone give me some advice?Thank you Very Much,Miguel
View 1 Replies
View Related
Oct 19, 2007
Hello,
I am creating a database where:
- I have a Blogs and Folders system.
- Use a common design so I can implement new systems in the future.
Users, Comments, Ratings, View, Tags and Categories are tables common to all systems, i.e., used by Posts and Files in Blogs and Folders.
- One Tag or Category can be associated to many Posts or Files.
- One Comment, View or Rating should be only associated to one Post or one File. I am missing this ... (1)
Relations between a File / Folder and Comments / Ratings / View / Tags / Categories are done using FilesRatings, FoldersViews, etc.
I am using UniqueIdentifier as Primary Keys.
I checked ASP.NET Membership tables, a few articles and few features in my project, such as renaming files with the GUID of their records.
I didn't decided yet for INT or UNIQUEIDENTIFIER.
I am looking for some feedback on the design of my database.
One thing I need to improve is mentioned in (1)
Thank You,
Miguel
My Database Script:
-- Users ...
create table dbo.Users
(
UserID uniqueidentifier not null
constraint PK_User primary key clustered,
[Name] nvarchar(200) not null,
Email nvarchar(200) null,
UpdatedDate datetime not null
)
-- Categories ...
create table dbo.Categories
(
CategoryID uniqueidentifier not null
constraint PK_Category primary key clustered,
[Name] nvarchar(100) not null
)
-- Comments ...
create table dbo.Comments
(
CommentID uniqueidentifier not null
constraint PK_Comment primary key clustered,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
constraint FK_Comments_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Ratings ...
create table dbo.Ratings
(
RatingID uniqueidentifier not null
constraint PK_Rating primary key clustered,
AuthorID uniqueidentifier not null,
Value float not null,
constraint FK_Ratings_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Tags ...
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
-- Views ...
create table dbo.Views
(
ViewID uniqueidentifier not null
constraint PK_View primary key clustered,
Ticket [datetime] not null
)
-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- PostsCategories ...
create table dbo.PostsCategories
(
PostID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_PostsCategories
primary key clustered (PostID, CategoryID),
constraint FK_PostsCategories_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- PostsComments ...
create table dbo.PostsComments
(
PostID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_PostsComments
primary key clustered (PostID, CommentID),
constraint FK_PostsComments_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- PostsRatings ...
create table dbo.PostsRatings
(
PostID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_PostsRatings
primary key clustered (PostID, RatingID),
constraint FK_PostsRatings_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- PostsTags ...
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- PostsViews ...
create table dbo.PostsViews
(
PostID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_PostsViews
primary key clustered (PostID, ViewID),
constraint FK_PostsViews_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Folders ...
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
[Name] nvarchar(100) null,
Description nvarchar(2000) null,
CreatedDate datetime not null,
URL nvarchar(400) not null
)
-- Files ...
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Description nvarchar(2000) null,
[Name] nvarchar(100) not null,
URL nvarchar(400) not null,
UpdatedDate datetime not null,
IsPublished bit not null,
Type nvarchar(50) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
constraint FK_Files_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- FilesCategories ...
create table dbo.FilesCategories
(
FileID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_FilesCategories
primary key clustered (FileID, CategoryID),
constraint FK_FilesCategories_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- FilesComments ...
create table dbo.FilesComments
(
FileID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_FilesComments
primary key clustered (FileID, CommentID),
constraint FK_FilesComments_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- FilesRatings ...
create table dbo.FilesRatings
(
FileID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_FilesRatings
primary key clustered (FileID, RatingID),
constraint FK_FilesRatings_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- FilesTags ...
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- FilesViews ...
create table dbo.FilesViews
(
FileID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_FilesViews
primary key clustered (FileID, ViewID),
constraint FK_FilesViews_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Run script
go
View 2 Replies
View Related
Feb 13, 2008
I'm at a new installation where there's no DBA at all, so, as a Coldfusion programmer, I'm now the DBA, LOL.
The main SQL2000 DB we use is approximately 100MB with about 7MB of free space and is allocated to expand by 10%.
I am adding a new, large table, about 60 columns with lots of variable-length unicode fields, mostly nvarchar. It's being used to track non-USA user-form information. Even though the DB is set to expand, I'm concerned that due to the potential size/volume of records, that the auto-expand could cause performance issues.
The SQL2000 server has plenty of room, about 49GB, so I'm wondering if I should expand the size of the current DB, or if the auto-expand feature will be ok.
At this point I'm not sure what the volume of the user-form records will be in the new table. It won't be a million records certainly, but I'm guessing it could climb to maybe 10-20,000 records.
If I should expand the DB, can I do this while the DB is still online?
thanks...
View 4 Replies
View Related
Jul 20, 2005
hi folks,i'm puzzled over this one, anyone with some solid db experience might beable to enlighten me here.i'm modelling a file system in a database as follows, and i can't figure outto cleanly implement an inheritance mechanism.i have a hierarchy of folders in an sql table. every folder has aparentFolderID, if this value is 0 then it means it's a root folder.then, in a 'files' table, every file has a parentFolderID to give it alocation in the structure. fairly basic.the hard part is that each file record has an attribute 'STYLE' that can beexplicitly specified, or inherited from it's parent folder, or it's parentsparent folder, or.. all the way back to the root.the 2 ways i've come up with representing it are:1) if the style is being inherited, enter a null value in the STYLE field.then to figure out what style applies to a file or folder, i trace backthrough it's parentFolderID records until i find a style attribute that isnot null.the good thing about this is that if i change the style that is applied tothe entire filesystem, it only takes one update.the bad thing is when i want to figure out what style applies to a file, ihave to traverse back through possibly several records to locate the folderthat actually specifies the style being inherited by the file.2) explicitly state all style values in each record.this is good for accessing the style of a file or folder because you get itstraight out first time from the db.the bad thing is if i update the entire file system, i might have severalhundred / thousand update sql statements to execute to update all the valuesin every folder and every file. nasty!thanks for any help, i'm really stumped with this and i'm thinking theremust be a more elegant way to implement inheritance.thankstim
View 2 Replies
View Related
May 1, 2006
Hi,
I.m trying to find the best practice for my solution.
This is the situation.
My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.
Many thanks.
-VJ
View 11 Replies
View Related
Oct 21, 2007
Hello,
I am creating a database where:
- I have a Blogs and Folders system.
- Use a common design so I can implement new systems in the future.
Users, Comments, Ratings, View, Tags and Categories are tables common to all systems, i.e., used by Posts and Files in Blogs and Folders.
- One Tag or Category can be associated to many Posts or Files.
- One Comment, View or Rating should be only associated to one Post or one File. I am missing this ... (1)
Relations between a File / Folder and Comments / Ratings / View / Tags / Categories are done using FilesRatings, FoldersViews, etc.
I am using UniqueIdentifier as Primary Keys.
I checked ASP.NET Membership tables, a few articles and few features in my project, such as renaming files with the GUID of their records.
I didn't decided yet for INT or UNIQUEIDENTIFIER
I am looking for some feedback on the design of my database.
One thing I think need to improve is mentioned in (1)
But any advices to improve it would be great.
Thank You,
Miguel
My Database Script:
-- Users ...
create table dbo.Users
(
UserID uniqueidentifier not null
constraint PK_User primary key clustered,
[Name] nvarchar(200) not null,
Email nvarchar(200) null,
UpdatedDate datetime not null
)
-- Categories ...
create table dbo.Categories
(
CategoryID uniqueidentifier not null
constraint PK_Category primary key clustered,
[Name] nvarchar(100) not null
)
-- Comments ...
create table dbo.Comments
(
CommentID uniqueidentifier not null
constraint PK_Comment primary key clustered,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
constraint FK_Comments_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Ratings ...
create table dbo.Ratings
(
RatingID uniqueidentifier not null
constraint PK_Rating primary key clustered,
AuthorID uniqueidentifier not null,
Value float not null,
constraint FK_Ratings_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Tags ...
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
-- Views ...
create table dbo.Views
(
ViewID uniqueidentifier not null
constraint PK_View primary key clustered,
Ticket [datetime] not null
)
-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- PostsCategories ...
create table dbo.PostsCategories
(
PostID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_PostsCategories
primary key clustered (PostID, CategoryID),
constraint FK_PostsCategories_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- PostsComments ...
create table dbo.PostsComments
(
PostID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_PostsComments
primary key clustered (PostID, CommentID),
constraint FK_PostsComments_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- PostsRatings ...
create table dbo.PostsRatings
(
PostID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_PostsRatings
primary key clustered (PostID, RatingID),
constraint FK_PostsRatings_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- PostsTags ...
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- PostsViews ...
create table dbo.PostsViews
(
PostID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_PostsViews
primary key clustered (PostID, ViewID),
constraint FK_PostsViews_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Folders ...
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
[Name] nvarchar(100) null,
Description nvarchar(2000) null,
CreatedDate datetime not null,
URL nvarchar(400) not null
)
-- Files ...
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Description nvarchar(2000) null,
[Name] nvarchar(100) not null,
URL nvarchar(400) not null,
UpdatedDate datetime not null,
IsPublished bit not null,
Type nvarchar(50) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
constraint FK_Files_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- FilesCategories ...
create table dbo.FilesCategories
(
FileID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_FilesCategories
primary key clustered (FileID, CategoryID),
constraint FK_FilesCategories_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- FilesComments ...
create table dbo.FilesComments
(
FileID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_FilesComments
primary key clustered (FileID, CommentID),
constraint FK_FilesComments_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- FilesRatings ...
create table dbo.FilesRatings
(
FileID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_FilesRatings
primary key clustered (FileID, RatingID),
constraint FK_FilesRatings_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- FilesTags ...
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- FilesViews ...
create table dbo.FilesViews
(
FileID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_FilesViews
primary key clustered (FileID, ViewID),
constraint FK_FilesViews_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Run script
go
View 2 Replies
View Related
Feb 27, 2008
hi,
iam thinking of changing my ajax slideshow so that it gets the data from the databse. currently i am finding it hard to add text functianlity the way i want with the slide show.
what my query is, that if i to using a datalist can i add javasscript functionality to the data being retrived. for example, currently i have written some javascript so that a series of text is diplayed one after the other in a sequence from just one button click. so if im pulling data out of a databse can i still add this javascript functionality to it? i hope this makes sense, if it doesnt then i am willing to elaborate. please can any one offer any advice or examples or any suggestions on how i can do this. any help is much appricated as i am struggling to find a solution as i orinally wanted to be able to add this javascript functionality with the play button of the slide show but i couldnt find a solution.also i think its better to use some kind of database as i can use the editing funtions visual web developer offers
thank you
View 2 Replies
View Related
Mar 13, 2008
Hi All Professionals Programmers,
I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can.
Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc.
you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships.
Hope you have understood what i am going to say and need advice of professional and expert user to resolve it.
Any concise quality material like articles, white paper etc will also be suitable for me.
Thanks in Advance
View 3 Replies
View Related
May 22, 2008
Greetings,
Have an interesting issue in a database I'm trying to design and I'm trying to find a better way to setup the structure, if there is one. Going to generic example of my issue.
StudentList --- This table is the list of all College Students(with Primary Key of StudentID)
MajorsList --- This is a table listing all of the majors available at the College (PK of MajorID)
CourseList --- This is the list of all of the courses at the school (PK of ClassID)
This college allows students to take 1 or more majors, with the number of majors unknown.
-- Any number of students can take the same majors or different majors.
-- -- (aka 1 StudentID -> 1+ Majors & 1 Major -> 1+ StudentID's)
The majors all have different numbers of courses in them.
-- Many of the majors can have the same, or different courses in their lists.
-- -- (aka 1 Major -> 1+ Courses & 1 Course -> 1+ Majors)
I'm looking to see if their is a better way of tieing together the Students -> Majors and Majors -> Courses.
If done properlly, a single select and a few joins should bring up every class a student is taking for all their majors, or any other relationship to any of the three main tables primary keys.
Currently, to tie the three tables together, I have made two Interrum tables:
-- StudentMajors - - With only two columns
-- -- StudentID (ForeignKey tied to PrimaryKey StudentID in StudentList)
-- -- MajorID (FK tied to PrimaryKey MajorID in MajorList)
and
-- MajorCourses - - with only two columns
-- -- MajorID (FK tied to PrimaryKey MajorID in MajorList)
-- -- CourseID (FK tied to PrimaryKey CourseID in CourseList)
These tables give clear and definate ties between the tables, but my worry is that there is no primary key for these two tables, and no column in the tables is elledgeable for becoming PK, because, in this example the StudentMajors table can lis tthe studentID multiple times, each with a different MajorID. Sames goes with MajorCourses.
Is there a better structure method for reaching this same goal?
Additional, I don't know how to do a contraint that should be in place of:
-- In StudentMajors, for each value of studentID, there can be no duplicate values on MajorID
-- -- (same in MajorCourses tables in relations to classid's)
Any advice on how to do this constraint?
View 3 Replies
View Related
Jun 2, 2008
Hello,
I have an asp.net application which connects to SQL Server 2005 database.
One out of 15 times (approx) the applicaiton does not make connection to the database and an exception is thrown.
I am not sure how to debug this. Should I write some code which can make connections in a loop to test how much stress the sever can handle?
Kindly suggest some ideas. Thanks.
View 3 Replies
View Related
Aug 24, 2005
Dear GroupI'd be grateful if you can give me some advice on the following.An application I wrote uses an MSDE backend and I wonder whetherthere's a way (even for the system administrator) of not seeing ortracing stored procedure code, view and table designs?And I also wonder whether you can advise me on an installer thathandles MSDE and database setup during installation without too mucheffort but is still affordable < USD 1000.Any articles, resources, advice hints for these two topics are veryappreciated.Thank you very much for your help & efforts!Martin
View 3 Replies
View Related
Jan 30, 2008
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View 1 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
May 23, 2006
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Thanks in advance for your help
Nick
View 4 Replies
View Related
Sep 10, 2012
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies
View Related
Mar 8, 2015
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO
View 1 Replies
View Related
Oct 29, 2015
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies
View Related
Mar 26, 2008
Hi!
I have an original database that I want to copy once to another database. Then I want to update the data weekly with the data of the original database. I don€™t change any tables or columns in that part of the database. Just some tables more in the €˜new€™ database (than the tables from the original database) with some references to the tables that have to be updated weekly.
How can I do this? (if you know what I mean... it's a sort of a datawarehouse where different sources come together.. that part that represents the original database is just a part of the datawarehouse. That part is exactly the same structure as the original database.)
Thanks,
Sandra
View 1 Replies
View Related
Jan 22, 2008
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
Best Regards,
Hans
View 8 Replies
View Related
Oct 26, 2015
I am using two database server. Both are sql server.Â
My task is :I want to insert data from server 1 table to server 2 table and update same when modified the existing data from server 1. is it possible to do the integration in single package.
I know to do insert and update seperately by ssis but need to do same with single task.Â
View 5 Replies
View Related
Mar 4, 2006
I'm new to ASP.NET (I normally work with Windows Forms) and have installed the Club Website Starter Kit. I created an Administrators role and a user called "admin". When I run the project locally, everything works as it should - I can log in as admin and I can create new users.
I then copied the site to localhost (C:Inetpubwwwroot), but when I run the site from localhost, I can't log in (as admin or any other user); instead I get a message that says, "Your login attempt was not successful. Please try again."
Also, I can't create a new user. When I try, I get the following error:
Server Error in '/' Application.
Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
I figure there must be a simple(?) explanation and solution for this problem, and ideally, some sort of step-by-step procedure that new users to ASP.NET can follow to configure a site - such as one of the starter kits - to work correctly. I can honestly say that trying to get this working has been an extremely frustrating experience.
If someone could help me out, I would really appreciate it. I've been working on this for two days and feeling I have hit the wall.
Thanks in advance.
View 1 Replies
View Related
Mar 28, 2006
Hi there,I am getting a real problem with ASP.NET 2.0, all i am trying to do is run a recovert password (using the login controls) and i got the following error.Does anybody know how to fix this? I have tried the SSEUTIL answer here and it didn't work. http://forums.asp.net/909168/ShowPost.aspxI also ensured that Network Services user has READ/WRITE/EXECUTE premissions on the directory below and that each file isn't read only and also that it inherits ACL from the directory which it seems to do..Bit of strange one this, it isn't created (and i am not using) a beta version of VS 2005 Pro... but a full retail version.The PC is the local PC with IIS 5.1 and VS installed... it is the one i am using for developing.. I don't need to copy my files to the IIS directory below as that is where i have created it and open it in VS 2005 from there..I did read that if you use IIS 5.1 then the user should be ASPNET ... but i don't seem to have that use but a Network Services user...Any help or guidance would be really appreciated... i have come to a stop stage and can't get any further..Thanksian
Server Error in '/igdotcom' Application.
Failed to update database
"C:INETPUBWWWROOTIGDOTCOMAPP_DATAASPNETDB.MDF" because the database
is read-only. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Failed to update database
"C:INETPUBWWWROOTIGDOTCOMAPP_DATAASPNETDB.MDF" because the database
is read-only.Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
View 3 Replies
View Related
Jun 20, 2007
Please help me How to update data from this Database to orther Database ?
Thank
View 5 Replies
View Related
