TOUGH INSERT: Copy Sale Record/Line Items For &"Duplicate&" Record
I have a client who needs to copy an existing sale. The problem is
the Sale is made up of three tables: Sale, SaleEquipment, SaleParts.
Each sale can have multiple pieces of equipment with corresponding
parts, or parts without equipment. My problem in copying is when I go
to copy the parts, how do I get the NEW sale equipment ids updated
correctly on their corresponding parts?
I can provide more information if necessary.
Thank you!!
Maria
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command
I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable. ---Data Access Layer---- If dt.Rows.Count > 0 Then 'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score") conndb.Open() sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt) InsertComm.ExecuteNonQuery()InsertComm.Dispose() End If ----Stored Procedure---- ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster] @ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL ASBEGIN SET NOCOUNT ON; Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore) END
View Replies !
View Related
Lookup && Update Record && Insert Record
Hi All, I am trying to create package something like that.. 1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup) I will be appreciated if you can help me...
View Replies !
View Related
Duplicate Record
Dear All, I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table I’m not sure how to write this query, can someone help? Thanks in advance!
View Replies !
View Related
Duplicate Record
Hi guys how do you hide duplicate records, how would I do a select statement for that In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 ) I have about had it with this database I have been asked to make a report out of
View Replies !
View Related
Duplicate Inserted Record
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
View Replies !
View Related
Duplicate Record Trigger
This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice. -- duplicate record check SET @step = 'Duplicate record' IF EXISTS ( SELECT i.myID, i.Type FROM INSERTED i INNER JOIN myDB.dbo.myTable c ON i.myID = c.myID GROUP BY i.myID, i.Type HAVING (COUNT(*) > 1) AND (i.Type = 'In') ) BEGIN ROLLBACK transaction RAISERROR('Error: step: %s. rollback is done.', 16, 1, @step) Return END What is problem?
View Replies !
View Related
Duplicate Record Problem
I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName, tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone, tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2, tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM tblAppointments CROSS JOIN tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.
View Replies !
View Related
Duplicate Record Question
In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert. The problem is what to actually test for. When the code throws the exception it is a big long string . . "Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'" I just thought that there has to be something simplar to test for than comparing the exception to the above string. Can anyone tell me of a better way of doing this ? (by the way I am only using Web Matrix and MSDE in case it matters) Mark
View Replies !
View Related
Update A Duplicate Record
Hi everybody, I have 2 fields in a table. Table Name--- StudentDetail Name Address Saju Kerala Balaji Bangalore Raj Kumar Tamilnadu Saju Kerala I want to Update one of the duplicate row as I don't have any unique id column. So can anybody update one of the the duplicate record without using any id or altering any column. I am waiting for your reply................. Regards, Saju S.V
View Replies !
View Related
Duplicate Record Problem
So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows? There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.
View Replies !
View Related
Eliminate Duplicate Record(s)?
Hey Again, I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL: SELECT r.[requestID] ,r.[requserID] ,r.[departmentID] ,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate ,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate ,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate ,r.[reqStatus] ,r.[completedDate] ,d.[departmentName] ,s.[statusName] ,u.lastName + ', ' + u.firstName AS submittedBy ,ra.userID FROMtblUserDepartment ud INNER JOIN tblRequest rON ud.departmentID = r.departmentID INNER JOIN tblDepartment dON r.departmentID = d.departmentID INNER JOIN tblStatus sON r.reqStatus = s.statusID INNER JOIN tblUser uON r.requserID = u.userID LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID WHEREud.userID= @userID This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening). Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.
View Replies !
View Related
Delete Duplicate Record
Hi , How can i delete the duplicate record from a table use Northwind create table Emp (Ecode char(2), Ename char(10)) Insert into Emp(Ecode, Ename) values('A1','A') Insert into Emp(Ecode, Ename) values('A1','A') Insert into Emp(Ecode, Ename) values('A2','B') Insert into Emp(Ecode, Ename) values('A2','B') Insert into Emp(Ecode, Ename) values('A3','C') Insert into Emp(Ecode, Ename) values('A3','C') Insert into Emp(Ecode, Ename) values('A4','D') Insert into Emp(Ecode, Ename) values('A4','D') select * from emp order by Ecode Thanks ASM
View Replies !
View Related
Duplicate Record Inserted. Weird.........
1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 2 Dim sqlStr As String 3 Dim sqlStr2 As String 4 Dim myConnection As MySqlConnection = Nothing 5 Dim myCommand As MySqlCommand = Nothing 6 Dim myConnection2 As MySqlConnection = Nothing 7 Dim myCommand2 As MySqlCommand = Nothing 8 Dim myReader As MySqlDataReader = Nothing 9 Dim IC As String 10 11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text 12 13 14 Try 15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'" 16 17 ' Connection 18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString()) 19 myConnection2.Open() 20 ' Command 21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2) 22 ' Reader 23 myReader = myCommand2.ExecuteReader() 24 Catch ex As Exception 25 ' Exception Error Here 26 Response.Write(Err.Number & " - " & Err.Description) 27 28 End Try 29 ' Checking 30 If myReader.Read() Then 31 32 33 Label2.Text = "Username already exist. Please choose another username" 34 Label3.Text = "*" 35 36 Else 37 38 Try 39 40 41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )" 42 43 44 45 ' Connection 46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString()) 47 myConnection.Open() 48 'Command 49 myCommand = New MySqlCommand(sqlStr, myConnection) 50 51 myCommand.Parameters.AddWithValue("?uName", txtName.Text) 52 myCommand.Parameters.AddWithValue("?uIC", IC) 53 54 55 myCommand.ExecuteNonQuery() 56 myConnection.Close() 57 Response.Redirect("Register.aspx", False) 58 59 Catch ex As Exception 60 ' Exception Error Here 61 Response.Write(Err.Number & " - " & Err.Description) 62 Finally 63 ' Clean Up 64 If Not IsNothing(myCommand) Then 65 myCommand.Dispose() 66 End If 67 ' 68 If Not IsNothing(myConnection) Then 69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close() 70 myConnection.Dispose() 71 End If 72 End Try 73 74 75 End If 76 77 End Sub 78 79 above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur. when it run,it run 55, then 57,then back to 55, then 57 again means that my db hav duplicate record being insert anyone know how to solve this problem?
View Replies !
View Related
Simple Duplicate Record Question
I have a table with 2 columns, col1 is unique, col2 is not. col1 is numeric col2 is varchar. Here is the problem, col2 will have duplicate values, I need the largest numeric value from col1 with unique value from col2. Thanx for any help.
View Replies !
View Related
Help Selecting Duplicate Record Details
I have the following query I am using to identify duplicate records in one of my database tables: Code: SELECT memberID, COUNT(memberID) AS NumOccurrences FROM ChapterMembers GROUP BY memberID HAVING ( COUNT(memberID) > 1 ) Executing the above proc returns 4079 records... Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table... I tried running the following procedure: Code: SELECT memberID, COUNT(memberID) AS NumOccurrences, chapterID FROM ChapterMembers GROUP BY memberID, chapterID HAVING ( COUNT(memberID) > 1 ) But zero results are returned ... The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database. Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated. Thanks, Jandrews
View Replies !
View Related
Deleting The Duplicate Record From Table
Hi , i am using sql server 2005. i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them my problem is table structure have idendtity column which is unique. that is m table structure is something like recid citycode hospcode doctorcode otherdesp 1 0001 hp001 d0001 ... 2 0002 hp002 d0002 ... 3 0001 hp001 d0001 ... 4 0002 hp002 d0002 ... please suggest thank you
View Replies !
View Related
Fetch Returning Duplicate Last Record
Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP! Thanks in advance, Larry ALTER FUNCTION dbo.TestFoodDisLikes ( @ResidentID int ) RETURNS varchar(250) AS BEGIN DECLARE @RDLike varchar(50) DECLARE @RDLikeList varchar(250) BEGIN SELECT @RDLikeList = '' DECLARE RDLike_cursor CURSOR LOCAL SCROLL STATIC FOR SELECT FoodItem FROM tblFoodDislikes WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True') OPEN RDLike_cursor FETCH NEXT FROM RDLike_cursor INTO @RDLike SELECT @RDLikeList = @RDLike WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM RDLike_cursor INTO @RDLike SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike END CLOSE RDLike_cursor DEALLOCATE RDLike_cursor END RETURN @RDLikeList END
View Replies !
View Related
Duplicate Record Inserted With Stored Procedure
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String 'Get the users IP address strIPAddress = Request.UserHostAddress Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub
View Replies !
View Related
Opinion On Preventing Duplicate Record Insertion
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005 I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
View Replies !
View Related
Duplicate A Record Using Update Trigger Question
I am new to SQL and these forums, so please bear with me :) My basic question is if I can create a update trigger that will pull info from another record in the same table if data in certain fields match the existing record. An example: The user creates a new record. If said user enters data in specified fields that matches data in the same fields in another record in the same table, can a update trigger be used to fill out the rest of this record with the data from the record that matches? If you need more Info on my problem, ask and I will try to explain better. There may be a better way of doing this than using a trigger, but I am not sure. The fields that I would use to match the data would not be the primary key fields. Thanks!
View Replies !
View Related
How Do I Select The Maximum Date For Each Record Having Duplicate ID
Hi All, Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse. Here is My source table Name: Source_table, CREATE TABLE dbo.Source_table ( AccountID INT PRIMARY KEY, Price int, Address varchar(30), added DATETIME DEFAULT GETDATE(), edited DATETIME DEFAULT GETDATE(), Flag_Status Int Default 1 ) GO Here is the Fact_table CREATE TABLE dbo.Fact_table ( Fact_table_Key Int Identity (1, 1) NOT NULL, AccountID INT, Price INT, Address varchar(30), added DATETIME DEFAULT GETDATE(), edited DATETIME DEFAULT GETDATE(), editor VARCHAR(64), Flag_Status Int Default 1, Inserted_Date DATETIME DEFAULT GETDATE(), Updated_Date DATETIME DEFAULT GETDATE() ) GO Source_table: AccountID Price Address added edited Flag_Status ---------------- --------- ------------- ----------- ------------- ------------------ 1 10 xyz 01-2006 01-2006 1 2 14 abc 02-2006 02-2006 1 3 13 mno 03-2006 03-2006 1 Here is the fact table, table name= Fact_table Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date -------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ ------------------- 1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL 2 2 14 abc 02-2006 02-2006 1 05-2006 NULL 3 3 13 mno 03-2006 03-2006 1 05-2006 NULL 4 1 17 ght 01-2006 06-2006 1 NULL 08-2006 5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006 6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006 7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006 8 2 19 klm 02-2006 01-2007 1 NULL 02-2007 9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007 Here is what i am thinking: But it gives me Wrong Flag_Status. UPDATE Fact_table SET Flag_Status = CASE WHEN (SELECT Max(Updated_Date) From Fact_table WHERE AccountID IN (SELECT AccountID FROM Fact_table Group By AccountID Having Count(AccountID)>1)) > (SELECT Max(edited) From Source_table WHERE Flag_Status = 1) THEN 1 WHEN (SELECT AccountID From Source_table Group By AccountID Having Count(AccountID) =1) = (SELECT AccountID From Fact_table WHERE Updated_Date IS NULL) THEN 1 ELSE 0 END
View Replies !
View Related
To Display An Alert Message While Inserting A Duplicate Record
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated. The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key. What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database. Here is my code. pls modify it to achieve the said problem if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
View Replies !
View Related
Need Help With Adding A Duplicate Record Count Column To Query
I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn
View Replies !
View Related
Slective Duplicate Record Delete/Identify Question
HI, I have a table similiar to this: Division Type Section Location ModificationDate ------- ----- ------- -------- --------------- 3 4 2 Los Angeles 2/1/05 3 4 2 New York 2/4/05 4 5 1 Los Angeles 2/4/05 3 4 2 Seattle 2/7/05 4 5 1 Dallas 2/6/05 3 4 4 London 2/3/05 I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like: Division Type Section Location ModificationDate ------- ----- ------- -------- --------------- 3 4 2 Seattle 2/7/05 3 4 4 London 2/3/05 4 5 1 Dallas 2/6/05 Does anyone have idea how I would do something like this? Thanks.
View Replies !
View Related
Copy A Record Using A Sp
In a sp I want to select a record from my sql table and copy it to a new record in the same table, changing the value of one of the fields. The value of this field will come from my web page. Is this possible ?
View Replies !
View Related
How Can I Get The Actual Line Number From Inside Flatfile Sources For Each Record?
hi all, maybe this is not a serious problem, but I tried for days to come toi a solution without success. My Problem: I have have several flatfile sources I need to import into a sql-server 2005 DB. It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow. I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script. How can I achieve my goal? Please help me... Thanks in advance .. Bernd
View Replies !
View Related
Copy A Record From One Table To Another?
Hello, Is there a way of copying/moving a record from one table to another identical table on the same database using ADO.NET I can obviously do it the long way (retrieving a record, then pushing it up using a second SQL command) I was just wondering if there is a way to do it in one database hit using some kind of cool SQL function.
View Replies !
View Related
Would SQL Prohibt Copy Record
Good Day, I have an SQL 2005 Server running with a table called Active and a linked table called Recall. In my .net APP I wrote a VB code that will copy an existing record and create a duplicate with a new PK ID. Now here is the problem, The copy record works perfect if I create a new record and then copy that record. I can press my copy button and it will make as many copies as I want. When I try to copy a record that is pre-existing from an import I did about 6 months ago it looks like it works but the record is never copied. My question come down to, Is their something within SQL that would prohibt a record from being copied ? If the answer is yes, Can anyone advise where to look to change setting in SQL or what type of correction I should pursue. I am new to SQL and coming from a foxpro arena, Which is also where the imported data came from.. Any advice would be great. Thank You..
View Replies !
View Related
How To Pass The Sale Date Which It Is The Highier Sale In The Month To Subreport
Hi Expert, I have a Main Report that have the following table:- Sale Date Sale Value 2007-03-01 S$1000.00 2007-03-02 S$130.00 2007-03-03 S$1500.00 2007-03-04 S$1009.00 2007-03-05 S$120.00 2007-03-06 S$1020.00 I need to get the Sale Date which having Highier Sale Value using the expression to pass it to a Sub-Report that display the detail Sale Order for that Day. I can get the Highier Value using the MAX function (Max(Fields!Sale Value.value)) but how can i return the Sale Date? I tring to avoid writing another SQL statement to solve the problem. Is there anyone know how to solve this using the SQL 2005 Report Service function? Thank you
View Replies !
View Related
How To Create A Copy Of A Specefic Record
Hi I have a table with a user column and other columns. User column id the primary key. I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key Thanks.
View Replies !
View Related
Ways To Make This Work: Several Selectable Related Record For One Main Record.
Hey all! Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen. To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues. On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form. I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it. Thanks in advance, Johan
View Replies !
View Related
Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem
Hi, We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker. If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below. Thanks, Sarah The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on. This is running on a Websphere Application Server v6.1. Code snippet - getting the record thru JDBC: DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer"); Connection wsCon = wsDataSource.getConnection(); // wsCon.setAutoCommit(false); //have tried with and without this flag - same results Statements stmt = wsCon.createStatement(); String sql = "SELECT * FROM Person where personID = 12345"; ResultSet rs = stmt.executeQuery(sql); if(rs.next()){ System.out.println(rs.getString("lastName")); } if (rs != null){ rs.close(); } if (stmt != null) { stmt.close(); } if (wsCon != null) { wsCon.close(); }
View Replies !
View Related
SSIS: Multi-Record File Extract With 9 Record Types
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx I have been able to create a sample file with two of my record types. I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file. does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this? Thanks in advance for any help you might be able to provide.
View Replies !
View Related
Delete Record Based On Existence Of Another Record In Same Table?
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View Replies !
View Related
Record Locking: Multiple Users Accessing The Same Record
I have read several discussions about SQL 7 having built-in record locking. I am assuming that this is only during the transaction process. I have a problem with multiple users access the same record on a SQL table. We have these users accessing the SQL data with an Access 2000 DB Project form. When one person accesses the form to pull up a record, someone doing this at the same time will get an error window that asks the user to Save/Drop changes. Is there any way to LOCK DOWN a record until a user has finished making changes to it?
View Replies !
View Related
|
TRACKER
