Applying the latest 2K5 CU Patch Scare me out of it

We're late to the party, as far as rolling out SQL2K5. I would like
to *GASP* apply the latest SP2 cumulative update to the servers(looks
like CU6). We of course have not yet encountered any of the errors
that are addressed in the CU updates. The probably-not-unique-to-us
deal is this: If we encounter any of the addressed bugs in
production, without 'proactively' applying the patch, we will get into
a lot of political hot water. Yes, MS says don't apply UNTIL you
encounter an issue, but that may not fly with the higher-ups.
I think it comes down to this for us: Are these CU patches any more
or less prone to create additional issues than a SP release?Examining how 'buggy' SQL Server service packs have been since SQL 2000 SP3,
I would say that CUs can't be any worse. :-))
There are some very significant bugs fixed in the CUs though, so I would see
if Microsoft will give you 6, or at least 5. I think CU4 (build 3186') is
the latest one you can get without going through MS.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<SqlRandall@.gmail.com> wrote in message
news:80f70278-531e-4db5-a9cf-dc0c5b5ecd81@.60g2000hsy.googlegroups.com...
> We're late to the party, as far as rolling out SQL2K5. I would like
> to *GASP* apply the latest SP2 cumulative update to the servers(looks
> like CU6). We of course have not yet encountered any of the errors
> that are addressed in the CU updates. The probably-not-unique-to-us
> deal is this: If we encounter any of the addressed bugs in
> production, without 'proactively' applying the patch, we will get into
> a lot of political hot water. Yes, MS says don't apply UNTIL you
> encounter an issue, but that may not fly with the higher-ups.
> I think it comes down to this for us: Are these CU patches any more
> or less prone to create additional issues than a SP release?|||I cuold be wrong, but I think CU4 is .3200, which is our default. Has
proven to be stable, or we have lots of really lucky customers...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt3t4ijltrb4@.corp.supernews.com...
> Examining how 'buggy' SQL Server service packs have been since SQL 2000
> SP3, I would say that CUs can't be any worse. :-))
> There are some very significant bugs fixed in the CUs though, so I would
> see if Microsoft will give you 6, or at least 5. I think CU4 (build
> 3186') is the latest one you can get without going through MS.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> <SqlRandall@.gmail.com> wrote in message
> news:80f70278-531e-4db5-a9cf-dc0c5b5ecd81@.60g2000hsy.googlegroups.com...
>> We're late to the party, as far as rolling out SQL2K5. I would like
>> to *GASP* apply the latest SP2 cumulative update to the servers(looks
>> like CU6). We of course have not yet encountered any of the errors
>> that are addressed in the CU updates. The probably-not-unique-to-us
>> deal is this: If we encounter any of the addressed bugs in
>> production, without 'proactively' applying the patch, we will get into
>> a lot of political hot water. Yes, MS says don't apply UNTIL you
>> encounter an issue, but that may not fly with the higher-ups.
>> I think it comes down to this for us: Are these CU patches any more
>> or less prone to create additional issues than a SP release?
>|||I have found the same with 3215; it is now on all of our dev, qa and
production servers. I have only installed 3228 in one development machine
so far due to time constraints.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:ue9k0zvfIHA.5996@.TK2MSFTNGP04.phx.gbl...
>I cuold be wrong, but I think CU4 is .3200, which is our default. Has
>proven to be stable, or we have lots of really lucky customers...

Apply insert duplicated key record

Hi~
When insert, just insert if it is a new item or update it if duplicated. The update operation should be summing or averaging. Anyone help?

INSTEAD OF INSERT Trigger
-Check for dupe
--If dupe, increment
--Else INSERT

Adamus

|||

Thank you for reply.

But would you give me some sample query?

|||

1. Validate if the data availabile in the Table

2. If Yes , execute the Update command

3. If No, execute the Insert command

(ex)

if Exists (Select * From SomeTable Where SomeColumn=SomeValue)

Update

Set

SomeValueColumn = SomeValueColumn + New Value

Where

SomeColumn=SomeValue

Else

Insert Into SomeTable values (...)

|||

Thank you for your help.
Here is more specific case.

CREATE TABLE mytable
(
id1 int,
id2 int,
value1 int,
PRIMARY KEY(id1,id2)
);

CREATE TRIGGER mytrigger ON mytable
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED I,mytable M
WHERE I.id1=M.id1 AND I.id2=M.id2)
UPDATE mytable SET value1 = value1 + 100
ELSE
INSERT INTO mytable SELECT id1,id2,value1 FROM INSERTED
END

BULK INSERT mytable FROM 'mydatafile_1.dat' WITH (FIRE_TRIGGERS);(1)
BULK INSERT mytable FROM 'mydatafile_2.dat' WITH (FIRE_TRIGGERS);(2)
BULK INSERT mytable FROM 'mydatafile_3.dat' WITH (FIRE_TRIGGERS);(3)

I want insert a record if new item, update it by adding new item value to the original value.

Suppose the 'mydatafile_1.dat' has all new items(1).
'mydatafile_2.dat' has some new items and some duplicated items(2)
and 'mydatafile_3.dat' has all new items(3).

Only (1) and (3) are applied correctly. (2) is updated all of record but never inserted the new items.
It seems that the validation part should be repaired.
And, I want to change 100 to the new inserted value.

Application times out while doing the updates

here's the scenario..

I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.

The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.

Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)

