MS SQL Server :: DB Deletion Time

Deletion Query
Ok, so I have an issue, was wondering if anybody else has any suggestions.

I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.

1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text)
2. Table contains about 70 million records
3. Table has 6 indexes associated to it
4. Table has 2 views associated to it.
5. Table has 8 foreign keys associated to it.

I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.

When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.

The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.

Here's a DBCC SHOWCONTIG on our table

DBCC SHOWCONTIG scanning 'message' table...
Table: 'message' (1448040590); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 51602
- Extents Scanned..............................: 6486
- Extent Switches..............................: 6948
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.83% [6451:6949]
- Logical Scan Fragmentation ..................: 0.54%
- Extent Scan Fragmentation ...................: 0.93%
- Avg. Bytes Free per Page.....................: 93.5
- Avg. Page Density (full).....................: 98.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)

Any suggestions on how to delete records efficiently?

View Replies !

User Deletion Log SQL
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.

View Replies !

0 KB File Deletion

Hello,
I want to delete a 0KB file on one of the disks in my server. Do any of you have any idea how to do it??
Thanks,
Narayan

View Replies !

How To Prevent Db Deletion
Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

View Replies !

Database Deletion
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.

View Replies !

Deletion And Identity Reset
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.

View Replies !

Alert On Data Deletion
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.

View Replies !

Recovering From Transaction Log Deletion In 6.5
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/

View Replies !

Database Still 'exists' After Deletion
hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

------Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

------The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

------The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'";
string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}

View Replies !

For Deletion..trigger Is Not Working
Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?

Any idea?

Any help will be highly appreciated.

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@var_DB_contract INTEGER,
@var_CQE INTEGER,
@var_PC INTEGER,
@var_item VARCHAR(7),
@var_AMT_PAID INTEGER,
@var_AMT_RET INTEGER,
@var_ITEM_NEW VARCHAR(1),
@var_quant DECIMAL,
@var_fiyr INTEGER,
@var_amt_result INTEGER,
@var_amt_ret_result INTEGER,
@var_amt_old INTEGER,
@var_amt_ret_old INTEGER,
@var_quant_result INTEGER,
@var_quant_new INTEGER,
@var_quant_old INTEGER,
@Item_new VARCHAR(7),
@var_chk varchar(1)

--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
IF @var_db_contract IS NOT NULL
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk ="Y"
END
ELSE
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk="N"
END
SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)

SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)

SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0)
SELECT @item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @var_db_contract
AND PC_CODE = @var_PC
AND ITEM_NO = @var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result
where db_contract = @var_db_contract

IF @item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and fy = @var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result,
qtd = isnull(qtd,0) + @var_quant_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and item_no = @var_item

View Replies !

Daily Deletion Of Records
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"

select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180

If it meets this criteria I can change the select to a delete? Please Let me know what you think

View Replies !

Deletion Of Duplicate Values
hi,

i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).

can you help?

View Replies !

How To Compare Data Before Deletion

SET identity_insert dbo.table1 on

GO

insert into dbo.table1(
PrimaryKeyCol,Col1, Col2 .....)

select

PrimaryKeyCol,Col1, Col2......

from [Sever].Database.dbo.table1 as ClientColumn

where not exists(

select * from dbo.table1 as ServerColumn

where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol

)

DELETE FROM [Server].Database.dbo.table1

where exists(

where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol

)

SET identity_insert dbo.table1 off

GO

I can't complie this code.. anybody see where I went wrong??

Thanks for all your help.

View Replies !

Table Deletion Error
hi

i am using sql server 2005 express edition , with asp.net

i am trying to delete a table programmatically

a button on a form , if the client clicked it , then a table
should be dropped .

but always i get an error message , that says "cannot drop table <table name> , becaust it does not exist or you do not have premissions to do that"

could any body help plz

thax

ghassan

View Replies !

Deletion Problem In Sql Table
i am using this statement for deleting a single row in sql table.
"DELETE FROM Random WHERE NewID= '" & strwinner & "'"

where "strwinner" is the variable which contains the row to be deleted.
the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something.
iam executing this statement by using ExecuteNonQuery in my .aspx page.
please help

View Replies !

DOS Utility For File Deletion.
Does anyone has a script/exe for periodic file deletion, using date comparison?

------------------------
I think, therefore I am - Rene Descartes

View Replies !

