How To Avoid PrimaryKey Duplication?
Hi, I having a problem with my query...
I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...
I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...
What i'm doing is like this:
Use osa (Destination Database)
Go
DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)
My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.
How is this possible sir? Anyone care to help? Thanks in advance! More Power!
Best Regards
View Complete Forum Thread with Replies
Related Forum Messages:
PrimaryKey
Hi, Got a question. I have a script that create a table-- I need Col1, Col2 and Col3 to have PrimaryKey. How do u do. Here is the script CREATE TABLE [dbo].[PROC_DATA] ( [id] [int] IDENTITY (1, 1) NOT NULL , [Col1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Col2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Col3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Date1] [datetime] NOT NULL , [Recod1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [DRI_CHASE_DATA_GRP1] GO Josephine
View Replies !
Identity -&> PrimaryKey
I've a question. I've an old db here with no Primary keys nearly but a lot of identity's set, one for each table (nearly). I'm wondering, could something somehow go wrong with some sp's or anything if I make primary keys of these identities? Question why I'm asking is that we're takinging about doing replication but can't since there are primary keys lacking but we dont dare to change ém into primary keys if something might go wrong since we dont have knowledge about that. Thanks for help.
View Replies !
Using Postcode As Primarykey
Hello I've taken over a database where for many tables the postcode field (equivilent to the US Zip Code) is the obvious primary key. [Mapping postcodes to distict or various types of area for statistical analysis]. However the people who set up the database have continually used an auto generated number for the primary key. There are never any duplicate postcodes in the various tables, so my question is what is the advantage of either using or not using the postcodes as primary keys.
View Replies !
DATETIME AS PrimaryKey?
DATETIME AS PrimaryKey? Hi Dears! declare @dtpk smalldatetime select @dtpk=getdate() print @dtpk in the above, i m using @dtpk as a primary key value in a table to keep a track of some events that are occuring with a difference of one second. the above method gives me value to store in the Primary key Filed (EventTime smalldatetime) in the form "Dec 1 2005 7:57PM" where my requirement is to save it in the form "Dec 1 2005 7:57:12.03PM" so as to make it unique for the primary key field(EventTime smalldatetime). how to get required datetime format with the help of above statements to store as primary key in EventTime field of type smalldatetime. THNX IN ADAVNCE
View Replies !
How Does Primary Key (primarykey) Work? Can Only Use Once?
I want to know if this primary key behavior is unique to Access or if it is THE WAY that all industry-standard primary keys work. Thus, I hope not to get too messed up on my SQL Server 2005 project--or any other SQL project. In Access, I set up a table 'tbl_Gen_Admin_sto' to store general administration expenses. This is a budget. So, there can be at most one entry per budget_year / cost_center / account_number / month. If there are two or more entries, the data is messed up, something is wrong. Therefore, I defined a muliple-field primary key for tbl_Gen_Admin_sto. If I make a programming mistake and enter data multiple times, the primary key feature will stop my program from entering the same data multiple times. As I often do, I copy the data into another table, in this case tbl_Gen_Admin_tmp for manipulation in a form. (I.e., I connect a form to tbl_Gen_Admin_tmp.) The tmp version is a pivoted version of the sto table. So, in sto, I have one amount field for each month. In tmp, I have no month field, but do have one amount field for every month of the year (12 amount fields). The transfer of data to tmp went fine. The user adds, changes, and deletes records in tmp through the form. When the user is done and wants to save the changes (and not discard changes), my program deletes the selected records from sto, then inserts the records from tmp. But lo! Access was not accepting the new records because (error message) "key violations". The delete went fine. But the insert would not take. I deleted the multiple-field primary key index in sto. Then I created a multiple-field regular index with no duplicates allowed. Now, sto accepts the insert. I thought that when I deleted the records, the primary key field is gone--zzzzpft! But aparently Access is keeping track of all primary keys. Even though I deleted the record, for all eternity I will never ever be able to re-insert with the same primary key. Here is where I need your help: Am I going to run into this same primary key behavior in SQL Server 2005? If so, is it because I have encountered the fundamental definition of primary key? (I hope you don't beat me up too much for abusing primary keys.)
View Replies !
Duplication
How can I achieve the following... I have a Membership No. field which comes from a bookings table, so multiple membership no. do exist. What I want to achieve is a list of membership No.s with no duplication. Sorry to be so dumb, but we all have to start somewhere.
View Replies !
InsertCommand.ExecuteNonQuery() And Violation Of PrimaryKey
Hey, I have a page that inserts into a customers table in the DataBase a new customer account using this function: Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "") Dim result As New DataSet Dim tempid As Integer Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn")) Dim Adcust As New SqlDataAdapter Adcust.InsertCommand = New SqlCommand Adcust.SelectCommand = New SqlCommand Adcust.InsertCommand.Connection = conn Adcust.SelectCommand.Connection = conn sessionExists(email, sessionid, 1) conn.Open() If fname = "" Then Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')" Else Dim strsql As String strsql = "Insert Into neelwafu.customers" strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values(" strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')" Adcust.InsertCommand.CommandText = strsql End If Adcust.InsertCommand.ExecuteNonQuery() Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers" tempid = CInt(Adcust.SelectCommand.ExecuteScalar()) conn.Close() Return tempid End Function -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Now, I am getting an error: Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The customers table has as a primary key the 'email'..... so plz can I know why am I getting this error ???? Thank you in advance Hiba
View Replies !
Duplication Error
i have taple translatio witch have theses coloumn ID,TypeID,Status,ComID,RecordID,Translator in this table we assign a certain company with a certain typeID to a certain translator . so next time when the translator log he goes to the company that he assigned to it when he log to the company page, there is another company page n arabic that is transalted by the translators , in the arabic page there is a button when you can send this company to transaltion. but a duplicate have been happen made because user send the same documnet to the translation table so many time so translator transalte the same company profile more than one time witch is not good. we handle the duplicate of the send to translation button put still there is duplicate record in the database witch is more taht 3000 record how ican to remove thsi record from the DB without make ant other erroe
View Replies !
Report Job Duplication
HI I have created a job to run a reporting services job which then named it in the job scheduler 354EEF12-404F-46BD-B54F-708B5027837F. I then renamed the job to Rpt ETL log. However it I was surpised to see two emails come with reference to this report. It seems to have created another one with the long job names. Is there any way to stop this as I would really like to name to schduled rpt jobs without it duplicating. Many Thanks Robert
View Replies !
Prevent Duplication On UPDATE
Hello I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling: UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside' The above code correctly updated the spelling error, but it also inserted a new row with the corrected data. So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows. What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them. Thanks Lynn
View Replies !
Track Duplication Of Records
Hello, I have a table which consists of 27,000 of records. Among these records, there is one record which is a duplication of another record. Is there any way to track this record from the same table by the SQL statement ? I have been advised to use the following statement but it does not help: Select count(*) As Duplicate, columnname from tablename group by columnname Scrolling 27,000 lines of records with bare eyes is very painful. Any help is appreciated. Cheers
View Replies !
Duplication In The Primary Key Column
Hi I have a set of excel files that i need to export to sql2005 using ssis. Now the issue is that i have no idea about he data ie it may have duplication in the primary key column. If i export it as it is to sql server, it will cause me problems. Is there any way i can filter out the rows which have duplication in the primary key column? Umer
View Replies !
De-Duplication Performance Issue
Hi All, Scenario: De-duplication logic should pick one record from source and check with all the destination records and insert if not duplicate. Else raise error. There are average 8 to 10 lookup check for each logic path. Key fields used for de-duplication check are FirstName, LastName, DOB, Gender, SSN. Issues: · Since picking row by row and processing the performance is constrained. · Since 8-10 comparison is done using lookup performance downstream. (Lookup is used without caching, if cashing is used the package is failing after sometimes as if memory is failing. Can we handle this memory problem?) Please give some suggestion to improve the performance. Current performance is around 2500 records per hour, but there are 8 lac records in total to process. I am looking for guidance on this issue. If someone can guide me on how to do it better it would help me a lot. Thanks, S Suresh
View Replies !
Single Field Duplication Problem
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed). Thanks Bill Howard
View Replies !
Error Catching On Data Duplication In A Sql2005 Db
Hello, everyone. I am having problems catching a data duplication issue. I hope I can get an answer in this forum. If not, I would appreciate it if someone can direct me to the right forum. I am working on a vs2005 app which is connected to a sql2005 db. Precisely, I am working on a registration form. Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site. The INSERT query works like a charm. The only problem is that I am trying to reject registrations for which an email address was used. I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected. How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address? Thank you for all your help. Antonio
View Replies !
Insert Statement Results In Data Duplication Using MDAC 2.8
I am using Remote Data service to Query an Sql Server Database using MDAC. The Os in which server is loaded in Window 2003 and the MDAC 2.8 version is installed. Now I create a table X with identity column. Then when I try to insert records in that table using Insert into X select * from Y statement. The statement gets executed by when i check the X table I find that the duplicate records are present with different identity no's. Even when i truncate and retry the same thing occurs. What could be the reason for the same?? Regards Pranali.cons
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 !
I Wish To Avoid ...
Hi All,I am working on Web Application which deals with history data forreports and keeping track of changes.Current Solution :1) For each Entity I am having a column TID (Tracking ID) whichkeep on increasing for an instance of Entity. (so history and presentdata in the same table) . It makes querying the data difficult.2) I am also using month year table for Managing data i.e<TableNameMMYYYY> for a given month and yearPurposed Soluion :1) Using a seperate table so that history and present data isplaced seperately .2) Store All data in one table <TableName>Please guide me on advantages and disadvantages you pin point in thetwo approaches.With warm regardsJatinder
View Replies !
Avoid Sp_executesql With ADO.NET
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
View Replies !
How To Avoid Duplicate Value
Hello, I have the following query, SELECT GroupInfo.GroupID, GroupInfo.GroupName FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID) INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber ) It's out put is as follow: Group ID GroupName 0 Abc 1 Beta 0 Abc 0 Abc 0 Abc 1 Beta 2 Alpha Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like, Group ID GroupName 0 Abc 1 Beta 2 Alpha Hope I explained what I need to see in result pane. Thanks, Junior
View Replies !
Avoid Using Cursors
Hi All,I want to avoid using cursors and loops in stored procedures.Please suggest alternate solutions with example (if possible).Any suggestion in these regards will be appreciated.Thanks in advance,T.S.Negi
View Replies !
Trying To Avoid Using Cursors!
i'm trying to write a batch that will perform a complex task usingset-based selects instead of a row-based cursor. let me know if you canhelp me figure out how.description of what i'm trying to do:there is TABLE1, TABLE2, and TABLE3i want to select each row from TABLE1, do some analysis on the data ofthat row, and then perform an insert of some data into TABLE2, and somedata into TABLE3how do i do this in a T-SQL batch?
View Replies !
I Want To Avoid Using A Cursor, Please!
Application is a Work Tracking/Timesheet database.The increments of work are stored in the TimesheetItem table. Thiscontains, inter alia, the Work Code, the Start and the Duration thatthe employee spent that day on a particular project.Some employees in the Network Support Department don't complete astandard 7.5 hour day for various reasons, so for every Network Supportperson I need to update these particular days with an amount to bringthe total day's hours to 7.5.This SQL will get me a list of all TimesheetItem records for thepersonnel concerned.SELECTTimesheetItem.TypeID,[Work].WorkCode,TimesheetItem.Start AS Start,SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS HoursFROMTimesheetItem LEFT OUTER JOIN[Work] ON TimesheetItem.WorkID = [Work].WorkIDWHERE(TimesheetItem.EmployeeID IN(SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))GROUP BYTimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCodeHAVING(TimesheetItem.Start >= @FromDate) AND (TimesheetItem.Start <= @ToDate)ORDER BYTimesheetItem.StartWhat I need is to group these records by EmployeeID where theaccumulated hours per day are < 7.5, so that I can then insert anincrement to make up the difference.I'm writing this from home and I don't have access to the tables toprovide a script, but there's nothing untoward there.Hope someone can help!ThanksEdward
View Replies !
How To Avoid Cursors
I have a long sql batch that does this:1. Gets a list of all tables in user database that start with name COREdeclare@tablenamevarchar(30),@commandvarchar(2000),@cntintegerdeclare GetCOREOids cursor forselect sysobjects.namefrom sysobjectswhere ( OBJECTPROPERTY(sysobjects.id,N'IsUserTable')=1 ) and( sysobjects.name like 'CORE%' or sysobjects.name ='CMNSTRStructGeomBasicPort') and( sysobjects.name not like 'CORESpatialIndex%') and (sysobjects.namenot like 'COREDeletedObjects%') and( sysobjects.name not in('CORERelationOrigin','CORERelationDestination') ) and( sysobjects.id in ( select id from syscolumns where name = 'oid') )for read only2. Populates a temporary table with distinct oids from the list.create table [#tSP3DCoreOid] (oid UNIQUEIDENTIFIER)open GetCOREOidsfetch GetCOREOids into @tablenamewhile ( @@fetch_status = 0)beginselect @command = 'insert into [#tSP3DCoreOid] select distinct oid from' + @tablenameexecute(@command)--print @tablename + ' rows: ' + convert(char,@@rowcount)fetch GetCOREOids into @tablenameend /* while */--Clean upclose GetCOREOidsdeallocate GetCOREOids3. Creates a cursor to get "invalid" oids from 2 other tablesdeclare DanglingRelation cursor forselect oid from [dbo].[CORERelationOrigin] RO where not exists( select oid from [#tSP3DCoreOid] where oid = RO.oid )unionselect oid from [dbo].[CORERelationDestination] RD where not exists( select oid from [#tSP3DCoreOid] where oid = RD.oid )4. Loops thru. the cursor examining each oid and then calls aStoredProc to update another tabledeclare @objectOid uniqueidentifierdeclare @tempOid uniqueidentifieropen DanglingRelationfetch DanglingRelation into @ObjectOidwhile ( @@fetch_status = 0)beginset @tempOid='00000000-0000-0000-0000-000000000000'if left(@ObjectOid,8)='00000002'select @tempOid=oid from COREToDoList where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000003'select @tempOid=oid from COREToDoRecord where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000004'elseselect @tempOid=oid from COREBaseClass where Oid=@ObjectOidif @tempOid = '00000000-0000-0000-0000-000000000000'BEGINexec CORESetObjectIntegrity @ObjectOid, 2ENDfetch DanglingRelation into @ObjectOidendclose DanglingRelationdeallocate DanglingRelationdrop table [#tSP3DCoreOid]Now the #tSP3DCoreOid temp table can sometimes get as many as 7.5million rows and this is making the batch fail on SQLServer as it runsout of memory.I think the problem is the cursor here and would like somefeedback/tips on how to best optimise it.thanks a lotSunit
View Replies !
To Avoid Looping
Hi All, Very Ad-hoc requirement I am having. I've got one table with two columns. Column Name Data Type 1) Id Integer Identity 2) RemDate DateTime I've to write one SP/JOB in that there will be an integer input parameter @numofday. Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month. This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping. Thanks in advance, Jai
View Replies !
Trying To Avoid A Cursor...
Happy new year to all! Now a question... I added a new column to StagePayments table - Activity - which is supposed to end up being the same as the JobActivityID from the JobActivities table. Basically, I need to get JobActivities (JA) info and put it in the StagePayment (SP) column. Problem is there are duplicate JA/Descriptions and SP/Activities, so what I need is to take the first sequence SP/Activity and grab the first JA/JobActivityID that matches for a particular JobID. Then get the next one of each and so on and so on... XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Tables... CREATE TABLE [dbo].[StagePayment] ( [PaymentID] [uniqueidentifier] NOT NULL , [JobID] [uniqueidentifier] NOT NULL , [Paid] [bit] NOT NULL , [Sequence] [smallint] NOT NULL , [Amount] [decimal](10, 2) NOT NULL , [Comment] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL , [Activity] [varchar] (30) COLLATE SQL_Latin1_General_CP437_BIN NULL , [ActivityID] [uniqueidentifier] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD CONSTRAINT [PK__StagePayment__457442E6] PRIMARY KEY CLUSTERED ( [PaymentID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD CONSTRAINT [DF__StagePayme__Paid__4668671F] DEFAULT (0) FOR [Paid] GO CREATE INDEX [IX_StagePayment] ON [dbo].[StagePayment]([JobID], [Sequence]) ON [PRIMARY] GO XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX CREATE TABLE [dbo].[JobActivities] ( [JobActivityID] [uniqueidentifier] NOT NULL , [JobID] [uniqueidentifier] NOT NULL , [Sequence] [smallint] NOT NULL , [Activity_Status] [char] (1) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL , [Description] [char] (30) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL , [PlanStartDate] [datetime] NULL , [PlanEndDate] [datetime] NULL , [ActEndDate] [datetime] NULL , [AmountDue] [decimal](10, 2) NOT NULL , [CanDelete] [bit] NOT NULL , [Comments] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD CONSTRAINT [PK_JobActivity] PRIMARY KEY CLUSTERED ( [JobActivityID] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD CONSTRAINT [DF_JobActivities_JobActivityID] DEFAULT (newid()) FOR [JobActivityID] GO CREATE INDEX [IX_JobActivity] ON [dbo].[JobActivities]([JobID]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_JobActivity_1] ON [dbo].[JobActivities]([JobID], [Activity_Status]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_JobActivity_2] ON [dbo].[JobActivities]([JobID], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_JobActivities] ON [dbo].[JobActivities]([JobID], [PlanEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [JobActivities53] ON [dbo].[JobActivities]([JobID], [Description], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [JobActivities50] ON [dbo].[JobActivities]([JobID], [Description], [PlanEndDate], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_JobActivities_6] ON [dbo].[JobActivities]([JobActivityID], [Activity_Status], [Description]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_JobActivities_1] ON [dbo].[JobActivities]([JobID], [Sequence], [Description], [JobActivityID]) ON [PRIMARY] GO CREATE INDEX [IX_JobActivities_2] ON [dbo].[JobActivities]([JobID], [Sequence], [ActEndDate]) ON [PRIMARY] GO ALTER TABLE [dbo].[JobActivities] ADD CONSTRAINT [FK_JobActivity_Job] FOREIGN KEY ( [JobID] ) REFERENCES [dbo].[Jobs] ( [JobID] ) ON DELETE CASCADE GO XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX My example for one single JobID... In JA - JobActivityID Seq Description 34969C69-FF70-4313-91CC-43921DB3D74D 18 FOLLOWUP 48ACFEEE-3349-4F16-83E0-58F9B19E28E5 16 INSTALL 22507D44-6D0D-42DE-A211-9C23FDFCD19D 5 PLOTPLAN BA88D04E-EBAE-40DB-A2C9-F909463D7F22 1 THANKU 83C48207-895B-4775-A62D-07059D8DEB62 10 NOTCUST DBB8DF00-E26B-4E6F-9482-08E8CFE1588D 11 ROOMORD BF621E91-E819-4F84-B507-0AA644D5C3F6 0 DWNPAY 6F595880-59D9-4E55-845D-19B477E8B179 2 THANKU 86D0A650-3B72-47E1-BDC2-2CA177DC3D53 12 NOTCSTRM B0ABCC4C-A626-41C2-890C-3B9580326774 13 ROOMREC F131C6FF-A86C-4527-A580-60FF7D3F0164 19 1YRFLWUP 7132625C-8E8B-4748-9176-6F06E8D0F20F 17 ARCMNT AE06A938-323B-46EA-BA11-7D17B0985ACC 15 24HRCALL DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A 14 24HRCALL EBC15C77-95D5-4C42-AD88-861F9DD7688B 9 RECPERMT 97D3D755-4B6F-4564-842B-A06945AA8890 8 SUBPERMT CAACAACA-3B97-41D5-9A4A-A4E3E963D0BF 6 SUBCAD 0C3CB2E0-F4E9-43CD-81E4-ACE9F4022033 3 PHONCALL 42D498BE-308A-413E-965D-ADE7A7A21B97 4 MEASURE 7654C5E3-BED5-4F78-ADC8-DD4E283ADDEE 7 RECCAD In SP - Seq Activity ActivityID 1 NULL 2 24HRCALL 3 24HRCALL 4 INSTALL I need to get SP to end up looking like this - Seq Activity ActivityID 1 NULL NULL 2 24HRCALL DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A 3 24HRCALL AE06A938-323B-46EA-BA11-7D17B0985ACC 4 INSTALL 48ACFEEE-3349-4F16-83E0-58F9B19E28E5 I have tried various versions of this... begin transaction update StagePayment set Activity = (Select J.JobActivityID from JobActivities J (nolock) inner join StagePayment SP (nolock) on J.JobID = SP.JobID and J.Description = SP.Activity) where Activity is not NULL way too basic as I get this error... Server: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. I know somehow I have to get the duplicate Activities in the same sequence as the Descriptions, but that's where I'm stuck?!? If anyone is able to offer suggestions on how to get this to update correctly, I'd be very happy to hear about it! Thanks in advance! Tiffanie
View Replies !
Would Like To Avoid Cursor, Please Help
Hi, I need to query a database for a recordset and insert this into another database row-by-row. For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MM:SS.0000 so for each inserted record the last decimal point would increment by 1. Can I do this without a cursor ? Any additional info I will provide gladly. Thanks!
View Replies !
How To Avoid Deadlocks
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data: Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock? Any advise would be much appreciated. Thanks!
View Replies !
How To Avoid .LDF Grow Too Big
In SQL Server 2000, we have a database which is frequently updated. The .mdf file of the Database is around few hundred mbs where as the .ldf (Transaction Log) is few Gbs. Eveyday the size of the file is growing big and the idea is to avoid this. I was trying to do a few things. 1) Auto Shrink the database size 2) Truncate the transaction log. Could you suggest which is the best way to avoid this problem, since I am not able why the database should grow so big within a few days..
View Replies !
To Avoid Fragmentaion
I found some fragmented data pages in my database tables. I have found out using DBCC Showcontig commands. I want to remove data fragmentation. My tables are replicated. Is it possible to remove fragmentation without using export and import. Please help me in fixing this problem. Is there any way to fix replication failure with out recreating it. My replication has failed saying is timout error.error is could not connect subscriber. But I can able to ping the subscriber and if I create a new publisher and subscriber it is working fine. But existing one is not working. What might be the possible reason. We have recreated the replication. We droped the existing one and recreated it. It took around 14 hours to sync. Is there any way to resync with out recreate publication. We are using transaction replication and merge replication. Vani
View Replies !
How To Avoid For..each When There's No Data
Hi everyone, As first task I've got Data Flow which loads a set of data into a .NET recordset. After that, inmediately flow execution goes to For..Each Loop. I'd like to avoid go in that direction when Data Flow returns zero rows. How can I do such thing? I've tried this on Precedence Constraint Editor but it doesn't work..It doesn't recognize EOF keyword.. @[User::ResulSet] == EOF Thanks in advance for your input, Enric
View Replies !
How To Avoid MSG 8152?
Hi, all! Having a table with a varchar(10) column, I try to insert a row where the data for that column is more than 10 characters long. No surprise, I get a MSG 8152 error. What I want is to get the overlong data clipped to the maximum column width, and no error. Is that possible?
View Replies !
How To Avoid 0/0 Error
Hi All, I use a report expression something like this. = SUM(Fields!MTD_TotNetCOAmt.Value)/SUM(Fields!TotBalance.Value)* 12 When Both fields have 0 vlaues, it gives a error massege. How do I avoid this? Thanks
View Replies !
How To Avoid Cursor
hi all, i have a huge database and i am using Cursor to retrieve data and with each fatched data i m doing some operation....as my database is increasing the time duration to execute the cycle is also increation hugely....how to solve it?? thanks,
View Replies !
How To Avoid Duplicates In Two Tables?
Hi all, I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion. Thank U.
View Replies !
Can I Avoid Temp Tables, Etc.
I need help on two questions:1. Is temp table the only way to pass recordsets from a nested storedprocedure to a calling stored procedure? Can we avoid temp tables inthis case?2. Are operations in a stored procedure are treated as a transaction?Any help will be greatly appreciated.Background: We need to use temp table to pass recordsets from a nestedstored procedure to a calling stored procedure. Our understanding isthat in this case, we have no choice but to use temp tables. So, weneed to optimize the performance as much as possible. To do this, wewanted to find out whether operations in a stored procedure are treatedas a transaction. We are using SQL 2000 SP4. I could not find anyanswers so I did the following experiment.Experiment 1:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASUpdateArticlesSETIsUpdate = 20whereArticlesId < 80000SELECT * from ArticlesGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO"SELECT * from Articles" takes a long time (about 40 seconds) tocompleteBefore executing the SP, the IsUpdate attribute for all articles is 30.Then I executed this SP. Before the SP is finished, I end the SPmanually. I checked the IsUpdate attribute again, and found that allArticles's (ArticlesId < 80000) Isupdate attribute is now 20. Theoperations did not rollback. I interpret this to mean that the whole SPis not treated as a transaction.Then, I did experiment 2 below. This time, I explicitly declared thetransaction.SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASBEGIN TRANSACTIONUpdateArticlesSETIsUpdate = 50whereArticlesId < 80000SELECT * from ArticlesIF @@ERROR <0 ROLLBACK TRANSACTIONCOMMIT TRANSACTIONGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOBefore this second SP, the IsUpdate attribute is 20 (set in the firstexperiment). I run this second SP and ended it manually before itfinished. I checked the IsUpdate attributes for all Articles's(ArticlesId < 80000), but their Isupdate attribute is 50. So theoperation did not rollback either. But we have declared the transactionexplicitly. Does this mean that the SP is still not treated as atransaction?
View Replies !
How To Avoid Reference Problems
Hi,I have some tables which have references between them.If i delete the reors in the child tables , since it references thecolumn of the parent , i get error msg in my front end (quitenatural).do u tell me how to delete the child table records with out affectingthe relation ships or can recreate the relations with out affecting mydata & structure and the constraints.With thanksRaghu
View Replies !
How Do I Avoid This Corelated Subquery?
Hello,I have a situation which would essentially use a co-related subquery.I am trying to avoid using a co-related subquery due to its slowperformanc and use a join statement instead.Here is what I am trying to do:Tables:========Limit-------RelID ProdID Days Amt1 Amt2----- ------ ---- ---- ------148 2 30 15000 30000148 2 7 1000 2500Temp Limits-----------RelID ProdID MaxDays Amt1 Amt2----- ------ ----- ------ ------148217500 10001482717500 5001482302500 200014829025000 75014821807500 -300I want the totals of Amt1 and Amt2 in the TempLimits table added toeach row in the Limits table where the Days in the Limits Table >=MaxDays in TempLimitsSo, when I run the query with the above dataI need to getRelID ProdID Days Amt1 Amt2----- ------ ---- ------ ------148 2 30 42500 (15000+7500+17500+2500) 33500(30000+1000+500+2000)148 2 7 26000 (1000+7500+17500) 4000(2500+1000+500)************************************************** **************************Here is the query that I have right now...update Limitset Amt1 = isnull(Amt1,0) + IsNull(temptable.temp_Amt1, 0), Amt2 = isnull(Amt2,0) + IsNull(temptable.temp_Amt2, 0)from Limit inner join(select Templimits.relationship_id relationship_id,Templimits.product_id product_id, sum(Templimits.Amt1) temp_Amt1,sum(Templimits.Amt2) temp_Amt2from TempLimitsWHERE TEMPLIMITS.MAXDAYS <= LIMIT.DAYSgroup by limit.relationship_id, limit.product_id) temptableon Limit.relid = temptable.relid andLimit.prodid = temptable.prodid************************************************** ****************************As you would see here, I am trying to use the condition for Days(listed in the query in CAPS) inside the inner query which would notwork... Is there any way I can do this apart from using temp tablesand co-related subqueries?Thanks in advanceKannan
View Replies !
How To Avoid Phantom Inserts?
How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed"). Thanks!
View Replies !
Avoid Increasing The Log File
Hello All, I have faced a network problem during some days, what forced one of our replications to be stopped. The Publisher database is a high volume database. After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted. My concern is the way it is working, there will be no enough space for the log or for its backup files. So, I have created a TSQL job within the following commands: BACKUP LOG database_name WITH TRUNCATE_ONLY DBCC SHRINKDATABASE (database_name,TRUNCATEONLY) It's running every 20 minutes, however the transaction log remains increasing. I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process. Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication? Thanks a lot! Regards, Felicia Schimidt felicia.schimidt@br.flextronics.com
View Replies !
Filling Systlogs ??? How To Avoid This
Msg 1105, Level 17, State 1 Can't allocate space for object 'nbcctactxxt' in database 'SDNETPRO' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment. Thas was the error messege I got when I tried to create index using this code: CREATE CLUSTERED INDEX Index_key ON dbo.nbcctactxxt (Ord_Nbr,Ord_Type,Ord_Supp,Locn, CCT_Nbr,CCT_Leg,Activ,Gen_Seq) WITH FILLFACTOR=100,ALLOW_DUP_ROW I ran this code: DBCC CHECKTABLE(syslogs) and got back : Checking syslogs The total number of data pages in this table is 1. *** NOTICE: Space used on the log segment is 0.00 Mbytes, 0.00. *** NOTICE: Space free on the log segment is 819.20 Mbytes, 100.00. Table has 34 data rows. DBCC execution completed. If DBCC printed error messages, see your System Administrator. Well, what seem to be the problem?, I have plenty of space... I even checked truncate Log on check point , yet I am having the same error again.Any one can help. ..... thanks Ali
View Replies !
How To Avoid A Growing Transaction Log
I have to admit, I'm usually using the MySql database, but in this particular case I have to use MSSQL 2000. Over to my problem. I'm building a web-based system (who isn't these days) in which the user types arbitrary information that is published when the user pushes the save button. Nothing new about that. Here comes the tricky part, when the user wants to edit an existing item I copy all information in the database and sets the id of the 'edit-copy' to the negative value ( id 45 becomes id -45 for the edit-copy). This is also done on all items in other tables that is connected to the main item. This way I get a copy that the user may edit without messing up the published information. When the user is done I either delete all the negative items (cancel) or delete the positive items and update the negative to become positive (save). So far so good, allmost... my problem is that the transaction log grows tremendously. Is there any other way to accomplish a safe edit that doesn't affect the transaction log as much as my current solution? Could I be doing something wrong when updateing or deleteing my items?
View Replies !
Avoid Sql Class 2733a
hate to gripe, but this was bad. I have taken several sql server classes over the years, and have always been happy with the class, but avoid the 2733a (upgrading you database administration skills to sql 2005). Class is poorly devloped and incomplete. Half of the examples were to do things such as mirroring and replication from a command prompt. I thought I was in an Oracle 8 class. It was clear much of this functionality is not complete. In fact most of the class was command prompt based. They had absolutley nothing about the upgrade process from SQL 2000 to SQL 2005. I hope MS is not taking a step backwards, I always thought the strong suite for MS was the enterprise manager tool suite. The instructor did tell me there was an upgrade to the class that just came out, but it has a long way to go.
View Replies !
How To Avoid Display Of Duplicates
Hi, I'd really appreciate advice on now to avoid displaying duplicates in a resultset like the one below, only displaying the first field once and all the rows that correspond to that field all listed listed below. Distinct doesn't work. Thanks Customer Name Address ============= ======= Big Bob's Books 123 Stone Lane 456 Seaside Drive 789 Waterhouse Square 1234 Mystery Drive 5678 Simple St Tiny Tim's Tools Tiny Tim's Tools
View Replies !
|
TRACKER
