HELP! sp or other tool to help diagnose recent record deletion?

I'm trying to trouble shoot an app that after two years of running
more or less fine has suddenly had a problem. An average user was
filling out a web form and clicked save, only to have their entire
multi page report form get obliterated. This user has no delete
rights, and even if they had pressed cancel the ASP page would simply
not write that page...but would not delete the entire series of
records.
I am stepping through ASP code and trying to recreate the problem but
I'm relatively new to SQL and not sure what sytem stored procedures or
functions might help me...records of recent deletes (within the last
few hours), userinfo (who really deleted the record)? Etc.
Coincidentally (perhaps) this server was nearly full (aprox 700 megs
left). I know...this is terrible. I've been harping my sysadmin to get
some non DB related stuff off my SQL server and we cleared a few Gigs
off this morning! Anyway, it's very likely the cause...again...could
someone point me to what logs or functions or documentation I should
be looking at that might help me verify this?
Many thanks!!
This might help you -- these guys have some good tools. I recommend,
though, that you restore the database to another area on the server and
compare the data with some custom queries to see what's missing...
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm?gclid=CK-roqn2mIoCFRyVFQodbFaonQ
mahalie@.gmail.com wrote:
> I'm trying to trouble shoot an app that after two years of running
> more or less fine has suddenly had a problem. An average user was
> filling out a web form and clicked save, only to have their entire
> multi page report form get obliterated. This user has no delete
> rights, and even if they had pressed cancel the ASP page would simply
> not write that page...but would not delete the entire series of
> records.
> I am stepping through ASP code and trying to recreate the problem but
> I'm relatively new to SQL and not sure what sytem stored procedures or
> functions might help me...records of recent deletes (within the last
> few hours), userinfo (who really deleted the record)? Etc.
> Coincidentally (perhaps) this server was nearly full (aprox 700 megs
> left). I know...this is terrible. I've been harping my sysadmin to get
> some non DB related stuff off my SQL server and we cleared a few Gigs
> off this morning! Anyway, it's very likely the cause...again...could
> someone point me to what logs or functions or documentation I should
> be looking at that might help me verify this?
> Many thanks!!
>
-Dave Markle
http://www.markleconsulting.com/blog
sql

HELP! sp or other tool to help diagnose recent record deletion?

I'm trying to trouble shoot an app that after two years of running
more or less fine has suddenly had a problem. An average user was
filling out a web form and clicked save, only to have their entire
multi page report form get obliterated. This user has no delete
rights, and even if they had pressed cancel the ASP page would simply
not write that page...but would not delete the entire series of
records.
I am stepping through ASP code and trying to recreate the problem but
I'm relatively new to SQL and not sure what sytem stored procedures or
functions might help me...records of recent deletes (within the last
few hours), userinfo (who really deleted the record)? Etc.
Coincidentally (perhaps) this server was nearly full (aprox 700 megs
left). I know...this is terrible. I've been harping my sysadmin to get
some non DB related stuff off my SQL server and we cleared a few Gigs
off this morning! Anyway, it's very likely the cause...again...could
someone point me to what logs or functions or documentation I should
be looking at that might help me verify this?
Many thanks!!This might help you -- these guys have some good tools. I recommend,
though, that you restore the database to another area on the server and
compare the data with some custom queries to see what's missing...
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm?gclid=CK-roqn2mIoCFRyVFQodbFaonQ
mahalie@.gmail.com wrote:
> I'm trying to trouble shoot an app that after two years of running
> more or less fine has suddenly had a problem. An average user was
> filling out a web form and clicked save, only to have their entire
> multi page report form get obliterated. This user has no delete
> rights, and even if they had pressed cancel the ASP page would simply
> not write that page...but would not delete the entire series of
> records.
> I am stepping through ASP code and trying to recreate the problem but
> I'm relatively new to SQL and not sure what sytem stored procedures or
> functions might help me...records of recent deletes (within the last
> few hours), userinfo (who really deleted the record)? Etc.
> Coincidentally (perhaps) this server was nearly full (aprox 700 megs
> left). I know...this is terrible. I've been harping my sysadmin to get
> some non DB related stuff off my SQL server and we cleared a few Gigs
> off this morning! Anyway, it's very likely the cause...again...could
> someone point me to what logs or functions or documentation I should
> be looking at that might help me verify this?
> Many thanks!!
>
-Dave Markle
http://www.markleconsulting.com/blog

