Alerts not firing

I have an MSDE 2000 installation on Windows 2000 Pro.
Can anyone give me any ideas why when I execute this command
RAISERROR('Test Error', 16, 1)
The corresponding severity level 16 Alert isn't fired?

Thanks
Phill
hi Phill,
Phill wrote:
> I have an MSDE 2000 installation on Windows 2000 Pro.
> Can anyone give me any ideas why when I execute this command
> RAISERROR('Test Error', 16, 1)
> The corresponding severity level 16 Alert isn't fired?
please modify your code as
RAISERROR('Test Error', 16, 1) WITH LOG
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ok, that still doesn't fire the alert though.
"Andrea Montanari" wrote:

> hi Phill,
> Phill wrote:
> please modify your code as
> RAISERROR('Test Error', 16, 1) WITH LOG
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Phill,
Phill wrote:
> Ok, that still doesn't fire the alert though.
>
is th SQL Server Agent running?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Yes the Agent is running.
It has 3 scheduled jobs that run every night. One of the jobs performs a
DBCC DBREINDEX as one one of 6 steps. This step fails because the database
size has reached 2gb. The other 2 jobs run without problems.
However as far as the server is concered the alerts didn't happen.
"Andrea Montanari" wrote:

> hi Phill,
> Phill wrote:
> is th SQL Server Agent running?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Hello,
For a stored procedure in vb6, I know that the first line of the stored
procedure must be
SET NOCOUNT ON
if there is more than one select. If you don't do it, vb 6 can't receive
the error in a stored procedure.
Perhaps you can try with that.
Marc Allard
Allcomp
Phill wrote:[vbcol=seagreen]
> Yes the Agent is running.
> It has 3 scheduled jobs that run every night. One of the jobs performs a
> DBCC DBREINDEX as one one of 6 steps. This step fails because the database
> size has reached 2gb. The other 2 jobs run without problems.
> However as far as the server is concered the alerts didn't happen.
>
> "Andrea Montanari" wrote:
>

Alert script

can anyone help me with a script that the help desk can
run or execute that will page me if there is a problem
with one of the databases that I support. We are trying to
use the help desk as the middle man between the DBA and
the users. Instead of the users calling the DBA direct
they will have to open a ticket with the help desk which
will automatically page the DBA on call.
Thanks for any help.Have you looked at Alerts in SQL Server. These can mail you (If you setup mail) when certain things go wrong. As for user problems. You could have a ticket type of DB Problem and when the database receives that after being inputted by your help
desk it would trigger a mail. This could be through SQL Mail or perhaps your Helpdesk software has this capability.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.orgsql

Alert Response Execute Job

I would like to have an alert execute a job as a response, but I also need to
have the error text passed into the job.
Is this possible or is there another way?Thom
>I would like to have an alert execute a job as a response,
Can you elaborate a little bit?
> have the error text passed into the job.
Yes , you have an option to define an output file to get out info.
(Step-Advanced--Output file)
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:1E82BB52-6536-4D37-965D-3896A3CC5C45@.microsoft.com...
>I would like to have an alert execute a job as a response, but I also need
>to
> have the error text passed into the job.
> Is this possible or is there another way?
>

Alert Response Execute Job

I would like to have an alert execute a job as a response, but I also need to
have the error text passed into the job.
Is this possible or is there another way?
Thom
>I would like to have an alert execute a job as a response,
Can you elaborate a little bit?

> have the error text passed into the job.
Yes , you have an option to define an output file to get out info.
(Step-Advanced--Output file)
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:1E82BB52-6536-4D37-965D-3896A3CC5C45@.microsoft.com...
>I would like to have an alert execute a job as a response, but I also need
>to
> have the error text passed into the job.
> Is this possible or is there another way?
>

Alert Response Execute Job

I would like to have an alert execute a job as a response, but I also need t
o
have the error text passed into the job.
Is this possible or is there another way?Thom
>I would like to have an alert execute a job as a response,
Can you elaborate a little bit?

> have the error text passed into the job.
Yes , you have an option to define an output file to get out info.
(Step-Advanced--Output file)
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:1E82BB52-6536-4D37-965D-3896A3CC5C45@.microsoft.com...
>I would like to have an alert execute a job as a response, but I also need
>to
> have the error text passed into the job.
> Is this possible or is there another way?
>

Alert gets executed, but not its job. Am I doing something wrong?

