Hi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:
- Week Number (the current week number in the year)
- User ID
- Events
- Meetings
- Activities
- Accomplishments
And each user would have one row in the database per week.
OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain:)
Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.
Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.
Here's what's going on. I have a 2 computers (x & y) running SQL2000. Ibacked up a copy a DB from x and restored it on y. I have a Stored procthat runs in under 2 seconds on both x & y when running it throughAnalyzer, but when I call this stored proc running it throuhg my C#winforms app (running on computer z) it takes over 3 minutes oncomputer x and under 10 seconds on y.This stored proc does have a select clause as part of the where clause,but again it works fine on y.I've check the indexes and that looks good and I just did a restore ofthe database so they should be identical. And I don't think it's aperformance issue because the rest of the app runs actaully a bitfaster on x.The plans do have differences. Specifically with a mention ofParallelism in the fast one.Here are the plans:X (slow):|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote] ASC,[Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+','+[g].[FirstName]))|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0 else[Expr1003]>=2))|--Nested Loops(Left Outer Join, OUTERREFERENCES:([r].[GuestId]))|--Hash Match(Inner Join,HASH:([g].[GuestId])=([r].[GuestId]),RESIDUAL:([r].[GuestId]=[g].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',isnull([g].[Email], ''), NULL)>1))| |--Clustered IndexSeek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWARD)|--Hash Match(Cache, HASH:([r].[GuestId]),RESIDUAL:([r].[GuestId]=[r].[GuestId]))|--ComputeScalar(DEFINE:([Expr1003]=Convert([Expr1011])))|--StreamAggregate(DEFINE:([Expr1011]=Count(*)))|--IndexSpool(SEEK:([r2].[GuestId]=[r].[GuestId]))|--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r2]))Y (Fast):|--Parallelism(Gather Streams)|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote]ASC, [Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))|--Parallelism(Repartition Streams, PARTITIONCOLUMNS:([r].[GuestId], [g].[GuestNote], [Expr1005], [g].[Email],[g].[Phone1]))|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+','+[g].[FirstName]))|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0else [Expr1003]>=2))|--ComputeScalar(DEFINE:([Expr1003]=Convert([Expr1013])))|--Hash Match Root(Right Outer Join,HASH:([r2].[GuestId])=([r].[GuestId]),RESIDUAL:([r2].[GuestId]=[r2].[GuestId]) AND([r2].[GuestId]=[r].[GuestId]) DEFINE:([Expr1013]=COUNT(*)))|--Parallelism(RepartitionStreams, PARTITION COLUMNS:([r2].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r2]))|--Hash Match Team(Inner Join,HASH:([g].[GuestId])=([r].[GuestId]),RESIDUAL:([r].[GuestId]=[g].[GuestId]))|--Bitmap(HASH:([g].[GuestId]), DEFINE:([Bitmap1014]))||--Parallelism(Repartition Streams, PARTITION COLUMNS:([g].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',isnull([g].[Email], ''), NULL)>1))|--Parallelism(RepartitionStreams, PARTITION COLUMNS:([r].[GuestId]),WHERE:(PROBE([Bitmap1014])=TRUE))|--Clustered IndexSeek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWAny ideas of what I can check for?Thanks for any help.
I trying to find a way to find what the SPID of a given job that is running. I am trying to create a script that will give me the SPID the JOB_ID, and JOB_NAME. The problem comes in that if I use sysprocesses I have to pull the JOB_ID from program_name in sysprocesses and convert it into something then join it to sysjobs. Have not been sucessfull in that conversion. Any Ideas
I have an old backup file that I am pretty sure contains a table that I am looking for. Is there a way to verify what exactly a backup file contains without restoring it to a test server?
I have 3 tables, Items, Brands and Categories. Brands and Categories consists of a primary key as well as the Brand/Category name. Items has a bunch of columns as well as two foreign key fields, one for Brands and the other for Categories.I want a SELECT statement that would select each Brand(id and name) as well as the associated categories for each brand (i want this info the menu control) if this was a one-to-many relation, i wouldn't have had any problem... but since this is basically a many-to-many relation, i just can't figure it out
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
I am a serious NT sql NEWBIE. I have installed v7 on my NT/MIIS. I want to allow another party now to create tables on their SQL db they have runnning on my server (their own domain)
What is the best way -? Do they send me the scripts or is there a way to run them remotely and create them.
Hi! I am using VS 2003 (v 1.1) I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net. How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation. Give me the complete code with can example (if possible)
I was just looking at the AdventureWorks Database Sample and I see the Tables and the Procedures have different prefixes other than dbo. How can I create Tables that use different prefixes as I would like to organize my tables better. Thanks in advance.
Hello,In my web site I have 2 types of users: students and professors.All users need to login but the personal information for each user type has different fields.So I create 3 tables: professors, students and authentication,"professors" will have all the personal data from professors."students" will have all the personal data from students.authentication will have the username and password from all users.How can i link authentication table to both professors and students tables so when the user logins i get the data from that user, either a professor or student?And is the right way to do this?I am using SQL 2005 and Asp.Net 2.0Thanks,Miguel
I know this may sound stupid but I really can't figure out how to do it. I just keep going around in cricles.
I need to create six table under three different users. so I would have users1 owning tableA and tableB. Then I would have users2 owning another copy of tableA and tableB. same for users3.
But I just can't find a way of doing it, I am using enterprise manager. Please help, Ed
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables. Is there a solution for the version 2000?
I'm attempting to use SQL statement to create tables within a new database, and I'm somewhat lacking in experience for this. Normally I'd simply create them through the GUI, but I want to get the hang of DDL statements.
I have written what I think are scripts to create one table with a primary key, create a unique index on it, and then create another table with a primary key and constrain two columns in the second table to the non-primary unique index in the first.
Code: Use SKUTracking CREATE TABLE SKUTracking.dbo.tblOraUnits
Hello all; I'm so new in NHibernate and I wonder that "can nhibernate create tables into my db"? If it can; how? Which confugrations are needed? Thanks...
Im trying to bring together two tables into one, but they're records dont always match, so im getting akward returned records. So now i want to combine the fields i need from these two tables to create a new one. How would that be coded.
I want to
SELECT
[Customer_Bill_Customer_No],
[Customer_Bill_Customer_No],
[Sales_Header_Order_DateTime],
[Sales_Header_Your_Reference]
from
[NavisionReplication].[dbo].[Tbl_Sales_Header]
and SELECT [Sales_Invoice_Header_No]
FROM [NavisionReplication].[dbo].[Tbl_Sales_Invoice_Header]
I'm trying to create two tables, Admins and SecurityGroups, I want to have the ability to assign a security group to an admin so that I don't have to specify all the rights individually to every admin. So, I have the securityGroupID in the Admins table and I reference the SecurityGroups table for data integrity. On the other hand, for audit purposes, I also want to record which admin created the security group so I have the AdminID in the SecurityGroups table and I reference the Admins table for data integrity. Of course when I try to create any of those tables I get an error message that the other table doesn't exist. I wonder if this can be done without having to remove the references constraint from one of the tables and then using alter table. Here's an example below to make it clearer:
CREATE TABLE Admins ( AdminID INT NOT NULL PRIMARY KEY, SecurityGroup NULL REFERENCES SecurityGroups (SecurityGroupID) )
CREATE TABLE SecurityGroups ( SecurityGroupID INT NOT NULL PRIMARY KEY, CreatedBy INT NOT NULL REFERENCES Admins (AdminID) )
I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:
public void CreateTypedLiterals(Type type) { String sql = String.Format( "CREATE TABLE [Literals_{1}] (" + "ID int DISTINCT NOT NULL, Value {1})",
// BUG: does not work // WARNING: introduces a potential sql-injection problem type.ToString() );
...
As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:
[SqlProcedure] public void CreateTypedLiterals(Type type) { DataTable TypedLiterals = new DataTable( String.Format("Literals_{0}", type.ToString())); TypedLiterals.Columns.Add( "ID", typeof(int), "DISTINCT NOT NULL"); TypedLiterals.Columns.Add("Value", type);
...
But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :
Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:
Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID
With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores ( courseId varchar(20) NOT NULL CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId) REFERENCES courseStructure (courseId), studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId) REFERENCES students (studentId), CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId) )
I have install the MSDE from Ofiice 2000 cd and my MSDE sql server is running fine that I can see from my tray bar with green arrow button. Now i want to create the database so can any body tell me how do i create database. Is there ne query analyser or enterprise manager by which I can create database. Also when i try to install starterkit it doen not allow me to install it. It ask me to select database from the dropdown list and when I select Localhost (only option available) and click on test connection it gives me error UnsuccessfullI am new at MSDN so please help me.
Hi All, 1st of all happy New Year to all asp.net forum members I am new at asp.net. I want to design a website using asp.net as frontend and sql database as backend. I am able to connect and add,update as well delete records when I use MsAcess and Asp.net using the following connection strings... ********** sub Page_Load dim dbconn,sql,dbcomm,dbread dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("/database/northwind.mdb")) dbconn.Open() sql="SELECT * FROM customers where city LIKE 'Berlin' order by city ASC" dbcomm=New OleDbCommand(sql,dbconn) dbread=dbcomm.ExecuteReader() customers.DataSource=dbread customers.DataBind() dbread.Close() dbconn.Close() end sub **********
But when I try to connect to sql database using the following connection strings I am unable to do so...
************** SqlConnection myConnection = new SqlConnection("server=PLATINUMVSdotNET;database=pubs;Trusted_Connection=yes"); SqlDataAdapter myCommand = new SqlDataAdapter(" * from Authors", myConnection);
DataSet ds = new DataSet(); myCommand.Fill(ds, "Authors");
************** I have written the servername as "PLATINUMVSdotNET" because when I installed SQL SERVER 2000 I found a tray icon where the server name was displaying the same (my computer name is PLATINUM). When I used webmatrix I enterd the same server name and windows authentication I was able to create a database but How to create table...
Player, Position, [From], [To], Fee, Type, ID, League, Window
I want to create a new table, EnglandFinal with all the data from the three tables although I'm guessing it would not be a good idea to copy the primary keys (ID column) as they would clash.
I have played around with CREATE and INSERT into and UNION but I get various errors. I'm sure I've done this before!
