MS SQL Server :: Replication :: Metadata Deletion Error

Replication Without Deletion

Mar 19, 2007

Hello there,

We are currently setting up out production server to the following requirements:

1. Every month, delete records that haven't been changed in the last 90 days.
2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.

The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.

Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.

Thanks for your time,

Ulrik Rasmussen

View 2 Replies View Related

Merge Replication / Metadata Cleanup / Help!

Jan 8, 2007

At a client site we just had a customer away on holidays, came back and went to replicate and received the error. I understand why this would happen after the default 14 days, and the obvious fix to prevent it from happening in the future is to extend the period, however I am hoping someone can offer me assistance on the best way to correct the issue now

Error is:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload).

The problem is we cannot lose the data that has been entered on this machine since the last replication. Writing scripts to manually save and pump this data will take days if not weeks (complex database). Is there any way to issue a command to cause a manual upload of the new information that is at the subscriber. Then I could just delete and recreate replication?

Any help or advice would be appreciated.

View 3 Replies View Related

Table Deletion Error

Oct 19, 2007

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 2 Replies View Related

Replication Problem After Running For 24 Hours With Action Msg Could Not Query Row Metadata At The 'Subscriber'

Sep 9, 2006

We are currently using a Merged replication and Push subscriber to replicate the databse from DB1 to DB2 every hours. The replication process successed for first 20 hours but we found that the replication process cannot be completed after 20 hours with the following error codes:

Action Code Last Action Msg

4 The process could not query row metadata at the 'Subscriber'

363 The process could not deliver insert(s) at the 'Subscriber'.

Thanks million for your kindly help!!!

View 1 Replies View Related

External Metadata Column Error

Oct 25, 2007

I keep getting the following error in SSIS. Also, I don't get the error on every server the package is run on, but less than 5 (the package is run on over 100).

"The external metadata column collection is out of synchronization with the data source columns. The column "Timestamp" needs to be added to the external metadata column collection"

Please tell me where I need to remove Timestamp from. Thanks

-Kyle

View 1 Replies View Related

Error: The Component Has Inconsistent Metadata

Jul 11, 2006

I have a Source Query with this sql set as a property expression:

"SELECT Category, Server_Name, Entitle_UserID,User_SubID,Start_Time,End_Time,Entitle_User_Name,Stat_Name,Stat_Count,Stat_Type,pk,create_date,run_num,Average,Median,Maximim FROM tbl_ws_stats WHERE pk > " + (DT_STR, 100, 1252)@[pk_var]

There is a message : 'The component has inconsistent metadata.'

Then when I click on the Source Query: 'The component is not in a valid state. Do you want the component to fix itself automatically?'

I also notice that there are no columns on the Column Mappings tab and no way to add columns.

How can I correct?

Thanks

View 9 Replies View Related

External Metadata Column Error

Oct 24, 2007

I keep getting the following error in SSIS. Also, I don't get the error on every server the package is run on, but less than 5 (the package is run on over 100).

"The external metadata column collection is out of synchronization with the data source columns. The column "Timestamp" needs to be added to the external metadata column collection"

Please tell me where I need to remove Timestamp from. Thanks
-Kyle

View 1 Replies View Related

An Error Occurred While Processing 'BackupMetadata' Metadata For Database

Jul 9, 2007

I have transferred a database from SQL 2000 to SQL 2005 and now I am getting an error when I try to run backup. I get there error "An error occurred while processing 'BackupMetadata' metedata for datase ID..." Does anyone have any suggestions on how to resolve this error?

View 5 Replies View Related

[Error Loading Mining Model Metadata: Not Connected.]

Dec 13, 2007

This has been driving me mad for several days. I have setup SQL Server 2005 Express (SP2) on a box. I have SQL Server Management Studio (9.00.3042.00) installed on a client box. I can connect to the 2005 server and created a test database on the machine. But when I try to open a query on the database, I always get the above error message "[Error loading mining model metadata: Not connected.]" in the LHS pane and the query window remains unconnected. If I try to connect (by clicking on the connect button) I get a login screen for Analysis Services, but I don't have Analysis Services running on any boxes. The login screen doesn't allow me to change anything other than Server Name. This happens even if I open a new Database Engine Query through the File/New menu.

All I want to do to execute a query against my database. I don't want to use Analysis Services. What am I doing wrong? I have almost the exact same setup at home and it works fine.

Any help is greatly appreciated.

View 5 Replies View Related

SQL Server 2012 :: Error Loading Metadata - No Cubes Found

Aug 22, 2014

