SSIS Crash On Breakpoint Deletion

May 23, 2008

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


ADVERTISEMENT

Integration Services :: SSIS 2008 Randomly Ignores Debug Breakpoint?

Oct 12, 2015

SSIS 2008 when I develop and debug in BIDS sometimes ignores debug break point.

The script component is in the main control flow and at some point the breakpoint did work.

If, for example, I create a new project and copy my script component there the debug breakpoint will work.So it's absolutely *random* when it works and when it does not.

Below is my BIDS detail:

Microsoft Visual Studio 2008
Version 9.0.30729.4462 QFE
Microsoft .NET Framework
Version 3.5 SP1
Installed Edition: IDE Standard
Enterprise Library v5 Configuration Editor   4.0

[code]....

View 2 Replies View Related

Executing SSIS Packages Crash

Apr 29, 2008

Hello,

First, I have read and applied the following threads and a dozen others from google before asking for help.


SSIS - DTS_E_PRODUCTLEVELTOLOW...
SSIS Execute Package with Exec...
SSIS Package Fails/Does Not Co...

I have SQL 2005 Enterprise with Integration Services installed. I REPEAT, it IS INSTALLED!
SP2 version 9.0.3042

SQL Job History
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:20:49 PM. The return value was unknown. The process exit code was -1073741795. The step failed.

Windows Error Log
Hanging application DTExecUI.exe, version 9.0.3042.0, hang module hungapp, version 0.0.0.0, hang address 0x00000000.

Precedeeded By:
.NET Runtime 2.0 Error Reporting

Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module dtspipeline.dll, version 2005.90.3042.0, stamp 45cd721f, debug? 0, fault address 0x0004c2a8.

I have a very simple package that is trying to pull data from a CSV (Flat File) into the DB.

1. I built the package using BIDS on workstation, compiled and deployed to the SQL Server.
2. I have tried running it using dtexecui and as a SQL Job. Neither work.
3. I have tried double clicking the file from the file system, not working.
4. I recompiled and deployed using BIDS on the server, didn't work.

If I execute the Package within BIDS, the process completes successfully.

Any thoughts? Your help is very much appreciated.

Thanks,

View 19 Replies View Related

SSIS Crash When Debugging Script Task

Feb 14, 2008

I'm getting the error Visual Studio has encountered a problem and needs to close. When sending the error report to Microsoft no resolution comes up but another message like:

Microsoft Visual Studio for Applications has lost the link to .
Your work will be exported to C:Documents and Settings.... when you quit the application

Any hints on what I can try?

Thanks in advance, Michael.

View 14 Replies View Related

HardCoded Breakpoint

Mar 5, 2001

My SQLAgent keeps failing with a DR. Watson message(see above) with differen addresses. Sometimes the SQL Agent will restart and other times it stays down.

I get no messages in the Agent Log or the SQL Server log. I get one message in the NT Event Log "SQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly".

Running NT 4 sp 5 w SQL 7 w sp 2.
If you look at the NT Services, it shows that SQLAgent is still running. But SQL Enterprise manager shows it stopped.

If you try to look at a scheduled job or a DTS package, you get the message that SQL Agent is starting and to try back later.

Have stopped the NT Services SQL Agent and then tried to start it with Enterprise manager. No luck.
Only way that I have found to get out of this mess is to restart the server.

Looking for clues

View 1 Replies View Related

Keeping Breakpoint And Debugging

Feb 27, 2007

hi, is there any possiblity to keep break point and run the step by step compilation as like we do in .net,if means please telll me how to do?

View 3 Replies View Related

Debug Not Stopping At Breakpoint

Nov 25, 2011

I'm trying to debug stored procedure code with SSDT, but the debugger don't stop at the breakpoint.

I imported the schema of northwind database from Denali to a new database project and used Run to publish it to localdb.

So, I inserted a breakpoint in SalesByCategory stored procedure, opened a new query window, write an execute statement and started the execution with debug.

The debugger stop at the exec statement, but never get into the stored procedure. I'm using a breakpoint in the stored procedure, already tryed F5, F11, nothing works.

Application Debugging option in localdb is checked.

The operating system is windows server 2008 R2. I'm still using CTP 3.

Did I forget to check/do something ?

View 2 Replies View Related

Putting Breakpoint For Stored Procedures

