Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View Complete Forum Thread with Replies
Related Forum Messages:
How To Find Missing Records From Tables Involving Composite Primary Keys
Table 1 Code Quarter 500002 26 500002 27 500002 28 500002 28.5 500002 29 Table 2 Code Qtr 500002 26 500002 27 I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS Can anybody help me with how to compare the two tables to find the records not present in Table 2 That is i need this result Code Quarter 500002 28 500002 28.5 500002 29 I have come up with this solution select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2) i need to know if there is some other way of doing the same Thanks in Advance Jacx
View Replies !
Auto Incremented Integer Primary Keys Vs Varchar Primary Keys
Hi, I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key. For example: id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null] isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime. Regards Mike
View Replies !
Order Of Records In The INSERTED/DELETED Tables In A Trigger
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding. Thanks! CB
View Replies !
How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View Replies !
Find Missing Records In Identical Tables
Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table. Any help would be great, cheers.
View Replies !
Comparing Data In Two Tables To Find Missing Records
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Return Missing Records Over Multiple Tables. Query Challenge!
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID. I need to find out which ones are in the second and third table that do not show up in the first one, which ones are in the first and third but not in the second, and which ones are in the first and second but not in the third. I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this. Here is an example: create table TBL1(PID int, info1 varchar(10) ) Create table TBL2(TID int,PID int) Create table TBL3(XID int,PID int) insert into TBL1 select '1','Someone' union all select '2','Will ' union all select '4','Have' union all select '7','An' union all select '8','Answer' union all select '9','ForMe' insert into TBL2 select '1','1' union all select '2','1' union all select '3','8' union all select '4','2' union all select '5','3' union all select '6','3' union all select '7','5' union all select '8','9' insert into TBL3 select '1','10' union all select '2','10' union all select '3','8' union all select '4','6' union all select '5','7' union all select '6','3' union all select '7','5' union all select '8','9' I need to find the PID and the table it is missing from. So the results should look like: PID MISSING FROM 1 TBL3 2 TBL3 3 TBL1 4 TBL2 4 TBL3 5 TBL1 6 TBL1 6 TBL2 7 TBL2 10 TBL1 10 TBL2 Thanks all.
View Replies !
Fix Legacy Data - Missing Primary Key + Duplication Record + Large Table
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table" Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
View Replies !
Table Scan On Inserted And Deleted Tables?
Hello, I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though. Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell. Thanks for any help! -Tom. UPDATE H_MyTable SET HIST_END_DT = @tran_date FROM H_MyTable his INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and (his.PrimaryKey2 = del.PrimaryKey2) INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and (his.PrimaryKey2 = ins.PrimaryKey2) WHERE (his.HIST_END_DT is null) and ((IsNull(del.PrimaryKey1, -918273645) <> IsNull(ins.PrimaryKey1, -918273645)) or (IsNull(del.PrimaryKey2, -918273645) <> IsNull(ins.PrimaryKey2, -918273645)) or (IsNull(del.Column3, -918273645) <> IsNull(ins.Column3, -918273645)))
View Replies !
Problem With Coping Primary Key Into Other Field In The Same Table
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance .. savvy
View Replies !
Get Primary Key Of Last Inserted Record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
View Replies !
Setting Primary Key Of New Row Being Inserted
Not sure if this is the right place for this post or not, but I'll give it a try... If I am inserting a record into a table that uses a Primary Key, but not an Identity column, how do I insert the new primary key value? I started out with SQL just letting the Identity column increment itself for primary keys, but I cannot do that for this particular situation. If 'AddressID' is the primary key in the 'Address' table, could I do something like this: INSERT INTO Address (AddressID, Field1, Field2, ...) VALUES (**max value of primary key in Address table, plus 1**, 'field1data', 'field2data', ...); If that works, how would I make this part of the insert query: **max value of primary key in Address table, plus 1** ? I hope this question is clear and makes sense. If it's not, please let me know.
View Replies !
How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database? My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e) 2 { 3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text; 4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue; 5 try 6 { 7 srcAddSkill.Insert(); 8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score."; 9 lblErrorMessage.Visible = true; 10 txtSkillName.Text = ""; 11 } 12 catch (Exception ex) 13 { 14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message; 15 lblErrorMessage.Visible = true; 16 }
View Replies !
How To Join Two Tables Having No Common Coloumns And Having No Primary Key Fields In Any Of The Table
hi friends i am reading a csv file and storing the data in table i have created the tables like this: coloumn names datatype constraints start_date_time varchar(50) allownull = true ani varchar(50) allownull=true dialed_digit varchar(50) allownull=true actual_dur varchar(50) allownull=true rounded_dur varchar(50) allownull=true cost varchar(50) allownull=true and the second table as coloumn names datatype constraints country varchar(50) allownull = true code varchar(50) allownull=true rate/min varchar(50) allownull=true now i want to relate and join the table so that i can create a view with the following coloumns such as start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
View Replies !
Need Help W/ SELECT From One Table, One Field, Multiple Unique Records
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so: Anna Smith John Doe etc. I did not see IDs listed for any of the names in the table when I looked. There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually. Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them: strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC" This gives me the total list but I only want to bring back 6 of the 20 for the pulldown. Is there a way to modify this statement to pull only the records that I want? Thanks for any help you can give. AJ
View Replies !
Loading Records In One Table To Multiple Tables
Data_Staging: Unique_id Gender Ethnicity Race MCP_key Admission_Dt Discharge_Date Enrollment_key Reason Disability Income Employment I need to load the data from this table to three different tables all have foreign key relationship Registration Table: Registration_key ( Indetity) -PK Unique_id Gender Ethnicity Race Episode: Episode_Key(Identity)- PK Registration_key (FK) MCP_key Admission_Dt Discharge_Date Assessment Table: Assessment_Key(Identity) €“ PK Registraion_Key(FK) Episode_Key(FK) Enrollment_key Reason Disability Income Employment
View Replies !
Help! Trigger Using "inserted" Not Inserting Some Records.
Hello all, I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly: EventName EventStatusID NumberofDays NumberofStores PreferredDate1 PleaseContactFlag EventStoreSelection EventClusterID Note: The following fields have their Default Value set to (0) SystemID EventStatusID Coop NewProductFlag TVSupportFlag RadioSupportFlag FSISupportFlag RollbackPricing PleaseContactFlag Default Value set to (1): KitInformationID Here is the trigger: CREATE TRIGGER EmoesImport ON mc_events FOR INSERT AS IF @@ROWCOUNT<>0 BEGIN SET IDENTITY_INSERT mcweb2.dbo.mc_events ON DECLARE @SystemID int DECLARE @EventID int DECLARE @AccountID int DECLARE @BillingContactID int DECLARE @EventName varchar(100) DECLARE @EventStatusID tinyint DECLARE @Coop bit DECLARE @CoopSupplier varchar DECLARE @SamplesPerDay int DECLARE @BrochuresPerDay int DECLARE @AverageDailyMovement int DECLARE @SalesGoal int DECLARE @NumberofDays int DECLARE @NumberofHours int DECLARE @NumberofStores int DECLARE @WeekNumber tinyint DECLARE @PreferredHourID tinyint DECLARE @PreferredHourother char(20) DECLARE @PreferredDate1 varchar(20) DECLARE @PreferredDate2 varchar(20) DECLARE @NewProductFlag bit DECLARE @TVSupportFlag bit DECLARE @RadioSupportFlag bit DECLARE @FSISupportFlag bit DECLARE @RollbackPricing bit DECLARE @PleaseContactFlag bit DECLARE @EventStoreSelection tinyint DECLARE @EventClusterID int DECLARE @KitInformationID tinyint DECLARE @KitDescription varchar(1000) DECLARE @KitOther varchar(200) DECLARE @MCProgNum varchar(7) DECLARE @rowguid uniqueidentifier SELECT @SystemID = SystemID FROM INSERTED SELECT @EventID = EventID FROM INSERTED SELECT @AccountID = AccountID FROM INSERTED SELECT @BillingContactID = BillingContactID FROM INSERTED SELECT @EventName = EventName FROM INSERTED SELECT @EventStatusID = EventStatusID FROM INSERTED SELECT @Coop = Coop FROM INSERTED SELECT @CoopSupplier = CoopSupplier FROM INSERTED SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED SELECT @SalesGoal = SalesGoal FROM INSERTED SELECT @NumberofDays = NumberofDays FROM INSERTED SELECT @NumberofHours = NumberofHours FROM INSERTED SELECT @NumberofStores = NumberofStores FROM INSERTED SELECT @WeekNumber = WeekNumber FROM INSERTED SELECT @PreferredHourID = PreferredHourID FROM INSERTED SELECT @PreferredHourother = PreferredHourother FROM INSERTED SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED SELECT @NewProductFlag = NewProductFlag FROM INSERTED SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED SELECT @RollbackPricing = RollbackPricing FROM INSERTED SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED SELECT @EventClusterID = EventClusterID FROM INSERTED SELECT @KitInformationID = KitInformationID FROM INSERTED SELECT @KitDescription = KitDescription FROM INSERTED SELECT @KitOther = KitOther FROM INSERTED SELECT @MCProgNum = MCProgNum FROM INSERTED SELECT @rowguid = rowguid FROM INSERTED INSERT INTO mcweb2.dbo.mc_events (SystemID, EventID, AccountID, BillingContactID, EventName, EventStatusID, Coop, CoopSupplier, SamplesPerDay, BrochuresPerDay, AverageDailyMovement, SalesGoal, NumberofDays, NumberofHours, NumberofStores, WeekNumber, PreferredHourID, PreferredHourother, PreferredDate1, PreferredDate2, NewProductFlag, TVSupportFlag, RadioSupportFlag, FSISupportFlag, RollbackPricing, PleaseContactFlag, EventStoreSelection, EventClusterID, KitInformationID, KitDescription, KitOther, MCProgNum, rowguid) VALUES (@SystemID, @EventID, @AccountID, @BillingContactID, @EventName, @EventStatusID, @Coop, @CoopSupplier, @SamplesPerDay, @BrochuresPerDay, @AverageDailyMovement, @SalesGoal, @NumberofDays, @NumberofHours, @NumberofStores, @WeekNumber, @PreferredHourID, @PreferredHourother, @PreferredDate1, @PreferredDate2, @NewProductFlag, @TVSupportFlag, @RadioSupportFlag, @FSISupportFlag, @RollbackPricing, @PleaseContactFlag, @EventStoreSelection, @EventClusterID, @KitInformationID, @KitDescription, @KitOther, @MCProgNum, @rowguid) SET IDENTITY_INSERT mcweb2.dbo.mc_events OFF END TIA, Bruce Wexler Programmer/Analyst IT Department Mass Connections Ph: (562) 365-0200 x1091 Fx: (562) 365-0283 http://www.massconnections.com
View Replies !
Records Being Inserted Into Gaps Of Identity Col
I have a table with the following specifications: [FutureArticleId] [bigint] IDENTITY(1,1) NOT NULL, [cFutureArticleTitle] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [gCategoryId] [bigint] NOT NULL, [cKeyword] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [bIsDeleted] [bit] NOT NULL CONSTRAINT [DF_tbl_FutureArticle_bIsDeleted] DEFAULT ((0)), [tOnCreated] [datetime] NOT NULL, [tOnUpdated] [datetime] NULL, [gCreatedBy] [bigint] NOT NULL, [gUpdatedBy] [bigint] NULL, After some insertions and deletion from the UI, obviously there are gaps in the FutureArticleId column which is an identity column. However, sometimes while inserting the records the records are being inserted into the gap and not in the next available Identity value. Is there any table setting which I need to do, to stop this. Please let me know Thanks Ankit
View Replies !
Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
Hi all, I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations: -- ImportCSVprojects.sql -- USE ChemDatabase GO CREATE TABLE Projects ( ProjectID int, ProjectName nvarchar(25), LabName nvarchar(25) ); BULK INSERT dbo.Projects FROM 'c:myfileProjects.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================= -- ImportCSVsamples.sql -- USE ChemDatabase GO CREATE TABLE Samples ( SampleID int, SampleName nvarchar(25), Matrix nvarchar(25), SampleType nvarchar(25), ChemGroup nvarchar(25), ProjectID int ); BULK INSERT dbo.Samples FROM 'c:myfileSamples.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ========================================= -- ImportCSVtestResult.sql -- USE ChemDatabase GO CREATE TABLE TestResults ( AnalyteID int, AnalyteName nvarchar(25), Result decimal(9,3), UnitForConc nvarchar(25), SampleID int ); BULK INSERT dbo.TestResults FROM 'c:myfileLabTests.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE. 2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables? Please help and advise. Thanks in advance, Scott Chang
View Replies !
Autogenerating Numbers For A Primary Key Field, &&"studyId,&&" In A Table--but With A Few Twists.
I have a question on autogenerating numbers for a primary key field, "studyID," in a table€”but with a few twists. We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001). Here€™s the range of values we want our StudyIDs to be (this is to be our validation rule as well): 10001-19999 for Site 1 20001-29999 for Site 2 Your suggestions are VERY VERY WELCOME! THANKS!
View Replies !
Get The Last Inserted Uniqueidentifier Field
As you know we can get the last identity by using these functions:@@IDENTITY, IDENT_CURRENT( '[Table_Name]' ) And More.But if I set my primary key datatype to uniqueidentifier, I Can't get the last GUID inserted by SQL.Anyone have any idea?Reach me at my blog: http://ramezanpour.spaces.live.com
View Replies !
Missing Primary Keys
HI Folks , I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help Burner
View Replies !
Duplicate Records Are Being Inserted With One Insert Command.
This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George
View Replies !
Modifying Inserted Or Updated Records Without Recursion?
I am looking for a way to update the information in the "inserted" logical record without having to call something like this: UPDATE tblX SET ValueY = ValueA/100 FROM tblX INNER JOIN inserted ON tblX.ID = inserted.ID because this may result in the update trigger firing (again). I'd like to avoid this. As a better example, if I have a table of transactions with cost and price info, plus a flag indicating whether the transaction has been merged to AR or GL, I want to be able to update cost or price directly, which will clear the flag and indicate that I have a batch out of balance. This I can do easily with Update(Cost) Or Update(Price). But, I also need to be able to change the supplier in the transaction record. If I do this, I want the trigger to fetch the new cost and price for me. If the cost or price change, I still want to update the flag. To get there I can call UPDATE tbl SET Cost = @EffectiveCost, Price = @EffectivePrice FROM tbl INNER JOIN inserted ON tbl.ID = inserted.ID which is recursive. I can't say "UPDATE inserted" because it's a logical table. Is there any way to avoid this and to set values in the middle of an insert/update?
View Replies !
Replicatioin - Primary Key Icon Missing!
I am performing replication from a Publisher to Subscriber Server. Both Servers are running NT 4.0, SP3, SQL Server 6.5 SP5a. The Servers are set up in a Workgroup. I noticed that when I do an Edit on the table that was copied to the Subscriber Server that I do not see the Icon that indicates a field is a Primary Key. All fields are being replicated. The Primary Key indicator is just not there. How do I get this Primary Key Icon to automatically appear on the replicated table? Thanks in advance, Kevin
View Replies !
Log Shipping - Backup Job Missing On Primary
Hi everyone, I'm trying to configure log shipping on a 2005 sql server. I follow the wizard's instructions (see http://technet.microsoft.com/en-us/library/ms190640.aspx) and everything looks right except for the backup job that somehow is not being created on the primary server. Secondary server contains copy, restore and alert jobs. No errors reported on job monitor. Any ideas? Regards,
View Replies !
Maximum Number Of Records Per Second That Can Be Inserted Into SQLServer 2000.
Summary: Maximum number of records per second that can be inserted intoSQLServer 2000.I am trying to insert hundreds (preferably even thousands) of recordsper second in to SQLServer table (see below) but I am getting thefollowing error in the Windows Event Viewer Application log file:"Insufficent Memory......"And very few records were inserted and no errors where sent back viathe JDBC.By removing the indexes on the table we have stopped getting the errormessage and have managed to load the table at 300 records per second.However I have couple of questions:1) Are the indexes definitely to blame for this error and is thereanyway of getting around this problem i.e. keeping the indexes in placewhen inserting?2) How should I configure SQLServer to maximise the speed ofinserts?3) What is the limiting factor for inserting into SQLServer?4) Does anyone know of any metrics for inserting records? At wantpoint should we consider load balancing across DBs.I currently populate 1.6 million records into this table. Once againthanks for the help!!CREATE TABLE [result] ([id] numeric(20,0) NOT NULL,[iid] numeric(20,0) NOT NULL,[sid] numeric(20,0) NOT NULL,[pn] varchar(30) NOT NULL,[tid] numeric(20,0) NOT NULL,[stid] numeric(6,0) NOT NULL,[cid] numeric(20,0) NOT NULL,[start] datetime NOT NULL,[ec] numeric(5,0) NOT NULL,)GOCREATE INDEX [ix_resultstart]ON [dbo].[result]([start])GOCREATE INDEX [indx_result_1]ON [dbo].[result]([id], [sid], [start], [ec])GOCREATE INDEX [indx_result_3]ON [dbo].[result]([id], [sid], [stid], [start])GOCREATE INDEX [indx_result_2]ON [dbo].[result]([id], [sid], [start])GO
View Replies !
Tables Joined On Multiple Columns, Exclude Records Found In Table A
I'm using SQL server 200 Table A has columns CompressedProduct, Tool, Operation Table B in a differnt database has columns ID, Product, Tool Operation I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables. But I want to be able to select any records that are in table A but not in Table B. ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear. This code return all the records from A. I need to filter out the records found in Table B. --------------------------------------------------------------------------------------------------------------------------------- SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
View Replies !
Clipping Data Inserted Into NText Field
I'm having a major problem. I'm executing an sql statement to insert data into an nText field. It's clipping the text though. I haven't seen any patterns yet. Example "Welcome to the show.", it would save "Welcome to t". And it's not all the time. Please Help. Thanks for your time, Randy
View Replies !
Can I Debug/watch On The Trigger's INSERTED And DELETED Records/values?
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
View Replies !
Report Model Problem: Missing Primary Key
Hi, I'm trying to create a report model but stumpled upon a little problem. Some of the tables that I'm using does not have any column that can be made into a primary key (all of the columns contains duplicate values). This causes some headaches since the tables won't get generated to the model. Is there some way around this? Perhaps create a "fake" PK column that works similar to an id column. Any help is highly appreciated. Kind Regards, Blixt
View Replies !
Trigger Problem With "inserted" And "deleted" On Text Field
I use SQL Server 6.5 I have create a trigger on UPDATE for history. This history send the old value and the new value in a table to follow all the modifications. Those values are "text datatype". When I execute the trigger (When I make a modification in the table), the two values are the same (the new value). If somone have a idea ... CREATE TABLE dbo.HELPDB ( ALMID int NOT NULL , MEMO text NULL , FIRSTAID text NULL ) GO CREATE TRIGGER trg_UpdateHelpDB ON dbo.HELPDB FOR UPDATE AS DECLARE @Id integer DECLARE @alm integer DECLARE @user varchar(20) DECLARE @almname varchar(24) DECLARE @usergroupproprid integer DECLARE @usergroupproprname varchar(30) DECLARE @oldFirstAid varchar(255) DECLARE @newFirstAid varchar(255) if(UPDATE(FIRSTAID)) begin select @alm = ALMID, @oldFirstAid = CONVERT(varchar(255), FIRSTAID) from deleted select @newFirstAid = CONVERT(varchar(255), FIRSTAID) from inserted Exec AMX.dbo.SpGetNewId 'HISTALM', @Id OUTPUT select @user = "testuser" select @almname = "testalmname" select @usergroupproprname = "testusergroupproprname" INSERT HISTALM (ID, ALMID, DATETIME, USERLOG, STATUS, ALMNAME, USERGRPPROPR,USERGRPOWNER, EVENTTYPE, OLDVALUE, NEWVALUE) VALUES(@Id, @alm, getdate(), @user, "First Aid", @almname, @usergroupproprname, "/", 0, @oldFirstAid, @newFirstAid) END GO CREATE TABLE dbo.HISTALM ( ID int NOT NULL , ALMID int NOT NULL , DATETIME datetime NOT NULL , USERLOG varchar (30) NOT NULL , STATUS varchar (25) NOT NULL , ALMNAME varchar (24) NOT NULL , USERGRPPROPR varchar (30) NOT NULL , USERGRPOWNER varchar (30) NOT NULL , EVENTTYPE int NOT NULL , OLDVALUE varchar (255) NULL , NEWVALUE varchar (255) NULL ) GO
View Replies !
For 2 Separate Tables, Can You Tell Which Row Was Inserted First?
I insert a row into Address table at 1:00 I insert a row into ZipCode table at 1:01 Is there an internal timestamp or a universal GUID across the entire database where I can tell which row was created FIRST from 2 separate tables? Within one table it is possible as there is a ROWGUILDCOL but within 2 separate tables, is there to identify which row was inserted FIRST???? Thanks Angel
View Replies !
Inserted And Deleted Temp Tables ??
I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables. I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages. The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced. Thanks in advance.
View Replies !
|
TRACKER
