HELP! Problems with Merge Replication

Hi,
I have created a publication on SQL Server 2005 and am using SQL CE RC1 to synchronize with the DB from the client. We did not have any problems until we implemented microsoft recommendations in the Mergereplication script. We changed the following
1.Changed column_tracking to true from false.
2. use_partition_groups = true from null
3. partition_options = 2 from 1
4. automatic_reinitialization_policy = 1 from 0
and 5. join_unique_key = 1 from 0( this change was made depending on the relationships within the tables)

I have a couple of questions before tho
1. Does replication take care of transactional integrity of the both the databases( i.e if the synchronization fails in between for any reason, does it rollback the server DB and maintain the transactional integrity. Is there a place where we need to enable this?
2. What happens when you change row level to column level tracking. I am seeing in the "view conflicts" section of the replication object that there are conflicts. Does SQL Server take care of resolving these issues. If yes, how do we provide the criteria where the subscriber wins or the publisher wins and why do I see conflicts in the "view conflicts" section .
Please do help. We are in time crunch situation and need to deliver this app into prod this friday.

Below are a couple of errors that we received during merge replication.

Error 1:
Error messages:
The row operation cannot be reapplied due to an integrity violation. Check the Publication filter. [,,,Table,Operation,RowGuid] (Source: MSSQLServer, Error number: 28549)
Get help: http://help/28549

Error 2:

Error messages:

· The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363

The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800

Thanks a lot in advance,
Kals.

Having the same 2 errors - did you identify the cause?

HELP! Problems with Merge Replication

Hi,
I have created a publication on SQL Server 2005 and am using SQL CE RC1 to synchronize with the DB from the client. We did not have any problems until we implemented microsoft recommendations in the Mergereplication script. We changed the following
1.Changed column_tracking to true from false.
2. use_partition_groups = true from null
3. partition_options = 2 from 1
4. automatic_reinitialization_policy = 1 from 0
and 5. join_unique_key = 1 from 0( this change was made depending on the relationships within the tables)

I have a couple of questions before tho
1. Does replication take care of transactional integrity of the both the databases( i.e if the synchronization fails in between for any reason, does it rollback the server DB and maintain the transactional integrity. Is there a place where we need to enable this?
2. What happens when you change row level to column level tracking. I am seeing in the "view conflicts" section of the replication object that there are conflicts. Does SQL Server take care of resolving these issues. If yes, how do we provide the criteria where the subscriber wins or the publisher wins and why do I see conflicts in the "view conflicts" section .
Please do help. We are in time crunch situation and need to deliver this app into prod this friday.

Below are a couple of errors that we received during merge replication.

Error 1:
Error messages:
The row operation cannot be reapplied due to an integrity violation. Check the Publication filter. [,,,Table,Operation,RowGuid] (Source: MSSQLServer, Error number: 28549)
Get help: http://help/28549

Error 2:

Error messages:

· The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363

The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800

Thanks a lot in advance,
Kals.

Having the same 2 errors - did you identify the cause?sql

Help! Printing Error from client (browser)

We have 2 servers with RS:

1.SQL RS 2000 SP3a

2.SQL RS 2005 SP1

When we try to use client side printing in SQL 2000 RS we getting error "An error occured during printing '0x8007F304' ".

When we try to use client side printing (from the same client PC) in SQL 2005 RS, it print fine without any problem

Thank you,

Lenn

Please check this posts

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=247869&SiteID=1

|||

That post refering to installing SQL2005 SP1 to fix printing problem. We have SQL2005 SP1 installed on 2005 server, but reports are not prints when RS running to SQL 2000 server!!

Thanks,

Lenn

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?
Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:
> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

HELP! Can't delete merge replication subscribers in 2005

My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
merge replication subscribers over the Internet with web synchronization.
During testing, several subscriptions were created and they will not drop
with the normal drop stored procedures.
This is an urgent problem because with my bogus, testing subscriptions
there, we have reached the 25 subscription limit and we cannot get their
final users subscribed.
I've seen others having this problem but haven't seen the resolution. Any
help would be greatly appreciated.
Thanks!
I take it you have tried to drop them using sp_dropmergesubscription and
sp_dropmergepullsubscription correct?
If so you should be able to drop them by deleting the contents of the
following tables on your subscriber db.
sysmergesubscriptions
MSmerge_supportability_settings
MSmerge_log_files
MSmerge_replinfo
MSmerge_supportability_settings
MSmerge_log_files
MSrepl_errors
MSmerge_history
MSmerge_articlehistory
MSmerge_sessions
MSmerge_replinfo
sysmergearticles
sysmergepublications
sysmergepartitioninfo
sysmergepartitioninfoview
sysmergeschemaarticles
sysmergeextendedarticlesview
sysmergeschemachange
sysmergesubsetfilters
sysmergesubscriptions
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Shannon Willison" <Shannon Willison@.discussions.microsoft.com> wrote in
message news:2ABD7D82-C738-4547-A9F6-DF29202DF39F@.microsoft.com...
> My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
> merge replication subscribers over the Internet with web synchronization.
> During testing, several subscriptions were created and they will not drop
> with the normal drop stored procedures.
> This is an urgent problem because with my bogus, testing subscriptions
> there, we have reached the 25 subscription limit and we cannot get their
> final users subscribed.
> I've seen others having this problem but haven't seen the resolution. Any
> help would be greatly appreciated.
> Thanks!

HELP! Cannot connect !

While I am connecting to SQL server 2000 EE from a
different client machine using Query Analyzer it gives me
the following error message .
Unable to connect to server >
Server : MSG 18452 Level 16 state 1
[Microsoft ODBC ][SQL Server Driver][SQL server] Login
failed for user '(null)' Reason , not associated with a
trusted SQL server connection.
I can connect to two other SQL servers in the same domain
just fine with QueryAnalyzer. The machine that I cannot
connect to is also my PDC. It is running Win2KSP4, the
client is WinXPSP1. I've added and removed the machine
from the domain (at my wits end) just to make sure the
accounts are not cached. This is the only computer I'm
having these client problems with, no matter who logs in.
SQL Server I cannot connect to using Windows Login is
also my PDC (Development environment)
I can connect to two other SQL servers that are member
servers on the same domain. All servers are running
SQL2000EE/SP3a MDAC 2.7SP1 on Win2K, including domain
controller.
I can log into the SQL server in question using sa
account from the client (which is running
XPSP1/SQL2000Tools/MDAC2.7FOR XP.
If I try to add databases using Enterprise Manager from
the client, I can add the other two DB servers, however I
get the same error when trying to add the SQL server in
question.
I'm stumped as the Windows Domain account has to be
working as I can authenticate to connect to the domain as
well as the other two SQL servers. (I've tried this with
a regular user account with SQL rights as well as my
domain admin account which has SQL rights.
I can connect to the server in question from any other
client in the office using the two windows domain
accounts that do not work on the client.
I'd rather not reformat te client and start over as there
are other tools (Rational) installed whose license is a
real pain to get redone. BTW, all other clients have
identical configurations (Rational, etc) which work.
I've tried uninstalling/resinstalling the SQL tools/SP3a,
reset the computer domain account (doesn't matter as I
can connect to the other two servers), something in the
registry etc must have got trashed is all I can figure.
Tried removing TCP and readding using client network
utility. Tried setting up an alias in same tool. Same
result. Can connect just fine as sa.
Thanks for any suggsetions!The login failed for user NULL indicates a problem with authenticating on
the DC when attempting the connection. It could also indicate that the user
who is attempting to logon to the SQL Server does not have permission to
access the server itself. Verify that the user can map to a share on the
SQL Server machine. If he can then check the NT Security log on the DC
where he is validated and see if there are any errors generated when he
attempts to connect to SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I tried this with my domain admin account as well. Can
map shares on that server ok. Can login to server using
sa or Windows login fine over Named Pipes (server set to
both auth). When I try to login over TCP, can only login
using sa, Windows Login won't work. This server is also
the single PDC (development net).. get this, can log into
two other SQL servers (member servers only) from same
client using Named Pipes AND TCP using sa or Windows
Login.
I've un-installed TCP, removed/readded machine from
domain, it has to be something on this client...|||You get that error when the sa account hasn't been set on the server. Expand
the sql server => management folder => right click sql server agent
properties =>. last tab is connection .You would see that the sql server
connection is set to use 'windows NT authentication', you would need to
change it to SQL authentication
hope that helps
Ayo Coker
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:fa4a01c3f1a9$47689870$a501280a@.phx.gbl...
> I tried this with my domain admin account as well. Can
> map shares on that server ok. Can login to server using
> sa or Windows login fine over Named Pipes (server set to
> both auth). When I try to login over TCP, can only login
> using sa, Windows Login won't work. This server is also
> the single PDC (development net).. get this, can log into
> two other SQL servers (member servers only) from same
> client using Named Pipes AND TCP using sa or Windows
> Login.
> I've un-installed TCP, removed/readded machine from
> domain, it has to be something on this client...
>

HELP! BACK button not working on matrix

I have a report that has a summary matrix containing all urls for a client
which will drill to a second report which is a detail matrix that shows
detail for the selected url from report #1. The problem is that when they
drill to report #2 ... if they expand the matrix to show individual detail
for past 30 days and then want to go back to the previous report and choose
another url ... the back button wont work unless they hit it twice. If they
happen to expand AND contract the detail and THEN want to go back to the prev
report they have to hit the browser back button THREE times. Report manager
or the browser seems to be keeping track of key strokes - however my user is
NOT HAPPY. How can I solve this? I need the equivalent of a BACK button.
But then ... do I have to put in all the parameters that the first report
required again? I am not sure what to do but I have to do it fast!Are you using the browser's back button or the report's back button? If
you're not using the browser's, try it. That should bring you back to
report #1.
You could also launch report two in a new window so that they could
just toggle between the two as they see fit, but I've had trouble
getting a report to launch in a new window.
Mike|||Mike,
Thanks for your reply. I am using the browser's back button (the report
doesnt have one that I can see that will take it to the previous report). I
have not had this problem with other reports ... just the matrix report and I
it has to do with the toggle to expand/contract detail. Launching another
window from the summary (first rpt) isnt allowed because this is deployed to
a portal and I am already launching a separate window there from an asp .net
page to pass report parameters to the first report. They dont want yet
*another* window to get the detail. Any other suggestions? I am not an asp
.net or vb .net wizard - someone else coded the initial UI that launches the
report so if you suggest something along those lines ... please be specific
with an example if possible. Thanks verry much!!
"Bassist695" wrote:
> Are you using the browser's back button or the report's back button? If
> you're not using the browser's, try it. That should bring you back to
> report #1.
> You could also launch report two in a new window so that they could
> just toggle between the two as they see fit, but I've had trouble
> getting a report to launch in a new window.
> Mike
>

Help with WHERE CLAUSE

I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.

Here is what i have tried:

I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag.
The table looks like this;

tblClientAddresses:
Address_ID,Client_ID,Address,Address2,City,State,Zip,Country,AddressType,StartDate,
EndDate,MailTo

WHERE (A.MailTo=1) AND (A.EndDate Is Null OR DatePart(mm,A.Enddate) >= DatePart(mm,GETDATE()) AND DatePart(dd,A.Enddate) >= DatePart(dd,GETDATE()))

Thank you for any help!I have tried this:

WHERE (A.MailTo=1) AND (Month(Day(GETDATE())) BETWEEN Month(Day(A.Startdate)) AND(Month(Day(A.Enddate))) OR (A.Startdate Is Null) AND (A.EndDate Is Null))

But it still not returning the proper row.

Does anyone have an idea if what i show above is even doable?

Thanks|||Try this:

WHERE (A.MailTo=1)
AND Convert(varchar(5),GetDate(),10) BETWEEN
Convert(varchar(5),A.Startdate,10) AND Convert(varchar(5),A.Enddate,10)

The datetime format #10 is mm-dd-yy. If you limit the cast to varchar(5) you only get mm-dd.

JK|||That has fixed the date issue, but now the MailTo flag is being ignored. Should I adjust the paranthises somehow? I have moved the flag to the end and added paranthises but no luck.

Thank you very much|||Hmm. It is odd that the MailTo flag is being ignored.

Could you post your full query command with all the trimmings?
JK

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.