Mar 31, 2008

Hi All,
How we breakpoint to the vb.net code to analyse the code. Is it possible to put breakpoint to stored proceudres so that analysis can be done.
 
Thanks
 Abdul
 
 
 
 

View 2 Replies View Related

Having Problems Using Breakpoint In Script Task.

Jul 12, 2007

I am still pretty new to SSIS, SQL, and DOT NET. I came from the UNIX world. I have a SSIS package. On the Control Flow one of the Items I have is a Script Task. I was able to successfully set breakpoints in the Script Task and they worked fine. I could step through the script and check values in variables. Life was good.

Now something happened. I set the breakpoint, from the menu I select €œstart with debugging€? and I get the following window:

----------------------------- -
Visual Studio Just-In-Time Debugger

An unhandled exception (€˜System.Runtime.InteropServices.COMException€™) occurred in DTAttach.exe [3380].


Possible Debuggers:

New instance of Microsoft CLR Debugger 2003
New instance of Visual Studio .NET 2003
New instance of Visual Studio 2005


[_] set the currently selected debugger as the default
[_] Manually choose the debugging engines

Do you want to debug using the selected debugger?

-----------------------------------------------------------
I have tried selecting Yes, but that doesn€™t work. If I delete all breakpoints the package runs fine. I greatly appreciate your help.

View 3 Replies View Related

Breakpoint In Child-package Triggered On First Execution Only.

Sep 23, 2007

I have a child package which is executed several times within the same SSIS ETL. I have placed a break point on one of the child package's tasks, set to trigger on a PreExecute() event. The first time the child package is invoked, the breakpoint is triggered. However, on each successive invocation the breakpoint is ignored. Does anybody know if this behaviour is normal? Thanks in advance!

View 1 Replies View Related

Unexpected Behavior When Setting A Script Task Breakpoint In Package W/ Multiple Script Tasks

Apr 16, 2006

I ran into a variety of problems trying to set a script task breakpoint in a package containing multiple script tasks. The debugger apparently treats the breakpoint as if it were set in ALL tasks in the package, not just the one in which it is actually set.

At best this results in hitting breakpoints in scripts where they are not set and at worst the debugger brings up the "Send error report" dialog and quits (while the package continues to run). The latter seems to happen most often when the script with the breakpoint has more lines than an earlier script and the breakpoint is set at a line number that exceeds the number of lines in the earlier script--it bombs when the earlier, shorter script starts.

To get the debugger to work under these circumstances I had to add some nonsense code like


While False
Dim i as Integer = 0
End While
to every script, at the same line number near the beginning of the script (line 40, for example). I then set a breakpoint on the middle statement in one of the scripts (doesn't matter which) to cause the debugger to open at runtime. It doesn't hit the breakpoint because the line is never executed. If the breakpoint is set on a line that can be executed in any script in the package, bad things tend to happen.
I then add a "stop" statement to the script that I want to debug. This only works if the debugger is already open, hence the dummy breakpoint above.
This workaround is usable, but I am debugging a package that has quite a few scripts and having to insert dummy code in all of them at a fixed line number is rather inconvenient. I would really like to see breakpoints work the way one would expect--only in the scripts where they are set.
Is there some other, easier way around this problem? Is there at least an easier way to get the debugger to open so that "stop" will work?

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

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

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

DOS Utility For File Deletion.

Apr 17, 2008

Does anyone has a script/exe for periodic file deletion, using date comparison?


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

View 9 Replies View Related

Deletion Of Duplicates Without ID Field?

Jun 17, 2014

I need to delete duplicates off a table. Duplicates are reliate on specific columns (COL1, COL2, COL3):

TABLE1

COL1,COL2,COL3,COl4,COL5
A,A,A,B,B
A,A,A,B,C
A,A,B,B,B
A,A,A,H,H
B,B,B,F,F
B,B,B,E,E

For table1, I want to get rid of a rows with matching COL1, COl2, and COl3. The results should be like this:

TABLE1

COL1,COL2,COL3,COl4,COL5
A,A,A,B,B
A,A,B,B,B
B,B,B,F,F

View 2 Replies View Related

Deletion Problem In Sql Table

Jan 2, 2006

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 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

Alert On Data Deletion

Jul 23, 2005

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







Copyrights 2005-15 www.BigResource.com, All rights reserved