AJI suggest you review the execution plan of the UPDATE query. Perhaps
additional indexes or query changes will speed it up substantially. If you
need additional help, please post you table DDL (including constraints,
indexes and triggers) along with you query and sample data.

Simply encapsulating the query in a proc is unlikely to improve performance.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0408272303.752889bc@.posting.google.c om...
> here's the scenario..
> I am running a DTS to collect the summarized info from Oracle database
> into SQL server. I then have a update job which updates my
> transactional table from the summarized table.
> The update takes a very long time (~ 3 minutes)even though it has
> around 1500 rows which causes the application to timeout. I want this
> job to be done in less than a minute.
> Thoughts on improving performance. Is stored procedure a way to go?
> (I have used Isolation,row hints etc etc..nothing seems to be working)
> AJ|||Thanks Dan,

Here's the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.

I am issuing this update statement

UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().

------------

Apart from this we also have stored procs, trigger on the same
table.But the thing is the jobs runs 15 minutes past the hour and
stalls everything.

Thanks again

AJ|||AJ (aj70000@.hotmail.com) writes:
> Here's the entire structure and query
> Table A has these columns
> a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
> and few other columns
> ftq,break_offs, completes,assigned are the summarized cols. which are
> updated every hour from the summary_table.
> Summary table has these cols.
> a_id,ftq break_offs,completes,assigned.

Note here: Dan asked for the DDL. By this he means the CREATE TABLE
statements. These are useful if you want to test a query, and they
are also easier to read than a free-form list. In this case, the
cure appears simple enough anyway:

> UPDATE A SET
> break_offs = (select case when break_offs<0 then 0 else break_offs end
> break_offs from summary_table where A.ano = summary_table.ano),
> ftq=(select ftq from summary_table where A.ano=summary_table.ano),
> actually_assigned=(select member_assigned from summary_table where
> A.ano=summary_table.ano),
> qualified_completes=( select member_completes from summary_table where
> A.ano=summary_table.ano)
> WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
> summary_table.ano) and
> end_date>=getdate().

This should be faster:

UPDATE A
SET break_offs = case when s.break_offs < 0 then 0
else s.break_offs
end,
ftq = s.ftq,
actually_assigned = s.member_assigned,
qualified_completes = s.member_completes,
FROM A
JOIN summary_table s ON a.ano = s.ano
and A.end_date >= getdate()

This is uses syntax that is proprietary to MS SQL Server and Sybase,
but it is a lot more effecient, since in your version each subquery
is evaluated separately.

Permit me also to note that the last condition looks funky. getdate()
returns the current time, so if A.end_date is a date only, this condition
may not do what you expect. That is, rows where end_date = today will
not be updated.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Dan/Erland,

Is there a way to save the execution plan?..I ran trace/execution. My
update job seems to be firing at the end since I have lots of table
level triggers. The changes that Erland is suggesting is infact taking
longer than my query.

Thoughts/Comments

AJ

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||AJ Jr (aj70000@.hotmail.com) writes:
> Is there a way to save the execution plan?..I ran trace/execution. My
> update job seems to be firing at the end since I have lots of table
> level triggers. The changes that Erland is suggesting is infact taking
> longer than my query.

You can run a query from Query Analyzer and press CTRL/K to get a graphical
showplan. Or you can press CTRL/L to get an estimated plan, so that the
query is not run. Tnis is not useful if you run the entire procedure, but
mainly if you run the troublesome statement separately.

If you have a Profiler trace with the Execution Plan event, you can find
the plan for the query, select that row, and the cut and paste from the
lower pane. Normally the plan you are looking for is the one before
StmtCompleted, but if there are triggers on the table, it may not be in
this case.

Too bad that my suggestion performed worse. That was indeed a bit of a
surprise. It would be interesting to see both plans.