Auto Deletion Of Records Sqlserver
Hi I am not sure if I am at right place, anyhow I hope I am :) Now the question: I am using an ASP.net Application with SQL-Server. I want to make a page so that it set the expiration time (date) for certain record and once that time reaches, it deletes those records, or make any updates to the record (what ever applicable). I also want to control this auto deletion from my application, means that turn this On/Off whenever needed. I am not sure how to start this. I was told by a friend that I need to use triggers from SQL-server but I need some help. Can anyone help me out on this? RegardsMykhan

View Replies !

Deletion Old Data In Replication Environment
Hi to allI have a question about deletion of amount of data:My production environment is this one:- one publisher with a database (historycal events)- 50 subscribers with the prev database in unidirectional replicationunidirectional (from subscribers to publisher)My target was capturing events from the subscribers to send them topublisher (later I can do reports on it).Once the data is on the server i don't need them any more in subscribers.Now I would like to delete the oldest data (year 2003) of some table on thepublisher (remember that replication is unidirectional S->P).The tables contain about 6-7 millions of records.I delete one month per time. The process is about 30 minutes long and themerge agent subscribers changes in retry state.Can I use these queries to make faster this process? Eventually what kind ofproblems can I have ?DELETE FROM mydb WITH (PAGLOCK) WHERE mydb.dbo.mydate Between date1 anddate2orDELETE FROM mydb WITH (ROWLOCK) WHERE mydb.dbo.mydate Between date1 anddate2Thank you very much for your support.Marco

View Replies !

Rows Deletion Affected By Cursor
Hello,

I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)

View Replies !

Retrival And Deletion Of Duplicate Rows.
I have a table...say tb1 of 20 columns which has 2.7 million rows. There is no PK and the only way of identifying a unique row can be done with combination of column1+column2+column3.
Can anyone help me how to idetify the duplicate rows and also delete the duplicate rows. And to commit after every 5000 rows.
ITS VERY URGENT....Thanks in advance.

View Replies !

Page 2 - Deletion Of Duplicate Values
Quote: Originally Posted by achoudry ps i am using sqlserver well, whaddya know, eh

do you realize you posted in the mysql forum?

i'm gonna move this thread to the sql server forum

View Replies !

SSIS Crash On Breakpoint Deletion
I'm having an issue with trying to delete breakpoints in my SSIS package. I think the breakpoints were created when I added dataviewers through my process, however the breakpoints were not deleted when I removed the dataviewers. It would then appear that my process would not halt on ANY breakpoints - orphaned or valid.

Furthermore, whenever I tried deleting the breakpoints manually, my whole IDE would crash. I got around the issue by closing the dtsx page first, then deleting the breakpoints, and reopening my dtsx.

Are these valid bugs with SSIS? Has anyone else experienced this? I'm running this against a SQL Server 2005 database using VStudio 2005 as an IDE.

Thanks!
Chris P

View Replies !

Deletion/Rename Of Master Database.
Hi All,

Can we have an sql server installation where we dont have a master database. Can the complete data dictionary be stored in another database , or put it other way can master database be renamed.

I have a need to assume that there will always be a master database for any SQL server instance. Want to confirm whether this assumption is true or not.

Thanks in advance.

Chandrakant Karale.

View Replies !

Unique Columns And Logical Deletion
In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships. However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for "active" rows. I'm hoping someone can provide some advice on how to best handle this situation.

Here is an example to work from. Suppose we have a table that contains job positions. The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been "deleted". This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company. You can substitute your own scenarios here as there are quite a few.

Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want. However we will want to be able to "delete" a position but later add a new position with the same name. One could argue that we should just "undelete" the original position but that is not often a good idea so assume "undelete" is not an option. Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name. We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.

My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column. The UniqueName column is set (via a trigger) to the Name column when the position is active. When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness. This is handled by the system and not exposed to clients.

Any advice or alternatives on this approach (preferably with advantages and disadvantages)? Thanks.

Michael Taylor - 5/31/07

http://p3net.mvps.org

View Replies !

Deletion Of Parent And Child Table
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

View Replies !

MS SQL Server 7: Deletion Of Child Records.
Hello All,

I have been involved in a project wherein i have to delete old records from two table and the corresponding child records from the interlinked tables.
I try to find a SQL Procedure that deletes the child records, but that procedure uses the sysforeignkeys table for recursively deleting the child records.
When i looked at my sysoreignkeys table, it was empty(no rows).
Request you all if you can provide me the SQL Procedure that can delete record in the table with the child records.

