Finding A Duplicate Transaction Number
Jan 16, 1999
I have a problem with a 3rd party piece of software. Doesn't matter which, really. The problem lies
in a table called payments, with a column called txnumber...the newest version of this software fails
a check during installation with the message "duplicate txnumber in payment table." Not sure how
this could have happened, since there is no way to manually assign the txnumber, but the point is
not important. What I'd like to do is figure out a sql script that will return only the duplicate number(s)
so that I can either remove or change them manually. Unfortunately, I'm not terribly familiar with sql.
Any suggestions?
View 2 Replies
ADVERTISEMENT
Oct 29, 2007
Hello,
I searched for all the posts which covered my question - but none were close enough to answer what i'm trying to do. Basically, the scenario is thus;
Table1 contains values for UserID, Account code, and Date.
My query (below) is trying to find all the accounts assigned to a particular user ID, but also those duplicate account codes which belong to a second user ID. The date column would be appended to the result set.
The query I'm using is as follows;
select acccountcode, userid, date from dbo.table1
where exists (select accountcode from dbo.table1 where accountcode = table1.accountcode
group by accountcode
having count(*) > 1)
and userid = 'x-x-x'
order by accountcode
What I think this produces is a list of all files where a duplicate exists, but of course it leaves out the 2nd UserID...which is crucial.
Hopefully this makes sense. Any insight my fellow DBA's can share would be greatly appreciated!
Thanks,
D.
View 1 Replies
View Related
Feb 23, 2001
Hi,
I am putting my problem in an example as I Feel it would be clear.
Assume my table PEOPLE is having 4 columns with 6 rows, the SlNo being primary key.
SlNo Name LastName birthdate
1 A B x --
2 C B x |-- 1 pair (A, B, x)
3 D E y --|------------
4 A E y | |
5 A B x __| |-- 2'nd pair (D, E, y)
6 D E y ---------------
In this scenario, I need to find SlNo values having similar values in other columns. The o/p for above must be:
1
5
0
3
6
0 (0 needs to include in output for distinction in the sets)
(a)IS THIS POSSIBLE TO DO IN ONE SELECT STATEMET? and HOW?
(b)If I create another temp table tempPEOPLE and select distinct row information of the 2'nd, 3'rd and 4'th columns from the PEOPLE table and
then selecting SlNo's where the information match, I am able to get o/p
1
5
3
6
without 0...and I cannot makeout the distinct sets in this.
HOW DO I FIND THE DISTINCTION IN SETS?
Reshma.
View 1 Replies
View Related
Sep 11, 2005
Hi everyone!
I am parsing a database structed like the following for duplicates.
Code:
keywordnegativeexactbroadphrase
Alpha0.120.36
Alpha0.120.37
Alpha0.120.37
Alpha0.220.37
Alpha0.120.37
Alpha0.120.37
Alpha0.120.37
Beta0.43212
Charlie0.240.31
Delta0.72212
Epsilon410.31
Foxtrot250.22
Grape420.215
Hotel 230.13
Indigo0.3721
Juliet21220.14
Kilroy0.3110.15
Lemon0.2201
Mario0.2502
Nugget0.130.72
Oprah2141
Polo01225
Polo01224
Polo01225
Q-Bert31442
Romeo12100.1
Salty2200.1
Tommy10.30.132.4
Uri10.30.132
Uri20.20.132
Uri20.30.122
Uri20.30.131
Vamprie0.120.1315
Wilco0.210.21
X-Ray00.220
Yeti020.20
Zebra1310
The duplicates that this thread relates to are the kind with duplicate "keyword" entries AND dissimilar field entries; i.e. :
Code:
keyword negative exact broad Phrase
Polo 0 122 4
Polo 0 122 5
I've come up with an SQL query that seems to return all of these duplicates (save one of each type- the 'real', unique entry). However I think I made the query very inefficient. My SQL is very bad; this query will be running over tens of thousands of rows, so if it can be at all optimized I would greatly appreciate your help!
What I have so far is:
Code:
string query1 = "SELECT * FROM TableName" +
" WHERE EXISTS (SELECT NULL FROM TableName" + " b" +
" WHERE b.[keyword]= " + "TableName"+ ".[keyword]" +
" AND b.[negative]<> " + "TableName"+ ".[negative]" +
" ORb.[keyword]= " + "TableName"+ ".[keyword]" +
" ANDb.[exact]<> " + "TableName"+ ".[exact]" +
" ORb.[keyword] = " + "TableName"+ ".[keyword]" +
" ANDb.[broad]<> " + "TableName"+ ".[broad]" +
" ORb.[keyword]= " +"TableName"+ ".[keyword]" +
" ANDb.[phrase]<> "+"TableName"+ ".[phrase]" +
" GROUP BY b.[keyword], b.[broad], b.[exact]" +
" HAVING Count(b.[keyword]) BETWEEN 2 AND 50000)" ;
the algoritm seems to check every column of every row in order to determine a duplicate. Seems straightforward to me, but alas slow...
Is there a better/faster way I can do this? Thanks for you help!
View 1 Replies
View Related
May 29, 2007
Hi
i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column
select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc
but i am not able to get the fks created more than once on same column refering to same pk
Thanks in Advance
View 4 Replies
View Related
Feb 8, 2007
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name.
How could you query this:
ID fname lname
10001 Bill Jones
10002 Joe Smith
10003 Sue Jenkins
10004 John Sanders
10005 Joe Smith
10006 Harrold Simpson
10007 Sue Jenkins
10008 Sam Worden
and get a result set of this:
ID fname lname
10002 Joe Smith
10005 Joe Smith
10003 Sue Jenkins
10007 Sue Jenkins
View 2 Replies
View Related
Jul 20, 2005
What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"
View 2 Replies
View Related
Feb 18, 2014
I am trying to build a select statement that will
1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.
I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.
Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.
Below you will find a temp table to create and try in your environment.
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int
[Code] ....
Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.
View 3 Replies
View Related
Nov 3, 2014
There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.
How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.
SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]
[code]....
View 5 Replies
View Related
Oct 30, 2006
i have id, start_date, end_date
View 2 Replies
View Related
Sep 7, 2006
I have a large query that I am trying to debug in query analyzer.However, the errors I get have no line number or reference to wherethey are failing. How can I find out what line in the query is causinga particular error message to appear?ThanksJohn IveySouth Pike School District
View 2 Replies
View Related
Apr 24, 2014
I need to write a SQL to find number of hours between a begin time and end time. The fields are varchar. There are several date functions in sql, but I am not able to figure out how to get the hours between two times that is not in date format.
BEGIN_TIME END_TIME
0900----- 1500
1000----- 1700
1000----- 1230
0930----- 1030
View 3 Replies
View Related
Nov 20, 2003
Hey,
Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.
View 5 Replies
View Related
Mar 28, 2000
I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.
Thanks in advance
View 1 Replies
View Related
Nov 27, 1999
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
View 1 Replies
View Related
Nov 27, 1999
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
View 1 Replies
View Related
Feb 13, 2008
I know the ..Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGErrorLog on the server contains the information. But that needs I to RDP to the server or open the file remotely. Is there any other way to find out the port from my SSMS connection?
I remember in SQL Server 2000 you could find the information by choosing Properties of a connection in EM and click on the Network Configuration button. (I know it works when you are ON THE SERVER! But when I tried from my desktop, pressing the button didn't do anything)
Canada DBA
View 2 Replies
View Related
Dec 19, 2006
Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list.
When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
The largest number comes back as CA997
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
The largest comes back as CA1278
What am I doing wrong?
View 3 Replies
View Related
Jan 26, 2012
RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23
The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:
RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34
The code below is almost there but falls over on Recordno 8:
select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno
[code]...
View 4 Replies
View Related
May 20, 2015
I have a table which dont any Identity column. The data in the table has duplicate values, some rows are totally identical. I just want to select the row which has maximum Row Id/Row number if I am getting multiple rows in my select statement.
View 2 Replies
View Related
Jul 16, 2014
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
View 9 Replies
View Related
Oct 13, 2004
As part of the credit card process I have to supply a unique transaction id.
I thought the best way to do this would be to have a file which just holds a number which I grab and increment each time some one registers. Then if everyting is alright I will write the user's details a long with the transaction id to the User file.
My question is, if two people try to register and both try to grab the next number from the file at the same time, what will happen. Will one get a record lock message, or will it wait untill the other one has finished, or could two people end up with the same number under these circumstances?
I am of course open to suggestions on the best way of approaching this, but please bare in mind that I am only using Web Matrix.
Thanks
Mark
View 1 Replies
View Related
Aug 21, 2006
Good day to all
SQL Server 2000.
I have a problem with a piece of code, which updates some tables using transaction. This process brings the program to a halt when updating large files.
With smaller files, the process finishes without problems.
I have noticed that, if a comment out the "begin transaction" and respective "commit", the same code executes without problems, even when updating large files.
I suspect that there is a limit on the number of records a transaction can hold before a commit is issued. I am surprised however, that SQL Server halts, without messages or warnings.
Is this a configuration issue? If there is a limit on the number of records a transaction can hold, what is this limit:? Anything I can do to have a warning form SQL Server when a situation like this is reached (or indeed to avoid this situation) ?
Thank you in advance for any help
Cecil Ricardo
View 5 Replies
View Related
Jul 22, 2015
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
IIf(IsNull([Redem]![FX Rate]),([PDHist]![Remaining Balance]+[PD]![Closing Balance(TC)]).
The addition isn’t working. I think with a small push I can get this straightened out.
View 6 Replies
View Related
Jul 27, 2007
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
View 2 Replies
View Related
Jul 10, 2015
I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.
I am attaching a sample spreadsheet with two tabs - current structure and desired structure.
Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.
The number of lines is variable per patient.
View 6 Replies
View Related
Aug 19, 2015
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
View 2 Replies
View Related
Oct 23, 2015
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
[Code] ...
View 2 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Oct 2, 2007
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
View 4 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
Dec 22, 2006
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
View 8 Replies
View Related