It would help to have the complete CREATE TABLE and CREATE INDEX statements
for the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erland's response, you may want to add unique constraints or
indexes on the ano columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0408281317.3e394ab2@.posting.google.c om...
> Thanks Dan,
>
> Here's the entire structure and query
> Table A has these columns
> a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
> and few other columns
> ftq,break_offs, completes,assigned are the summarized cols. which are
> updated every hour from the summary_table.
> Summary table has these cols.
> a_id,ftq break_offs,completes,assigned.
> I am issuing this update statement
> UPDATE A SET
> break_offs = (select case when break_offs<0 then 0 else break_offs end
> break_offs from summary_table where A.ano = summary_table.ano),
> ftq=(select ftq from summary_table where A.ano=summary_table.ano),
> actually_assigned=(select member_assigned from summary_table where
> A.ano=summary_table.ano),
> qualified_completes=( select member_completes from summary_table where
> A.ano=summary_table.ano)
> WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
> summary_table.ano) and
> end_date>=getdate().
>
> ------------
> Apart from this we also have stored procs, trigger on the same
> table.But the thing is the jobs runs 15 minutes past the hour and
> stalls everything.
> Thanks again
> AJ|||Hi Dan/Erland

I have identified a problem : It is the Update trigger which has a
cursor which checks all the records before allowing the update.

Comments/Thoughts on this one :

The update job is the scheduled job..it runs as user x (I can specify
sa or DBO).
Is there a way in SQL server that I can fire the trigger on a table
for a particular user only (Application user).

Thanks ia advance

AJ|||AJ (aj70000@.hotmail.com) writes:
> I have identified a problem : It is the Update trigger which has a
> cursor which checks all the records before allowing the update.
> Comments/Thoughts on this one :
> The update job is the scheduled job..it runs as user x (I can specify
> sa or DBO).
> Is there a way in SQL server that I can fire the trigger on a table
> for a particular user only (Application user).

No, but you can use ALTER TRIGGER to disable the trigger. But this is
a little risky if the job dies for some reason in the middle of and leaves
the trigger disabled.

A better technique is to define a temp table, call it #triggerdisable, just
before the update, and then in the trigger add:

if object_id('tempdb..#triggerdisable') IS NOT NULL
RETURN

Note: what columns the table has or what data there is does not matter. It's
the sheer existence that matter.

But of course the best solution would be rewrite the trigger to use set-
based operations!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

But I am kinda lost...By defining #triggerdisable..What do you mean?

Thanks again

AJ|||AJ (aj70000@.hotmail.com) writes:
> But I am kinda lost...By defining #triggerdisable..What do you mean?

CREATE TABLE #triggerdisable(a int NOT NULL)

The temp table serves as a process-global flag to test for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erland/Dan for all your help.

AJ

Application Security

We have a Visual Basic 5 .exe that is used to launch an application process
to import files from our application server to update the database server.
Both servers are Windows 2003 Server Standard Edition. The application laun
ches MS Access 2000 and imp
orts files into an Access database table and then connects to our database s
erver hosting SQL Server 2000 Sp3 to update a master table of users.
The VB app is launched with a local account on the app server and uses a reg
istry value to get the database connection string using SQL Authentication.
We have noticed an authentication error message (Failure Event ID 529) on t
he SQL server for the accou
nt launching the scheduled task that runs the imports. The error only occur
s on the SQL server when a load occurs on the application server. The load
occurs when users run a Web .ASP application and run end of month procedures
. It connects to the same
SQL server with the same SQL Authenticated credentials.
The import VB application does not start to authenticate unless a load is pr
esent on the server. Why would the security context change and go away from
SQL authentication' The work around so far is to use a domain account or
mirrored account on the SQL
server.
Thanks.It sounds like the VB application is using a connection string that is
requesting Windows Authentication. This is why it works if you
use a domain account or duplicate the user account and passwords. Check
the Security tab in Enterprise manager and verify that you are allowing
both SQL and Windows Authentication. Enable auditing for both failed and
successfull logins and test using both standard and Windows authentication
to validate the logging.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Application role help

Hi
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
Manoj
See sp_helprotect in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj

Application role help

Hi
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
ManojSee sp_helprotect in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj

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|||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...
> 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...
> > > 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
> >
> >
> >

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:
>

Application insert\update\delete