Thanks in advance

Regards,
Manoj Kumar

View Replies !

Deletion Of Data Other Than English Language
Anil Chauhan writes "Hello experts,

I have a table in sql server 2000 which has over 94000 records.
I have to delete a record from table ,which record having a language other than english .
I need to clean the table by removing all the data which are in other language .
My main table has 12 fields .

Thanks in advance."

View Replies !

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.

Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View Replies !

Capturing Mysterious Truncation/deletion Of A Table
I have a dts which creates a table which is utilized on my localintranet. The DTS runs without error and the table iscreated/populated/transfered to the appropriate db. Then it appearsthat there is an action on this table which truncates it. I have beenunable to determine the culprit. Can I create a trigger that willcapture truncation? I have tried to create a trigger to capture thisinformation but none that I attempt seem to work on capturing atruncation or a drop table and re-create.Any help would be greatly appreciated.MT.

View Replies !

File System Task-deletion Operation
I am using file system task for deletion of text file from a specified folder.

but i want that one text file with name ="ssis.txt" and "ssrs.txt "should not get delete.

Is there any option or any expression that we can set and avoide the deletion of ssis.txt and ssrs.txt and still delete all the remaining all text file from a folder.

I just wish to use the file system task and perform this.

Cannt we use some expression infile system task and disable the deletion for say 2 files and let the deletion happen for other remaining files.

Please suggest any method or expression by example.

View Replies !

Data Deletion On MS Access Table Hangs
Hello,
I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager.

There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database.

Any ideas?

Thank you for your help!

cdun2

View Replies !

Control If A SQL Database Exists Before Its Creation Or Deletion
Hi,
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]

I tried to adapt the statement to the database case, modifying it as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1)
drop database [Database1]

but it didn't function (it was a blind attempt).
Can you suggest me a statement to do that?
Thank you very much

View Replies !

How To Avoid Non-published Columns Deletion On Subscriptions' Reset
Hi all!
I have been doing some testing with transactional replication. I have a table (TEST) with the following columns:

[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
[stock] [int] NOT NULL CONSTRAINT [DF_Prueba1_stock] DEFAULT ((0)),

I want the table rows to have an independent stock value for each database. So,
I made a transactional publication with updatable subscriptions, to replicate all the table columns, except the stock column.

The problem is when I reset subscriptions: the table is deleted an created again on the subscriber, so all the data stored at the stock column is lost. I tried to solve this changing the "Action if name is in use" option at the publication. I choose to keep the current object without changes, but I began having problems with the generation of the identity values at the subscriber.

Any suggestions will be welcomed

Thanks in advance ;-)

View Replies !

Large Amount Data Deletion Blocking Other Operations
Hi Experts:

We have several database linked via merge replications. Due to business requirements, we need to delete 5M rows in one table, we did it on one subscriber. However, the publisher kept uploading the deletion operations from the subscriber and blocked any downloading operation from publisher to subscriber. How can we acceralte the replications now as this has already operated in 2 days, and will continue 1-2 days? Is it possible to set the publisher take the downloading before uploading? How to speed up large amount data deletion operations in replication environment?

Thanks in advance!

Ron

View Replies !

Insertion And Deletion Of Records Inside A Single Stored Procedure
Hi,
I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days.
Thanx in Advance

View Replies !

Query Problem: Automatically Resetting A Sequential Number List After Row Deletion
Hello!
Got a problem I hope some clever people can help me out with..

I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this

PageNum ItemNum ItemDescription
----------- ----------- -------------------
1 1 aaaaaaa
1 2 bbbbbbb
1 3 cccccccc
2 1 ddddddd
2 2 eeeeeee
2 3 ffffffffffff
3 1 ggggggg
3 2 hhhhhhh
3 3 iiiiiiiiiiiiiiii

The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc).

This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com:

DECLARE @intCounter int
SET @intCounter = 0
UPDATE <Item Table>
@intCounter = ItemNum = @intCounter + 1
WHERE Pagenumber = <Currently Selected Page>

However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is:

DECLARE @intCounter int
SET @intCounter = 1
UPDATE <Itemtable>
SET @intCounter = PageNum = CASE
WHEN @intCounter = PageNum - 1 THEN @intCounter + 1
WHEN @intCounter = PageNum - 2 THEN @intCounter + 1
ELSE @intCounter
END

But this doesn't quite work.
Anyone got any other ideas??

