I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
Thanks!
Vic(1) to create a blank DB from an existing DB
generate a full script from the existing DB and run it. it is that easy
(2) to populate data
use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
(3) reporting differences
there r tools that can compare 2 DB and generate a report of differences|||Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
Thanks,|||sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.|||Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.|||In SQL Server Management Studio:
Management >> Legacy >> Data Transformation Services.
They're fairl self explanatory...
Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.
Hope that's of some help. Feel free to ask away!|||I just went back and re-read my original post and I see where I need to clarify a few points.
I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.
Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better! :o
So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.
Thanks in advance for helping.|||to generate scripts for all objects, you might try this free app I wrote, using SMO. The source is available so you can tweak it if it's not exactly what you are looking for:
http://www.elsasoft.org/tools.htm
it will also script all the data out (optionally) using bcp.exe.
I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Tuesday, March 27, 2012
Clear all the tables of a database
Is there a simple method to clear all the tables in a db or to make a copy of the mdf file without the datas ?
I tried to use TRUNCATE TABLE but it doesn't work with foreign keys ?
Thanks.
You probably will have to write your own script for this to reflect the dependencies. As you already stated, Truncate won′t work with foreign keys and an undocumented procedure like sp_msforeachtable won′t care about the order to process.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Cleanup of unused database objects
Hi,
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
quote:
> Hi,
> I've inherited a database with lots of unused objects (tables and
> SPs). What I want to do is determine wich objects have not been used
> for the last 30 days and remove them from the database.
> Is there a way to determine the last time a table was accessed or a
> Stored Procedure was run. I've looked at the system tables but haven't
> seen anything that indicates this. I can put a trace on, but that
> would consume too many cycles of the machine.
> Any ideas or help would be greatly appreciated.
> Ed
Cleanup of unused database objects
Hi,
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
> Hi,
> I've inherited a database with lots of unused objects (tables and
> SPs). What I want to do is determine wich objects have not been used
> for the last 30 days and remove them from the database.
> Is there a way to determine the last time a table was accessed or a
> Stored Procedure was run. I've looked at the system tables but haven't
> seen anything that indicates this. I can put a trace on, but that
> would consume too many cycles of the machine.
> Any ideas or help would be greatly appreciated.
> Ed
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
> Hi,
> I've inherited a database with lots of unused objects (tables and
> SPs). What I want to do is determine wich objects have not been used
> for the last 30 days and remove them from the database.
> Is there a way to determine the last time a table was accessed or a
> Stored Procedure was run. I've looked at the system tables but haven't
> seen anything that indicates this. I can put a trace on, but that
> would consume too many cycles of the machine.
> Any ideas or help would be greatly appreciated.
> Ed
cleaning wrong PK FK
Hello
my 2 tables in MS SQL 2000
Report :
Report_id (PK)
name
Product :
Product_id (PK)
Report_id (FK)
name
the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand
now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK
i am trying :
DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))
DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))
but the database crash : time overflow !
how can I do it ?
thank youHere are two alternate methods:--Method #1: EXISTS
delete
from Product
where not exists (select * from Report where Report.Report_id = Product.Report_id)
--Method #2: LEFT OUTER JOIN
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
In either method, make sure Report_id is indexed in both tables.|||thank you BlindMan
on the 2nd method i am getting :
Incorrect syntax near the keyword 'left'.|||Post your code.|||that one
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
Incorrect syntax near the keyword 'left'
you said : in either method, make sure Report_id is indexed in both tables.
they are PK to FK but they are not indexed
how can I do it when the tables are allready full
ALTER TABLE create index ?
thank's a lot|||I'm assuming that you defined the PK and FK in your head, but haven't done anything with the database. A PK that doesn't exist using a PRIMARY KEY definition is only a good intention from my perspective. ;) I would suggest using something like:CREATE INDEX dropme01 ON Report (Report_Id)
CREATE INDEX dropme02 ON Product (Report_Id)
DELETE FROM Report
WHERE NOT EXISTS (SELECT *
FROM Product
WHERE Product.Report_Id = Report.Report_Id)
DELETE FROM Product
WHERE NOT EXISTS (SELECT *
FROM Report
WHERE Report.Report_id = Product.Report_Id)
DROP INDEX Report.dropme01
DROP INDEX Product.dropme02
ALTER TABLE Report
ADD CONSTRAINT XPKReport
PRIMARY KEY (Report_Id)
ALTER TABLE Product
ADD CONSTRAINT XPKProduct
PRIMARY KEY (Product_Id)
ALTER TABLE Product
ADD CONSTRAINT XFK01Report
FOREIGN KEY (Report_Id)
REFERENCES Report (Report_Id)-PatP|||Pat FK and PK are allready in the tables|||Pat FK and PK are allready in the tables
Are you having any problem now?|||with your first method it works very well
i was just wondering why it doesnt with the second method
but it works ...
thanks a lot|||Public kya Time pass karne aati hai kya idhar?|||Public kya Time pass karne aati hai kya idhar?
No Hindi man, I think English would be more appropiate to express anything that you post here.
He was telling ," Do the people come here only to pass time?"|||No Hindi man, I think English would be more appropiate to express anything that you post here.
He was telling ," Do the people come here only to pass time?"Lol - well I think we all know the answer to that.
Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)|||Lol - well I think we all know the answer to that.
Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)
........;)|||Sorry. There was a syntax error in my second example. This should work:
delete Product
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null|||i try it thank you
my 2 tables in MS SQL 2000
Report :
Report_id (PK)
name
Product :
Product_id (PK)
Report_id (FK)
name
the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand
now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK
i am trying :
DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))
DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))
but the database crash : time overflow !
how can I do it ?
thank youHere are two alternate methods:--Method #1: EXISTS
delete
from Product
where not exists (select * from Report where Report.Report_id = Product.Report_id)
--Method #2: LEFT OUTER JOIN
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
In either method, make sure Report_id is indexed in both tables.|||thank you BlindMan
on the 2nd method i am getting :
Incorrect syntax near the keyword 'left'.|||Post your code.|||that one
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
Incorrect syntax near the keyword 'left'
you said : in either method, make sure Report_id is indexed in both tables.
they are PK to FK but they are not indexed
how can I do it when the tables are allready full
ALTER TABLE create index ?
thank's a lot|||I'm assuming that you defined the PK and FK in your head, but haven't done anything with the database. A PK that doesn't exist using a PRIMARY KEY definition is only a good intention from my perspective. ;) I would suggest using something like:CREATE INDEX dropme01 ON Report (Report_Id)
CREATE INDEX dropme02 ON Product (Report_Id)
DELETE FROM Report
WHERE NOT EXISTS (SELECT *
FROM Product
WHERE Product.Report_Id = Report.Report_Id)
DELETE FROM Product
WHERE NOT EXISTS (SELECT *
FROM Report
WHERE Report.Report_id = Product.Report_Id)
DROP INDEX Report.dropme01
DROP INDEX Product.dropme02
ALTER TABLE Report
ADD CONSTRAINT XPKReport
PRIMARY KEY (Report_Id)
ALTER TABLE Product
ADD CONSTRAINT XPKProduct
PRIMARY KEY (Product_Id)
ALTER TABLE Product
ADD CONSTRAINT XFK01Report
FOREIGN KEY (Report_Id)
REFERENCES Report (Report_Id)-PatP|||Pat FK and PK are allready in the tables|||Pat FK and PK are allready in the tables
Are you having any problem now?|||with your first method it works very well
i was just wondering why it doesnt with the second method
but it works ...
thanks a lot|||Public kya Time pass karne aati hai kya idhar?|||Public kya Time pass karne aati hai kya idhar?
No Hindi man, I think English would be more appropiate to express anything that you post here.
He was telling ," Do the people come here only to pass time?"|||No Hindi man, I think English would be more appropiate to express anything that you post here.
He was telling ," Do the people come here only to pass time?"Lol - well I think we all know the answer to that.
Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)|||Lol - well I think we all know the answer to that.
Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)
........;)|||Sorry. There was a syntax error in my second example. This should work:
delete Product
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null|||i try it thank you
Sunday, March 25, 2012
cleaning up database
Hey,
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
cleaning up database
Hey,
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?
DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?
DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
cleaning up database
Hey,
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
This is puzzling me ... I had a relative big database (60GB) full with
data. Now I got rid of most of the data (when outputing tables with
sp_spaceused proc I get total of only 90MB) to create a dev db with the
same procs and structure that the production is. However after
reindexing and executing maintenance plan, the database is still 20 GB.
I know I don't have 19GB worth of procedures and triggers or viewes and
tables.
What else do I have to clean up in order to cut the database size down?DBCC SHRINKDATABASE
"laimis" wrote:
> Hey,
> This is puzzling me ... I had a relative big database (60GB) full with
> data. Now I got rid of most of the data (when outputing tables with
> sp_spaceused proc I get total of only 90MB) to create a dev db with the
> same procs and structure that the production is. However after
> reindexing and executing maintenance plan, the database is still 20 GB.
> I know I don't have 19GB worth of procedures and triggers or viewes and
> tables.
> What else do I have to clean up in order to cut the database size down?
>
cleaning up conflicts table
We have 30 remote subscribers and a publisher.We have
experienced in past that the conflict tables grow to the
limit and for some reason after that we start having
replication issues.(It will take anywhere from 3 hr to 6
hrs compare to 10 mins)
becos of the timings the data gets replicated to all the
sites but it doesn't clear them up from the conflicts
tables. How do we manually purge the conflict records so
that it shouldn't cause us problems in long run.
thanks
delete table
where....
Conflict tables are just that tables. There is nothing special about them,
so you can freely insert/update/delete from them. (They are required for
merge so just playing with data isn't recommended.) But, you won't get a
failure on a transaction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||HI should I use delte table name or I should use truncate
table name statement.
thanks
>--Original Message--
>delete table
>where....
>Conflict tables are just that tables. There is nothing
special about them,
>so you can freely insert/update/delete from them. (They
are required for
>merge so just playing with data isn't recommended.) But,
you won't get a
>failure on a transaction.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
sqlsql
experienced in past that the conflict tables grow to the
limit and for some reason after that we start having
replication issues.(It will take anywhere from 3 hr to 6
hrs compare to 10 mins)
becos of the timings the data gets replicated to all the
sites but it doesn't clear them up from the conflicts
tables. How do we manually purge the conflict records so
that it shouldn't cause us problems in long run.
thanks
delete table
where....
Conflict tables are just that tables. There is nothing special about them,
so you can freely insert/update/delete from them. (They are required for
merge so just playing with data isn't recommended.) But, you won't get a
failure on a transaction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||HI should I use delte table name or I should use truncate
table name statement.
thanks
>--Original Message--
>delete table
>where....
>Conflict tables are just that tables. There is nothing
special about them,
>so you can freely insert/update/delete from them. (They
are required for
>merge so just playing with data isn't recommended.) But,
you won't get a
>failure on a transaction.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
sqlsql
Clean up a table & save to another table
I'm trying to filter and update the table AllAgreements (below) by
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id
> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT
> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John
> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id
> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT
> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John
> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Thursday, March 22, 2012
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
--
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
--
Tuesday, March 20, 2012
Ciclic Foreign Keys?
Hi:
Scenario:
-) I have four tables TableA, TableB, TableC and ProductTable.
-) TableA is the main header table of TableB, TableB contains a
reference to a 'Product' in table ProductTable.
-) TableB is, in turn, a header table of TableC, TableC contains a
reference to a 'Product' in table ProductTable.
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
to TableA, so if the row in TableA is deleted, it also deletes TableB's
corresponding row(s).
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
to TableB, so if the row in TableB is deleted, it also deletes TableC's
corresponding row(s).
Now I want to add 2 more ForeignKeys from TablaB and TableC to the
ProductTable with CASCADING DELETES, so if the product is deleted it
will automatically delete corresponding rows from TableB and TableC.
I can set the ForeignKey on *one* of the tables, but I can't set it on
both as the servers informs me that this would cause a cyclic action. I
can't see what the problem is. If TableB contains a reference to a
particular product (lets call it 'ProductX') and TableC contains a
reference to another product ('ProductY') there is not cyclic deletion.
Can anyone help me workout what the problem is.
TIA,
MartinH.The problem is that there are multiple cascade paths from ProductTable to
TableC. There isn't any declarative way to specify that the reference
between TableB and ProductTable and the reference between TableC and
ProductTable can never refer to the same product, so SQL Server must assume
that they can, and consequently prevents the referential action declaration.
It looks like you're going to have to use a trigger to implement the cascade
delete.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi:
> Scenario:
> -) I have four tables TableA, TableB, TableC and ProductTable.
> -) TableA is the main header table of TableB, TableB contains a
> reference to a 'Product' in table ProductTable.
> -) TableB is, in turn, a header table of TableC, TableC contains a
> reference to a 'Product' in table ProductTable.
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
> to TableA, so if the row in TableA is deleted, it also deletes TableB's
> corresponding row(s).
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
> to TableB, so if the row in TableB is deleted, it also deletes TableC's
> corresponding row(s).
> Now I want to add 2 more ForeignKeys from TablaB and TableC to the
> ProductTable with CASCADING DELETES, so if the product is deleted it
> will automatically delete corresponding rows from TableB and TableC.
> I can set the ForeignKey on *one* of the tables, but I can't set it on
> both as the servers informs me that this would cause a cyclic action. I
> can't see what the problem is. If TableB contains a reference to a
> particular product (lets call it 'ProductX') and TableC contains a
> reference to another product ('ProductY') there is not cyclic deletion.
> Can anyone help me workout what the problem is.
> TIA,
> MartinH.|||Brian:
Thanks, I now understand why, and thanks to your suggestion 'how' I can
get around the problem.
Thanks again.
Martin.
Brian Selzer escribi:
> The problem is that there are multiple cascade paths from ProductTable to
> TableC. There isn't any declarative way to specify that the reference
> between TableB and ProductTable and the reference between TableC and
> ProductTable can never refer to the same product, so SQL Server must assum
e
> that they can, and consequently prevents the referential action declaratio
n.
> It looks like you're going to have to use a trigger to implement the casca
de
> delete.
>
> "Martin Hart" <martin.hartturner@.gmail.com> wrote in message
> news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>
>|||I prefer to avoid cascading referential actions whenever possible because
they introduce an additional level of complexity into deadlock minimization
and avoidance. It's better in my opinion to spell out the deletes or
updates, either in an instead of trigger or in a stored procededure. This
way I control the order in which locks are obtained, thus minimizing
deadlocks.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:#f7DXN3kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Brian:
> Thanks, I now understand why, and thanks to your suggestion 'how' I can
> get around the problem.
> Thanks again.
> Martin.
> Brian Selzer escribi:
to
assume
declaration.
cascade
Scenario:
-) I have four tables TableA, TableB, TableC and ProductTable.
-) TableA is the main header table of TableB, TableB contains a
reference to a 'Product' in table ProductTable.
-) TableB is, in turn, a header table of TableC, TableC contains a
reference to a 'Product' in table ProductTable.
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
to TableA, so if the row in TableA is deleted, it also deletes TableB's
corresponding row(s).
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
to TableB, so if the row in TableB is deleted, it also deletes TableC's
corresponding row(s).
Now I want to add 2 more ForeignKeys from TablaB and TableC to the
ProductTable with CASCADING DELETES, so if the product is deleted it
will automatically delete corresponding rows from TableB and TableC.
I can set the ForeignKey on *one* of the tables, but I can't set it on
both as the servers informs me that this would cause a cyclic action. I
can't see what the problem is. If TableB contains a reference to a
particular product (lets call it 'ProductX') and TableC contains a
reference to another product ('ProductY') there is not cyclic deletion.
Can anyone help me workout what the problem is.
TIA,
MartinH.The problem is that there are multiple cascade paths from ProductTable to
TableC. There isn't any declarative way to specify that the reference
between TableB and ProductTable and the reference between TableC and
ProductTable can never refer to the same product, so SQL Server must assume
that they can, and consequently prevents the referential action declaration.
It looks like you're going to have to use a trigger to implement the cascade
delete.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi:
> Scenario:
> -) I have four tables TableA, TableB, TableC and ProductTable.
> -) TableA is the main header table of TableB, TableB contains a
> reference to a 'Product' in table ProductTable.
> -) TableB is, in turn, a header table of TableC, TableC contains a
> reference to a 'Product' in table ProductTable.
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
> to TableA, so if the row in TableA is deleted, it also deletes TableB's
> corresponding row(s).
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
> to TableB, so if the row in TableB is deleted, it also deletes TableC's
> corresponding row(s).
> Now I want to add 2 more ForeignKeys from TablaB and TableC to the
> ProductTable with CASCADING DELETES, so if the product is deleted it
> will automatically delete corresponding rows from TableB and TableC.
> I can set the ForeignKey on *one* of the tables, but I can't set it on
> both as the servers informs me that this would cause a cyclic action. I
> can't see what the problem is. If TableB contains a reference to a
> particular product (lets call it 'ProductX') and TableC contains a
> reference to another product ('ProductY') there is not cyclic deletion.
> Can anyone help me workout what the problem is.
> TIA,
> MartinH.|||Brian:
Thanks, I now understand why, and thanks to your suggestion 'how' I can
get around the problem.
Thanks again.
Martin.
Brian Selzer escribi:
> The problem is that there are multiple cascade paths from ProductTable to
> TableC. There isn't any declarative way to specify that the reference
> between TableB and ProductTable and the reference between TableC and
> ProductTable can never refer to the same product, so SQL Server must assum
e
> that they can, and consequently prevents the referential action declaratio
n.
> It looks like you're going to have to use a trigger to implement the casca
de
> delete.
>
> "Martin Hart" <martin.hartturner@.gmail.com> wrote in message
> news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>
>|||I prefer to avoid cascading referential actions whenever possible because
they introduce an additional level of complexity into deadlock minimization
and avoidance. It's better in my opinion to spell out the deletes or
updates, either in an instead of trigger or in a stored procededure. This
way I control the order in which locks are obtained, thus minimizing
deadlocks.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:#f7DXN3kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Brian:
> Thanks, I now understand why, and thanks to your suggestion 'how' I can
> get around the problem.
> Thanks again.
> Martin.
> Brian Selzer escribi:
to
assume
declaration.
cascade
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
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
chunkdata and snapshotdata tables
Can someone advise under what conditions these tables are populated? I
gather from the documentation that if you set the 'Render this report from
an execution snapshot ' Properties of a report through ReportManager via a
schedule or whatever then the snapshot will be created as per the schedule
and stored.
I don't believe there are any snapshot schedules on the RS server, and the
'History' and 'Schedule' table is empty but the 'SnapshotData' and
'ChunkData' tables are accumulating daily.
I think they should be empty; nothing in BOL about, any insight on these
table contents?Snapshots are generated for every report rendering. Depending on the
context the report was rendered in the snapshot may only be available to
certain users under certain conditions. The RS Temp db and the catalog DB
both store snapshots depending on how "permanent" the snapshot is.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"CR" <CR@.discussions.microsoft.com> wrote in message
news:8D34CCC3-1404-4DD7-B074-21B95DB57027@.microsoft.com...
> Can someone advise under what conditions these tables are populated? I
> gather from the documentation that if you set the 'Render this report
from
> an execution snapshot ' Properties of a report through ReportManager via
a
> schedule or whatever then the snapshot will be created as per the schedule
> and stored.
> I don't believe there are any snapshot schedules on the RS server, and the
> 'History' and 'Schedule' table is empty but the 'SnapshotData' and
> 'ChunkData' tables are accumulating daily.
> I think they should be empty; nothing in BOL about, any insight on these
> table contents?
gather from the documentation that if you set the 'Render this report from
an execution snapshot ' Properties of a report through ReportManager via a
schedule or whatever then the snapshot will be created as per the schedule
and stored.
I don't believe there are any snapshot schedules on the RS server, and the
'History' and 'Schedule' table is empty but the 'SnapshotData' and
'ChunkData' tables are accumulating daily.
I think they should be empty; nothing in BOL about, any insight on these
table contents?Snapshots are generated for every report rendering. Depending on the
context the report was rendered in the snapshot may only be available to
certain users under certain conditions. The RS Temp db and the catalog DB
both store snapshots depending on how "permanent" the snapshot is.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"CR" <CR@.discussions.microsoft.com> wrote in message
news:8D34CCC3-1404-4DD7-B074-21B95DB57027@.microsoft.com...
> Can someone advise under what conditions these tables are populated? I
> gather from the documentation that if you set the 'Render this report
from
> an execution snapshot ' Properties of a report through ReportManager via
a
> schedule or whatever then the snapshot will be created as per the schedule
> and stored.
> I don't believe there are any snapshot schedules on the RS server, and the
> 'History' and 'Schedule' table is empty but the 'SnapshotData' and
> 'ChunkData' tables are accumulating daily.
> I think they should be empty; nothing in BOL about, any insight on these
> table contents?
Wednesday, March 7, 2012
Checkpoint restart
I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
Gordon,
That's an interesting question - I'd like to think that it is possible - but I don't know.
There is a workaround - its a bit messy but it will work. A ForEach Loop is the unit of restartability, rather than the containers inside it so if you set up a ForEach loop that only looped once with a matching Execute SQL Task and data-flow inside it - they would both execute on restart.
-Jamie
|||Another way is to have matching Execute SQL Task and data-flow in a child package...|||
Thanks Jamie,
I'll give that a try after I try something else first. I'm gonna try and get the data flow to completely rollback by putting it in a transaction using the Required setting. That way I won't have to rerun the truncate step. My first attemp raised a DTC error just like the ones you have documented in the past. These are corporate servers so I don't know if firewalls are at play between the servers in question. Anyway, I've read your comments on that and have forwarded them to our tech guys.
Gordy
|||Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
|||Mark Challen wrote:
Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126489
If you think this would be a good feature to have, click through, comment and vote.
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
Use the 'ForEach Item' enumerator and just put one item in the collection.
-Jamie
|||
I added my vote.
Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!
|||I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
The container approach does work but you'll have to set the options correctly.
Try this:
Put the truncate & matching load pair in the container|||Ravi, I would love for this to work, but cannot get the "container restart" behavior you're describing. Here's what I'm seeing instead.
Follow your instructions (sequence container FailPackageOnFailure = True and contained tasks FailParentOnFailure = true). After a failure in the load task, the package restart does indeed occur from the beginning of the sequence container, but none of the contained tasks run at all.
First, is this what you're seeing?
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point?|||
Jaegd,
Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct?
For this to work, your task should not be checkpointed.
i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.
This approach does work for me regardless of whether the container is transacted or not.
In the meantime, I'll see where I can post an example package.
|||The contained tasks' only non-default setting was 'FailParentOnFailure' = True. If you could post an example package to a blog or a file sharing site, that would be great.In the meantime, here's a blog entry which contains the code for C# console app that will generate a package (thanks to Ivolva). The package contains a single sequence container, with two contained tasks, the second of which will fail half the time, and configured with the sequence container set the FailPackageOnFailure=true, and the tasks only non-default setting to FailParentOnFailure=true.
http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry
Here's the xml for the package as well, since we can't add attachments on this forum.
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">FPS\jaegd</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">FPS</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">12/9/2006 12:53:33 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">8</DTS:Property><DTS:Property DTS:Name="VersionGUID">{BE15E67D-BFF8-4ED0-BA4E-FFAF36D3B304}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName">AtomicSequence.xml</DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">-1</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">1</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE"><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">1</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Truncate</DTS:Property><DTS:Property DTS:Name="DTSID">{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Load</DTS:Property><DTS:Property DTS:Name="DTSID">{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptMain.vsaitem">
<![CDATA[Imports System
Imports System.Data
Imports System.Math
Imports System.Security.Cryptography
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
r.GetBytes(randomNumber)
randInt = Convert.ToInt32(randomNumber(0))
If randInt Mod 2 = 0 Then
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property><DTS:Property DTS:Name="DTSID">{EF54EA09-00A5-40A0-B192-78EF3F7E731C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">SEQ Atomic</DTS:Property><DTS:Property DTS:Name="DTSID">{39E92202-B557-4E08-9155-6ABAC07535BE}</DTS:Property><DTS:Property DTS:Name="Description">Sequence Container</DTS:Property><DTS:Property DTS:Name="CreationName">STOCK:SEQUENCE</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package3</DTS:Property><DTS:Property DTS:Name="DTSID">{22A10402-108C-4F32-89B3-EBD3C48990D5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
Checkpoint restart
I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
Gordon,
That's an interesting question - I'd like to think that it is possible - but I don't know.
There is a workaround - its a bit messy but it will work. A ForEach Loop is the unit of restartability, rather than the containers inside it so if you set up a ForEach loop that only looped once with a matching Execute SQL Task and data-flow inside it - they would both execute on restart.
-Jamie
|||Another way is to have matching Execute SQL Task and data-flow in a child package...|||
Thanks Jamie,
I'll give that a try after I try something else first. I'm gonna try and get the data flow to completely rollback by putting it in a transaction using the Required setting. That way I won't have to rerun the truncate step. My first attemp raised a DTC error just like the ones you have documented in the past. These are corporate servers so I don't know if firewalls are at play between the servers in question. Anyway, I've read your comments on that and have forwarded them to our tech guys.
Gordy
|||Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
|||Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126489
If you think this would be a good feature to have, click through, comment and vote.
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
Use the 'ForEach Item' enumerator and just put one item in the collection.
-Jamie
|||
I added my vote.
Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!
|||I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
The container approach does work but you'll have to set the options correctly.
Try this:
Put the truncate & matching load pair in the container|||Ravi, I would love for this to work, but cannot get the "container restart" behavior you're describing. Here's what I'm seeing instead.
Follow your instructions (sequence container FailPackageOnFailure = True and contained tasks FailParentOnFailure = true). After a failure in the load task, the package restart does indeed occur from the beginning of the sequence container, but none of the contained tasks run at all.
First, is this what you're seeing?
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point?|||
Jaegd,
Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct?
For this to work, your task should not be checkpointed.
i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.
This approach does work for me regardless of whether the container is transacted or not.
In the meantime, I'll see where I can post an example package.
|||The contained tasks' only non-default setting was 'FailParentOnFailure' = True. If you could post an example package to a blog or a file sharing site, that would be great.In the meantime, here's a blog entry which contains the code for C# console app that will generate a package (thanks to Ivolva). The package contains a single sequence container, with two contained tasks, the second of which will fail half the time, and configured with the sequence container set the FailPackageOnFailure=true, and the tasks only non-default setting to FailParentOnFailure=true.
http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry
Here's the xml for the package as well, since we can't add attachments on this forum.
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">FPS\jaegd</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">FPS</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">12/9/2006 12:53:33 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">8</DTS:Property><DTS:Property DTS:Name="VersionGUID">{BE15E67D-BFF8-4ED0-BA4E-FFAF36D3B304}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName">AtomicSequence.xml</DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">-1</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">1</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE"><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">1</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Truncate</DTS:Property><DTS:Property DTS:Name="DTSID">{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Load</DTS:Property><DTS:Property DTS:Name="DTSID">{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptMain.vsaitem">
<![CDATA[Imports System
Imports System.Data
Imports System.Math
Imports System.Security.Cryptography
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
r.GetBytes(randomNumber)
randInt = Convert.ToInt32(randomNumber(0))
If randInt Mod 2 = 0 Then
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property><DTS:Property DTS:Name="DTSID">{EF54EA09-00A5-40A0-B192-78EF3F7E731C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">SEQ Atomic</DTS:Property><DTS:Property DTS:Name="DTSID">{39E92202-B557-4E08-9155-6ABAC07535BE}</DTS:Property><DTS:Property DTS:Name="Description">Sequence Container</DTS:Property><DTS:Property DTS:Name="CreationName">STOCK:SEQUENCE</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package3</DTS:Property><DTS:Property DTS:Name="DTSID">{22A10402-108C-4F32-89B3-EBD3C48990D5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
Checkpoint restart
I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
Gordon,
That's an interesting question - I'd like to think that it is possible - but I don't know.
There is a workaround - its a bit messy but it will work. A ForEach Loop is the unit of restartability, rather than the containers inside it so if you set up a ForEach loop that only looped once with a matching Execute SQL Task and data-flow inside it - they would both execute on restart.
-Jamie
|||Another way is to have matching Execute SQL Task and data-flow in a child package...|||
Thanks Jamie,
I'll give that a try after I try something else first. I'm gonna try and get the data flow to completely rollback by putting it in a transaction using the Required setting. That way I won't have to rerun the truncate step. My first attemp raised a DTC error just like the ones you have documented in the past. These are corporate servers so I don't know if firewalls are at play between the servers in question. Anyway, I've read your comments on that and have forwarded them to our tech guys.
Gordy
|||Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
|||Mark Challen wrote:
Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!
This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126489
If you think this would be a good feature to have, click through, comment and vote.
Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.
Use the 'ForEach Item' enumerator and just put one item in the collection.
-Jamie
|||
I added my vote.
Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!
|||I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
The container approach does work but you'll have to set the options correctly.
Try this:
Put the truncate & matching load pair in the container|||Ravi, I would love for this to work, but cannot get the "container restart" behavior you're describing. Here's what I'm seeing instead.
Follow your instructions (sequence container FailPackageOnFailure = True and contained tasks FailParentOnFailure = true). After a failure in the load task, the package restart does indeed occur from the beginning of the sequence container, but none of the contained tasks run at all.
First, is this what you're seeing?
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point?|||
Jaegd,
Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct?
For this to work, your task should not be checkpointed.
i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.
This approach does work for me regardless of whether the container is transacted or not.
In the meantime, I'll see where I can post an example package.
|||The contained tasks' only non-default setting was 'FailParentOnFailure' = True. If you could post an example package to a blog or a file sharing site, that would be great.In the meantime, here's a blog entry which contains the code for C# console app that will generate a package (thanks to Ivolva). The package contains a single sequence container, with two contained tasks, the second of which will fail half the time, and configured with the sequence container set the FailPackageOnFailure=true, and the tasks only non-default setting to FailParentOnFailure=true.
http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry
Here's the xml for the package as well, since we can't add attachments on this forum.
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">FPS\jaegd</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">FPS</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">12/9/2006 12:53:33 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">8</DTS:Property><DTS:Property DTS:Name="VersionGUID">{BE15E67D-BFF8-4ED0-BA4E-FFAF36D3B304}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName">AtomicSequence.xml</DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">-1</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">1</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE"><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">1</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Truncate</DTS:Property><DTS:Property DTS:Name="DTSID">{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Load</DTS:Property><DTS:Property DTS:Name="DTSID">{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptMain.vsaitem">
<![CDATA[Imports System
Imports System.Data
Imports System.Math
Imports System.Security.Cryptography
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
r.GetBytes(randomNumber)
randInt = Convert.ToInt32(randomNumber(0))
If randInt Mod 2 = 0 Then
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property><DTS:Property DTS:Name="DTSID">{EF54EA09-00A5-40A0-B192-78EF3F7E731C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">SEQ Atomic</DTS:Property><DTS:Property DTS:Name="DTSID">{39E92202-B557-4E08-9155-6ABAC07535BE}</DTS:Property><DTS:Property DTS:Name="Description">Sequence Container</DTS:Property><DTS:Property DTS:Name="CreationName">STOCK:SEQUENCE</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package3</DTS:Property><DTS:Property DTS:Name="DTSID">{22A10402-108C-4F32-89B3-EBD3C48990D5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
Subscribe to:
Posts (Atom)