archiving

Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>sql

Archival logic/algoritm

We are in need of finalizing an archival approach for one of our Web and Client server application. The major requirements are



a) User can click on Web front end to start archival process.

b) The system should move the related data to archived space in a backup location.

c) Could be a batch process.

d) The relation between tables is extensive i.e. > 30 tables need to be managed for archival one component.

e) Database size is not very huge < 10 MB.





We were planning to have a table to store archival flag, which will be set when user click on Archival. Then a batch program will copy the database in to a backup location and delete the entries from archived database where archive flag is not set and delete entry from master database where archive flag is set. The problem is how to synchronize the changes when archival process runs next time i.e. the master database would have changed so how to put that data in archival database with out removing existing data.



Any other approach/practical solutions will be very helpful.





Regards,

Mridul MishraWant to reread that and tell us if you think that makes sense?

Archive what? How do you know what to mark?

Maybe a simple explination:

"I build widgets...and when a widget is shipped I need to archive the order..."

What are you doing?|||Hmmm...Sorry for being not clear enough. Some how i thought every one is bugged with the problem as much as i do...;)

Well archival is more an event based task...Let me try to give more relevant information here..it's normal microsoft project plan which are stored and displayed for users. In most of the cases users might decide on their own whims(as most of the users are ;) ) when they feel data need to be moved to archive.

Thanks and regards
Mridul

Approach Help to load Data from Flatfiles into relational table where Data is coming as spaces i

Hi ,

My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .

what approach should i take so that data gets loaded in a relational structure.

for example

Name Age Salary Address

dsds 23 fghghgh

Salary description level

2345 nnncncn 4

here salary is used in this example , the datatype is char in real scenario

what approach should i take to load the data in with cleansing the spaces in ssis

Assumption: You don't have any control over the structure of the destination tables, but you can add records.

To maintain your foreign key constraint, you will need to create a "salary" record that you can use where salary is not defined. EG:

Salary Description Level

9999 Not Assigned 1

Then you can use a Derived Column expression to create a new column in your dataset that uses an If expression and then write that derived column to your destination table

Like this:

Code Snippet

[Salary]=="" ? 9999 : [Salary]

This is not an optimal solution (even though it is very practical.) Ideally you should restructure your tables so:

Assumption: You can change the structure of your destination tables

In this case, you need to take the Salary column out of your destination table, and put it into another table. The relationship is that employees can have zero or one salary code. So you can create a table relating employees to salary codes with two columns (EmployeeNumber, SalaryCode) and a primary key on EmployeeCode.

(This is a drastic simplification, of course. In a real-world scenario you would probably want to track historical information too.)

|||

Thanks for the Quick reply i tried doing as you said using derived transformation and using replace function but it doesnot seem to capture the space as value in the replace expression in ssis.

Replace(salary," ",9999) it does not work it says that there might be a possibility of divide by zero error. what expression should i use here to replace the values

|||

first of all, since this is a char value, you should put quotes around the 9999.

Seond, I didn't say to use the REPLACE function, though I guess that would work, but you run the risk of having a valid id messed up by replacing a leading or trailing space with a 9999.

Also, if your Salary code was " " (eight spaces) then the REPLACE function will put in:

"99999999999999999999999999999999" which migh be too big for your destination field.

So, try using TRIM first to get ris of spaces, then ?: to replace blank with 9999.

Put this into the Expression column of Derived Value step:

Code Snippet

TRIM([SALARY])=="" ? "9999" : TRIM([Salary])

sql

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
HernnDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
Hernn|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
atte,
Hernn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernn
|
|

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
HernánDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
Hernán|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
--
atte,
Hernán
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribió en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernán
|
|