Hi everyone,
I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation).
Is there a possibility to empty a data table automatically every day (at a certain moment)?
Kind regards,
Maxime
I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.
However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!
Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?
Andy
I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.
View 5 Replies View RelatedHi,
Ive got a server which replicates an awful lot of constantly changing data - the db itself could be around 2gb in size, however with the replication data in it it has ballooned to 6gb. On top of this, there is all the data in the D:MSSQLREPLDATAunc directories which seem to represent another log of some description.
Can I delete this data? I cant see what it would be used for and it totals some 25Gb! Obviously I cannot have this lying around on my server.
Does anyone know what it is used for?
thanks
Pete Storey
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies View RelatedI have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I need to delete from this table all the duplicate records and leave only one record. The table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I work with sqlCE for Mobile...
I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber
Customer 1 - Phone number 1
Customer 2 - Phone number 2
Customer 2 - Phone number 3
Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
Hi,I have this stored procedure that I need to run daily, how do I dothis?Thanks,Mike<><
View 5 Replies View RelatedI'd like to remove any cruft that's built up in the system over thelast couple of years. Obviously there's old information no longerneeded, I'm just not sure what is there.I've been cleaning up backup history in MSDB withsp_delete_backuphistory (leaving the last year's data) - what else isthere?P.S. to anyone else who needs to use sp_delete_backuphistory - if youhave a lot of backups, you really need to add some indexes, otherwiseit'll take days to remove the history. Go through the SP and figureout which tables/fields you need to index, as there are several. Thebiggies are (in multiple tables) media_set_id, backup_set_id, andrestore_history_id.
View 1 Replies View RelatedHi,I have a form that should show 2 pictures based on table entries.I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.Can someone help me with the sql query?I can do SELECT VoteId FROM tblVotes WHERE Approved=True..But how do I make selection a random one that changes every time the user gets another entry?
View 2 Replies View RelatedIs there a way I can have all entries in a table automatically in caps once entered by user, import or any other way ?
Thanks a lot in advance.
-PFD
For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .
i.e
'some_table' has data like this
3,entry3
2,entry2
4,entry4
1,entry1
I want some DB table or Index that holds data like this about above 'some_table'
row_id .... .... ....
1
2
3
4
here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...
Is there an automated way of regenerating the stored procedures, views, and triggers? Currently I drop and recreate them periodically to ensure that the dependancies are all correct and SQL uses the best plan. Is there a way to have SQL Server do this for me???
Thanks for any help with this issue...
Hello,
Since we started using another sales program (1C Enterprise), SQL Server started "crashing" (connection between our program and SQL Server breaks)
with this assertion error:
Event ID: 17066
SQL Server Assertion: File: <"xrange.cpp">, line=399 Failed Assertion = '0 == pxteRangeHrow->m_pxteFetch'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
After this error DBCC CHECKDB doesn't find any priblems.
I can't find what exactly causes it, sometimes we get it only once a week, but today I got it four times in a row.
We had the same error on all versions of SQL Server 2005, SQL Server SP1, SQL Server SP1+Hot fixes, etc.
select @@version
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)
AS
SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return
Hi all
I would like to know if its possible to "Save" records when they get deleted.
For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...
In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.
All this should go to a seperate database or seperate table, it doesnt really matter.
My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.
The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.
Here is a sample of some data:
User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3
Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:
User1 Group1
User2 Group2
User1 Group1
I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.
Is there a simpler way to prevent duplicate entries in a table using sql?
Thanks a lot,
Chris
I have a question on appending and deleting entries in mysql table. This is my sample table.
table name: details
id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
[Code] ....
I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries
E.g. for id name ="PSK_30s1207681L002" AND median = 5 we have four entries
id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
But I want to retain only the one having longest first and end points ie
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
Here ,since for id name ="PSK_30s1207681L002" & median = N we have only 1 entry I want retain that one.
My final output in mysql table should look like this.
id_name | model | mode | media| first | last | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
| PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
| PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |
[Code] ....
Is there anything that I could do to append it?
I am troubling SQL stored procedures. The SP has temp table created to contain data. How can I populate the temp table in the SQL debug mode?
View 1 Replies View RelatedHi,
I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it.
Can anyone provide me the syntax for the same?
I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX.
Please help me out...
Helo everybody,
How can i find a dupplicate entry in one table?
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[code]....
I got error while updating a table ,duplicate entries and trigger got fired ..so how to debug the trigger to know duplicate record.
View 1 Replies View RelatedHiOur product uses MS-SQL Server 2000. One of our customer has 10installations with each installation stroring data in its own database.Now the customer wants to consolidate these databases into one and wealready have plan for that by consolidating one DB at a time. But firstthey want to find how many unique or duplicate entries they have acrossall the 10 databasesAssumptions:1. All the databases reside on the same server. (This is just anassumption, not the real environment at customer site)2. Databases can not be merged before it is found how many unique orduplicate rows exist.Table under consideration:Message(HashID PK,....)# of rows in Message table in each of databases: 1 MillionHere is my question: How can I find how many unique or duplicateentries they have across all the 10 databases. I easily find uniquerows for two databases with a query like this:SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ONdb2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULLHow can I do this for 10 databases. This will require factorial of 10queries to solve this problem.I will appreciate if someone can provide hint on this.RegardsAK
View 3 Replies View RelatedI have a table 'Group2Operation' that stores many to many relationsbetween the 'Group' table and the 'Operation' table (each group is haspermission to perform one or more of the available operations)PROBLEM=======I need to prevent duplicate entries being created. e.g. lets say thatin the 'Group2Operation' table a record links the 'editor' group tothe 'publish' operation. Should I prevent an administrator creating aduplicate of that record? (Otherwise deleting that permission willhave to be done twice or more for it to be effective)SOLUTION?=========So far I've done this with a trigger:CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2OperationFOR INSERT, UPDATEAS UPDATE Group2OperationSET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTagThe 'NoDuplicate' unique index column in the Group2Operation tablestores a concatenation of the unique group and operation identifiers.So when an attempt is made to create a record, the trigger is fired.If there is a duplicate, this will mean a duplicate entry in the'NoDuplicate' column. As a result, the INSERT or UPDATE will fail andthe duplication will be prevented.WHAT DO YOU THINK?==================What do you think? Am I going about this in the right way? Is atrigger a good way to do this or should I rely on application logic toprevent duplicates?Any help appreciated by this db novice.John Grist
View 2 Replies View RelatedSomeone ran an update statement multiple times so their are multiple entries in the table. What is the quickest way to track down the multiple entries? I would only want to see where timein and timeoff exist in the table multiple times for the same id. So this would be a duplicate
EntryID -- ID -- timein -- timeoff
1487 11 2015-05-05 16:33:23 2015-05-05 18:45:26
1623 11 2015-05-05 16:33:23 2015-05-05 18:45:26
I am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?
I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;
This is a snap shot of the output:
ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS
[Code] ....
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
Hello all,
I've been struggling with an interesting problem. I currently have a solution but it is very slow.
I will be cycling data through a table. Each cycle has 1 million records with 60 fields. One procedure I need to perform on this data is a character cleanse. I have a list of 12 characters that need to be removed.
Right now I have a stored procedure that pulls the characters from a table one at a time. It feeds it to a nested loop that replaces the character with nothing ('') on records that contain the character (something like "update tbl1 set FIELD = replace(FIELD, '&', '') where Field like '%&%'"). This works... but seems rather inefficient. It can take 10 minutes to do a 250,000 record table.
I have tried borrowing regular expressions from VBscript using com objects, it worked and seemed more efficient at first but then I threw a large file at it and it took a half hour to complete.
Im running SQL 2005 on a dual Xeon 3.4 box with 2 gb of ram.
Any advice would be greatly appreciated!!
~~~Thanks~~
I am looking for some T_SQL code that would help us eliminateduplicate data entry into our mailing db. Finding and removing theexisting dups is not rocket science, but I am looking for a completesolution.We also want to be able to come up with a T-SQL script to beintelligent enough to know if:"Bill Jones" is the same as "Billy Jones" or"Bill Jones" is the same as "William Jones" or"Bill Jones at 123 My street" is the same as "Bill Jones at 123 MySt."Purchasing scripts from a company is not out of the question. Its amatter of script cost versus my hourly rate to create them... Needlessto say, something cheap.We have only heard of one app that is dynamic enough to handle this,and the cost was way, way out of our range, 6 figures is almost 2years salary.Any ideas would be greatly appreciated.
View 4 Replies View RelatedWe can use rs.exe to publish a report automatically, but how do we do the reverse - remove a report automatically?
Is there a way by using a MS utility?
Is there a way by writing code, using library objects?
Part of the problem we face is having servers with RS all set up with a particular connection string and published reports. We then want to change everything and we want to do it automatically.