HELP! sp or other tool to help diagnose recent record deletion?

I'm trying to trouble shoot an app that after two years of running
more or less fine has suddenly had a problem. An average user was
filling out a web form and clicked save, only to have their entire
multi page report form get obliterated. This user has no delete
rights, and even if they had pressed cancel the ASP page would simply
not write that page...but would not delete the entire series of
records.
I am stepping through ASP code and trying to recreate the problem but
I'm relatively new to SQL and not sure what sytem stored procedures or
functions might help me...records of recent deletes (within the last
few hours), userinfo (who really deleted the record)? Etc.
Coincidentally (perhaps) this server was nearly full (aprox 700 megs
left). I know...this is terrible. I've been harping my sysadmin to get
some non DB related stuff off my SQL server and we cleared a few Gigs
off this morning! Anyway, it's very likely the cause...again...could
someone point me to what logs or functions or documentation I should
be looking at that might help me verify this?
Many thanks!!This might help you -- these guys have some good tools. I recommend,
though, that you restore the database to another area on the server and
compare the data with some custom queries to see what's missing...
http://www.red-gate.com/products/SQ...br />
odbFaonQ
mahalie@.gmail.com wrote:
> I'm trying to trouble shoot an app that after two years of running
> more or less fine has suddenly had a problem. An average user was
> filling out a web form and clicked save, only to have their entire
> multi page report form get obliterated. This user has no delete
> rights, and even if they had pressed cancel the ASP page would simply
> not write that page...but would not delete the entire series of
> records.
> I am stepping through ASP code and trying to recreate the problem but
> I'm relatively new to SQL and not sure what sytem stored procedures or
> functions might help me...records of recent deletes (within the last
> few hours), userinfo (who really deleted the record)? Etc.
> Coincidentally (perhaps) this server was nearly full (aprox 700 megs
> left). I know...this is terrible. I've been harping my sysadmin to get
> some non DB related stuff off my SQL server and we cleared a few Gigs
> off this morning! Anyway, it's very likely the cause...again...could
> someone point me to what logs or functions or documentation I should
> be looking at that might help me verify this?
> Many thanks!!
>
-Dave Markle
http://www.markleconsulting.com/blog

HELP! insert record

I'm doing a program by using Java to insert record into SQL file. it's a registration program. but i'm not familiar with SQL, i got an error msg "An attempt was made to insert a null value into a column that does not accept nulls. " and i cant solve it. my SQL file contains

connect 'jdbc:rmi://localhost:1099/jdbc:cloudscape:customers;create=true'
;

drop table custProducts
;
drop table products
;
drop table custinfor
;

create table custinfor (
userName varchar (20) NOT NULL,
firstName varchar (20) NOT NULL,
lastName varchar (30) NOT NULL,
email varchar (30) NOT NULL,
password varchar (20) NOT NULL,
verify varchar (20) NOT NULL,
constraint pk_custinfor primary key (userName)
)
;
create table products (
productID int DEFAULT AUTOINCREMENT,
productName varchar (100) NOT NULL,
description varchar (100) NOT NULL,
price real NOT NULL,
imageFile varchar (50) NOT NULL,
constraint pk_products primary key (productID)
)
;
create table custProducts (
userName varchar (20) NOT NULL,
productID int NOT NULL,
constraint fk_custProducts_1 foreign key (userName)
references custinfor (userName),
constraint fk_custProducts_2 foreign key (productID)
references products (productID)
)
;
insert into custinfor (userName,firstName,lastName,email,password,verify ) values ('jSmith','John','Smith','sjohn@.hotmail.com','jsmi th123','jsmith123')
;
insert into custinfor (userName,firstName,lastName,email,password,verify ) values ('jess88','Jess','Ling','jess88@.yahoo.com','jess45 6','jess456')
;

