Archival and purging

How to implement a optimal archival and purging in MSSQL SERVER
databases?? That depends on what you mean by archival and purging. You need to find out what the business requirements are for all of your data first. After you have clearly defined that, you can map out the data that will fit that business requirement. You can only then create the optimal archive and purging processes, which will vary greatly by what those requirements are.|||Thanks for that, My requirement is quite simple.
I am working for a popular bank and I have to retain data only of the past 3
years and other data must be archived.

any clue/url links to assist me|||Create views for all tables that need to be archived/purged. Don't forget to take into account all dependencies. The views should have a WHERE clause that would filter out anything that is less than 3 years old:

...WHERE <date_field> <= convert(char(10), getdate(), 101)

Use those views to archive the data. You can use BCP if you want to keep that as text files and then zip them up, or you can have a separate database that you can populate with archived data, then back it up, and then empty it (truncate for example on all tables). Then you use the same set of views to perform your deletes from your production database.

Application roles

Can anybody tell, how to implement the application roles in SQL Server
2000.

Basically, I want to Implement the application roles in our
application, so that it can be application specific. Its' an clients
requirement from we people.

Thanks

Prashant Thakwanithakwani@.rediffmail.com (Prashant Thakwani) wrote in message news:<bf0d42bf.0403032120.588fb947@.posting.google.com>...
> Can anybody tell, how to implement the application roles in SQL Server
> 2000.
> Basically, I want to Implement the application roles in our
> application, so that it can be application specific. Its' an clients
> requirement from we people.
> Thanks
> Prashant Thakwani

1. Create the role with sp_addapprole
2. Grant permissions to the role with GRANT
3. Use sp_setapprole to activate the role - you now have the role's
permissions, not your own permissions
4. Code your application to use sp_setapprole

There are examples for these commands in Books Online - are you having
a specific problem implementing them? If so, perhaps you could give
more information about what commands you're using, what errors or
unexpected behaviour you see etc.

Simon

Application and database performance benchmarks

Hi
I need to develop and implement procedures to monitor performance of sql
server 2000/2005 databases and various applications. Is there a place where I
can look at for creating benchmarks .
Thanks
--
ontario, canadaChapter 1 of Inside Microsoft SQL Server 2004 Query Tuning and
Optimization has a section on creating a baseline for your workload,
and then monitoring the workload. That would be a reasonable place to
start to study the subject.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Apr 2008 10:10:01 -0700, db <db@.discussions.microsoft.com>
wrote:
>Hi
>I need to develop and implement procedures to monitor performance of sql
>server 2000/2005 databases and various applications. Is there a place where I
>can look at for creating benchmarks .
>Thanks

App.Config files in a Custom Database Extension Class Library

Good Morning..

We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.

In our SDE class library we have included an App.Config file where we want to store configuration settings..

Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..

Here's our app.config file:

-

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<configSections>

</configSections>

<appSettings>

<add key="eventLogName" value="FocusDPEEventLog" />

</appSettings>

<connectionStrings>

<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"

providerName="System.Data.OracleClient" />

</connectionStrings>

</configuration>

-

Here's what our Immediate window Debugger is tellin' us about our configuration settings:

-

ConfigurationManager.AppSettings

{System.Configuration.KeyValueInternalCollection}

[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}

base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}

AllKeys: {Dimensions:[0]}<-incorrect should be 1

ConfigurationManager.ConnectionStrings

Count = 1 <-ok, is one, but the wrong 1, see 3 lines down...

base {System.Configuration.ConfigurationElementCollection}: Count = 1

ConfigurationManager.ConnectionStrings[0]

{data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString

base {System.Configuration.ConfigurationElement}: {data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}

ConnectionString: "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Name: "LocalSqlServer"

ProviderName: "System.Data.SqlClient"

Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...

thanks..

Doug

Hi

I am facing the same problem. I dont know where to set the custom configuration settings. I am pretty sure it will have to be somewhere in Report Server Config files, but where?

Regarding the stuff in bold you are seeing is coming from the base asp.net machine settings.

App.Config files in a Custom Database Extension Class Library

Good Morning..

We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.

In our SDE class library we have included an App.Config file where we want to store configuration settings..

Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..

Here's our app.config file:

-

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<configSections>

</configSections>

<appSettings>

<add key="eventLogName" value="FocusDPEEventLog" />

</appSettings>

<connectionStrings>

<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"

providerName="System.Data.OracleClient" />

</connectionStrings>

</configuration>

-

Here's what our Immediate window Debugger is tellin' us about our configuration settings:

-

ConfigurationManager.AppSettings

{System.Configuration.KeyValueInternalCollection}

[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}

base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}

AllKeys: {Dimensions:[0]}<-incorrect should be 1

ConfigurationManager.ConnectionStrings

Count = 1 <-ok, is one, but the wrong 1, see 3 lines down...

base {System.Configuration.ConfigurationElementCollection}: Count = 1

ConfigurationManager.ConnectionStrings[0]

{data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString

base {System.Configuration.ConfigurationElement}: {data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}

ConnectionString: "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Name: "LocalSqlServer"

ProviderName: "System.Data.SqlClient"

Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...

thanks..

Doug

Hi

I am facing the same problem. I dont know where to set the custom configuration settings. I am pretty sure it will have to be somewhere in Report Server Config files, but where?

Regarding the stuff in bold you are seeing is coming from the base asp.net machine settings.