i created a simple table to record all uploaded files to my website. now, it works, but the problem is, it posts to the table 2 times, as in it executes "Button1_Click" event twice. The result is i get two records which are the same, and only differs in primary key (because i set it as an autonumber). how do i fix this? thanks in advance
I have implemented a script to perform a MD5 hash on each row processed by the SSIS package so that it can be compared with a stored value to see if there has been a change in the record. This package processes over 1 million rows. In 12 of these rows I get a hash value that is different than the stored value despite the fact that the rows "look" identical. Curious about this, I used the both the CheckSum and Binary_Checksum feature from t-sql to check the rows and they both show the identical checksum value. I have exported the rows into text and did a compare and the records are identical. I assume there must be some hidden characters that is causing the hash to be different, has anyone else run into this issue? Any help is much appreciated.
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b task_c task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus: queue_num task_num team queue_1 task_a red task_b red task_c red task_d red
No bonus for any team here queue_num task_num team queue_1 task_a red task_b blue task_c red task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
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.
Hi,Currently, I am developing an application for my client. The issue is that the client has the office and warehouse which are in different locations. He wants the application run and updated all the data in to a database (called warehouse database). Every month, all records in warehouse database will be synchronized to another database which is identical with the warehouse database but it is run in his laptop at the office. Since networking is not an option, I have to create two identical database like this. Does any one know what is the good solution to this? Is there any synchronize feature in SQL Server 2005?Many thanks,
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query: -------- select distinct 'On-Demand' as 'Business Line', o.OrderID as 'Order #', isnull(d.DisplayCode,'UNK') as Hub, isnull(rz.RouteID,'UNK') as 'Default Route', 'On-Demand' as 'Assigned Route',
I have an "insert into" statement that creates two identical rows in a table, with this statement: delete from [table] where [column] = @parameterINSERT INTO [table]([fields]) VALUES ([parameter values]) This is the code-behind that performs the insert: Dim dbConn As New SqlConnection(strConn)Dim cmd As New SqlCommand("sp_CreateUser", dbConn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@UserID", strUserID)cmd.Parameters.AddWithValue("@UserName", strUserName)cmd.Parameters.AddWithValue("@Email", strEmail)cmd.Parameters.AddWithValue("@FirstName", strFirstName)cmd.Parameters.AddWithValue("@LastName", strLastName)cmd.Parameters.AddWithValue("@Teacher", strTeacher)cmd.Parameters.AddWithValue("@GradYr", lngGradYr)Using dbConndbConn.Open()cmd.ExecuteNonQuery()dbConn.Close()cmd.Dispose()dbConn.Dispose()End Using I wonder if it inserts twice due to a postback issue. Is there a way to stop two rows from being created in the first place with the same "insert into" statement? I'd appreciate any advice.
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario: -------------- I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.
On my site users can register using ASP Membership Create user Wizard control. I am also using the wizard control to design a simple question and answer form that logged in users have access to. it has 2 questions including a text box for Q1 and dropdown list for Q2. I have a table in my database called "Players" which has 3 Columns UserId Primary Key of type Unique Identifyer PlayerName Type String PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table. I am having problems getting this to work and keep getting exceptions. Be very helpful if somebody could check the code and advise where the problem is??
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e) { SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1"); MembershipUser myUser = Membership.GetUser(this.User.Identity.Name); Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue; DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString()); DataSource.Insert();
I have a spreadsheet that we download from one of our customers that contains Sales Order Release information. The spreadsheets contains 1-n releases per part #.
I have a SQL 2000 view that groups this information into a smaller table (after importing into SQL).
I need to take this view and create 1-n release records based on our production run qty and put it in a new table. I have a field in my view that contains the desired # of records to create.
Since I never used TSQL to loop through table/view, how do I do this. Following is what the output should look like:
From SQL view --------------------------------------- Part #: 11124A1 Qty Due:175 Run Qty: 50 # Release to Generate: 4
This question is regarding a "helper app" I'm building to go along with my ASP.NET appplication. It will be inserting/updating records in the database as a nightly process. It is a Windows application built in VB.net.
I have a table which should always only have one of each type of record in it. This table on average will have between 100k and 500k records.
Which operation would be faster and less strain on the server?
a. Use a "if exists" and see if a record of this type already exists, if it does, update it, if not, insert the new one.
b. Unconditionally issue a delete for the record I'm about to insert, then insert the new one.
c. Create a trigger that will delete the old record if a new one is inserted?
I have a table like the followingField1 Field2 Field3------ ------- ------x1 y1 z1x1 y2 z2x1 y3 z3x1 y4 z4x2 y1 z5x2 y2 z6x2 y3 z7x2 y4 z8x3 y1 z9............and so onI want to create a view with x1, x2, x3.. as uniquerecords; y1, y2, y3.... as fields; and z1, z2, z3.... as the valuesWhen I doCREATE VIEW xyz (y1, y2, y3, y4) ASSELECT field1 ,( SELECT field3 FROM table WHERE field2 = 'y1'),( SELECT field3 .....FROM tableI get the error that the sql query creates duplicate values. I think Imay have to do a join using distinct values of field1. I was lookingfor some guidance with the join.Thanks for your help in advance(using SQLSERVER 2000)
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.
My dataset:
A 20 abc B 24 mnp c 44 apq
Now I need to add another column within my transformation before I store the result set to destination like this:
A 20 abc 3 b 24 mnp 3 c 44 apq 3
Here. new column gives count of total rows in our dataset which was 3.
How can I achieve this? Can I use derive column to this?
i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated. Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student( studentId char(4) not null primary key, fName varchar(25) not null, mI char(1) null, lName varchar(25) not null, insCheck char(1) not null, );
create table instructor( instructorId char(4) not null primary key, fName varchar(25) not null, mI char(1) null, lName varchar(25) not null, instructorQual varchar(100) not null, );
Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert 2 3 @companyid int, 4 @companyname varchar(20), 5 @address1 varchar(30) 6 7 AS 8 9 INSERT INTO companymaster 10 ( companyname, address1) 11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which was not supplied.
The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK) Select * From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm Set to: Post Action: results.asp Text Field: Keyword Drop Down: CategoryTable Drop Down: Location UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType FROM Query1 WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail' ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults idSearch (AutoNumber) location (Text) "Want to insert the 'Location' result here" category (Text) "Want to insert the 'CategoryTable' result here" user (Text) "Want to insert the UserName Session Variable result here" result (Text) "Want to insert the 'Keyword' result here"
hello everyone , i have a table named "Employee" with EmpID as PK. i want to insert EmpID one by one in another table named "AssignedComplaints" so if i have all the EmpID inserted in "AssignedComplaints" table then on next insert operation , the first EmpID will be inserted and then second on so on. like i gave u a sample i have three EmpIDs in "Employee" table named M1,M2,M3 first M1 is inserted secondly M2 and lastly M3. now i have all EmpID in "AssignedCompalints" table. now if i do want to insert again then the whole process will repeat , first M1 will be inserted secondly M2 and so on. i need the query i have created a trigger and will use this query in that trigger. thanks
Hello all, I am having a lot of trouble with stored procedures. Could anyone help me out. I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today. I can select the values, and I can insert the values. But how do I store the values so that i can pass the results of the select to the insert? Im also having a lot of trouble with storing date values. ANy help would be greatly appreciated. Regards, Padraic Hickey
1. Inserting records from front end using begin trans ... commit trans 2. By using stored procedures ? - Is there any begin trans .. commit trans in stored procedure ? If so how to use it.
I am trying to insert 200 records on a table that has two fields: TagID and Name. TagID is a Unique Identifier and is not generated in the table. I created some code but is not working and I am a little bit confused:
declare@i integer select @i = 1
while @i <= 800 begin insert into Tags (TagID, [name]) values(newid(), select ('Tag ' + right('000' + convert(varchar(3), @i), 3))) select@i = @i + 1 end
Hi, I use the SqlDataSource Control for generating SQL-statements that I easily can modify. But on some tables I cant autogenerate the statements for Insert, Delete and Update. The checkbox is dimmed/not enabled. Why cant I use the autogenerate feature on some tables?
Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.
Hi , I have datatable which have atleast ten records . i want to insert these records at once in sql server 2000 when user clicks save button. please send me the code for procedure and code for sending bulk of data at once from asp.net form
I am currently working on a simple page to insert 1.6 million UK postcode records into an SQL server table. The table has three columns for the postcode, longditude coordinate and lattitude coordinate. The data is sourced from a pipe (|) delimited txt file and inserted into the database using a FOR loop. The problem I have is that the page will hang after inserting only 10,000 records, the page displays either an invalid View State error or a page cannot be found error. Now I assume the viewstate error stems from the fact that there is a form on the page which simply contains a button to execute the script and a few labels to show the progress. But without the form and associated viewstate the insert still fails to complete.... any ideas?? Would I be better running this on a thread or should I just do it in stages and be patient. I have now modified the page to read the database on load and pick up from where it crashes?
