Cleaning up merge metadata manually is not working

Hi all,

I'm trying delete metadata of a sql 2005 sp1 subscriber from a sql 2005 sp1 merge publication, but is not working, the "retention" parameter for the publication is 999 and this is the code I'm using:

declare @.num_genhistory_rows int,

@.num_contents_rows int,

@.num_tombstone_rows int

declare @.retcode smallint

--select count(*) from msmerge_contents

-- records before 2,633,848

exec @.retcode = sys.sp_mergemetadataretentioncleanup @.num_genhistory_rows OUTPUT , @.num_contents_rows OUTPUT , @.num_tombstone_rows OUTPUT

select retcode =@.retcode

select num_genhistory_rows =@.num_genhistory_rows

select num_contents_rows=@.num_contents_rows

select num_tombstone_rows=@.num_tombstone_rows

--select count(*) from msmerge_contents

-- records after 2,633,8

Results :

retcode

0

num_genhistory_rows

0

num_contents_rows

0

num_tombstone_rows

0

Has omebody any idea why this is not working ?

I did check "sp_mergemetadataretentioncleanup " and I note that is using a function to calculate the limit date, but I could not testing because it give me the below error :

declare @.curdate datetime, @.cutoffdate datetime

select @.curdate = getdate()

select @.cutoffdate = null

-- find max retention of all pubs the article belongs to.

-- add some safety margin to compensate for different clock speeds

select @.cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @.curdate))

from dbo.sysmergepublications where

pubid in (select pubid from dbo.sysmergearticles where nickname = 5088000)

select @.cutoffdate

and this is the message error:

Msg 4121, Level 16, State 1, Line 7

Cannot find either column "sys" or the user-defined function or aggregate "sys.fn_subtract_units_from_date", or the name is ambiguous.

I looked this function but I didn't find it.

any help will be appreciated !

Firstly, if your retention is 999 days, it means that only metadata that is 1000 days old will be cleaned up (automatically or manually). So do you have data that old in the first place?

If you want to cleanup metadata, set the retention to a lower value and you will start seeing the metadata getting cleaned up automatically when merge anget runs.

The sys.xxx functions are internal functions that reside in the system resource and hence cannot be called explictly by a user.

|||

Thanks for your response Mahesh,

Here is the complete story about 999

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

I suspect that if I change the "retention" parameter the subscriptions will expire, is this possible with sql 2005 SP1 ?

|||

the retention cleanup will take time on the first call because it will need to do some real cleanup.

However after this, every time merge agent runs, this proc will be called. But the delta to cleanup will be very less so it should not take that long, unless of course your data load everyday is very huge.

So now if you set it to 999, and not enough metadata is genenrated, you will not see anything/or see less metadata cleaned up.

sqlsql

Class for DDL

I want to create, alter and delete tables (not records) from ASP code. Does
anybody know of a class (preferably ASP or DLL) that hides the complexity of
the Data Definition Language?
CedricTake a look at the DMO API. However, first consider your reasons for wanting
to do this. DMO is a reasonable choice if you want to retrieve info about DB
objects or if you are developing a general-purpose database utility
(something like Enterprise Manager for example). For deployment of a
database application however, TSQL DDL is far more concise and much easier
to deploy in most environments.
At runtime, in a business process application, there should of course be no
general need to create and change database objects at all. Not if you have
the correct design anyway.
Hope this helps.
David Portas
SQL Server MVP
--|||Look at SQL-DMO in Books Online.
Paul
"Cedric" wrote:

> I want to create, alter and delete tables (not records) from ASP code. Doe
s
> anybody know of a class (preferably ASP or DLL) that hides the complexity
of
> the Data Definition Language?
> Cedric
>
>|||>I want to create, alter and delete tables (not records) from ASP code
Now I re-read that I am pretty alarmed! Why would you want to alter and
delete tables from ASP? Are you really writing a replacement for Enterprise
Manager? If so, you should first take a look at some of the similar third
party tools already available.
David Portas
SQL Server MVP
--

CIRUCLAR REFERENCE Microsoft SQL Server 2000 Access ON DELETE UPDATE CASCADE

