I want to modify a current DTS job I have. It simply copies records from one table to another after a given date. The catch is I only have read priviliges on the source table. I currently delete all the records from the destination table but have to use Query Analyzer to do it and then run the current package that is just a simple select statement. The statement in the DTS job is something like:
SELECT *
FROM tablename
WHERE date_field > '2004-09-30'
I have been running this multiple times per month since every week or so I want to get more recent data to run a couple of reports. The source table is not ours, it is in a different department, so I can only read off of it. The destination server is ours so I have full priviliges there. I assume the fact that the source server allows me limited access that I cannot run a "delete from tablename" first even though it is on our table? Is there another way to set this up? Thanks again. You guys are the best.
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
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.
Hi,I am having trouble importing data from an excel spreadsheet into MSSQL Server 2000 using DTS Wizard. The DTS import process issuccessfull, no errors, but only 50 rows of approx. 1500 rows of dataare imported. I tried to remove 20 rows in the excel spreadsheet inthe interval row 0-50. When i later ran the import, only 30 rows wereimported. I deleted almost every row in the interval 0-50, with theresult of the import having 0 rows imported (but job ransuccessfully). I decided to delete rows 0-100 in the spreadsheet inorder to see if the resolved the problem, but it didn't. As Isuspected something in the excel file to be the cause, I exported theexcel spreadsheeet to a tab delimited textfile, with only one row. ADTS import resulted in importing approx 100 rows, double the amount ofthe textfile, but the other 1400 rows were not imported. The data inthe column is containing numeric values only.Please help me! What could possibly be the cause of DTS skipping rowslike that. DTS doesn't feel reliable at all :/Regards,Björn
We are using SQL Server 7 on Win 2k and there are some DTS packages set up which empty some large tables (delete from) and then import some datafiles. The imported files are about 13 GB and during the process the log file gets to about 10GB and then runs out of disk space.
Is there a trick to empty a table without logging it? (a la LOAD Replace from Null in DB2)? How can I go about keeping the log file size down during this operation?
I think the DB is set to autocommit, the trunc log on chkpt. is set on as is the select into/bulk copy (altho I'm reasonable sure we arent availaing of the bulk copy for the import).
Hello, its hard to explain, i have a table like this: --userpage_visitors-- id bigint owner nvarchar(20) visitor nvarchar(20) created datetime Then i have some code like this: (@Visitor is send to the stored proc)DECLARE @lastuser nvarchar(20)SELECT TOP 1 @lastuser = visitor FROM userpage_visitors WHERE (owner = @UserName) ORDER BY created DESCIF (@lastuser <> @Visitor)BEGININSERT INTO userpage_visitors (owner, visitor, created) VALUES (@UserName, @Visitor, @Created)-- delete hereEND Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas? Patrick
Hello, I have 3 tables with their columns as follows: + LabelsInDocs [LabelId] PK FK , [DocsId] PK FK + Labels [LabelId] PK , [LabelName] + Docs [DocId] PK , [DocUrl] I set Cascade Delete On so when I delete a Doc all records in LabelsInDocs will be deleted. However, when a Doc is deleted I want also to delete all records in Labels for the labels which do not have any Doc associated to it in LabelsInDocs. How can I do this? Thanks, Miguel
hello friends. i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50) i want to delete my record automatically from my table1 after two weeks from inserting my record date.. i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before how can i do this ? cheers
Currently i writing a program to delete Records in the SQL database using VB.net language... BUT i not sure whether i am right??? Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help
Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc
Dim objConnection As New SqlConnection(strConnection) Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no" Dim dbComm As New SqlCommand(strSQL, objConnection)
Hello,I have a database called articlesI want to delete all articles from a subgroup e.g. DVDIf I am in the enterprisemanager what steps do I have to take to do thisthanks in advancePaul
I have some records that will not delete, whenever I run a deletestatement in the Query analyzer, it never completes the statement, andI am only deleting one record at a time. Can anyone tell me why arecord wouldn't delete?
I finally put together a SSIS package that takes a Text File and successfully imports its data into the right table. My question is, where in the package's properties can I find the option to Delete all rows from Destination Columns prior to Importing. I have looked everywhere in the Package Explorer for this setting. Thanx in advance.
I have a table, gdbdoc, that contains record-key pairs, linking records in another table. There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is SELECT ... Where DCIindiid = A ... union SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2) The link-creation process normally checks whether there is already a link in either direction. Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction. However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link. If I run a query: - select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid this displays all the records where one record links A -> B and there is also another record that links B -> A. How do I write a query to delete ONE of the pair of duplicate records? I have two problems: - Problem 1: Table gdbdoc is keyed on (DCIindiid, DCILinkid). Both guids are needed to create a unique key, and the table does not have a single key field. You can't write DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid as the DELETE ... SELECT ... syntax only seems to support a single returned value. Problem 2. If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links. Afterthought: Problem 2 seems easily solvable: add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement. Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select. Obviously I don't care which of the two links (A->B or B->A) is deleted. Regards, Robert Barnes
I use a tabel for storin log data from a mail server. I noticed that I'm getting duplicate records, is there a way to delete the socond and/or third entry so I dont have any duplicates?
Userid is auto number, lastname and emailaddress are PK.
I want to delete duplicate records. If lastname and emailaddress are the same, only keep a record which createdate is the most newest date. See above example I only want to the record which userid is 3. I have alreday created a code which I attached below. This code onle keep a record which userid is 1.
Anybody can help me to solve this problem? Thanks.
============== My current code ==================== delete from userprofile where userprofile.userid in --list all rows that have duplicates (select p.userid from userprofile as p where exists (select lastname, emailaddress from userprofile where lastname = p.lastname and emailaddress = p.emailaddress group by lastname, emailaddress having count (userid)>1)) and userprofile.userid not in --list on row from each set of duplicate (select min(p.userid) from userprofile as p where exists (select lastname, emailaddress from userprofile where lastname = p.lastname and emailaddress = p.emailaddress group by lastname, emailaddress having count (userid)>1) group by lastname, emailaddress)
:confused: Urgent!! Hi there. I use MS SQL server. I would like to separate the data from one table to two tables refer to two reference tables and the following conditions:
Let say these two reference tables are called: Table A & Table B
Group A: 1. Same date in Table A & Table B 2. Same ID in Table A & Table B (ID is not unique) 3. Same name in Table A & Table B (Name is not unique)
Combine all of these three conditions for unique identifier.
I used the following SQL code to separate the required data that match the above conditions to the new table. (Code) select a.Project, a.Site, a.S_number, a.Field_ID, a.Method, a.Analyte, a.Result, a.Units, a.Qualifier, a.Dilution_Factor, a.Reporting_limit, a.Recovery_, a.Matrix, a.CAS_Number, a.Sample_Date, a.Received_Date, a.Prep_Date, a.Analysis_Date, a.Batch_ID, a.Data_Package_num_SDG, a.Lab_Sample_ID, a.Lab into APPL_union_exist from APPL_union_update a, Before_01012004_report b where a.Field_ID = b.[Field Sample] and a.Sample_Date = b.Collected and a.Analyte = b.Analyte
However, I don't know how to delete the data that copied to the new table in original table, or separate that to the new table. Wish someone can help me. Thanks a lot
i don't know if this is the right forum to post to, but here i go:
I'm having a problem... I need to delete several records having as criteria their pk. The deletion is made using SqlParameter in the code and a stored procedure on server.
as far as i know, i can do this, either using a IN list:
- build and pass the list of id's from code to the sp: delete from tbl where attachment.id in (1,2,3)
OR
- execute a simple delete several times.
So far i tried first option, but i get errors in converting the list to ints; that is what the sql engine says. I prefer this option because i think is faster and requires less roundtrips to the sql server.
My questions are: 1. how do i build a sane IN list? 2. is it worth to use a delete in a loop? the records that must be deleted are 5 at most, per job. 1 job can have [0..5] attachments.
I have a database in Simple mode from which I wish to delete several million rows of one table (not all rows so 'TRUNCATE TABLE' is no good), without making the transaction log file grow to several GB in size. Any ideas??? I'm almost at the point where I might create a new table and copy the records I want to keep instead... Thanks, Simon.
I have a little dilemma. I have a table ALLTABLE that has duplicate records and I want to delete them. ALLTABLE has these columns with these values for example:
As you can see there is some duplicates in the first 3 rows and the final 2 (the entity number is the only difference). I want the table to look like this:
Policy Premium Class State Entity Number ADC-WC-0010005-0 25476 63 31 1 ADC-WC-0010005-0 1457 63 29 4 ADC-WC-0010092-1 2322 63 37 1 ADC-WC-0010344-0 515 63 01 1
Thank you so much for the help. It is really appreciated.
I have the following table customerid customername ------------------------ 1 AAA 1 AAA 2 BBB 2 BBB 2 BBB 3 CCC 3 CCC
Here, I need to delete duplicate records from the above table. After deleting the duplicate records the table should be like this: customerid customername ------------------------ 1 AAA 2 BBB 3 CCC
I was wondering if any one could help me. I have two tables from which I need to delete information from. I have an excel spreadsheet which has the reecords in need to delete.
Is there any way I can delete them all in one go instead of going through each delete query manually?
In SQL Server 2005 is there a way to delete records from a table when the user logs out of the software? Other db products allow you to create a trigger on the logout event. Does SQL Server have an equivalent?
one table must be deleted based on a filter (I mean the table is not delete completely but only some records), I would like to delete same records in the second table. for ex: table 1: pk: 1,2,3,4,5 table 2: pk: 1,2,3,4,5 table 1: deleting 1,2, 3 thus also in table 2 pk: 1,2,3 must be deleted. At the and of process Table1 and Table2 must have the same records (always also in the case of failure, errors and so on ).
The target is avoid using triggers. OUTPUT is not useful because it writes what is deleted (or may be useful but how to use it?).
How can i import records into SQL Compact I have the information in Access and also the same information in Excel spreadsheets I dont have a full version of SQL What are my choices, is there perhaps someone that makes a import program
I've got an application I've written that should be able to (using a table) insert, update or delete records in SQL Server 2005. It can insert and update fine but it cannot delete any records. I'm totally stumped for how to figure out what's going on. I can remote on to the Windows 2003 Enterprise Server without any trouble so I should be able to read any log files or open the SQL Managemenent tools. How do I figure out what the issue is? It's a very strange thing and I'm stumped.
