Archive Database

Hi
I have some questions about archive database in the real word case (now we have only 4 gig of data and start to consider archieve database for the last year)
1)Is it correct to archive just only transaction data? No need for some table such as Customers, Vendors
2)Do we use DTS for this mechanism
3)What is the real practice that you do in normal work
Any suggestion welcome because we have no experience about this. If it is not in this group, please recommend me the right group
Best Regards
James JarupanJames,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> ¦b¶l¥ó
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> ¦b¶l¥ó
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>

Archive Database

Hi
I have some questions about archive database in the real word case (now we have only 4 gig of data and start to consider archieve database for the last year).
1)Is it correct to archive just only transaction data? No need for some table such as Customers, Vendors?
2)Do we use DTS for this mechanism?
3)What is the real practice that you do in normal work?
Any suggestion welcome because we have no experience about this. If it is not in this group, please recommend me the right group.
Best Regards,
James Jarupan
James,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison
|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon
|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>
|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon
|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>
sql

Archive Database

Hi
I have some questions about archive database in the real word case (now we h
ave only 4 gig of data and start to consider archieve database for the last
year).
1)Is it correct to archive just only transaction data? No need for some tabl
e such as Customers, Vendors?
2)Do we use DTS for this mechanism?
3)What is the real practice that you do in normal work?
Any suggestion welcome because we have no experience about this. If it is no
t in this group, please recommend me the right group.
Best Regards,
James JarupanJames,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>

Applying CASE in Stored Procedures

I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:

ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)

AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.

Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys

But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.

I'm using SQL Server 2000, connecting with Visual Studio.NET 2003

Moving to the Transact-SQL forum, which is best suited to this type of questions.|||

What is the value of @.keys?

If you hard-code the values do you get results?

|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Well, the CASE in the WHERE does works

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.

|||

This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:

http://www.sommarskog.se/dyn-search.html

It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.

All of this is nicely covered in that article.

|||

Hi,

In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..

Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

Appending one datatable to another ?

Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?

You can use UNION ALL to join two tables all records with same structrure( watch for the identity issue). Like

Select col1, col2 From table1

UNION ALL

Select col1, col2 From table2

|||

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();

dt1.Merge(dt2);

This will append dt2 to dt1