Hi,
How can i know how many insert\update\delete done by application daily
in sql server 2000 in particaular database
is there any cmd to check the same in sql server 2000
Regards
AbhinavSeveral options:
1) Get a copy of ApexSQL Log and use it for auditing.
2) Purchase an auditing package (or write your own) using triggers to log
that an update/insert/delete was performed. You will not be able to
actually know the exact statement this way.
3) Modify the application code to log these statements prior to or just
after executing them.
4) Write a network packet interogator and disassemble all sql server bound
packets and check for appropriate statements and log them.
Some of these are easier and more efficient than others!! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1188302835.786870.105940@.r23g2000prd.googlegroups.com...
> Hi,
> How can i know how many insert\update\delete done by application daily
> in sql server 2000 in particaular database
> is there any cmd to check the same in sql server 2000
> Regards
> Abhinav
>

Appending to a text field using UPDATE

I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement

UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0)

I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement.

Thanks,

SBRcreate procedure spU_update_table_with_text (
@.key_field_value int,
@.text text = null)
as
declare @.txtptr binary(16), @.insert_offset int
select @.txtptr = textptr(text_field),
@.insert_offset = datalength(text_field) + 1 --or 2 if a space is needed
from your_table
where key_field = @.key_field_value
updatetext your_table.text_field @.insert_offset 0 @.text
return

Appending Records + Update

Hello, I have a need to refresh a table called Employees each day from Activ
e
Directory. This table will be used in a training database. When employees
terminate, they disappear from AD. So I would like to run a query that woul
d
select all of today's employees, and selectively append to the Employees
table. New employees would be added. If the employee no longer exists in
the AD data source, I'd like to set a value from 1 to 0 in the Active column
,
indicating that the employee is no longer active but keeping the record for
historical searches. Any suggestions would be appreciated. Thanks, Pancho.Hi
Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
compare Employees
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
> Hello, I have a need to refresh a table called Employees each day from
> Active
> Directory. This table will be used in a training database. When
> employees
> terminate, they disappear from AD. So I would like to run a query that
> would
> select all of today's employees, and selectively append to the Employees
> table. New employees would be added. If the employee no longer exists in
> the AD data source, I'd like to set a value from 1 to 0 in the Active
> column,
> indicating that the employee is no longer active but keeping the record
> for
> historical searches. Any suggestions would be appreciated. Thanks,
> Pancho.|||Sorry
Should be IF EXISTS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eM2JtevUGHA.5108@.tk2msftngp13.phx.gbl...
> Hi
> Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
> compare Employees
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
>|||On Tue, 28 Mar 2006 08:50:01 -0800, Pancho wrote:
>Hello, I have a need to refresh a table called Employees each day from Acti
ve
>Directory. This table will be used in a training database. When employees
>terminate, they disappear from AD. So I would like to run a query that wou
ld
>select all of today's employees, and selectively append to the Employees
>table. New employees would be added. If the employee no longer exists in
>the AD data source, I'd like to set a value from 1 to 0 in the Active colum
n,
>indicating that the employee is no longer active but keeping the record for
>historical searches. Any suggestions would be appreciated. Thanks, Pancho.[/color
]
Hi Pancho,
UPDATE Employees
SET Active = 0
WHERE Active = 1
AND NOT EXISTS
(SELECT *
FROM AD
WHERE AD.KeyColumn = Employees.KeyColumn)
--
INSERT INTO Employees (KeyColumn, OtherColumn, Active)
SELECT KeyColumn, OtherColumn, 1
FROM AD
WHERE NOT EXISTS
(SELECT *
FROM Employees
WHERE AD.KeyColumn = Employees.KeyColumn)
Hugo Kornelis, SQL Server MVP

Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.
Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.
' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.
Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.
I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
MandarText datatype won't support UPDATEs. Check BOL
--
Thanks & Rate the Postings.
-Ravi-
"reachmandar@.gmail.com" wrote:

> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>

Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.
Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.
' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.
Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.
I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
Mandar
Text datatype won't support UPDATEs. Check BOL
Thanks & Rate the Postings.
-Ravi-
"reachmandar@.gmail.com" wrote:

> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>

Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.
Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.
' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.
Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.
I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
MandarText datatype won't support UPDATEs. Check BOL
--
Thanks & Rate the Postings.
-Ravi-
"reachmandar@.gmail.com" wrote:
> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>

Append a Date to the end of a table name

Is there a way to do so on the fly in SQL Server 2000? In other words, a field has the latest update date for the table and we wish to use this date as part of the table name. If so, please provide an example.
ddaveDo you really want to do this? It sounds like a receipe for disaster to me. The general idea would go something like:DECLARE @.fubar VARCHAR(500)
SELECT @.fubar = 'SELECT * INTO [foo' + Convert(CHAR(10), GetDate(), 121)
+ '] FROM foo'
EXECUTE (@.fubar)-PatP

anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
END
I don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/de...aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>

anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/d.../>
sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>

anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>