Thanks
Greg

View Replies !

The Publisher Failed To Allocate A New Set Of Identity Ranges For The Subscription After Another Publication Deletion
Hi.

First of all, I apologize for my english

I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.

But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"

If i'm watching the status of the leftover replication through the replication monitor, i get this error message :

"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"

I checked the given links but they're useless.

So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :

"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

Failed to pr"

I didnt have any idea to correct this issue, so I would appreciate any help.

Thanks.

David.

View Replies !

FullText Search Error 1075: The Dependency Service Does Not Exist Or Has Been Marked For Deletion
Hi Guys,

Can you please help me with the following problem:

My website keeps crashing and the FullText Search cannot start. I keep getting errors 7003 in the Event Log with the following message:

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7003
Date:  3/8/2008
Time:  3:18:24 PM
User:  N/A
Computer: MASTER
Description:
The SQL Server FullText Search (MSSQLSERVER) service depends on the following nonexistent service: NTLMSSP
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

When trying to start the service from Services window I get the following error:

FullText Search Error 1075: The dependency service does not exist or has been marked for deletion

Checked the Dependencies in Registry and sqlserver has the following dependencies: RPCSS NTLMSSP

I have had to restart the server multiple times and cannot figure out what the problem is.

Windows Server 2003 Standart Edition
SQL Server 2005 Workgroup Edition

Please help

Albano

View Replies !

Problem With Getdate() In Transaction Takes The Insert Time Instead Of The Commit Time
Hi,

We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.

If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.

We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.

We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:

/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
        declare @time datetime
        select @time = getdate()

insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
        declare @time datetime
        select @time = getdate()

   update COMMIT_TEST_UPDATE
   set LAST_UPDATE = getdate()
   from COMMIT_TEST_UPDATE, deleted, inserted
   where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
     delete COMMIT_TEST_UPDATE
  from COMMIT_TEST_UPDATE, deleted
     where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE

Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).

Regards,

Mark

View Replies !

It Takes A Long Time To Insert The First Record Each Time When The Program Start
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

        cn.Open()
        sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
        scb = New SqlCeCommandBuilder(sda)
        sda.Update(dataset)
        cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

However, when I give up the dataset and using the following code:

            cn.Open()
            Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

           cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
            cn.Close()
            StartTime = Environment.TickCount

I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

View Replies !

A Time Series Prediction Was Requested With A Start Time Further In The Past Than The Internal Models Of The Mining Model
Hi all,

I have a very simple time series model which processing works fine without any problem. However when I run the following query

SELECT

[TimeSeries].[PriceChange],

[TimeSeries].[Symbol],

PredictTimeSeries(PriceChange, -3, 2)

From

[TimeSeries]

WHERE

[TimeSeries].[Symbol] = 'x'

I get the following error:

TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.

The following is the excerpt of the minding model script related to the two parameters:

<AlgorithmParameters>

<AlgorithmParameter>

<Name>MISSING_VALUE_SUBSTITUTION</Name>

<Value xsi:type="xsdtring">Previous</Value>

</AlgorithmParameter>

<AlgorithmParameter>

<Name>HISTORIC_MODEL_GAP</Name>

<Value xsi:type="xsd:int">1</Value>

</AlgorithmParameter>

<AlgorithmParameter>

<Name>HISTORIC_MODEL_COUNT</Name>

<Value xsi:type="xsd:int">10</Value>

</AlgorithmParameter>

</AlgorithmParameters>

These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?

View Replies !

Optimization Advice (CPU Time = 15203 Ms, Elapsed Time = 8114 Ms.)!!
Ok, so I have some horribly convuluted SQL that I would love to optomize. I'm not happy leaving it in it's current state, that's for sure!

I'm currently working on our test bed servers, so obviously my stats are out because of the "crap-ness" (yes, that's the technical term) of the hardware, but still, it should NEVER need to take this long!!

Basically, the issue arises in the nasty join to the career table (one employee can have multiple career lines). Just to make things complicated, employees can have any number of career records on any given date, these can even be input for future career events. The following SQL picks out the latest-current career date for each employee based on the career_date being <= GetDate() and the date of entry for this date being the greatest.

E.g.
career_date | datetime_created
2009-01-01 | 2006-05-05 13:55:21.000
2007-01-01 | 2006-05-05 13:54:18.000
2007-01-01 | 2006-05-05 13:52:55.000

