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 log and backuo operations

I'm using Log Shipping in order to update a backup database on SQL Server
2000. I scheduled the transaction log backup every 5 minutes. The problem is
that SQL Server logs an event into the Application Log every time backup is
completed so the Application Log becomes full very quickly.
Is there any way to disable this behaviour ?
Thank you for any suggestions.
Luigi
Hi,
In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
task. Double click above that and
select "Notifications"
In that screen "UNCHECK" the Write to windows Application log and clieck
Apply and OK.
Thanks
Hari
MCDBA
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> I'm using Log Shipping in order to update a backup database on SQL Server
> 2000. I scheduled the transaction log backup every 5 minutes. The problem
is
> that SQL Server logs an event into the Application Log every time backup
is
> completed so the Application Log becomes full very quickly.
> Is there any way to disable this behaviour ?
> Thank you for any suggestions.
> Luigi
|||Thank you Hari for your suggestion.
I tried it but it doesn't solve my problem. The problem isn't about the
job's result : the job is set up in order to write to tha application log in
case of error only. It seems instead that it is the BACKUP command that logs
the event: when I launch my job, I find in the application log the same log
that I find when I backup database by Enterprise Maganer's facilities.
Bye
Luigi
"Hari Prasad" wrote:

> Hi,
> In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
> task. Double click above that and
> select "Notifications"
> In that screen "UNCHECK" the Write to windows Application log and clieck
> Apply and OK.
> Thanks
> Hari
> MCDBA
>
> "Luigi" <Luigi@.discussions.microsoft.com> wrote in message
> news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> is
> is
>
>
|||SQL Server always write these messages to your logs. Even if you define in sysmessages that they shouldn't be
written. I have suggested to MS that SQL Server honors the setting in sysmessages, we'll see if any such
change appears in the future. If you want to communicate such a wish, you can use sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:07C296BE-FD47-46D1-94A6-669FD6FB9C67@.microsoft.com...[vbcol=seagreen]
> Thank you Hari for your suggestion.
> I tried it but it doesn't solve my problem. The problem isn't about the
> job's result : the job is set up in order to write to tha application log in
> case of error only. It seems instead that it is the BACKUP command that logs
> the event: when I launch my job, I find in the application log the same log
> that I find when I backup database by Enterprise Maganer's facilities.
> Bye
> Luigi
>
> "Hari Prasad" wrote:

Application log and backuo operations

I'm using Log Shipping in order to update a backup database on SQL Server
2000. I scheduled the transaction log backup every 5 minutes. The problem is
that SQL Server logs an event into the Application Log every time backup is
completed so the Application Log becomes full very quickly.
Is there any way to disable this behaviour '
Thank you for any suggestions.
LuigiHi,
In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
task. Double click above that and
select "Notifications"
In that screen "UNCHECK" the Write to windows Application log and clieck
Apply and OK.
Thanks
Hari
MCDBA
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> I'm using Log Shipping in order to update a backup database on SQL Server
> 2000. I scheduled the transaction log backup every 5 minutes. The problem
is
> that SQL Server logs an event into the Application Log every time backup
is
> completed so the Application Log becomes full very quickly.
> Is there any way to disable this behaviour '
> Thank you for any suggestions.
> Luigi|||Thank you Hari for your suggestion.
I tried it but it doesn't solve my problem. The problem isn't about the
job's result : the job is set up in order to write to tha application log in
case of error only. It seems instead that it is the BACKUP command that logs
the event: when I launch my job, I find in the application log the same log
that I find when I backup database by Enterprise Maganer's facilities.
Bye
Luigi
"Hari Prasad" wrote:

> Hi,
> In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshippin
g
> task. Double click above that and
> select "Notifications"
> In that screen "UNCHECK" the Write to windows Application log and clieck
> Apply and OK.
> Thanks
> Hari
> MCDBA
>
> "Luigi" <Luigi@.discussions.microsoft.com> wrote in message
> news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> is
> is
>
>|||SQL Server always write these messages to your logs. Even if you define in s
ysmessages that they shouldn't be
written. I have suggested to MS that SQL Server honors the setting in sysmes
sages, we'll see if any such
change appears in the future. If you want to communicate such a wish, you ca
n use sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:07C296BE-FD47-46D1-94A6-669FD6FB9C67@.microsoft.com...[vbcol=seagreen]
> Thank you Hari for your suggestion.
> I tried it but it doesn't solve my problem. The problem isn't about the
> job's result : the job is set up in order to write to tha application log
in
> case of error only. It seems instead that it is the BACKUP command that lo
gs
> the event: when I launch my job, I find in the application log the same lo
g
> that I find when I backup database by Enterprise Maganer's facilities.
> Bye
> Luigi
>
> "Hari Prasad" wrote:
>