I have an Alert that I verify gets activated (looked at its history under the General Tab) but it never executes the Execute Job under the Response tab.

Here is what I did:

1) Checked the Execute Job box
2) Created a job
a) Gave it a name
b) Gave it an owner
c) Left the category as Uncategorized
d) Under the Steps tab added a new step
i) Gave the step a name
ii) Selected Operating System Command (CmdExec) as the type
iii) In the Command box typed the following

cd C:\Blat
blat StandardMessage.txt -to Test.Test@.MyURL.com -s "TEST"
cd C:\

When I copy and paste that EXACT message (cd...blat...cd...) on the command line it works perfectly so it looks like the job isn't getting executed at all.

I also tried to set up an output file (under the Advanted Tab...Operating System Command. That file never gets created either! That makes me REALLY sure the job isn't getting executed.

So, if my Alert gets executed as verified by the history, why isn't my job being kicked off?

THANKS!what account the service is running under? probably system account. you need to have a domain account with minimal rights.|||Originally posted by ms_sql_dba
what account the service is running under? probably system account. you need to have a domain account with minimal rights.

You have lost me here.

My Alert executes just fine. I don't see any way to specify any "account" for the job that gets kicked off to run under.

Where should I look to specify the "account the service is running under"?

Thanks.|||Originally posted by darrinps
You have lost me here.

My Alert executes just fine. I don't see any way to specify any "account" for the job that gets kicked off to run under.

Where should I look to specify the "account the service is running under"?

Thanks.

I THINK what is happening is that the command I want to execute is local to my PC and the exec is attempting ro tun against the local server box.

What I need to do is either install the software there, or better yet (for testing) somehow redirect it to my box.|||try copying all the command line steps in a .bat file and then run that bat file from your scheduler.

Aid with store procedure

Hello, I have a problem, I want to execute in a single transaction three insert in store procedure, as it could implement. Thanks for its attentione.g.

create proc usp
as
begin tran
insert tb1 values(...)
if @.@.error<>0 goto rollbk
insert tb2 values(...)
if @.@.error<>0 goto rollbk
insert tb3 values(...)
if @.@.error=0
begin
commit
goto done
end

rollbk:
rollback

done:
return

GO

Agent Jobs

hello,
i am trying to create a job to execute an exe. i would like to kick off the
job through a trigger. however i am running into some prblems.
i created a job - myjob.
i created a step - step1
the step1 has operating system command: c:\myexe.exe
then i create a trigger on my table
CREATE TRIGGER MyTrigger1
ON testtable FOR INSERT
AS
EXEC sp_start_job @.job_name = 'myjob'
however when i add a row to the table i get this error:
[microsoft][odbc sql server driver][sql server] could not find stored
procedure 'sp_start_job'
this seems like a very basic problem.
why does it think i have a job sp_start_job. i thought sp_start_job just
kicks off the job?
thanks for any help
Try,
exec msdb.dbo.sp_start_job @.job_name = 'myjob'
AMB
"brian" wrote:

> hello,
> i am trying to create a job to execute an exe. i would like to kick off the
> job through a trigger. however i am running into some prblems.
> i created a job - myjob.
> i created a step - step1
> the step1 has operating system command: c:\myexe.exe
> then i create a trigger on my table
> CREATE TRIGGER MyTrigger1
> ON testtable FOR INSERT
> AS
> EXEC sp_start_job @.job_name = 'myjob'
> however when i add a row to the table i get this error:
> [microsoft][odbc sql server driver][sql server] could not find stored
> procedure 'sp_start_job'
> this seems like a very basic problem.
> why does it think i have a job sp_start_job. i thought sp_start_job just
> kicks off the job?
> thanks for any help
>
>
>
|||thanks. that got me a little further. but i see this now.
when i add my row i get the message:
[microsoft][odbc sql server driver][sql server]SQLServerAgent is not
currently running so it cannot be notified of this action.
which is simular to if i go to the job ad click start. i get this message:
[microsoft][odbc sql server driver][sql server]SQLServerAgent is not running
so it cannot be notified of this action.
is there something i have to set on the job to make it runnable? either
from sp_start_job or from "start job"?
thanks for the help
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...[vbcol=seagreen]
> Try,
> exec msdb.dbo.sp_start_job @.job_name = 'myjob'
>
> AMB
> "brian" wrote:
|||i think i have it.
you need to start the SQL Agent service.
"brian" <brian@.nospam.com> wrote in message
news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
> thanks. that got me a little further. but i see this now.
> when i add my row i get the message:
> [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
> currently running so it cannot be notified of this action.
> which is simular to if i go to the job ad click start. i get this
> message:
> [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
> running so it cannot be notified of this action.
> is there something i have to set on the job to make it runnable? either
> from sp_start_job or from "start job"?
> thanks for the help
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...
>
|||You got it.
AMB
"brian" wrote:

> i think i have it.
> you need to start the SQL Agent service.
>
> "brian" <brian@.nospam.com> wrote in message
> news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
>
>
|||Starting a job from a trigger? Sounds painful from a couple of stances.
What are you trying to accomplish?
Performance on your inserts is going to be horrible.
Only one copy of the job can be running at any time. If multiple insert
batches occur at nearly the same time one will fail.
The trigger will fire once for each insert batch not each row. This may or
may not be your intention.
"brian" <brian@.nospam.com> wrote in message
news:e45VUnpzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> hello,
> i am trying to create a job to execute an exe. i would like to kick off
> the job through a trigger. however i am running into some prblems.
> i created a job - myjob.
> i created a step - step1
> the step1 has operating system command: c:\myexe.exe
> then i create a trigger on my table
> CREATE TRIGGER MyTrigger1
> ON testtable FOR INSERT
> AS
> EXEC sp_start_job @.job_name = 'myjob'
> however when i add a row to the table i get this error:
> [microsoft][odbc sql server driver][sql server] could not find stored
> procedure 'sp_start_job'
> this seems like a very basic problem.
> why does it think i have a job sp_start_job. i thought sp_start_job just
> kicks off the job?
> thanks for any help
>
>
|||And in addition, you need to execute sp_start_job in msdb:
EXEC msdb.dbo.sp_start_job ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3BAABBB2-27C5-4BB4-BEC3-CCE48CF6D6C7@.microsoft.com...[vbcol=seagreen]
> You got it.
>
> AMB
> "brian" wrote:

Agent Jobs

hello,
i am trying to create a job to execute an exe. i would like to kick off the
job through a trigger. however i am running into some prblems.
i created a job - myjob.
i created a step - step1
the step1 has operating system command: c:\myexe.exe
then i create a trigger on my table
CREATE TRIGGER MyTrigger1
ON testtable FOR INSERT
AS
EXEC sp_start_job @.job_name = 'myjob'
however when i add a row to the table i get this error:
[microsoft][odbc sql server driver][sql server] could not find s
tored
procedure 'sp_start_job'
this seems like a very basic problem.
why does it think i have a job sp_start_job. i thought sp_start_job just
kicks off the job?
thanks for any helpTry,
exec msdb.dbo.sp_start_job @.job_name = 'myjob'
AMB
"brian" wrote:

> hello,
> i am trying to create a job to execute an exe. i would like to kick off t
he
> job through a trigger. however i am running into some prblems.
> i created a job - myjob.
> i created a step - step1
> the step1 has operating system command: c:\myexe.exe
> then i create a trigger on my table
> CREATE TRIGGER MyTrigger1
> ON testtable FOR INSERT
> AS
> EXEC sp_start_job @.job_name = 'myjob'
> however when i add a row to the table i get this error:
> [microsoft][odbc sql server driver][sql server] could not find
stored
> procedure 'sp_start_job'
> this seems like a very basic problem.
> why does it think i have a job sp_start_job. i thought sp_start_job just
> kicks off the job?
> thanks for any help
>
>
>|||thanks. that got me a little further. but i see this now.
when i add my row i get the message:
[microsoft][odbc sql server driver][sql server]SQLServerAgent is
not
currently running so it cannot be notified of this action.
which is simular to if i go to the job ad click start. i get this message:
[microsoft][odbc sql server driver][sql server]SQLServerAgent is
not running
so it cannot be notified of this action.
is there something i have to set on the job to make it runnable? either
from sp_start_job or from "start job"?
thanks for the help
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...[vbcol=seagreen]
> Try,
> exec msdb.dbo.sp_start_job @.job_name = 'myjob'
>
> AMB
> "brian" wrote:
>|||i think i have it.
you need to start the SQL Agent service.
"brian" <brian@.nospam.com> wrote in message
news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
> thanks. that got me a little further. but i see this now.
> when i add my row i get the message:
> [microsoft][odbc sql server driver][sql server]SQLServerAgent
is not
> currently running so it cannot be notified of this action.
> which is simular to if i go to the job ad click start. i get this
> message:
> [microsoft][odbc sql server driver][sql server]SQLServerAgent
is not
> running so it cannot be notified of this action.
> is there something i have to set on the job to make it runnable? either
> from sp_start_job or from "start job"?
> thanks for the help
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...
>|||You got it.
AMB
"brian" wrote:

> i think i have it.
> you need to start the SQL Agent service.
>
> "brian" <brian@.nospam.com> wrote in message
> news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
>
>|||Starting a job from a trigger? Sounds painful from a couple of stances.
What are you trying to accomplish?
Performance on your inserts is going to be horrible.
Only one copy of the job can be running at any time. If multiple insert
batches occur at nearly the same time one will fail.
The trigger will fire once for each insert batch not each row. This may or
may not be your intention.
"brian" <brian@.nospam.com> wrote in message
news:e45VUnpzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> hello,
> i am trying to create a job to execute an exe. i would like to kick off
> the job through a trigger. however i am running into some prblems.
> i created a job - myjob.
> i created a step - step1
> the step1 has operating system command: c:\myexe.exe
> then i create a trigger on my table
> CREATE TRIGGER MyTrigger1
> ON testtable FOR INSERT
> AS
> EXEC sp_start_job @.job_name = 'myjob'
> however when i add a row to the table i get this error:
> [microsoft][odbc sql server driver][sql server] could not find
stored
> procedure 'sp_start_job'
> this seems like a very basic problem.
> why does it think i have a job sp_start_job. i thought sp_start_job just
> kicks off the job?
> thanks for any help
>
>|||And in addition, you need to execute sp_start_job in msdb:
EXEC msdb.dbo.sp_start_job ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3BAABBB2-27C5-4BB4-BEC3-CCE48CF6D6C7@.microsoft.com...[vbcol=seagreen]
> You got it.
>
> AMB
> "brian" wrote:
>

Agent Jobs

hello,
i am trying to create a job to execute an exe. i would like to kick off the
job through a trigger. however i am running into some prblems.
i created a job - myjob.
i created a step - step1
the step1 has operating system command: c:\myexe.exe
then i create a trigger on my table
CREATE TRIGGER MyTrigger1
ON testtable FOR INSERT
AS
EXEC sp_start_job @.job_name = 'myjob'
however when i add a row to the table i get this error:
[microsoft][odbc sql server driver][sql server] could not find stored
procedure 'sp_start_job'
this seems like a very basic problem.
why does it think i have a job sp_start_job. i thought sp_start_job just
kicks off the job?
thanks for any helpTry,
exec msdb.dbo.sp_start_job @.job_name = 'myjob'
AMB
"brian" wrote:
> hello,
> i am trying to create a job to execute an exe. i would like to kick off the
> job through a trigger. however i am running into some prblems.
> i created a job - myjob.
> i created a step - step1
> the step1 has operating system command: c:\myexe.exe
> then i create a trigger on my table
> CREATE TRIGGER MyTrigger1
> ON testtable FOR INSERT
> AS
> EXEC sp_start_job @.job_name = 'myjob'
> however when i add a row to the table i get this error:
> [microsoft][odbc sql server driver][sql server] could not find stored
> procedure 'sp_start_job'
> this seems like a very basic problem.
> why does it think i have a job sp_start_job. i thought sp_start_job just
> kicks off the job?
> thanks for any help
>
>
>|||thanks. that got me a little further. but i see this now.
when i add my row i get the message:
[microsoft][odbc sql server driver][sql server]SQLServerAgent is not
currently running so it cannot be notified of this action.
which is simular to if i go to the job ad click start. i get this message:
[microsoft][odbc sql server driver][sql server]SQLServerAgent is not running
so it cannot be notified of this action.
is there something i have to set on the job to make it runnable? either
from sp_start_job or from "start job"?
thanks for the help
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...
> Try,
> exec msdb.dbo.sp_start_job @.job_name = 'myjob'
>
> AMB
> "brian" wrote:
>> hello,
>> i am trying to create a job to execute an exe. i would like to kick off
>> the
>> job through a trigger. however i am running into some prblems.
>> i created a job - myjob.
>> i created a step - step1
>> the step1 has operating system command: c:\myexe.exe
>> then i create a trigger on my table
>> CREATE TRIGGER MyTrigger1
>> ON testtable FOR INSERT
>> AS
>> EXEC sp_start_job @.job_name = 'myjob'
>> however when i add a row to the table i get this error:
>> [microsoft][odbc sql server driver][sql server] could not find stored
>> procedure 'sp_start_job'
>> this seems like a very basic problem.
>> why does it think i have a job sp_start_job. i thought sp_start_job just
>> kicks off the job?
>> thanks for any help
>>
>>|||i think i have it.
you need to start the SQL Agent service.
"brian" <brian@.nospam.com> wrote in message
news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
> thanks. that got me a little further. but i see this now.
> when i add my row i get the message:
> [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
> currently running so it cannot be notified of this action.
> which is simular to if i go to the job ad click start. i get this
> message:
> [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
> running so it cannot be notified of this action.
> is there something i have to set on the job to make it runnable? either
> from sp_start_job or from "start job"?
> thanks for the help
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...
>> Try,
>> exec msdb.dbo.sp_start_job @.job_name = 'myjob'
>>
>> AMB
>> "brian" wrote:
>> hello,
>> i am trying to create a job to execute an exe. i would like to kick off
>> the
>> job through a trigger. however i am running into some prblems.
>> i created a job - myjob.
>> i created a step - step1
>> the step1 has operating system command: c:\myexe.exe
>> then i create a trigger on my table
>> CREATE TRIGGER MyTrigger1
>> ON testtable FOR INSERT
>> AS
>> EXEC sp_start_job @.job_name = 'myjob'
>> however when i add a row to the table i get this error:
>> [microsoft][odbc sql server driver][sql server] could not find stored
>> procedure 'sp_start_job'
>> this seems like a very basic problem.
>> why does it think i have a job sp_start_job. i thought sp_start_job
>> just
>> kicks off the job?
>> thanks for any help
>>
>>
>|||You got it.
AMB
"brian" wrote:
> i think i have it.
> you need to start the SQL Agent service.
>
> "brian" <brian@.nospam.com> wrote in message
> news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
> > thanks. that got me a little further. but i see this now.
> > when i add my row i get the message:
> > [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
> > currently running so it cannot be notified of this action.
> >
> > which is simular to if i go to the job ad click start. i get this
> > message:
> > [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
> > running so it cannot be notified of this action.
> >
> > is there something i have to set on the job to make it runnable? either
> > from sp_start_job or from "start job"?
> >
> > thanks for the help
> >
> >
> >
> > "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> > message news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...
> >> Try,
> >>
> >> exec msdb.dbo.sp_start_job @.job_name = 'myjob'
> >>
> >>
> >> AMB
> >>
> >> "brian" wrote:
> >>
> >> hello,
> >> i am trying to create a job to execute an exe. i would like to kick off
> >> the
> >> job through a trigger. however i am running into some prblems.
> >>
> >> i created a job - myjob.
> >> i created a step - step1
> >> the step1 has operating system command: c:\myexe.exe
> >>
> >> then i create a trigger on my table
> >> CREATE TRIGGER MyTrigger1
> >> ON testtable FOR INSERT
> >> AS
> >> EXEC sp_start_job @.job_name = 'myjob'
> >>
> >> however when i add a row to the table i get this error:
> >> [microsoft][odbc sql server driver][sql server] could not find stored
> >> procedure 'sp_start_job'
> >>
> >> this seems like a very basic problem.
> >> why does it think i have a job sp_start_job. i thought sp_start_job
> >> just
> >> kicks off the job?
> >>
> >> thanks for any help
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>|||Starting a job from a trigger? Sounds painful from a couple of stances.
What are you trying to accomplish?
Performance on your inserts is going to be horrible.
Only one copy of the job can be running at any time. If multiple insert
batches occur at nearly the same time one will fail.
The trigger will fire once for each insert batch not each row. This may or
may not be your intention.
"brian" <brian@.nospam.com> wrote in message
news:e45VUnpzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> hello,
> i am trying to create a job to execute an exe. i would like to kick off
> the job through a trigger. however i am running into some prblems.
> i created a job - myjob.
> i created a step - step1
> the step1 has operating system command: c:\myexe.exe
> then i create a trigger on my table
> CREATE TRIGGER MyTrigger1
> ON testtable FOR INSERT
> AS
> EXEC sp_start_job @.job_name = 'myjob'
> however when i add a row to the table i get this error:
> [microsoft][odbc sql server driver][sql server] could not find stored
> procedure 'sp_start_job'
> this seems like a very basic problem.
> why does it think i have a job sp_start_job. i thought sp_start_job just
> kicks off the job?
> thanks for any help
>
>|||And in addition, you need to execute sp_start_job in msdb:
EXEC msdb.dbo.sp_start_job ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3BAABBB2-27C5-4BB4-BEC3-CCE48CF6D6C7@.microsoft.com...
> You got it.
>
> AMB
> "brian" wrote:
>> i think i have it.
>> you need to start the SQL Agent service.
>>
>> "brian" <brian@.nospam.com> wrote in message
>> news:%23wHFt3pzFHA.464@.TK2MSFTNGP15.phx.gbl...
>> > thanks. that got me a little further. but i see this now.
>> > when i add my row i get the message:
>> > [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
>> > currently running so it cannot be notified of this action.
>> >
>> > which is simular to if i go to the job ad click start. i get this
>> > message:
>> > [microsoft][odbc sql server driver][sql server]SQLServerAgent is not
>> > running so it cannot be notified of this action.
>> >
>> > is there something i have to set on the job to make it runnable? either
>> > from sp_start_job or from "start job"?
>> >
>> > thanks for the help
>> >
>> >
>> >
>> > "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> > message news:D628A23E-F59E-4B27-B015-38E431C080B2@.microsoft.com...
>> >> Try,
>> >>
>> >> exec msdb.dbo.sp_start_job @.job_name = 'myjob'
>> >>
>> >>
>> >> AMB
>> >>
>> >> "brian" wrote:
>> >>
>> >> hello,
>> >> i am trying to create a job to execute an exe. i would like to kick off
>> >> the
>> >> job through a trigger. however i am running into some prblems.
>> >>
>> >> i created a job - myjob.
>> >> i created a step - step1
>> >> the step1 has operating system command: c:\myexe.exe
>> >>
>> >> then i create a trigger on my table
>> >> CREATE TRIGGER MyTrigger1
>> >> ON testtable FOR INSERT
>> >> AS
>> >> EXEC sp_start_job @.job_name = 'myjob'
>> >>
>> >> however when i add a row to the table i get this error:
>> >> [microsoft][odbc sql server driver][sql server] could not find stored
>> >> procedure 'sp_start_job'
>> >>
>> >> this seems like a very basic problem.
>> >> why does it think i have a job sp_start_job. i thought sp_start_job
>> >> just
>> >> kicks off the job?
>> >>
>> >> thanks for any help
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>

Again Timeout errors

There is this sp which is used in the webpage, it takes 3 seconds to execute from the
query analyser, but when it is called from the asp page it ends up in
"Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired "
I know it is an General error and there can be many reasons to it,
but on setting a profiler I found out that the sp is called only when the page timeouts,
meaning for some time there is no activity say for 5 -8 seconds and after that the sp is called and
the page timeouts...
There is no rendering/formatting on the page. what can be the possible cause of it??Whada heck...
the sp just needed a recompile...
can anyone explain why?
no base tables were modified, tables are referenced acrossed two different
databases, WITH (NOLOCK) query hint is used in the sp.|||I've got the same prob. but the asp pages use dynamic SQL rather than SPs. When a user enters credentials into login page and press GO, it takes 3-5 minutes for welcome page to get displayed!!! :mad: When i run trance on the server there's no execution (only one query executes and the second comes after several seconds) being made nor there's any processor usage; what the page is spinning and doing. Is the problem with IIS. ?
Any body had the same experience? How to square this.

Howdy.

After copying a record with an SP all textfields has max lenght

Dear All,

After copying a record using an Stored procedure all textfields (nvarchar)
has max lenght !

See VBA-code and SP below

VBA-code tos execute SP

Dim objcommand As ADODB.Command
Dim intReturnParam As Long
Set objcommand = New ADODB.Command
With objcommand
.CommandType = adCmdStoredProc
.CommandText = "FB_CopyOrder"
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("ORD_ID", adInteger,
adParamInput, , Me.ORD_ID)
.Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
adParamInput, , Me.ORD_P_ID)
.Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
adParamInput, , Me.ORD_PHTI_ID)
.Parameters.Append .CreateParameter("ORD_NAME", adWChar,
adParamInput, 50, Me.ORD_NAME)
.Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
adParamInput, 50, Me.ORD_CLIENT_CODE)
.Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
.Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
.ActiveConnection = CurrentProject.Connection
.Execute
intReturnParam = .Parameters(0).Value
End With

Stored procedure

Alter Procedure FB_CopyOrder

--List of parameters to be added to the parametercollection of the
ADO-commandobject before executing the command

@.SourceOrderID int,

@.ORD_P_ID int,

@.ORD_PHTI_ID int,

@.ORD_NAME nvarchar(50),

@.ORD_CLIENT_CODE nvarchar(50),

@.ORD_INTERNAL_NOTE nvarchar(1024),

@.ORD_REQUESTED_DELIVERY_DATE datetime,

@.ORD_REQUESTED_QUANTITY int,

@.ORD_AVAILABLE_QUANTITY int

as

declare @.err int

declare @.NewOrderid int

begin tran

-- add new order values = command-parameters

insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)

values (@.ORD_P_ID, @.ORD_PHTI_ID, @.ORD_NAME, @.ORD_CLIENT_CODE,
convert(varchar,getdate(),101), @.ORD_INTERNAL_NOTE,
convert(varchar,@.ORD_REQUESTED_DELIVERY_DATE,101), @.ORD_REQUESTED_QUANTITY,
@.ORD_AVAILABLE_QUANTITY)

set @.err = @.@.Error

select @.NewOrderID =SCOPE_IDENTITY()

etc................................."Filips Benoit" <benoit.filips@.pandora.be> wrote in message
news:G3bkc.90727$o73.5680193@.phobos.telenet-ops.be...
> Dear All,
> After copying a record using an Stored procedure all textfields (nvarchar)
> has max lenght !
>
> See VBA-code and SP below
> VBA-code tos execute SP
> Dim objcommand As ADODB.Command
> Dim intReturnParam As Long
> Set objcommand = New ADODB.Command
> With objcommand
> .CommandType = adCmdStoredProc
> .CommandText = "FB_CopyOrder"
> .Parameters.Append .CreateParameter("return_value", adInteger,
> adParamReturnValue)
> .Parameters.Append .CreateParameter("ORD_ID", adInteger,
> adParamInput, , Me.ORD_ID)
> .Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
> adParamInput, , Me.ORD_P_ID)
> .Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
> adParamInput, , Me.ORD_PHTI_ID)
> .Parameters.Append .CreateParameter("ORD_NAME", adWChar,
> adParamInput, 50, Me.ORD_NAME)
> .Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
> adParamInput, 50, Me.ORD_CLIENT_CODE)
> .Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
> adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
> .Parameters.Append
..CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
> adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
> .Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
> adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
> .Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
> adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
> .ActiveConnection = CurrentProject.Connection
> .Execute
> intReturnParam = .Parameters(0).Value
> End With
> Stored procedure
> Alter Procedure FB_CopyOrder
> --List of parameters to be added to the parametercollection of the
> ADO-commandobject before executing the command
> @.SourceOrderID int,
> @.ORD_P_ID int,
> @.ORD_PHTI_ID int,
> @.ORD_NAME nvarchar(50),
> @.ORD_CLIENT_CODE nvarchar(50),
> @.ORD_INTERNAL_NOTE nvarchar(1024),
> @.ORD_REQUESTED_DELIVERY_DATE datetime,
> @.ORD_REQUESTED_QUANTITY int,
> @.ORD_AVAILABLE_QUANTITY int
> as
> declare @.err int
> declare @.NewOrderid int
> begin tran
> -- add new order values = command-parameters
> insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
> ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
> ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)
> values (@.ORD_P_ID, @.ORD_PHTI_ID, @.ORD_NAME, @.ORD_CLIENT_CODE,
> convert(varchar,getdate(),101), @.ORD_INTERNAL_NOTE,
> convert(varchar,@.ORD_REQUESTED_DELIVERY_DATE,101),
@.ORD_REQUESTED_QUANTITY,
> @.ORD_AVAILABLE_QUANTITY)
> set @.err = @.@.Error
> select @.NewOrderID =SCOPE_IDENTITY()
> etc.................................

It looks like you should be using adVarWChar, not adWChar - the data is
being treated as nchar, not nvarchar, so it's being 'padded out' with
spaces.

Simon