Urgent - Dupes..
Apr 6, 2001Hi ,
i need to delete duplicate rows in a table, i want a good logic and example to solve this issue. Please help me in this..
urs
vj
ADVERTISEMENT
Dupes In My Db
Feb 5, 2000Greetings!
I have a database with several million records, I have found dupes and I need to get rid of them while keeping the original data in the db, kind of like delete all but 1. Any ideas of an easy way to do this?
Thanks,
Jimmy Ipock, MCSE, MCP+I
Dupes From A Join :(
Dec 9, 2001I have 3 tables, and im doing a join like:
select top 10 thits.fhits as hits, tmain.fheadline as rubrik, tmain.fpubfile as pub
from thits
join tmain on tmain.postid=tHits.postid
join tkeyscat on tkeyscat.postid=tmain.postid
where tkeyscat.fkeycat=60 order by hits desc
Which works great (almost).
The problem is when an article in tmain is cathegorized in more then
one cathegory, so the join tkeyscat on tkeyscat.postid=tmain.postid
might join in more then one result.
Im trying to select the 10 most read articles from tmain/thits where the article is in keycat 60.
How can I solve this?
tia
/frax
Help Eliminate Dupes
Nov 30, 2006I am VERY new to SQL and I am having a heck of a time biulding a script to find and remove duplicate entries.
Here is the table structure.
CREATE TABLE [dbo].[SecurityEvents](
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This small script seems to eliminate the dupes, but I can't seem to figure out to properly replce the table the with output of the script with all the dupes gone.
select distinct * from dbo.SecurityEventsTest where recordnumber IN
(select recordnumber from dbo.SecurityEvents)
order by recordnumber
Could someone help??
Thank You,
John Fuhrman
http://www.titangs.com
Deleting Dupes In Special Cases
Feb 7, 2005I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.
I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];
but it doesn't work since it's keeping the subset of the dupe row(s).
Can someone shed some light?
TIA
Help Finding And Updating Dupes In 2 Tables
Oct 13, 2005Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.
I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode
In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).
I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.
What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.
Any help or guidance that anybody can give will be most appreciated.
Dale
Timeout Expired -- URGENT, URGENT, URGENT!!!
Sep 27, 2000This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
URGENT.Sql Server Does Not Recognise MAPI , Or Profile Name URGENT
Oct 26, 2000hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Urgent, Urgent !! My Sql Server Refused To Start Due To Encrypton
Mar 23, 2001Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.
Please help me to start sql server.
Thanks.
Michel
Urgent Urgent Please.(Access SQL Pass Through Queries)
Jul 6, 2000Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
Scheduling(URGENT... URGENT)
Dec 20, 2000Hi everybody,
I have a batch which needs to be run every day night at 2:00 am.I Using At command but it is not working. if u have idea, please let me know
I AM USING THE FOLLOWING COMMAND :
AT 2:00AM C:SCHED.BAT
Thanks in advance
Krishna
Urgent Really Urgent Dbcc
Apr 19, 2008hi all
its urgent really urgent
please reply soon
I am getting error in sysindexes when i run dbcc checkdb on a production db.
the error is Server: Msg 8928, Level 16, State 1, Line 1
please help me to remove this
all the options of dbcc checkdb as well as table are not helping me
thanks in advance
Urgent Help Please
Feb 25, 2007Hi Guys
i write a thread before asking for help with reading an uploaded csv file,
i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db.
Heres the code<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If (IsPostBack) Then
Grid1.Visible = True
Else
Grid1.Visible = False
End If
End Sub
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Save the uploaded file to an "Uploads" directory
' that already exists in the file system of the
' currently executing ASP.NET application.
' Creating an "Uploads" directory isolates uploaded
' files in a separate directory. This helps prevent
' users from overwriting existing application files by
' uploading files with names like "Web.config".
Dim saveDir As String = "Data"
' Get the physical file system path for the currently
' executing application.
Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the
' uploaded file to the specified path.
' This example does not perform all
' the necessary error checking.
' If a file with the same name
' already exists in the specified path,
' the uploaded file overwrites it.
FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If
End Sub
Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited"""
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "csv.txt")
Grid1.DataSource = objDataset1.Tables(0).DefaultView
Grid1.DataBind()
objConn.Close()
End Sub
</script>
My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode
I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app.
Thanks for your help
T-SQL URGENT URGENT URGENT
Nov 18, 2007Hello
I'm trying to execute the following
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
but its giving me an error becuase of @dbName which is a variable decleared as the followingDECLARE @dbName as varchar(100)
the Database name is assigned to @dbName
but i can't take the value of @dbName to combine it to the rest of the statment
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
what should i do ! please help :(
Help Me With SQL DMO!! Urgent!!
Nov 10, 2003Hi, I am trying to make a backup of my database using Sql-DMO
When I do the backup on my server, my code runs fine. But when I backup from across the network from another computer, the database is saved on the server and not on the computer from where I am executing my application. How set SQLl-DMO to backup on the computer from where I am?
here is my code (found on the net)... Any suggestions greatly appreciated!
Cursor = Cursors.WaitCursor
'create an instance of a server class
Dim my_srv As SQLDMO._SQLServer = New SQLDMO.SQLServerClass
'connect to the server
my_srv.Connect("servername", "userid", "password")
'create a backup class instance
Dim my_backup As SQLDMO.Backup = New SQLDMO.BackupClass
'set the backup device = files property
my_backup.Devices = my_backup.Files
'set the files property to the File Name text box
my_backup.Files = Me.txtFilePath.Text
'set the database to the chosen database
my_backup.Database = "MYDB"
'perform the backup
my_backup.SQLBackup(my_srv)
MsgBox("Database successfully backed up.", MsgBoxStyle.Information)
Cursor = Cursors.Default
Plz Urgent
Dec 12, 2001How can i insert a word document or an a image file into a column of image datatype using T-SQL.
View 2 Replies View RelatedUrgent
May 3, 2002Hello,
I've a bactch file that is ran by the user and it looks for files and if it doesnot exist it waits for it and when it exsits, through isql it truncates the table and bcp in the file. In bcp files the loginname used in isql has dbo rights to truncate the table and the batch file also has password. The user who runs the script can also view the batch file and can know the password, Is there any way I could rewrite it so that the user cann't view the password or other way like SQL DMO or DTS package
Any help is appreciated
Thanks
Rea
FTP Urgent!
Jul 17, 2002I need to FTP files to another drive on the network. Do I need to setup an FTP server on the other computer where I will send file to?
Thanks!!
Ravi.
Hello No One Is There To Help Me!!!!!!!!--Urgent
Mar 14, 2001Greetings!!!
Can any one tell me what is the database log size for a datafiles (tables and Indexes 500MB)
On What basis database log size is estimated for the Database Creation of 500MB ?
On What percentage will be the dump file size and log file size with respect to Database file size??
Can any body help!!!!!!!!!!
Thanks in Advance
A Junior DBA
Urgent
Mar 21, 2001Hello,
I've a table which has clustered index on one column and I tried to run begin transaction and then 2000 insert statements and then commit transaction, the transaction took 15 min and then it hung up the table, I could not access the table I had to stop the process, the only info I received was the table had 4517 locks, I've no clue how it got so many locks, there was db backup running as I had scheduled 15 min backup. could anyone help me that inside each open transaction how many insert statement is advisible and how many maximum locks does sql server works on a table and what could have gone wrong in my situation.
Thanks
Rea
Urgent
Mar 22, 2001Hello,
Can anyone guide me how many insert statements can be written in one transaction?
Thanks
Rea
Urgent- Help
Apr 6, 2001I get following result set for blocked process: wait_type 14, wait_resource TAB: 5:1485248346 [[COMPILE]] . what does it mean?
View 1 Replies View RelatedDTS Urgent Please!!!!!!!!!
Apr 25, 2001Hi,
I am trying to do DTS for transfering data from one server to other server,both are on T1 link,after some time DTS failed by giving error message saying that
" Error at destination for Row number 3357844,Errors encountered so far in this test:1,,,Invalid charector value for cast Specification "
that table have 24 million rows..
How can i solve this problem,Please help me in this.this is urgent please
Mark A
SQL Help! URGENT!!
Apr 25, 2001I have a field 'slnumber' defined as int.Values are like 70000,70001,70002 etc.I need to export this field using bcp to a text file and corresponding field in the text file should have 10 characters and should read like 0000070000,0000070001,0000070002 etc.How do we achieve this format? Any help is greatly appreciated.
Thanks.
Reddy.
Urgent
Apr 27, 2001Can anyone tell me that Does Sql Server 7.0 runs fine in Windows 2000?
or is there any problem?
Thanks.
Sejal
Urgent
May 10, 2001Hello,
I've made major changes to all the tbls in my db. I created new tables and rename the old ones as table1Old and in table1 I've added 2 columns. I want the data from table1Old to table1(my new modified table). Can anyone guide me how can I do this.
insert into table1
select * from table1Old.
It gives me Insert error
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
The DTS doesnot work as its in same database.
Can anyone guide me
Thanks.
Khurram
Need Urgent Help
May 24, 2001One of my website page run with the following error:
"Microsoft OLE DB Provider for SQL Server error '80040e09'
SELECT permission denied on object 'Student', database 'stuents', owner 'dbo'. "
I know the problem is because of the permission setup is not correct. But I don't know how to set up it?
My server is MS SQL 7.0.
Thanks for your kind help
ddd
Very Urgent
Aug 7, 2001I tried to run a query on production server and it gave me this error.
Msg 10008, Level 9, State 10008
Possible network error: Bad token from SQL Server: Datastream processing out of sync. General network error. Check your documentation.
Msg 10005, Level 1, State 10005
DBPROCESS is dead or not enabled.
Msg 10018, Level 9, State 10018
Possible network error: Error in closing network connection. General network error. Check your documentation.
Please help. I am completely clueless about what to do and company DBA is out of city.
Thanks
Urgent
Aug 31, 2001According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
As Said by someone i took a full backup...tooooo it does not solve the issue.
Very Very URgent Please Help !!!!!
Oct 5, 2001Need to write a storedprocedure to find the disk space of the server and disk space used on the server,there are devices created for the backup on these guide lines i need to check the is the disk space used is more than cetain % say 75% than find out which device occupies the maximum space out of all and reintialize it so as to free up the space.
We need to then schedule this every night so that it checks the space.
Could anyone help me with the procedure.
Whole heartdly thanks to every one in advance..
Kavitha
Very Urgent Help
Oct 22, 2001Hello,
I am trying to run the following
if (select REQUEST from TEAM3B_PULL_TOTAL_TST) != (select REQUEST from REQUEST_BUS_REQ_TST)
begin
Insert into REQUEST_BUS_REQ_TST (b.request,b.business_req_id,b.bus_test,b.test_cas e_id,b.test_case_source,b.test_case_descr)
select a.request,a.business_req_id,a.bus_test,a.test_case _id,a.test_case_source,a.test_case as test_case_description
from TEAM3B_PULL_TOTAL_TST as A JOIN REQUEST_BUS_REQ_TST AS B ON A.REQUEST = B.REQUEST
Order by b.REQUEST,b.BUSINESS_REQ_ID,b.TEST_CASE_ID
end
and I keep getting the error message of
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have tried using not in with this statement and it also gives me the same error. I need to say if request in table one does not equal request in table two then insert values into table one. Else insert the values into another table(table 3).
Any help would be greatly appreicated.!!
Urgent Help
Oct 26, 2001Hello,
I have the following code and it is only inserting one row and I need to insert almost 1000 rows. Can anyone tell me what I am doing wrong? The books on line really are not helping me that much.
What I am trying to do is if the request in TEAM3B_PULL_TOTAL_TST is not in the tables REQUEST_BUS_REQ_TST and BUS_REQ_DESCRIPTION_TST then insert that request id into those tables. If the id does exists in those tables then insert that request id into the table RDD_UPDATE_TST
Thanks in advance.
declare @temp_request as varchar(8)
Select @temp_request = request
from TEAM3B_PULL_TOTAL_TST
if not exists (select *
from TEST_REPORT.dbo.REQUEST_BUS_REQ_TST
where REQUEST = @temp_request)
begin
Insert into REQUEST_BUS_REQ_TST(REQUEST,BUSINESS_REQ_ID,BUS_TE ST,TEST_CASE_ID,TEST_CASE_SOURCE,TEST_CASE_DESCR)
select REQUEST,BUSINESS_REQ_ID,BUS_TEST,TEST_CASE_ID,TEST _CASE_SOURCE,TEST_CASE_DESCR
from TEAM3B_PULL_TOTAL_TST
where request = @temp_request
end
if not exists (select *
from BUS_REQ_DESCRIPTION_TST
where REQUEST = @temp_request)
begin
Insert into TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST(request,bu siness_req_id,source)
select request,business_req_id,test_case_source
from TEAM3B_PULL_TOTAL_TST
where request = @temp_request
end
Else
begin
Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr)
select request,business_req_id,test_case_id,test_case_des cr
from TEAM3B_PULL_TOTAL_TST
where request = @temp_request
end
Urgent Help Pls!
Oct 26, 2001how do i Start sqlagent service with domain account?
View 1 Replies View Related