From the above we want to return
2007-01-01 | 2006-05-05 13:54:18.000

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT a.sAMAccountNameAs 'sAMAccountName'
, a.userPrincipalNameAs 'userPrincipalName'
, 'TRUE'As 'Modify'
, RTRIM(e.unique_identifier)As 'employeeID'
, RTRIM(e.employee_number)As 'employeeNumber'
, RTRIM(e.known_as)
+ CASE WHEN RTRIM(e.surname) IS NOT NULL THEN
' ' + RTRIM(e.surname) ELSE NULL ENDAs 'displayName'
, RTRIM(e.known_as)As 'givenName'
, RTRIM(e.surname)As 'sn'
, RTRIM(c.job_title)As 'title'
, RTRIM(c.division)As 'company'
, RTRIM(c.department)As 'department'
, RTRIM(l.description)As 'physicalDeliveryOfficeName'
, RTRIM(REPLACE(am.dn,'\',''))As 'manager'
, t.full_mobile
+ CASE WHEN RTRIM(t.mobile_number) IS NOT NULL THEN
' (DD: ' + RTRIM(t.mobile_number) + ')'ELSE NULL END
As 'mobile'
, t.mobile_numberAs 'otherMobile'
, ad.address_ad_countryAs 'c'
, ad.address_ad_address1
+ CASE WHEN ad.address_ad_address2 IS NOT NULL THEN
', ' + ad.address_ad_address2 ELSE NULL END
+ CASE WHEN ad.address_ad_address3 IS NOT NULL THEN
', ' + ad.address_ad_address3 ELSE NULL END
+ CASE WHEN ad.address_ad_address4 IS NOT NULL THEN
', ' + ad.address_ad_address4 ELSE NULL END
+ CASE WHEN ad.address_ad_address5 IS NOT NULL THEN
', ' + ad.address_ad_address5 ELSE NULL ENDAs 'streetAddress'
, ad.address_ad_poboxAs 'postOfficeBox'
, ad.address_ad_cityAs 'l'
, ad.address_ad_CountyAs 'st'
, ad.address_ad_postcodeAs 'postalCode'
, RTRIM(ad.address_ad_telephone) +
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN
' (Ext: ' + RTRIM(a.othertelephone) + ')'
ELSE
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NULL THEN
'Ext: ' + RTRIM(a.othertelephone)
ELSE NULL
END
ENDAs 'telephoneNumber'
FROM employee e
LEFT
JOIN career c
ON c.parent_identifier = e.unique_identifier
AND c.career_date =(
SELECTmax(c2.career_date)
FROMpwa_master.career c2
WHEREc2.parent_identifier = c.parent_identifier
ANDc2.career_date <= GetDate()
)
AND c.datetime_created =(
SELECT max(c3.datetime_created)
FROMpwa_master.career c3
WHEREc3.parent_identifier = c.parent_identifier
ANDc3.career_date = c.career_date
)
LEFT
OUTER
JOIN AD_Import am
ON am.employeeNumber = c.manager_number
INNER
JOIN AD_Import a
ON a.employeeID = e.unique_identifier
LEFT
JOIN AD_Telephone t
ON t.unique_identifier = e.unique_identifier
LEFT
JOIN AD_Address ad
ON ad.address_pwa_location = e.location
LEFT
JOIN xlocat l
ON l.code = c.location
WHERE (a.employeeNumber IS NOT NULL
OR a.employeeID IS NOT NULL)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1706 row(s) affected)

Table 'AD_Import'. Scan count 4, logical reads 106, physical reads 0, read-ahead reads 0.
Table 'AD_Address'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'AD_Telephone'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 868, logical reads 956, physical reads 0, read-ahead reads 0.
Table 'xlocat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.
Table 'career'. Scan count 5088, logical reads 2564843, physical reads 0, read-ahead reads 0.
Table 'people'. Scan count 1697, logical reads 5253, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 826, logical reads 914, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15203 ms, elapsed time = 8114 ms.

Any advice on what I can do to optomize?

Oh judt to point out that "employee" is a view on the "Table 'people'."
EDIT: I know it's pointing out the obvious, but I'm pulling out the managers "DN" from AD_Import based on the manager_number and employeeNumber matching.

View Replies !

DB Deletion Time

Is there an option to find out the deleted DBs on a server? ------------------------ I think, therefore I am - Rene Descartes

Is there an option to find out the deleted DBs on a server?

------------------------
I think, therefore I am - Rene Descartes