Append data to Sql Server 2000 table from DB2 table (linked server)

Hello,
I am trying to append new records from a DB2 database to a Sql Server
2000 database. Basically table A (Sql Server) has been uploaded with a
dump from table B (DB2) and from know on I would like to append any new
records added to table B to table A. I am running a DTS package to do
this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
primary keys.
Any ideas.
Thanks for your help
The usual approach is to load the data into a staging table, which
matches the target table in layout but is truncated before the load.
Then you can INSERT to the target table from the staging table where
NOT EXISTS the key. You also have the option of doing an UPDATE to
the target table from the staging table, which would be done before
the INSERT.
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 10:57:21 -0800, mrdata1701@.gmail.com wrote:

>Hello,
>I am trying to append new records from a DB2 database to a Sql Server
>2000 database. Basically table A (Sql Server) has been uploaded with a
>dump from table B (DB2) and from know on I would like to append any new
>records added to table B to table A. I am running a DTS package to do
>this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
>NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
>primary keys.
>Any ideas.
>Thanks for your help
|||Thanks Roy! The thing is the table has over 380K records so it would
take a while to update and this is something that will be run daily so
it could end up using more resources that I would like.
On Jan 15, 2:16 pm, Roy Harvey <roy_har...@.snet.net> wrote:[vbcol=seagreen]
> The usual approach is to load the data into a staging table, which
> matches the target table in layout but is truncated before the load.
> Then you can INSERT to the target table from the staging table where
> NOT EXISTS the key. You also have the option of doing an UPDATE to
> the target table from the staging table, which would be done before
> the INSERT.
> Roy Harvey
> Beacon Falls, CT
> On 15 Jan 2007 10:57:21 -0800, mrdata1...@.gmail.com wrote:
>
>
>
|||On 15 Jan 2007 11:26:59 -0800, mrdata1701@.gmail.com wrote:

>Thanks Roy! The thing is the table has over 380K records so it would
>take a while to update and this is something that will be run daily so
>it could end up using more resources that I would like.
The ideal would be to have the DB2 data marked in some indicating
which rows had been updated. Lacking that your alternatives are to
use a staging table as already described, or process today's DB2
extract against yesterday's DB2 extract in an old fashioned match-file
program written in whatever language you prefer. That assumes that
the files are already sorted on the key, of course. Personally I
would try the staging table approach first.
Roy Harvey
Beacon Falls, CT

Append data to Sql Server 2000 table from DB2 table (linked server)

Hello,
I am trying to append new records from a DB2 database to a Sql Server
2000 database. Basically table A (Sql Server) has been uploaded with a
dump from table B (DB2) and from know on I would like to append any new
records added to table B to table A. I am running a DTS package to do
this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
primary keys.
Any ideas.
Thanks for your helpThe usual approach is to load the data into a staging table, which
matches the target table in layout but is truncated before the load.
Then you can INSERT to the target table from the staging table where
NOT EXISTS the key. You also have the option of doing an UPDATE to
the target table from the staging table, which would be done before
the INSERT.
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 10:57:21 -0800, mrdata1701@.gmail.com wrote:

>Hello,
>I am trying to append new records from a DB2 database to a Sql Server
>2000 database. Basically table A (Sql Server) has been uploaded with a
>dump from table B (DB2) and from know on I would like to append any new
>records added to table B to table A. I am running a DTS package to do
>this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
>NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
>primary keys.
>Any ideas.
>Thanks for your help|||Thanks Roy! The thing is the table has over 380K records so it would
take a while to update and this is something that will be run daily so
it could end up using more resources that I would like.
On Jan 15, 2:16 pm, Roy Harvey <roy_har...@.snet.net> wrote:[vbcol=seagreen]
> The usual approach is to load the data into a staging table, which
> matches the target table in layout but is truncated before the load.
> Then you can INSERT to the target table from the staging table where
> NOT EXISTS the key. You also have the option of doing an UPDATE to
> the target table from the staging table, which would be done before
> the INSERT.
> Roy Harvey
> Beacon Falls, CT
> On 15 Jan 2007 10:57:21 -0800, mrdata1...@.gmail.com wrote:
>
>
>
>
>|||On 15 Jan 2007 11:26:59 -0800, mrdata1701@.gmail.com wrote:

>Thanks Roy! The thing is the table has over 380K records so it would
>take a while to update and this is something that will be run daily so
>it could end up using more resources that I would like.
The ideal would be to have the DB2 data marked in some indicating
which rows had been updated. Lacking that your alternatives are to
use a staging table as already described, or process today's DB2
extract against yesterday's DB2 extract in an old fashioned match-file
program written in whatever language you prefer. That assumes that
the files are already sorted on the key, of course. Personally I
would try the staging table approach first.
Roy Harvey
Beacon Falls, CT