While starting querying with sql server mdx query i m getting error loading metadata:no cubes found.

View 6 Replies View Related

Help: Error In Metadata Manager. The D Msample ~MC Cube Has No Measure Groups

Oct 22, 2007

Hi,

I'm having an issue running the clustering algorithm in the data mining view of Visual Studio. The databases connect properly and the data subsequently loads. However, upon clicking on the "Mining Model Viewer" tab, I receive the following error message:

Errors in the metadata manager. The D Msample ~MC cube has no measure groups. Errors in the metadata manager. An error occurred when loading the D Msample ~MC cube, from the file, '\?C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataewDM_sample.0.dbD Msample ~MC.2.cub.xml'.

We are not using a data cube, so I am assuming that this file is being called through the clustering algorithm. Furthermore, I have run the same process on different systems successfully. The only difference I can detect is that this error resulted on a 64bit system.

Any help would be greatly appreciated.

Thanks

View 8 Replies View Related

Error: The External Metadata Column Collection Is Out Of Synchronization With The Data Source Columns

Apr 17, 2007

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.

I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.

On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....

What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.

Can anyone help me to resolve this issue.

Thanks.

View 3 Replies View Related

[Excel Destination [91]] Error: Cannot Create An OLE DB Accessor. Verify That The Column Metadata Is Valid.

Apr 19, 2008

I was trying to get my data from olb table to excel. but i got this error
my dataflow is as follows:
oledb source-> excel destination

i have got a excel file template at the destination all ready.
with the column names+ the sheet all ready.
but somehow it got stuck...
anybody can help?
thanks!

View 8 Replies View Related

ALTER ASSEMBLY Error Msg 6509 An Error Occurred While Gathering Metadata From Assembly ‘&&<Assembly Name&&>’ With HRESULT 0x1.

Feb 22, 2008

I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly ��<Assembly name>�� with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
� The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
� The signatures of methods in the assembly that are called from other assemblies.
� The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
� The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
� The FillRow method name attribute for CLR table-valued functions.
� The Accumulate and Terminate method signature for user-defined aggregates.
� System assemblies.
� Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
� Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
� Adding new public methods.
� Modifying private methods in any way.

But I haven��t done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. I��ve got the same error, if I add or change any method in assembly before rebuilding.

View 9 Replies View Related

SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.

Jun 15, 2007

I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.

View 3 Replies View Related

Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.

Mar 6, 2007

Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm

View 1 Replies View Related

Deletion

Nov 6, 2007

Hi all,

I have a table in xyz database and there is no column in table like creation_date or modified_date.

The problem is I want to delete records which has been added in the table before 1st jan 2007.

The size of table is 85 GB

Immediate help would be appriciable.

Regards,
Frozen

View 9 Replies View Related

Restrict Deletion

Mar 11, 2007

What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?

Thank you in advance.

View 8 Replies View Related

Deletion Problem

Jun 4, 1999

It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.

TIA,

Stella Liu

View 2 Replies View Related

Database Deletion

Apr 15, 2008

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 1 Replies View Related

DB Deletion Time

Jun 4, 2008

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

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

View 14 Replies View Related

Deletion In Trigger

Oct 12, 2013

table1 has a PK_tbl1 which matches FK_tbl1 in table2 and the relationship is one to one. Like this:

table1 has columns of UnitId, Code ...
table2 has columns of ItemID, UnitId ...

So if I need remove an item from table1 I must remove the matched one in table2 first.

delete from table2 where UnitId = xxx
delete from table1 where UnitId = xxx

Now I need create a trigger on table1 for deletion:

CREATE Trigger [dbo].[Table1_Delete]
ON [dbo].[Table1]
FOR DELETE
As

[code]....

View 2 Replies View Related

How To Prevent Db Deletion

Nov 22, 2006

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 8 Replies View Related

Deletion Of Duplicate Row

Jul 23, 2005

Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard

View 2 Replies View Related

Deletion Query

May 29, 2008

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 4 Replies View Related

0 KB File Deletion

Feb 7, 2008

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 11 Replies View Related

User Deletion Log SQL

Aug 15, 2006

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 1 Replies View Related

Deletion And Identity Reset

Oct 13, 2005

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 3 Replies View Related

Deletion Of Duplicate Values

Jun 11, 2007

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 14 Replies View Related

Database Still 'exists' After Deletion

Apr 14, 2006

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 1 Replies View Related

For Deletion..trigger Is Not Working

May 4, 2004

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 1 Replies View Related

Daily Deletion Of Records

May 12, 2004

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 10 Replies View Related