insert into products (productName,description,price,imageFile) values ('Desktop-1','Intel Pentium 4 Processor 1.8Gb,256MB RAM',2998.00,'pentium4 processor.jpg')
;
insert into products (productName,description,price,imageFile) values ('Desktop-2','Intel Celeron Processor,256MB RAM',2260.00,'pentium4 processor.jpg')
;

insert into custProducts (userName,productID) values ('jSmith',1)
;
insert into custProducts (userName,productID) values ('jess88',2)
;

can someone tell me what's the mistake that i made?Hi:

Look... your problem is easy.
Please, try to execute one insert at the time and then you can figure which sentence has the problem.
One of them is tryng to insert a null value in a null column.|||At first glance, everything in your SQL looks good. I notice that you probably should handle the users' password verification in the UI instead of the DB, but that's a minor nit-pick.

Which statement gives you the error?|||thanks moku and avarta. i cancelled the some of the NOT NULL and it works. thanks.

Help! Insert a record into SQL database with ASP.net and C#

I'm trying to make a website that people can upload file to the server, then the webpage will automatically insert the username and the file name into the database. I have thought about this for couple days but still got nothing. can some one help me?

can some one post a very simple aspx file that can insert a record to the sql database with C#? Not the code that generated by ASP.net 2.0!

I want to see how to access the database manually.

Thank you.

A quick example:

protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute;Initial Catalog=AdventureWorks;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(connectionString))
{

SqlDataSource sds = new SqlDataSource(connectionString, "select * from Orders (where EmployeeID in (@.list))");

SqlCommand cmd = new SqlCommand("insert into t1 (name) select @.name", connection);

//add text of txtBox_Name as input parameter for the insert command
cmd.Parameters.Add("name", txtBox_Name.Text);

connection.Open();
cmd.ExecuteNonQuery();
}

}

|||It is really help! Thank you very much

Help with: "TSLV Record Not Found."

Ok,
here is my environment:

1 - I have Crysta Reports 9 runtime installed under C:\Programmi\Crystal Decisions\Report Designer Component

2 - I have a rpt file created with Crystal Reports 8

3 - I have an MFC application made in VC++ 6

4 - I access crystal with this class:

// Crw9Wrapper.h
// Interface for the class CCrw9Wrapper

#pragma once

#import "C:\Programmi\Crystal Decisions\Report Designer Component\craxdrt9.dll"

class CCrw9Wrapper
{
public:
CCrw9Wrapper();
~CCrw9Wrapper();

private:
CRAXDRT::IApplicationPtr m_pApplication;
CRAXDRT::IReportPtr m_pReport;

public:
BOOL OpenReport(CString ReportName);
BOOL SetConnection(CString Server, CString DBName, CString UserID, CString Passwo);
BOOL SetTableLocation(CString Name, CString Location);
BOOL SetSelectionFormula(CString SelFormula);
BOOL SetFormulaValue(CString Name, CString Value);
BOOL AddSortField(CString FieldName, bool ascending = true);
BOOL SetSQLQuery(CString Query);
BOOL ExportPDF(CString DocumentName);

protected:
void DumpCOMError(_com_error &e);
};

// Crw9Wrapper.cpp
// Implementation for the class: CCrw9Wrapper

#include <afxwin.h> // MFC core and standard components
#include <afxext.h> // MFC extensions (including VB)
#include <afxdb.h> // MFC database classes
#include <atlbase.h>
#include <atldbcli.h>
#include <process.h>
#include <comdef.h>

