Referencing A Users Table For History
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Connecting Users From ASPNETDB.MDF To A Shopping Cart And Order History
I'm trying to figure out how to associate users in my ASPNETDB.MDF to create a shopping cart.IE: I have 3 tables, for a list of existing orders, list of products, and list of specific orders.The idea is for users who've signed up with the built-in user creation control, can then place orders from the shopping cart, and view their existing previous orders. This would normally be done with a unique UserID # to associate orders with specific users, however I don't BELIEVE there's anything like UserID's associated with each user in the ASPNETDB.MDF, it just uses usernames as the unique identifiers right?Anyway I'm not sure I'm explaining what I'm trying to do perfectly and I apologize, but I don't think it's an un-common problem I'm having. Basically I'm trying to avoid having to have existing ASPNETDB users create a sort of SECOND user that would associate them into a seperate Customers table, it seems like extra user steps that shouldn't be necessary.
View Replies !
View Related
Delete Rows In One Table By Referencing Another Table Info
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table. Here is a basic breakdown of what I am trying to do: Table1 (the table where the rows need to be deleted from) Column_x (Holds the id that is unique to the various rows of data - User ID) Table2 (Holds the user information & has the associated ID) Column_z (holds the User ID) I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2. Code: SELECT Column_z FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z <> Column_x This did not seem to do what I needed, it did not delete any rows at all. I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2 Then I tried another scenerio that I also needed to do: Code: SELECT Column_z, Column_a FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z = Column_x AND Column_a='0' '0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2. Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected. Any ideas on what I am doing wrong here?
View Replies !
View Related
Self-referencing Table
Hi, I'm using MS SQL 2005 Express. CREATE TABLE Folder ( iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY, folderName varchar(50) NOT NULL, parentFolderID int NULL FOREIGN KEY REFERENCES Folder (iD) ) GO if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted. The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.' Anyone got any advice?
View Replies !
View Related
Updating Table Referencing 2nd Table Using Case
Hi Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong... Using T-sql!!! update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END Any help would be greatly appreciated
View Replies !
View Related
Inserting Data Into A Table Referencing PK From Another Table
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event Schedules data is entered into the Schedules Table first then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table. How do i insert data into Event table referencing the (PK ID) from Schedules Table ? Fields inside each of the tables can be found below: Event Table (PK,FK) ScheduleID EventTitle AccountManager Presenter EventStatus Comment Schedule Table (PK) ID AletrnateID name UserID UserName StartTime EndTime ReserveSource Status StatusRetry NextStatusDateTime StatusRemarks
View Replies !
View Related
Select Self Referencing Table
I have a table that holds a ParentID and the RecordID. There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not. 0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if ' 1- 2 ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J
View Replies !
View Related
Referencing A Table By Variable
I would like a stored proc to be fed the table name as a paramater. I tried below create procedure testit as set nocount on declare @tblnm varchar(50) select * from @tblnm but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5 Line 5: Incorrect syntax near '@tblnm'. Any idea
View Replies !
View Related
Referencing A Lookup Table In A View
I am creating a view that consists of 4 tables. Within the 4 tables I have 15 lookup columns that are storing tinyints that map to the lookup table. I have tried two ways to display the actual business text within the view. 1) Using a CASE statement within the select portion. This is slow and it is not dynamic. 2) Using an alias for each lookup column within the view. This is dynamic but I then have 19 joins. Does anyone know of a way to scan the lookup table once to retrive the lookup text values? There has got to be a better way then 19 joins...There killing me...And the business people want the joins because the views are then maitenance free with regards to changing lookup text. Thanks for any help in advance. SFogli
View Replies !
View Related
Referencing A Field In Another Table Within A SELECT
I have a SELECT line in an UPDATE query that looks like this: UPDATE Terms SET Field1 = LEFT(Emp_SSN,9), Field2 = Health, Field3 = (SELECT Emp_PayCode FROM Rates WHERE Health = ID) FROM Inserted,Rates WHERE Terms.Field1 = Emp_SSN On the Field3 update, how do I evaluate Emp_PayCode. Emp_PayCode is a field in another table (Employees). The value of that field is Instructional24 and if I hard code that value into the SELECT line, it works fine and returns the value ($0.00).
View Replies !
View Related
Instead Of Update Trigger W/ Self-Referencing Table
Setup: Using SQL 2005 TableA ID INT PK Name VARCHAR RefID FK ID Name RefID 1 Test1 <NULL> 2 Test1a 1 3 Test1b 1 4 Test1b1 3 5 Test1b1a 4 CREATE TRIGGER Update_ID ON TableA INSTEAD OF UPDATE AS IF UPDATE(ID) BEGIN DECLARE @old_ID INT DECLARE @new_ID INT SELECT @old_ID = ID FROM DELETED SELECT @new_ID = ID FROM INSERTED UPDATE TableA SET ID = @new_ID WHERE ID = @old_ID UPDATE TableA SET RefID = @new_ID WHERE RefID = @old_ID END SPROC: UPDATE TableA SET ID=6 WHERE ID=2 RefID is the FK to ID ID is also the PK to another table and has the relationship use cascading Delete and Update. Problem: "The UPDATE statement conflicted with the SAME TABLE REFERENCE constraint" This is referring to the first UPDATE of the Trigger. It was my understanding that Instead Of checks the constraints after it is all done yet it errs with the first Trigger UPDATE. I was expecting to overcome the self-reference constraint issue by using Instead Of and with the first Trigger UPDATE, change the ID(PK) and then change the RefID(FK) with the second Trigger UPDATE. Once that was done, it should have not had any constraint problems. Thoughts? Thanks, Nathan
View Replies !
View Related
Using Employee/Boss Self Referencing Table
I have an Employee table that has EmployeeID (PK) SupervisorID (which is really just another EmployeeID) ..random junk... Now that part makes sense, everyone gets one and only one boss. Their boss can change, and therefore the SupervisorID would be updated. Now I have an EmployeeEvals table that has quarterly evaluation data. I want to relate these two tables. Eval table has EvalID (PK) ReviewedEmployeeID (the one being evaluated) SupervisorID (the one doing the evaluation) Now I need to link this back to the employee table (at least I think I do). So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same... But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like EvalID: 12345 ReviewedEmployeeID: 55 SupervisorID: 55 which of course wouldn't happen as an employee wouldn't evaluate themself. How do I handle the relationships for this properly? Do I just not link the SupervisorID back to anything?
View Replies !
View Related
SQL Server Table's Column Referencing Two Parent Tables.
Hi everybody, I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
View Replies !
View Related
Retrieve Manager/Subordinate Hierarchy From Self-Referencing Table
Hi SQL GurusCould anyone please explain how the following stored procedure can beamended to retrieve Subordinates in alphabetical order ?The example below simply retrives records in the order in which theywere entered.It sounds very easy but I can't sort it out ?The following code was taken from Narayana Vyas Kondreddi's website(http://vyaskn.tripod.com/index.htm)Consider the employee table of an organization, that stores all theemployee records. Each employee is linked to his/her manager by amanger ID.CREATE TABLE dbo.Emp(EmpIDintPRIMARY KEY,EmpNamevarchar(30),MgrIDintFOREIGN KEY REFERENCES Emp(EmpID))GOCREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)GOINSERT dbo.Emp SELECT 1, 'President', NULLINSERT dbo.Emp SELECT 2, 'Vice President', 1INSERT dbo.Emp SELECT 3, 'CEO', 2INSERT dbo.Emp SELECT 4, 'CTO', 2INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6INSERT dbo.Emp SELECT 12, 'Tester 1', 11INSERT dbo.Emp SELECT 13, 'Tester 2', 11INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7INSERT dbo.Emp SELECT 18, 'Tester 3', 17INSERT dbo.Emp SELECT 19, 'Tester 4', 17INSERT dbo.Emp SELECT 20, 'Tester 5', 17GOCREATE PROC dbo.ShowHierarchy(@Root int)ASBEGINSET NOCOUNT ONDECLARE @EmpID int, @EmpName varchar(30)SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNameSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)WHILE @EmpID IS NOT NULLBEGINEXEC dbo.ShowHierarchy @EmpIDSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root ANDEmpID > @EmpID)ENDENDGOEXEC dbo.ShowHierarchy 1GO---President------Vice President---------CEO---------CTO------------Group Project Manager---------------Project Manager 1------------------Team Leader 1---------------------Software Engineer 1---------------------Software Engineer 2------------------Test Lead 1---------------------Tester 1---------------------Tester 2---------------Project Manager 2------------------Team Leader 2---------------------Software Engineer 3---------------------Software Engineer 4------------------Test Lead 2---------------------Tester 3---------------------Tester 4---------------------Tester 5
View Replies !
View Related
Problem Referencing A Column In A Table Variable In A Query.
Hello All I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc. declare @THeader TABLE ( TransactionHeaderID [int] NOT NULL , PatientID [int] NOT NULL , TransactionAllocationAmount [money] NOT NULL , ApplyAmt [money] NULL ) - create table variable insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable UPDATE @THeader set TransactionAllocationAmount = (SELECT isnull(Sum(mtblTransactionAllocation.Amount),0) FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable Update @THeader set ApplyAmt = (SELECT mtblTransactionAllocation.Amount FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable - below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt UPDATE mtblTransactionHeader SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount, mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt FROM @THeader, mtblTransactionHeader WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table Thanks in advance smHaig
View Replies !
View Related
Visual Studio Closes When Accessing The Columns Tab Of A Lookup Transformation Referencing SQL 7 Table
I'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems. Is this supposed to happen? My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro. Microsoft SQL Server Integration Services Designer Version 9.00.1399.00 The server that doesn't work for a reference table is running Windows 2000 Server SP4 SQL 7.00.623 Thanks for your help, Kirk
View Replies !
View Related
Common Need: History Table
Hi there! I'm working on an application designed like this: There's a table "DailyTransations" (DT) containing daily transactions... Then there's an archive table "TransationsArchive" (TA) with the exact same structure. When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA. Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing. First, what do you think of that implementation? We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically... What is the common solution for this need? Thanks Frantz
View Replies !
View Related
Instances And History In One Table
I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way. Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X. So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time. I want to keep track of which users used a instance of a wheelchair. No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap. If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime. Is this a good or a common practice? Why use it, or stay away from it? Henri ~~~~ There's no place like 127.0.0.1
View Replies !
View Related
History Table Design Issue
Hi all, this is more of a design issue for a History table. Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table. Regards, General Problem
View Replies !
View Related
How To Design The History Table To Be More Efficient?
I am running a website of crossword puzzle and Sudoku games. The website is designed to be: There are 20-30 games onlines each day. Every registered user could play and submit the game to win scores. For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before. To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game. The current solution is: 3 tables are designed for the functions mentioned above. Table A: UserTable --storing usering information, userid Table B: GameList --storing all the game information. Related fields: GameID primary key FinshiedTimes recording how many times the game has been finished Table C: FinishHistory --storing who and when finished the game Related fields: GameID ID of the game UserID ID of the user FinishedDate the time when the game was finshied PS: Fields listed above are only related ones, not the complete structure. Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play. When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score. Existing Problems: With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck. Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?
View Replies !
View Related
New Unique Number In History Table
Hello, I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811 This is the table "People" I have created: |PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes.... Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice. My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order. As always, thanks for the help!
View Replies !
View Related
History Table &> X Days Between Progression
I work for a college and have recently been working on our enquiries and applications process (getting it onto our big enrollment db rather than standalone). It has all been going well but now they have asked for a report of students where it has taken more than x days or weeks to progress to the next stage code. For stage codes they basically follow something like application, guidance interview, programme area interview, conditional/ unconditional offer... Although they could skip a stage code. Any ideas how to do this bearing in mind I can't guarantee them to go to every stage so really I need to look in the history table and find records more than x days apart where one is the next progression date of the other. Hope I explained that ok. history table is similar to: StudentID CourseCode StageDate InputDate Stage
View Replies !
View Related
Complex Stored Procedure On History Table
Hi All, I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this? Thanks in advance : )
View Replies !
View Related
SQL Query History Table To Return Only Active Rows
Given the following data how do I make a SQL query that returns only 1 row per product? The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }). The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created. Example Data: HistoryID ProductID Name Color DateEffective RowTimeStamp (auto-number PK) 1 1 Wheel Red 2/1/2008 2/1/2008 2 1 Wheel Blue 3/5/2008 3/1/2008 3 1 Wheel Orange 3/5/2008 3/2/2008 4 1 Wheel Black 1/1/2010 3/3/2008 5 2 Knob Blue 3/2/2008 3/2/2008 6 2 Knob Green 3/3/2008 3/3/2008 Query should return: 3 1 Wheel Orange 3/5/2008 3/2/2008 5 2 Knob Green 3/3/2008 3/3/2008 The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes. Can anyone help?
View Replies !
View Related
A Basic History Table - Foreign Keys And Deletes
Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when. An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes. If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes. I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table? Thanks, -Dan
View Replies !
View Related
Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries
Hi, My scenario: I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to 1) separate the master (static) data from the flat files, 2) check whether that data is present in the master table, if not then insert that data into the master table 3) If data present then move that existing record to an history table and then update the main master table. All the 7 fields need to be checked to uniquely identify a single record in the master table. How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this. Thanks in advance for your help. Regards, $wapnil
View Replies !
View Related
Updating Daily Information In A History Table (was &"Help-Brainstorming&")
Hi everyone, I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this: date ID A B C D 1/1/65 X Null Null Null Null 1/4/65 X 1 2 3 4 ... 2/25/05 X 2 3 4 5 1/1/65 Y Null Null Null Null 1/4/65 Y Null Null Null Null ... 2/25/05 Y 2 3 4 5 ... The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all? Thank you everyone for the help. shiparsons
View Replies !
View Related
Locking Users Out Of Table
SQL Server 2000, MSDE 2000I have a procedure in my application that I would like only one user at atime to be able to run. Is there a TSQL command I can run that willesentially lock a set of tables so others cannot access the table until theuser is done with the procedure or until the user disconnects from thatsession (in case of a hung app, I would like to lock released)TIA--Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehiclerelated website.http://www.vehiclewebstudio.com
View Replies !
View Related
Nothing Written In &&"View History Table&&"
Hi guys, acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that... Can any one tell's me about this...
View Replies !
View Related
Updating A Users Table In SQL Server
Hello all, I am relatively new to this platform and am trying to update a table of user information and passwords through a series of SQL commands. We import a new file of users every two hours and I would like to have three SQL statements: - One to add new users not currently in Users table - One to delete users no longer in our source file - One to update every field (but one or two) for already existing users. Any help on creating the SQL (as well as where to store it and how to automate it) is greatly appreciated! Thanks!
View Replies !
View Related
How To Store Users Logs In A Table
Hello, In my co-operation we have a database.What I am trying to do is; I want to store all the logs of the users.For example, if a user makes a change on the database I want to store what kind of change does he/she makes.Is it possible to store log infos of users?How can I do that? Thanks, Bugra KORKMAZ
View Replies !
View Related
How To Create A Script For Table Changes For Users
We are converting our application to SQL Server and have multiple installations. As we send updates to our users in the future, what is the best way to update their SQL Table structures without them losing data? Is there a recommended utility I can use to generate the necessary scripts automatically? Any references to walkthroughs,papers, utilities or examples are appreciated!
View Replies !
View Related
Allocating Rows In A Table To Multiple Users
Dear all In our organization there is a call center where operators entry call details and custormer qruery in a table using front end application. For these queries there are multiple no of executive who will reply the query using a front end application. using the application the exectives retrive specific no of records from call center table(if there are unreplied call exists) and reply them. When a excutive retrives specfic no of rows, i want to update a flag in that rows, so that others do not get them. I use the following procedure which is creating dead lock when multiple users execute it simultaneously create procedure get_from_maintab @cnt int as declare @slno int,@curcnt int select @curcnt=1 set rowcount 1 while @curcnt<=@cnt begin begin tran select @slno=serial_no from maintab where getflag='' if @@rowcount=0 begin rollback return end update maintab set getflag='Y' where serial_no=@slno and getflag='' if @@rowcount = 0 begin rollback return end insert into detab select fld1,fld2,fld3 from maintab where serial_no=@slno if @@rowcount = 0 begin rollback return end commit tran select @curcnt=@curcnt+1 end set rowcount 0 return
View Replies !
View Related
How To Send Email Automatically To Users From Table?
Hi, First of all I want to apologize if I am in the wrong forum. I am new to SQL Server. I have a table with a field containing email address from user input. Is there a build-in function or script in SQL Server 2005 that I can send a template of a email message to users? If not in what way can I acheive this? Thanks for all your reply.
View Replies !
View Related
How To Handle Users Hits To Single Table
Hello friends I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more. Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter. And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
View Replies !
View Related
Can An Authorized User Like SA Create A Table Which The Owner Is Any Users Who Use The Application?
Hi, autorized user: sa any user : nuran temporary table: birtablo I need a stored procedure will execute by sa and it will create some required temporary tables for each users. For example table name is birtablo. I mean sa will create table for nuran, and when I checked the owner of the table (birtablo) I want to see nuran not dbo. sa will execute following command: create table nuran.birtablo (...........) Is it possible to cerate a table by sa on behalf of any user? If it is, could you please explain? Thanks Nuran
View Replies !
View Related
|
TRACKER