I need some Microsoft Access / SQL Server 2000 help. I have a SQL
Server 2000 database that is using Access as a front end (adp file).
Since SQL server does not allow cycle cascading or circular reference,
I am having problems.
For example, suppose I have tableA, tableB, tableC, and tableD. tableB
and tableC each have a foreign key (tableAID) which is tableA's primary
key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
tableB's primary key and one is tableC's primary Key. The problem is
that I can not have ON UPDATE/DELETE CASCADE because it is
circular/cyclic referential integrity. (I get an error when I try to
set both tableB and tableC to ON DELETE CASCADE... I can only set it
for one of them) What can I do to still have the behavior of ON
UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
but I don't know how to program a trigger. My objective is that when
I delete (or update) a row in tableA, the corresponding rows in tableB,
tableC, and tableD will be deleted/updated. Please help!The best way would be to not depend on cascading updates. Rather, you should
be
using stored procedures and *explicitly* delete referenced data as part of a
conscious design. However, I'd imagine you have your frontend linked directl
y to
the tables in the backend. This, among other reasons, is why this is a weak
design.
Thomas
<mneufeld@.gmail.com> wrote in message
news:1121874815.358446.298010@.f14g2000cwb.googlegroups.com...
>I need some Microsoft Access / SQL Server 2000 help. I have a SQL
> Server 2000 database that is using Access as a front end (adp file).
> Since SQL server does not allow cycle cascading or circular reference,
> I am having problems.
> For example, suppose I have tableA, tableB, tableC, and tableD. tableB
> and tableC each have a foreign key (tableAID) which is tableA's primary
> key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
> tableB's primary key and one is tableC's primary Key. The problem is
> that I can not have ON UPDATE/DELETE CASCADE because it is
> circular/cyclic referential integrity. (I get an error when I try to
> set both tableB and tableC to ON DELETE CASCADE... I can only set it
> for one of them) What can I do to still have the behavior of ON
> UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
> but I don't know how to program a trigger. My objective is that when
> I delete (or update) a row in tableA, the corresponding rows in tableB,
> tableC, and tableD will be deleted/updated. Please help!
>|||Why is this a weak design? Please Explain.
Is there anything wrong with depending on cascading deletes and
updates?
Also, how would I create a stored procedure or a trigger to
automatically delete the corresponding rows in tableB tableC and tableD
when I delete a row in tableA?|||>> Since SQL server does not allow cycle cascading or circular reference, I
am having problems. <<
Other SQLs do not have this problem, so you might want to switch to one
of them. Otherwise, you can use triggers (remember to comment it as
kludge) or write a stored procedure to do the job.
Don't do narratives; do DDL or at least psuedo-code. Is this what you
meant?
CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Gamma
(gamma_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Delta
(delta_id INTEGER NOT NULL PRIMARY KEY,
beta_id INTEGER NOT NULL
REFERENCES Beta(beta_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
gamma_id INTEGER NOT NULL
REFERENCES Gamma(gamma_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
There is two paths from Alpha to Delta (A -> B -> D) and( A -> C -> D).
SQL Server sees this and chokes.
One ugly trick is to create a BetaGamma table with a compound key so
there is only one path (A ->. BC ->. D). Another trick is split Delta
so you have (A -> B -> D1) and( A -> C -> D2). But we'd need more
information to do one of these.
One of the advantages of DRI is that it gives information to the
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc.|||Thanks Celko for replying. It is actually funny because I was looking
at other threads that you replied to and I was thinking, "Wow, it looks
like Celko knows his stuff and I wish he would reply to my thread."
Then low and behold I checked my thread and you replied! I have a few
questions though.
I'm new to groups and I don't know what DDL is?
Yes
kludge) or write a stored procedure to do the job. <<
I have never written a trigger and I do not know how to. Can you please
explain.
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc. <<
I don't understand this entire paragraph. I'm new to groups and to
databases so can you please explain it to me.
Thanx.|||>> I'm new to groups and I don't know what DDL is? <<
SQL has three sub-languages: DDL (Data Declaration Language), DML (Data
Manipulation Language) and DCL (Data Control Language). DDL is the
CREATE, ALTER and DROP stuff.
For that, you need a book not a newsgroup. A trigger is a kind of
stored procedure that is attachd to a table and it gets "fired"
(invoked) when a database event occurs (update, delete, insert). Three
are some special things about triggers which involve the delted and
inserted rows in the T_SQL model and there are some limits as to what
you can do in a Trigger.
Oh Boy! This way outside a newsgroup! You tell SQL *what" you want
and the optimzer figures out *how* to find it. The optimizer uses
statistics, constraints, and DRI to pick the right access methods.
PK-FK can pass on some ifno. For example, can you look at this
predicate and tell me if it is TRUE or FALSE?
EXISTS
(SELECT *
FROM Delta
WHERE Delta.beta_id = Beta.beta_id).
Likewise if I do a JOIN on (Delta.beta_id = Beta.beta_id), I can pull
up stats to see the statistical distribution, number of disticnt
beta_id values in each table, etc.|||Celko,
Thanx for explaining heres the DDL:
CREATE TABLE tbServer
(lngServerID INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE tbDatabase
(lngDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID )
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplication
(lngApplicationID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplicationToDatabase
(lngApplicationToDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngApplicationID INTEGER NOT NULL
REFERENCES tbApplication(lngApplicationID)
ON DELETE CASCADE
ON UPDATE CASCADE,
lngDatabaseID INTEGER NOT NULL
REFERENCES tbDatabase(lngDatabaseID)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
First of all keep in mind that this is a sample DDL, not the real one.
OK so basically this is keeping track of the server's application and
databases and sometimes an Application connects to a DB for storage
etc.
I have database(s) that are running on server(s) that connect to
application(s) which may be running on different server(s). So when I
delete a server record I want all the applications and databases
running on this particular server deleted AND any connections deleted
(since the DB or App does not exist anymore). How can I do this. If the
only way is to do it with triggers then please show me the code for it.
Thanx a lot!|||
--CELKO-- wrote:
> Other SQLs do not have this problem, so you might want to switch to one
> of them.
Yep, even ACCESS can do this correctly! They've fixed it for SQL Server
2005, I understand.|||It is a weak design because it lacks abstraction and thereby limits your abi
lity
to make design changes. By linking directly to the source tables, you have n
o
ability to effective business rules in the backend without using something l
ike
triggers. Your example is quite illustrative of this design's short comings.
You
want to effect a business rule (delete X when I delete Y). This would be a s
nap
if you were doing all of your deletes through stored procedures which shield
the
UI to some degree from the backend structure. However, since there is no
abstraction between the UI and the backend database, you are left with a han
dful
of ugly choices (e.g. triggers, or UI logic).
A good indication of a weak design is one that prevents you from easily
effecting a simple change.
Thomas
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>|||> Is there anything wrong with depending on cascading deletes and
> updates?
YES!!!!! Cascading referential actions are problematic for a number of
reasons, but the main one involves locks, concurrency, and deadlocks.
Cascading updates and deletes complicate deadlock resolution because the
order in which locks are obtained for a cascading referential action is
undefined. From a programmer's perspective, all locks in the cascade chain
are obtained at the same time. While the problem is more prevalent with
cascading updates, the same principles apply to a cascading delete. The way
to minimize deadlocks is to always obtain locks in the same order in every
transaction. Cascading referential actions limit the control you as a
programmer have in specifying that order. I never use them. I use
surrogate keys so I don't have to worry about cascading updates, and I
explicitly code deletes either in a trigger, or in a stored procedure. That
way I have complete control over the order in which resources are locked,
and I only have to worry about deadlocks caused by a malformed execution
plan. In those very rare cases, I can manipulate the execution plan by
specifying optimizer hints.
CREATE TRIGGER tIOD_tableA ON tableA INSTEAD OF DELETE AS
BEGIN
DELETE tableD
FROM
(SELECT keyD FROM deleted JOIN tableB ON (tableB.keyA =
deleted.keyA)
UNION SELECT keyD FROM deleted JOIN tableC ON (tableC.keyA =
deleted.keyA))) allD
WHERE tableD.keyD = allD.keyD
DELETE tableB FROM deleted WHERE tableB.keyA = deleted.keyA
DELETE tableC FROM deleted WHERE tableC.keyA = deleted.keyA
DELETE tableA FROM deleted WHERE tableA.keyA = deleted.keyA
END
GO
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>

Chunked Delete?

I can't truncate a table because I'd lose the Identity columns that
are referenced in other tables, but I do need to delete all 2 million
rows in there.
Is there a way to recusrively delete rows in say 1,000-row chunks to
keep the transactions small and DB load lighter?
Thanks.See if this thread helps:
http://groups-beta.google.com/group...
a0488274c6e93
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"xenophon" <xenophon@.online.nospam> wrote in message
news:8qhga11qephoebnh1afec88usol8rulg44@.
4ax.com...
I can't truncate a table because I'd lose the Identity columns that
are referenced in other tables, but I do need to delete all 2 million
rows in there.
Is there a way to recusrively delete rows in say 1,000-row chunks to
keep the transactions small and DB load lighter?
Thanks.sqlsql

Checkbox

Hi,

Even this question is not related to this forum but may be some one help me.

I have a master detail form. I want to delete record but only those record that user click with checkbox. There is no field like boolean. How can I make a scenerio about adding an unbound checkbox in detail form and how would i link with rows in detail sub form.

any help will be highly appreciated.

Regards,I'm guessing you are using MS Access? You should probably post your message in the MS access forum.

Use the bit data type in SQL Server to represent boolean values.|||i'd rub it with bacon.
everything works better with bacon.

mmmmmmmm

Check who have INSERT, UPDATE and DELETE privileges?

Hi,
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bing
sp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing

Check who have INSERT, UPDATE and DELETE privileges?

Hi,
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bingsp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing

Check who have INSERT, UPDATE and DELETE privileges?

Hi,
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bingsp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing

Check to see if a row exists in another table before insert

I have a stored procedure that selects invoices based on the date range

delete from BillingCurrent

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

delete from Billing30

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing3

Now, I need to check to see if the row exists in Billing30, if it exists in Billing 30 then I don't want it to insert into BillingCurrent.

You can use something along the lines of the code listed below.

Chris

IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is found

END

ELSE

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is not found

END

|||

Ok I understand that and it's very helpful but,

for

IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is found

I want it to do Nothing if the row exists but I don't want it to exit because I need to do this for 5 tables

END

|||

There's nothing to stop you using multiple IF statements or even nesting them if you desire, see below - note that I've reversed the logic.

Chris

IF NOT EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is not found

END

IF NOT EXISTS (SELECT 1 FROM Table2 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Table2 record is not found

END

IF NOT EXISTS (SELECT 1 FROM Table3 WHERE <insert criteria>)

etc....

|||

ok so I can nest all of the if not exists and then after those just

if exists

end

to make it do nothing if the row already exists?

|||

In my previous example none of the code within the BEGIN END blocks will execute if at least one row meeting the relevant criteria exists in each of the tables that you are checking. There's no need to add any additional code to make SQL Server do nothing - if a condition fails then the code within the associated BEGIN END block will not be executed, it's as simple as that. If all of the conditions fail then the batch will complete without executing any of the code within any of the BEGIN END blocks.

It isn't clear from the description of your scenario whether you will need to use nested or multiple IF statements so I can't help any further in that respect without more info.

Chris

|||

Ok, Heres my SP

This prints out (because I use a relation from the billing tables to the InvoiceDetails Table) A Billing statement for each customer, problem is: if a customer has an invoice this month and last month then if prints out two invoices. I'm trying to get it to check each table first billing120 then billing90 then billing60..... So the customer row only gets inserted once.

delete from Billing120

insert into Billing120(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120

delete from Billing90

IF NOT EXISTS (SELECT CustomerID FROM Billing120)--WHERE <insert criteria>)

BEGIN

insert into Billing90(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90

END

delete from Billing60

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into Billing60(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60

End

delete from Billing30

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30

END

delete from BillingCurrent

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

END

RETURN

|||

Maybe you should try a different approach then, see below. This approach allows you to analyze the contents of the tables before performing any INSERTs etc...

Chris

DECLARE @.Billing120Exists BIT

DECLARE @.Billing90Exists BIT

DECLARE @.Billing60Exists BIT

DECLARE @.Billing30Exists BIT

SET @.Billing120Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing120) THEN 1 ELSE 0 END

SET @.Billing90Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing90) THEN 1 ELSE 0 END

SET @.Billing60Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing60) THEN 1 ELSE 0 END

SET @.Billing30Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing30) THEN 1 ELSE 0 END

--Insert logic here that examines the values of the @.BillingExists variables and performs the appropriate actions.

--If you want you can declare additional variables to indicate whether or not rows have subsequently been inserted into one of the tables.

|||

Actually I ended up doing it this way,

delete from Billing120

insert into Billing120(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120

delete from Billing90

BEGIN

insert into Billing90(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90

END

delete from Billing60

BEGIN

insert into Billing60(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60

End

delete from Billing30

BEGIN

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30

END

delete from BillingCurrent

BEGIN

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)

And not exists (select 1 from billing30 e where E.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

END

RETURN

Thanks for the Help!