#include "Crw9Wrapper.h"

using namespace CRAXDRT;

CCrw9Wrapper::CCrw9Wrapper(){}

CCrw9Wrapper::~CCrw9Wrapper()
{
try
{
if(m_pReport.GetInterfacePtr())
{
m_pReport.Release();
}
if(m_pApplication.GetInterfacePtr())
{
m_pApplication.Release();
}
}
catch(_com_error &e)
{
DumpCOMError(e);
}
}

BOOL CCrw9Wrapper::OpenReport(CString ReportName)
{
try
{
if(!m_pApplication.GetInterfacePtr())
{
m_pApplication.CreateInstance("CrystalRuntime.Application");
}

_bstr_t FileName(ReportName);
m_pReport = m_pApplication->OpenReport(FileName);

}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;

}

BOOL CCrw9Wrapper::SetConnection(CString Server, CString DBName, CString UserID, CString Passwo)
{
try
{
long TableCount = m_pReport->Database->Tables->Count;
long i, j;

// Collego le tabelle del report
for(i = 1; i <= TableCount; i++)
{
CString loc = (TCHAR*)m_pReport->Database->Tables->Item[i ]->Name;
if(loc.Find("__") < 0)
m_pReport->Database->Tables->GetItem(i)->SetLogOnInfo((LPCTSTR)Server, (LPCTSTR)DBName, (LPCTSTR)UserID, (LPCTSTR)Passwo);
else
m_pReport->Database->Tables->GetItem(i)->SetLogOnInfo((LPCTSTR)Server, "tempdb", (LPCTSTR)UserID, (LPCTSTR)Passwo);
}

// Collego le tabelle dei sottoreport
CRAXDRT::IReportObjectPtr pRepObject = NULL;
CRAXDRT::IReportPtr pSubreport = NULL;

for(i = 1; i <= m_pReport->GetSections()->GetCount(); i++) // CICLO PER LE SEZIONI DEL REPORT
{
if(!m_pReport->GetSections()->GetItem(i)->GetReportObjects()->GetCount() == 0)
{
for(j = 1; j <= m_pReport->GetSections()->GetItem(i)->GetReportObjects()->GetCount(); j++)
{
pRepObject = m_pReport->GetSections()->GetItem(i)->GetReportObjects()->GetItem(j);
if(pRepObject->GetKind() == crSubreportObject)
{
pSubreport = ((ISubreportObjectPtr)pRepObject)->OpenSubreport();

TableCount = pSubreport->Database->Tables->Count;
for(long tn = 1; tn <= TableCount; tn++)
{
CString loc = (TCHAR*)pSubreport->Database->Tables->Item[(i)]->Name;
if(loc.Find("__") < 0)
pSubreport->Database->Tables->GetItem(i)->SetLogOnInfo((LPCTSTR)Server, (LPCTSTR)DBName, (LPCTSTR)UserID, (LPCTSTR)Passwo);
else
pSubreport->Database->Tables->GetItem(i)->SetLogOnInfo((LPCTSTR)Server, "tempdb", (LPCTSTR)UserID, (LPCTSTR)Passwo);
}
}
}
}
}
if(pSubreport != NULL) pSubreport.Release();
if(pRepObject != NULL) pRepObject.Release();
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

BOOL CCrw9Wrapper::SetSelectionFormula(CString SelFormula)
{
try
{
m_pReport->PutRecordSelectionFormula((LPCTSTR)SelFormula);
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

BOOL CCrw9Wrapper::SetFormulaValue(CString Name, CString Value)
{
try
{
m_pReport->FormulaFields->GetItemByName((LPCTSTR)Name)->PutText((LPCTSTR)Value);
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

BOOL CCrw9Wrapper::SetTableLocation(CString Name, CString Location)
{
try
{
long i, count;
_bstr_t name((LPCTSTR)Name);

count = m_pReport->Database->Tables->Count;
for(i = 1; i <= count; i++)
{
if(m_pReport->Database->Tables->Item[(i)]->Name == name)
{
m_pReport->Database->Tables->Item[(i)]->Location = (LPCTSTR)Location;
break;
}
}
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

BOOL CCrw9Wrapper::SetSQLQuery(CString Query)
{
try
{
//m_pReport->put_SQLQueryString(_bstr_t((LPCTSTR)Query));
m_pReport->PutSQLQueryString(_bstr_t((LPCTSTR)Query));
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

BOOL CCrw9Wrapper::AddSortField(CString FieldName, bool ascending)
{
try
{
CRAXDRT::IDatabaseFieldDefinitionPtr pDbFieldDef;

pDbFieldDef = m_pReport->Database->Tables->GetItem(1)->Fields->GetItemByName((LPCTSTR)FieldName);
m_pReport->RecordSortFields->Add((IFieldDefinitionPtr)pDbFieldDef, ascending ? crAscendingOrder : crDescendingOrder);
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

BOOL CCrw9Wrapper::ExportPDF(CString DocumentName)
{
try
{
m_pReport->ExportOptions->DiskFileName = (LPCTSTR)DocumentName;
m_pReport->ExportOptions->DestinationType = crEDTDiskFile;
m_pReport->ExportOptions->FormatType = crEFTPortableDocFormat;
m_pReport->Export(false);
}
catch(_com_error &e)
{
DumpCOMError(e);
return FALSE;
}

return TRUE;
}

void CCrw9Wrapper::DumpCOMError(_com_error &e)
{
CString Titolo;
CString strMsg;

_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());

Titolo.Format("Attenzione: Errore 0x%08lX", e.Error());

strMsg.Format(
"Errore:\n\t%s\n"
"Fonte:\n\t%s\n"
"Dettagli:\n\t%s",
e.ErrorMessage(),
(LPCSTR)bstrSource,
(LPCSTR)bstrDescription
);

MessageBox(GetFocus(), strMsg, Titolo, MB_ICONSTOP);
}

Now here is what I do: my report is based on an SQL Server OLEDB connection and uses a single temporary table named ##FORNITO_PDD.
The report is perfectly verifyed and runs OK.

Then in my application I create a table PERFECTLY identical to ##FORNITO_PDD but named ##FORNITO_PDD_USERNAME.

Then I run this code:

CCrw9Wrapper crw;

CString stringa = "myreport.rpt";

if(!crw.OpenReport(stringa))
return;

if(!crw.SetConnection(Server, DBName, UserID, Passwo))
return;

stringa.Format(
"SELECT "
"__FORNITO_PDD.\"TITOLO_INFORETE\", "
"__FORNITO_PDD.\"SOTTOTITOLO\", "
"__FORNITO_PDD.\"CD_INFORETE\", "
"__FORNITO_PDD.\"ADD_ON\", "
"__FORNITO_PDD.\"PREZZO\", "
"__FORNITO_PDD.\"QUANTITA_ORDINATA\", "
"__FORNITO_PDD.\"ANNO_ESTRATTO_CONTO\", "
"__FORNITO_PDD.\"MESE_ESTRATTO_CONTO\", "
"__FORNITO_PDD.\"COPIE_PACCO\", "
"__FORNITO_PDD.\"NOTE_DIFF\", "
"__FORNITO_PDD.\"MULTIPLO\", "
"__FORNITO_PDD.\"PUNTO_DISTRIBUZIONE_ID\", "
"__FORNITO_PDD.\"LOCALITA\", "
"__FORNITO_PDD.\"DS_PDD\", "
"__FORNITO_PDD.\"DATA_USCITA_NAZIONALE\", "
"__FORNITO_PDD.\"DATA_USCITA_ROMA\", "
"__FORNITO_PDD.\"PERC_DL\", "
"__FORNITO_PDD.\"PERIODICITA\", "
"__FORNITO_PDD.\"STATO_ORD\" "
"FROM "
"\"##FORNITO_PDD_%s\" __FORNITO_PDD ",

szComputerName
);

if(!crw.SetSQLQuery(stringa))
return;

if(!crw.AddSortField("PUNTO_DISTRIBUZIONE_ID"))
return;

docname = "C:\mydoc.pdf";

if(!crw.ExportPDF(docname))
return;

What I get is:

Sometimes it works perfectly
sometimes SetSQLQuery dumps:

Attenzione: Errore 0x80043AE5
--------
Errore:
IDispatch error #14565
Fonte:
Crystal Reports ActiveX Designer
Dettagli:
TSLV Record Not Found.

I also noticed that as long as I work in debug mode the thing works.
When I use the release executable to access the report (the same report file) I get the error.
Then the debug version starts to give problems even if I use a backup copy of the report file.

Is there a solution to all this or I have to surrender saying: "OK, Crystal Reports demonstrated once again to be the f****d s**t thas is, I must find another way."

Please help, I don't have any clue.

Thanx
AndreaOk, I added this thing after the SetConnection method call:

stringa.Format("##FORNITO_PDD_%s", szComputerName);
if(!crw.SetTableLocation("__FORNITO_PDD", stringa))
return;

now it always work with my debug executable.
But when in release I continue getting:

--------
Attenzione: Errore 0x80047E44
--------
Errore:
IDispatch error #31812
Fonte:
Crystal Reports ActiveX Designer
Dettagli:
The table '##FORNITO_PDD_ROSSINI' could not be found.

Help with Variables in XI?

I have a calculated value on a record. Is there a way to return that value to the next record like below?
Record 1 : $300,000 $100,000 $200,000 {300,000-100,000}
Record 2 : $200,000{from above} $100,000 $100,000 {100,000-100,000}Try the function "Previous"

Help with Update Procedure

Ok, so here is what I'm trying to do. If I have a duplicate client record I want to change the client_id in our tblClientCodes table where the client_id is the ID of the duplicate that we will remove at some point. All this I can do.

My problem is this, the two client records might have some of the same client codes. so the entire update will fail if any primary key violations are found as the update is being executed. Some of the client codes may not be the same and I want the client_id changed on those. How can I get the update to skip the key violations and change the rows that should be changed?

There are two rows in our tblClient table that I'm trying to merge into one and then I will delete the RemoveClientID record. But the delete in not done here.

ALTER PROCEDURE [dbo].[UpdateMergeDuplicates]
@.KeepClientID int,
@.RemoveClientID int
AS
BEGIN
UPDATE tbOrgCodes
SET tblOrgCodes.Client_ID = @.KeepClientID
WHERE tblOrgCodes.Client_ID = @.RemoveClientID
END

I am confused by your question, is it duplicate records in tblClientCodes or tblOrgCodes that is the problem?

Are your Id column identity integer or are they something else?

Please clarify and I will endeavour to answer Monday evening UK time.

|||

This is an example of what might happen. A client has been entered into tblClient twice, so before removing the duplicate record, I need to change the client_id on the many side of the relationship between tblClient and tblOrgCodes. Both of the records in tblClients were assinged Org Codes. I want to simply change the client_id number for the rows assinged to the duplicate client record to the client_id that we will be keeping.

The problem is that both records could have been assigned some of the same codes, so my update statement will fail when it encounters a duplicate code. I want it to still update the codes that are not the same and skip the ones that are the same.

tblClient (1) ---- (m) tblOrgCodes

client(1234) non dup, has code1 and code2 in tblOrgCodes

client(4321) dup, has code1 and code3 in tblOrgCodes

I want to change the client_id for the code3 in tblOrgCodes from (4321) to (1234) and skip the code1 because it is a key violation.

This is a process so I will be doing it to other tables as well. But I need to no how to get the stored procedure to let me change the client_id of code3 and not terminate the statement. I tried the try/catch and it stopped the error, however, it did not update the code3 client_id.

@.KeepClientIDint, @.RemoveClientIDintASBEGINBEGIN TRYUPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND TRYBEGIN CATCH--?END CATCHEND

|||

Does anyone have any idea how to do the try/catch here?

I appreciate any help,

|||

Jackxxx:

I want to change the client_id for the code3 in tblOrgCodes from (4321) to (1234) and skip the code1 because it is a key violation.

I did not understand how its a key violation? because its the same client_Id in reverse? or are there 2 records for client_id = 4321. Please post some more sample data..

|||

There are two records for the same client, one was entered as a duplicate, thats the 4321. So I'm just changing the client_ID (this is NOT the key violation field) for records added to tblOrgCodes for client_id 4321 to client_id 1234. It is a code that is causing the key violation (code1). So as the sproc is doing the update it does something like this:

It finds a row in tblOrgCodes with the client_ID 4321 and tries to change it to 1234, if 1234 already has that code it causes a key viloation and terminates the statement. But there could be other codes in tblOrgCodes that need to be changed and do not cause a key violation. I need these to be updated even though a key violation was found on one of the previous rows.

I appreciate your help.

|||

So is there a unique key on tbOrgCodes.Client_ID ?

You can have a secondary key without making it unique!

|||

The key of the tblOrgCodes table is OrgCodelookup_ID and the Client_ID combined.

Normally I tell an update what to find in the where clause.

Is there a way to tell the update to skip any rows where the Client_ID and the OrgCodelookup_ID are the same?

|||

 The following should do it:

UPDATE tbOrgCodes
SET tblOrgCodes.Client_ID = @.KeepClientID
WHERE tblOrgCodes.Client_ID = @.RemoveClientID

 AND  tblOrgCodes.OrgCodelookup_ID NOT IN (SELECT OrgCodelookup_ID FROM tbOrgCodes

 WHERE tblOrgCodes.Client_ID = @.KeepClientID)

However this hides the problem, a better way would to add a warning by using a @.WARNING INT OUPUT parameter

SET @.WARNING = 0

IF EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2 AND O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID

 AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) SET @.WARNING = 1

ELSE

UPDATE tbOrgCodes
SET tblOrgCodes.Client_ID = @.KeepClientID
WHERE tblOrgCodes.Client_ID = @.RemoveClientID
.

|||

TATWORTH,

The subquery worked great. I have not been able to get the IF statement to clear syntax checking. Can you tell me what it will do and how it will effect the application?

Good things are always worth waiting for!

|||

Try

IF EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2
WHERE O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) SET @.WARNING = 1
ELSE
UPDATE tbOrgCodes
SET Client_ID = @.KeepClientID
WHERE Client_ID = @.RemoveClientID

(this time I checked the syntax in Query Analyser)

Now what do you do with the warning? If you are going to delete the Client Record whose Id = @.RemoveClientID, then the following might be a better idea:

IF NOT EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2
WHERE O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) BEGIN
UPDATE tbOrgCodes
SET Client_ID = @.KeepClientID
WHERE Client_ID = @.RemoveClientID
DELETE Client WHERE id = @.RemoveClientID
END

This way you can action both table change in one stored procedure or even make it a transaction as in

IF NOT EXISTS(SELECT * FROM tblOrgCodes O1, tblOrgCodes O2
WHERE O1.OrgCodelookup_ID = Q2.OrgCodelookup_ID
AND O1.Client_ID = @.KeepClientID AND O2.Client_ID = @.RemoveClientID) BEGIN
BEGIN TRANSACTION
UPDATE tbOrgCodes
SET Client_ID = @.KeepClientID
WHERE Client_ID = @.RemoveClientID
DELETE Client WHERE id = @.RemoveClientID
COMMIT
END

|||

TATWORTH,

Again Thank You very much for explaining this and helping me. I look forward to giving it a try.