Is there anyway to make MS SQL Server 2000 perform some stored procedures,
say 3 times a day? If so, how? They told me to go to Management->SQL Server
Agent-> Jobs. But for writing the steps, does a stored procedures belong to
a
"Transact-SQL Script" type? Is there any documents or examples available?
Sorry, I know I'm troublesome.Yes. You would script it like this in the Command textbox:
exec dbo.MyStoredProcedure
If you need to pass in certain arguements, you may want to
create a wrapper stored procedure that can automatically
determine your parameters( ie. start date/end date if applicable).
It is problem easier to manage it this way.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:451C02E7-87CC-46CD-AAF9-81D099E7D018@.microsoft.com...
> Is there anyway to make MS SQL Server 2000 perform some stored procedures,
> say 3 times a day? If so, how? They told me to go to Management->SQL
> Server
> Agent-> Jobs. But for writing the steps, does a stored procedures belong
> to a
> "Transact-SQL Script" type? Is there any documents or examples available?
> Sorry, I know I'm troublesome.|||Yes you can schedule stored procedures to run 3 times a day using a job.
and yes, a stored procedure belong to a "Transact-SQL Script" type.
In the command box, type exec StoredProcedureName.
Schedule it to run daily every 8 hours.
Simon Worth
wrytat wrote:
> Is there anyway to make MS SQL Server 2000 perform some stored procedures,
> say 3 times a day? If so, how? They told me to go to Management->SQL Serve
r
> Agent-> Jobs. But for writing the steps, does a stored procedures belong t
o a
> "Transact-SQL Script" type? Is there any documents or examples available?
> Sorry, I know I'm troublesome.|||Thank you for your advice. Just asking if there's any available articles
online in MSDN or wherever that demonstrate how to create a job? Am I asking
for too much?|||This is just an example - hope it helps.
Look in BOL as well under "How to Create a job (Transact-SQL)"
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'JobName')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''JobName'' since there is
already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'JobName'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'JobName', @.owner_login_name = N'DEML\sworth2',
@.description = N'This is the job to run a stored procedure',
@.category_name = N'[Uncategorized (Local)]', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend =
0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name = N'TSQLStep1', @.command = N'EXEC
SP_Spaceused', @.database_name = N'Northwind', @.server = N'',
@.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code =
0, @.flags = 0, @.retry_attempts = 0, @.retry_interval = 1,
@.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action =
1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID,
@.name = N'3TimesADay', @.enabled = 1, @.freq_type = 4, @.active_start_date
= 20050331, @.active_start_time = 0, @.freq_interval = 1,
@.freq_subday_type = 8, @.freq_subday_interval = 8,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Simon Worth
wrytat wrote:
> Thank you for your advice. Just asking if there's any available articles
> online in MSDN or wherever that demonstrate how to create a job? Am I aski
ng
> for too much?|||As others have suggested, you could use a SQL Agent Job. You may also want
to consider using the Windows task scheduler with the osql utility
(http://msdn.microsoft.com/library/d...r />
_1wxl.asp)
or Windows Script Host. Other scheduling software such as Control-M if your
organization has it is probably an even better choice.
Although I have some simple jobs set up I prefer the flexibility offered
through other scheduling solutions, particularly if the job is dependent on
another task or other tasks depend on it.
HTH
--
Dave Fancher
http://davefancher.blogspot.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:451C02E7-87CC-46CD-AAF9-81D099E7D018@.microsoft.com...
> Is there anyway to make MS SQL Server 2000 perform some stored procedures,
> say 3 times a day? If so, how? They told me to go to Management->SQL
> Server
> Agent-> Jobs. But for writing the steps, does a stored procedures belong
> to a
> "Transact-SQL Script" type? Is there any documents or examples available?
> Sorry, I know I'm troublesome.
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
Scheduling Stored Procedures for MS SQL Server 2000
Is there any way to make MS SQL Server 2000 to perform some stored procedures, say 3 times a day? If so, how? Thanks.Use the SQL Agent. Google for examples. It's a core part of a SQL server installation. Set up your jobs, let fly.|||
I need to execute a webmethod form awebservice every 5 minutes and wanted 2 use a stored procedure but I can't schedulea job because Tiscali won't allow me. Is there another way to schedule a storedprocedure or call a webmethod with out having my computer do it over the internet?They also won't allow me to install a windows service in the server.
sqlscheduling stored procedure
how can i schedule a sp to run every 10 minutescreate an Agent Job with a single step that calls the sproc.|||thanks how do i do that in sql 2005
i'm using management studio|||Start by either reading Books Online or by right-clicking the Agent tab.
i'm using management studio|||Start by either reading Books Online or by right-clicking the Agent tab.
scheduling stored procedure
Hi,
I am trying to schedule a stored procedure using SQL server Agent and i get the following error:
DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
please help
regards,
hirenI found this in SQL Books Online.
Is it appropriate, ie do you have computed columns etc ?
If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.|||Well what's it set to?
Do you have a trigger on the table?|||Hi,
No there are no triggers. I have found the solution.
The connection related setting for the SQL server needs to be checked for the same. I performed the following steps...
1. In enterprise manager, right clicked on SQL Server i was accessing.
2. Selected properties->Connections.
3. Under 'default connection option', selected 'Arithmetic abort'
4. Clicked OK and restarted the server.
Thanks and regards,
henry
I am trying to schedule a stored procedure using SQL server Agent and i get the following error:
DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
please help
regards,
hirenI found this in SQL Books Online.
Is it appropriate, ie do you have computed columns etc ?
If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.|||Well what's it set to?
Do you have a trigger on the table?|||Hi,
No there are no triggers. I have found the solution.
The connection related setting for the SQL server needs to be checked for the same. I performed the following steps...
1. In enterprise manager, right clicked on SQL Server i was accessing.
2. Selected properties->Connections.
3. Under 'default connection option', selected 'Arithmetic abort'
4. Clicked OK and restarted the server.
Thanks and regards,
henry
Scheduling stored proc.
How do you schedule stored procedures? I can see no way of linking them to the job scheduler.
Also, how do I time the stored proc. schedule to activate after a scheduled DTS package?
Thanks again guys!OK, I see how to schedule a stored proc...by just scheduling an execute command.
But I still cant see how to time this with the scheduling of a DTS package.|||You create a new DTS.
You drop your connection,
You drop "Execute SQL TASK". You write your SQL-query
(example: "exec myproc")
you choose "save as" and name your DTS.
Now you will have a DTS in the list, and you right click on it and choose "Scedule Package"
Good luck!|||Its possible to execute and schedule a stored procedure using job scheduler just mention SP name and select connected database, schedule it.
Also, how do I time the stored proc. schedule to activate after a scheduled DTS package?
Thanks again guys!OK, I see how to schedule a stored proc...by just scheduling an execute command.
But I still cant see how to time this with the scheduling of a DTS package.|||You create a new DTS.
You drop your connection,
You drop "Execute SQL TASK". You write your SQL-query
(example: "exec myproc")
you choose "save as" and name your DTS.
Now you will have a DTS in the list, and you right click on it and choose "Scedule Package"
Good luck!|||Its possible to execute and schedule a stored procedure using job scheduler just mention SP name and select connected database, schedule it.
scheduling sql sp needing to access mapped network drives
How to make network drives accessible to a stored procedure? Currently the sp works only if I open a session on the server and execute the sp with SQL Server Management Studio on the server.
If you want to use ressources within the network you will need appropiate permissions either for the service account SQL Server Service is running with or the service account SQL Server Agent is running with (if you do that within a scheduled job). An alternative would be to setup a proxy account for the service [1]
[1] http://www.databasejournal.com/features/mssql/article.php/1580041
In SQL Server 2005 proxy accounts can also be configured through the Configuration interface which you will find in the program collection.
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
Do check out security implication posted by Jens.sql
scheduling question for SQL Server agent job for 1/1,4/1,7/1,10,1
HI I am executing a stored procedure from a job using the server agent but
can not create the correct run times. I only want it to run 4 times a year,
the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
anyone has any ideas?
Thanks.
--
Paul G
Software engineer.I've never used that scheduler but I got curious. Assuming you are
using SQL Server 2000, it appears that if you choose Occurs Monthly,
then Day [1] of every [3] months, with a start date of 110/1/2006, it
would do what you ask.
But that is not the voice of experience, just the way it looks.
Roy Harvey
Beacon Falls, CT
On Thu, 7 Sep 2006 15:53:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>HI I am executing a stored procedure from a job using the server agent but
>can not create the correct run times. I only want it to run 4 times a year,
>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
>anyone has any ideas?
>Thanks.|||What's that start date Roy?
I think that perhaps Roy meant a start date of 10/1/2006...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bub1g2tvcgqffb148lblutfmm1f519338p@.4ax.com...
> I've never used that scheduler but I got curious. Assuming you are
> using SQL Server 2000, it appears that if you choose Occurs Monthly,
> then Day [1] of every [3] months, with a start date of 110/1/2006, it
> would do what you ask.
> But that is not the voice of experience, just the way it looks.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 7 Sep 2006 15:53:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>>HI I am executing a stored procedure from a job using the server agent but
>>can not create the correct run times. I only want it to run 4 times a
>>year,
>>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering
>>if
>>anyone has any ideas?
>>Thanks.|||On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What's that start date Roy?
>I think that perhaps Roy meant a start date of 10/1/2006...
What? You mean your calendar does not have month 110?
Thanks for catching that, Arnie.
Roy|||yep seems to work thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
> >What's that start date Roy?
> >
> >I think that perhaps Roy meant a start date of 10/1/2006...
> What? You mean your calendar does not have month 110?
> Thanks for catching that, Arnie.
> Roy
>
can not create the correct run times. I only want it to run 4 times a year,
the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
anyone has any ideas?
Thanks.
--
Paul G
Software engineer.I've never used that scheduler but I got curious. Assuming you are
using SQL Server 2000, it appears that if you choose Occurs Monthly,
then Day [1] of every [3] months, with a start date of 110/1/2006, it
would do what you ask.
But that is not the voice of experience, just the way it looks.
Roy Harvey
Beacon Falls, CT
On Thu, 7 Sep 2006 15:53:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>HI I am executing a stored procedure from a job using the server agent but
>can not create the correct run times. I only want it to run 4 times a year,
>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
>anyone has any ideas?
>Thanks.|||What's that start date Roy?
I think that perhaps Roy meant a start date of 10/1/2006...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bub1g2tvcgqffb148lblutfmm1f519338p@.4ax.com...
> I've never used that scheduler but I got curious. Assuming you are
> using SQL Server 2000, it appears that if you choose Occurs Monthly,
> then Day [1] of every [3] months, with a start date of 110/1/2006, it
> would do what you ask.
> But that is not the voice of experience, just the way it looks.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 7 Sep 2006 15:53:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>>HI I am executing a stored procedure from a job using the server agent but
>>can not create the correct run times. I only want it to run 4 times a
>>year,
>>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering
>>if
>>anyone has any ideas?
>>Thanks.|||On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What's that start date Roy?
>I think that perhaps Roy meant a start date of 10/1/2006...
What? You mean your calendar does not have month 110?
Thanks for catching that, Arnie.
Roy|||yep seems to work thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
> >What's that start date Roy?
> >
> >I think that perhaps Roy meant a start date of 10/1/2006...
> What? You mean your calendar does not have month 110?
> Thanks for catching that, Arnie.
> Roy
>
scheduling question for SQL Server agent job for 1/1,4/1,7/1,10,1
HI I am executing a stored procedure from a job using the server agent but
can not create the correct run times. I only want it to run 4 times a year
,
the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
anyone has any ideas?
Thanks.
--
Paul G
Software engineer.I've never used that scheduler but I got curious. Assuming you are
using SQL Server 2000, it appears that if you choose Occurs Monthly,
then Day [1] of every [3] months, with a start date of 110/1/2006, i
t
would do what you ask.
But that is not the voice of experience, just the way it looks.
Roy Harvey
Beacon Falls, CT
On Thu, 7 Sep 2006 15:53:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>HI I am executing a stored procedure from a job using the server agent but
>can not create the correct run times. I only want it to run 4 times a yea
r,
>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering i
f
>anyone has any ideas?
>Thanks.|||What's that start date Roy?
I think that perhaps Roy meant a start date of 10/1/2006...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bub1g2tvcgqffb148lblutfmm1f519338p@.
4ax.com...[vbcol=seagreen]
> I've never used that scheduler but I got curious. Assuming you are
> using SQL Server 2000, it appears that if you choose Occurs Monthly,
> then Day [1] of every [3] months, with a start date of 110/1/2006,
it
> would do what you ask.
> But that is not the voice of experience, just the way it looks.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 7 Sep 2006 15:53:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>|||On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What's that start date Roy?
>I think that perhaps Roy meant a start date of 10/1/2006...
What? You mean your calendar does not have month 110?
Thanks for catching that, Arnie.
Roy
can not create the correct run times. I only want it to run 4 times a year
,
the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
anyone has any ideas?
Thanks.
--
Paul G
Software engineer.I've never used that scheduler but I got curious. Assuming you are
using SQL Server 2000, it appears that if you choose Occurs Monthly,
then Day [1] of every [3] months, with a start date of 110/1/2006, i
t
would do what you ask.
But that is not the voice of experience, just the way it looks.
Roy Harvey
Beacon Falls, CT
On Thu, 7 Sep 2006 15:53:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>HI I am executing a stored procedure from a job using the server agent but
>can not create the correct run times. I only want it to run 4 times a yea
r,
>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering i
f
>anyone has any ideas?
>Thanks.|||What's that start date Roy?
I think that perhaps Roy meant a start date of 10/1/2006...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bub1g2tvcgqffb148lblutfmm1f519338p@.
4ax.com...[vbcol=seagreen]
> I've never used that scheduler but I got curious. Assuming you are
> using SQL Server 2000, it appears that if you choose Occurs Monthly,
> then Day [1] of every [3] months, with a start date of 110/1/2006,
it
> would do what you ask.
> But that is not the voice of experience, just the way it looks.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 7 Sep 2006 15:53:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>|||On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What's that start date Roy?
>I think that perhaps Roy meant a start date of 10/1/2006...
What? You mean your calendar does not have month 110?
Thanks for catching that, Arnie.
Roy
Wednesday, March 28, 2012
scheduling jobs that query linked servers
I have some SQL Servers that are linked and have created
a stored procedure on my primary SQL Server that queries
the linked servers. I also created a new login ID for
this.
I can run the stored proc anytime and it works great.
However, when I attempt to execute the stored proc from a
scheduled job I get the message: "Remote access not
allowed for Windows NT user activated by SETUSER."
I don't see how to get around this and still use the job
scheduler. Do I really have to use isql and put it in
scheduler software running outside MSSQL?
Can I run this T-SQL inside the scheduler with a
configuration switch somewhere? Or is this a "feature"
of MSSQL?
thanksIf you go to step properties, there is a drop down
box "run as user", from that list select the login that
has access to the linked server, that login also need to
have some access privileges to the database from which the
step is run.
hth.
>--Original Message--
>I have some SQL Servers that are linked and have created
>a stored procedure on my primary SQL Server that queries
>the linked servers. I also created a new login ID for
>this.
>I can run the stored proc anytime and it works great.
>However, when I attempt to execute the stored proc from a
>scheduled job I get the message: "Remote access not
>allowed for Windows NT user activated by SETUSER."
>I don't see how to get around this and still use the job
>scheduler. Do I really have to use isql and put it in
>scheduler software running outside MSSQL?
>Can I run this T-SQL inside the scheduler with a
>configuration switch somewhere? Or is this a "feature"
>of MSSQL?
>thanks
>
>.
>|||I tried that before and it did not work. I have not been
able to run this via any user in that drop down.
I have a situation where I can login via isql (sql server
authentication - not Windows auth.) and I am able to
execute the stored proc just fine. The stored proc
queries linked servers and works fine.
However when I try to use the Job Scheduler, I get the
error: "Remote access not allowed for Windows NT user
activated by SETUSER." When I attempt to run the job as
[self] I get: "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'."
Unless there is a hidden config option somewhere it
appears that the job scheduler cannot be used for remote
access to linked servers unless via a windows auth. login.
Is that true? If this is supposed to be a "security
feature" then it is pointless as far as I can tell.
We're on 2000 sp 3a
Jeff
>--Original Message--
>If you go to step properties, there is a drop down
>box "run as user", from that list select the login that
>has access to the linked server, that login also need to
>have some access privileges to the database from which
the
>step is run.
>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
created
>>a stored procedure on my primary SQL Server that
queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>.
>|||I tried it myself and came to same conclusion as you for
now that you have to use windows auth. to access linked
servers via a jobschedule.
And looks like the problem is with the setuser command,
because when using nt auth. sqlagent is not using setuser
command, and if you use sql auth. then sqlagent is using
the setuser command. I tried this in QA also using setuser
for an nt auth. user who has sysadmin rights on both
servers and came back with the same error message "emote
access not allowed for Windows NT user activated by
SETUSER."
I will post more if I find something.
>--Original Message--
>I tried that before and it did not work. I have not been
>able to run this via any user in that drop down.
>I have a situation where I can login via isql (sql server
>authentication - not Windows auth.) and I am able to
>execute the stored proc just fine. The stored proc
>queries linked servers and works fine.
>However when I try to use the Job Scheduler, I get the
>error: "Remote access not allowed for Windows NT user
>activated by SETUSER." When I attempt to run the job as
>[self] I get: "Login failed for user 'NT
>AUTHORITY\ANONYMOUS LOGON'."
>Unless there is a hidden config option somewhere it
>appears that the job scheduler cannot be used for remote
>access to linked servers unless via a windows auth. login.
>Is that true? If this is supposed to be a "security
>feature" then it is pointless as far as I can tell.
>We're on 2000 sp 3a
>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login that
>>has access to the linked server, that login also need to
>>have some access privileges to the database from which
>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>created
>>a stored procedure on my primary SQL Server that
>queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>.
>|||This just gets stranger and stranger. On a hunch I set
up execution of my stored proc by a DTS package. I
schedule it and voila! it works!!?! It's not my
preferred way of doing this but if this is the kludge I
need to use to employ our of these inconsistent
behaviors, I'm going to use it.
Jeff
>--Original Message--
>I tried it myself and came to same conclusion as you for
>now that you have to use windows auth. to access linked
>servers via a jobschedule.
>And looks like the problem is with the setuser command,
>because when using nt auth. sqlagent is not using
setuser
>command, and if you use sql auth. then sqlagent is using
>the setuser command. I tried this in QA also using
setuser
>for an nt auth. user who has sysadmin rights on both
>servers and came back with the same error
message "emote
>access not allowed for Windows NT user activated by
>SETUSER."
>I will post more if I find something.
>
>>--Original Message--
>>I tried that before and it did not work. I have not
been
>>able to run this via any user in that drop down.
>>I have a situation where I can login via isql (sql
server
>>authentication - not Windows auth.) and I am able to
>>execute the stored proc just fine. The stored proc
>>queries linked servers and works fine.
>>However when I try to use the Job Scheduler, I get the
>>error: "Remote access not allowed for Windows NT user
>>activated by SETUSER." When I attempt to run the job
as
>>[self] I get: "Login failed for user 'NT
>>AUTHORITY\ANONYMOUS LOGON'."
>>Unless there is a hidden config option somewhere it
>>appears that the job scheduler cannot be used for
remote
>>access to linked servers unless via a windows auth.
login.
>>Is that true? If this is supposed to be a "security
>>feature" then it is pointless as far as I can tell.
>>We're on 2000 sp 3a
>>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login
that
>>has access to the linked server, that login also need
to
>>have some access privileges to the database from which
>>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>>created
>>a stored procedure on my primary SQL Server that
>>queries
>>the linked servers. I also created a new login ID
for
>>this.
>>I can run the stored proc anytime and it works
great.
>>However, when I attempt to execute the stored proc
from
>>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>>job
>>scheduler. Do I really have to use isql and put it
in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this
a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>>.
>.
>
a stored procedure on my primary SQL Server that queries
the linked servers. I also created a new login ID for
this.
I can run the stored proc anytime and it works great.
However, when I attempt to execute the stored proc from a
scheduled job I get the message: "Remote access not
allowed for Windows NT user activated by SETUSER."
I don't see how to get around this and still use the job
scheduler. Do I really have to use isql and put it in
scheduler software running outside MSSQL?
Can I run this T-SQL inside the scheduler with a
configuration switch somewhere? Or is this a "feature"
of MSSQL?
thanksIf you go to step properties, there is a drop down
box "run as user", from that list select the login that
has access to the linked server, that login also need to
have some access privileges to the database from which the
step is run.
hth.
>--Original Message--
>I have some SQL Servers that are linked and have created
>a stored procedure on my primary SQL Server that queries
>the linked servers. I also created a new login ID for
>this.
>I can run the stored proc anytime and it works great.
>However, when I attempt to execute the stored proc from a
>scheduled job I get the message: "Remote access not
>allowed for Windows NT user activated by SETUSER."
>I don't see how to get around this and still use the job
>scheduler. Do I really have to use isql and put it in
>scheduler software running outside MSSQL?
>Can I run this T-SQL inside the scheduler with a
>configuration switch somewhere? Or is this a "feature"
>of MSSQL?
>thanks
>
>.
>|||I tried that before and it did not work. I have not been
able to run this via any user in that drop down.
I have a situation where I can login via isql (sql server
authentication - not Windows auth.) and I am able to
execute the stored proc just fine. The stored proc
queries linked servers and works fine.
However when I try to use the Job Scheduler, I get the
error: "Remote access not allowed for Windows NT user
activated by SETUSER." When I attempt to run the job as
[self] I get: "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'."
Unless there is a hidden config option somewhere it
appears that the job scheduler cannot be used for remote
access to linked servers unless via a windows auth. login.
Is that true? If this is supposed to be a "security
feature" then it is pointless as far as I can tell.
We're on 2000 sp 3a
Jeff
>--Original Message--
>If you go to step properties, there is a drop down
>box "run as user", from that list select the login that
>has access to the linked server, that login also need to
>have some access privileges to the database from which
the
>step is run.
>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
created
>>a stored procedure on my primary SQL Server that
queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>.
>|||I tried it myself and came to same conclusion as you for
now that you have to use windows auth. to access linked
servers via a jobschedule.
And looks like the problem is with the setuser command,
because when using nt auth. sqlagent is not using setuser
command, and if you use sql auth. then sqlagent is using
the setuser command. I tried this in QA also using setuser
for an nt auth. user who has sysadmin rights on both
servers and came back with the same error message "emote
access not allowed for Windows NT user activated by
SETUSER."
I will post more if I find something.
>--Original Message--
>I tried that before and it did not work. I have not been
>able to run this via any user in that drop down.
>I have a situation where I can login via isql (sql server
>authentication - not Windows auth.) and I am able to
>execute the stored proc just fine. The stored proc
>queries linked servers and works fine.
>However when I try to use the Job Scheduler, I get the
>error: "Remote access not allowed for Windows NT user
>activated by SETUSER." When I attempt to run the job as
>[self] I get: "Login failed for user 'NT
>AUTHORITY\ANONYMOUS LOGON'."
>Unless there is a hidden config option somewhere it
>appears that the job scheduler cannot be used for remote
>access to linked servers unless via a windows auth. login.
>Is that true? If this is supposed to be a "security
>feature" then it is pointless as far as I can tell.
>We're on 2000 sp 3a
>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login that
>>has access to the linked server, that login also need to
>>have some access privileges to the database from which
>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>created
>>a stored procedure on my primary SQL Server that
>queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>.
>|||This just gets stranger and stranger. On a hunch I set
up execution of my stored proc by a DTS package. I
schedule it and voila! it works!!?! It's not my
preferred way of doing this but if this is the kludge I
need to use to employ our of these inconsistent
behaviors, I'm going to use it.
Jeff
>--Original Message--
>I tried it myself and came to same conclusion as you for
>now that you have to use windows auth. to access linked
>servers via a jobschedule.
>And looks like the problem is with the setuser command,
>because when using nt auth. sqlagent is not using
setuser
>command, and if you use sql auth. then sqlagent is using
>the setuser command. I tried this in QA also using
setuser
>for an nt auth. user who has sysadmin rights on both
>servers and came back with the same error
message "emote
>access not allowed for Windows NT user activated by
>SETUSER."
>I will post more if I find something.
>
>>--Original Message--
>>I tried that before and it did not work. I have not
been
>>able to run this via any user in that drop down.
>>I have a situation where I can login via isql (sql
server
>>authentication - not Windows auth.) and I am able to
>>execute the stored proc just fine. The stored proc
>>queries linked servers and works fine.
>>However when I try to use the Job Scheduler, I get the
>>error: "Remote access not allowed for Windows NT user
>>activated by SETUSER." When I attempt to run the job
as
>>[self] I get: "Login failed for user 'NT
>>AUTHORITY\ANONYMOUS LOGON'."
>>Unless there is a hidden config option somewhere it
>>appears that the job scheduler cannot be used for
remote
>>access to linked servers unless via a windows auth.
login.
>>Is that true? If this is supposed to be a "security
>>feature" then it is pointless as far as I can tell.
>>We're on 2000 sp 3a
>>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login
that
>>has access to the linked server, that login also need
to
>>have some access privileges to the database from which
>>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>>created
>>a stored procedure on my primary SQL Server that
>>queries
>>the linked servers. I also created a new login ID
for
>>this.
>>I can run the stored proc anytime and it works
great.
>>However, when I attempt to execute the stored proc
from
>>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>>job
>>scheduler. Do I really have to use isql and put it
in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this
a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>>.
>.
>
Scheduling DTS Package using a stored procedure problem
The DTS package runs fine through Enterprise manager successfully.
However, when scheduled through a job that runs the dts through the
following code:
However, when scheduled through a job that runs the dts through the
following code:
DECLARE @.findfile int
Exec @.findfile = master.dbo.xp_cmdShell 'dir
\\ServerName\folder\filename.xls', no_output
IF (@.findfile=0)
BEGIN
Exec master.dbo.xp_cmdshell 'dtsrun -E -Server\Instance
-N"DataImport"'
END
The servername specified in the above statement in a different server
than the server that the package resides on.
This is the error that I get when I try to run the same code using
query analyzer:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147024893
(80070003)
Error string: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error Detail Records:
Error: -2147024893 (80070003); Provider Error: 0 (0)
Error string: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error: -2147024893 (80070003); Provider Error: 0 (0)
Error string: Cannot open a log file of specified name. The system
cannot find the path specified.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4700
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
NULL
The job is owned by the SQLService account(Windows account) that has
System Admin rights and also is part of the Domain Admin User group.
The Domain Admin User group has full rights on the file that the DTS is
trying to access.
Any help in trying to figure out why the schedule job cannot find the
file path would be appreciated.
Thanks
KRI found the problem was the path in the log file : was using a mapped
drive vs a UNC path.
Monday, March 26, 2012
Scheduling a summary Stored Procedure
I had stored procedure[this read the data from couple of table and summarize
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhu
If the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>
|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own[vbcol=seagreen]
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> summarize
> half
start
>
|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my[vbcol=seagreen]
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> the
proc[vbcol=seagreen]
> own
to[vbcol=seagreen]
> start
to
>
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhu
If the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>
|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own[vbcol=seagreen]
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> summarize
> half
start
>
|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my[vbcol=seagreen]
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> the
proc[vbcol=seagreen]
> own
to[vbcol=seagreen]
> start
to
>
Scheduling a summary Stored Procedure
I had stored procedure[this read the data from couple of table and summarize
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhuIf the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > I had stored procedure[this read the data from couple of table and
> summarize
> > them based on the input paramter] which needs to be executed for every
> half
> > hour.I need to pass some input parameter to that proc. I am planning to
> > schedule this proc using below system procs
> > sp_add_jobschedule
> > sp_add_job
> > I need to pass some input parameter to the stored proc. I need give
start
> > interval and end interval and some configurable values[int 1...int 5) to
> > filter out columns. These values can be modifiable by the end user
> > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > int5...)
> > How do I pass this input params to my stored proc, when I shceduled .?
> > Is it the right approch to do this kind of summary procedure ?
> > Thanks in advance for your thoughts and comments appreciate it
> >
> > prabhu
> >
> >
> >
> >
>|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> > If the procedure you need to run have changing parameters' values, what
> > application supplies the parameters' values? That application should be
> the
> > job scheduled (within SQL server or outside).
> >
> > If you have a few sets of such values that are fed to the same stored
proc
> > to be run at different time, you can create separate jobs, each has its
> own
> > schedule and parameter values.
> >
> >
> >
> > "prabhu" <> wrote in message
> > news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > > I had stored procedure[this read the data from couple of table and
> > summarize
> > > them based on the input paramter] which needs to be executed for every
> > half
> > > hour.I need to pass some input parameter to that proc. I am planning
to
> > > schedule this proc using below system procs
> > > sp_add_jobschedule
> > > sp_add_job
> > > I need to pass some input parameter to the stored proc. I need give
> start
> > > interval and end interval and some configurable values[int 1...int 5)
to
> > > filter out columns. These values can be modifiable by the end user
> > > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > > int5...)
> > > How do I pass this input params to my stored proc, when I shceduled .?
> > > Is it the right approch to do this kind of summary procedure ?
> > > Thanks in advance for your thoughts and comments appreciate it
> > >
> > > prabhu
> > >
> > >
> > >
> > >
> >
> >
>
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhuIf the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > I had stored procedure[this read the data from couple of table and
> summarize
> > them based on the input paramter] which needs to be executed for every
> half
> > hour.I need to pass some input parameter to that proc. I am planning to
> > schedule this proc using below system procs
> > sp_add_jobschedule
> > sp_add_job
> > I need to pass some input parameter to the stored proc. I need give
start
> > interval and end interval and some configurable values[int 1...int 5) to
> > filter out columns. These values can be modifiable by the end user
> > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > int5...)
> > How do I pass this input params to my stored proc, when I shceduled .?
> > Is it the right approch to do this kind of summary procedure ?
> > Thanks in advance for your thoughts and comments appreciate it
> >
> > prabhu
> >
> >
> >
> >
>|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> > If the procedure you need to run have changing parameters' values, what
> > application supplies the parameters' values? That application should be
> the
> > job scheduled (within SQL server or outside).
> >
> > If you have a few sets of such values that are fed to the same stored
proc
> > to be run at different time, you can create separate jobs, each has its
> own
> > schedule and parameter values.
> >
> >
> >
> > "prabhu" <> wrote in message
> > news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > > I had stored procedure[this read the data from couple of table and
> > summarize
> > > them based on the input paramter] which needs to be executed for every
> > half
> > > hour.I need to pass some input parameter to that proc. I am planning
to
> > > schedule this proc using below system procs
> > > sp_add_jobschedule
> > > sp_add_job
> > > I need to pass some input parameter to the stored proc. I need give
> start
> > > interval and end interval and some configurable values[int 1...int 5)
to
> > > filter out columns. These values can be modifiable by the end user
> > > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > > int5...)
> > > How do I pass this input params to my stored proc, when I shceduled .?
> > > Is it the right approch to do this kind of summary procedure ?
> > > Thanks in advance for your thoughts and comments appreciate it
> > >
> > > prabhu
> > >
> > >
> > >
> > >
> >
> >
>
Scheduling A Stored Procedure?
Hi,
We are using SQL Server 2000. Is it possible to somehow schedule a stored
procedure to run at a specified date and time? I know that its possible
with DTS packages, but this is just a stored proc that performs a
calculation. We want it to run at a certain time every day.
JDSQL Agent - add a job
in EM it's under <server> - Management - SQL Agent - Jobs.
Joe Delphi wrote:
> Hi,
> We are using SQL Server 2000. Is it possible to somehow schedule a stored
> procedure to run at a specified date and time? I know that its possible
> with DTS packages, but this is just a stored proc that performs a
> calculation. We want it to run at a certain time every day.
>
> JD
>|||On Wed, 30 Nov 2005 19:59:44 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:
>SQL Agent - add a job
>in EM it's under <server> - Management - SQL Agent - Jobs.
>Joe Delphi wrote:
or you can run it from inside a DTS package.
J.|||Another alternative:
You can create a bat file that will run the stored procedure using osql.exe.
Then schedule that bat file to run using windows scheduler.
Lucas
"jxstern" wrote:
> On Wed, 30 Nov 2005 19:59:44 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
> or you can run it from inside a DTS package.
> J.
>
We are using SQL Server 2000. Is it possible to somehow schedule a stored
procedure to run at a specified date and time? I know that its possible
with DTS packages, but this is just a stored proc that performs a
calculation. We want it to run at a certain time every day.
JDSQL Agent - add a job
in EM it's under <server> - Management - SQL Agent - Jobs.
Joe Delphi wrote:
> Hi,
> We are using SQL Server 2000. Is it possible to somehow schedule a stored
> procedure to run at a specified date and time? I know that its possible
> with DTS packages, but this is just a stored proc that performs a
> calculation. We want it to run at a certain time every day.
>
> JD
>|||On Wed, 30 Nov 2005 19:59:44 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:
>SQL Agent - add a job
>in EM it's under <server> - Management - SQL Agent - Jobs.
>Joe Delphi wrote:
or you can run it from inside a DTS package.
J.|||Another alternative:
You can create a bat file that will run the stored procedure using osql.exe.
Then schedule that bat file to run using windows scheduler.
Lucas
"jxstern" wrote:
> On Wed, 30 Nov 2005 19:59:44 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
> or you can run it from inside a DTS package.
> J.
>
Scheduling a Report based on an event
Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If it's possible to detect that your stored proc is finished within SQL
(either because it sets a flag in a table or be the mire presence of data it
creates or something) there might be a way. Might not be the optimal
solution, but your SQL dataset could reference that flag so that no data is
returned at all to the report if the flag doesn't exist. You'd have to set
it up so that the data table the report references doesn't exist at all until
the stored proc is finished. Then schedule your report job to run multiple
times near the time when you expect the SQL proc to finish. Without the data
table existing, your Rpt Services job should just error out and product
nothing. Again, not an elegant solution, but if you're desperate might be
something to toy around with ... a place to start.
sebring1130
"SqlJunkies User" wrote:
> Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>|||Sure this is real easy to do.
Create a schedule that has completed in the past - so effectively it will
never fire. Associate this schedule with a Report.
Now what happens is that a SQL Agent Job is created - that maps to the
schedule. You can run SQL Agent Jobs from the SQL Agent Management interface
by hand - or you can cause that job to run through T-SQL.
All that the SQL Agent Job does is create an entry in the Report Server's
Event table at the scheduled time. The Report Server Windows Service is
polling the Event table every 10 seconds or so - and if there are any events
to process it gets on and processes them.
So what you do is either include in your long running stored procedure a
call that will create the required entry in the Event table directly - or a
call that fires the SQL Agent Job.
- One word of warning though if you start editing the schedule in the Report
Manager, then the Report Manager can end up re-creating the SQL Agent Jobs -
and you lose reference to the actual Job.
However if you are disciplined enough then this approach works fine -
(Schedule in the past, have your own process force the SQL Agent Job to run)
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...
> Is it possible to schedule a report based on a flag or stored procedure
> completing. Currently have an overnight load process which must complete
> before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If it's possible to detect that your stored proc is finished within SQL
(either because it sets a flag in a table or be the mire presence of data it
creates or something) there might be a way. Might not be the optimal
solution, but your SQL dataset could reference that flag so that no data is
returned at all to the report if the flag doesn't exist. You'd have to set
it up so that the data table the report references doesn't exist at all until
the stored proc is finished. Then schedule your report job to run multiple
times near the time when you expect the SQL proc to finish. Without the data
table existing, your Rpt Services job should just error out and product
nothing. Again, not an elegant solution, but if you're desperate might be
something to toy around with ... a place to start.
sebring1130
"SqlJunkies User" wrote:
> Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>|||Sure this is real easy to do.
Create a schedule that has completed in the past - so effectively it will
never fire. Associate this schedule with a Report.
Now what happens is that a SQL Agent Job is created - that maps to the
schedule. You can run SQL Agent Jobs from the SQL Agent Management interface
by hand - or you can cause that job to run through T-SQL.
All that the SQL Agent Job does is create an entry in the Report Server's
Event table at the scheduled time. The Report Server Windows Service is
polling the Event table every 10 seconds or so - and if there are any events
to process it gets on and processes them.
So what you do is either include in your long running stored procedure a
call that will create the required entry in the Event table directly - or a
call that fires the SQL Agent Job.
- One word of warning though if you start editing the schedule in the Report
Manager, then the Report Manager can end up re-creating the SQL Agent Jobs -
and you lose reference to the actual Job.
However if you are disciplined enough then this approach works fine -
(Schedule in the past, have your own process force the SQL Agent Job to run)
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...
> Is it possible to schedule a report based on a flag or stored procedure
> completing. Currently have an overnight load process which must complete
> before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
Scheduling a job using a stored procedure through a Stored Procedure
I currently new to SQL server and have been assigned a project to develop an auction site as part of my course. I would like to create a stored procedure which schedules a job to modify the 'auction state' field in a table to 'active' once the auction start time is reached and also run a scheduled job to close the auction at the end time.
I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?EXECUTE msdb.dbo.sp_add_job-PatP|||The code snippet below has completed successfully, but i there seems to be a problem with it, can you use a local variable as a value for job name like below?
exec msdb.dbo.sp_add_job @.job_name = @.AuctionID
exec msdb.dbo.sp_add_jobstep @.job_name = @.AuctionID,
@.stepname = 'Step1',
@.command = 'Update Auction
SET AuctionState = ''closed''
WHERE AuctionID = @.AuctionID'
thanks for the earlier response.|||Can anyone see a problem with the code below, STEP1 doesn't get included when the job is scheduled. Suspected errors highlighted in red. The errors haven't been added cos they are confusing.
_______________________________________________
INSERT INTO Auction
(...
)
VALUES
(
...)
SELECT
@.AuctionID = @.@.Identity
declare @.Aid varchar
set @.Aid = convert(varchar,@.auctionID,15)
Declare @.sqlcommand varchar(255)
Set @.sqlcommand = 'UPDATE Auction SET AuctionState = ''closed'' WHERE AuctionID = ' + @.AID
exec msdb.dbo.sp_add_job @.job_name = @.AUCTIONID
exec msdb.dbo.sp_add_jobstep @.job_name = '@.AUCTIONID',
@.step_name = 'Step1',
@.command = @.sqlcommand,
@.database_name = 'Auction',
@.server = 'XX'
exec msdb.dbo.sp_add_jobschedule @.job_name = @.AuctionID,
@.name = 'AuctionsScheduled',
@.freq_type = 1,
@.active_start_time ='120000',
@.active_start_date = '20040703',
@.freq_recurrence_factor = 1
exec msdb.dbo.sp_add_jobserver @.job_name = @.auctionID,
@.server_name = 'XX'
_____________________________________________
I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?EXECUTE msdb.dbo.sp_add_job-PatP|||The code snippet below has completed successfully, but i there seems to be a problem with it, can you use a local variable as a value for job name like below?
exec msdb.dbo.sp_add_job @.job_name = @.AuctionID
exec msdb.dbo.sp_add_jobstep @.job_name = @.AuctionID,
@.stepname = 'Step1',
@.command = 'Update Auction
SET AuctionState = ''closed''
WHERE AuctionID = @.AuctionID'
thanks for the earlier response.|||Can anyone see a problem with the code below, STEP1 doesn't get included when the job is scheduled. Suspected errors highlighted in red. The errors haven't been added cos they are confusing.
_______________________________________________
INSERT INTO Auction
(...
)
VALUES
(
...)
SELECT
@.AuctionID = @.@.Identity
declare @.Aid varchar
set @.Aid = convert(varchar,@.auctionID,15)
Declare @.sqlcommand varchar(255)
Set @.sqlcommand = 'UPDATE Auction SET AuctionState = ''closed'' WHERE AuctionID = ' + @.AID
exec msdb.dbo.sp_add_job @.job_name = @.AUCTIONID
exec msdb.dbo.sp_add_jobstep @.job_name = '@.AUCTIONID',
@.step_name = 'Step1',
@.command = @.sqlcommand,
@.database_name = 'Auction',
@.server = 'XX'
exec msdb.dbo.sp_add_jobschedule @.job_name = @.AuctionID,
@.name = 'AuctionsScheduled',
@.freq_type = 1,
@.active_start_time ='120000',
@.active_start_date = '20040703',
@.freq_recurrence_factor = 1
exec msdb.dbo.sp_add_jobserver @.job_name = @.auctionID,
@.server_name = 'XX'
_____________________________________________
Friday, March 23, 2012
Scheduler UTC problem
Hi all,
I'm having trouble to implement an "scheduler" in a stored procedure with UTC date.
In one table (ScheduleCfg) I have one field that indicates which days of the week my banner must be exhibited, separated by commas (e.g. 0,5,6)
In my table "Schedule", I have a datetime field that indicates which is the next time that the banner must be shown.
(To explain: I have a process which runs every hour to republicate the banners and recalculate the next exhibition hour).
My SP gets the "Last Display" datetime and sums one hour... If the day still the same, then the new value of "Next Display" is this value.
But if it changes the day, then I'll have to verify if the new day's dayofweek number matches with the tables...
Until this everything works fine... But all my problem is: UTC TIME!
My application is globalizated, so many countries can use it. All the dates stored in my database must be UTC, so here's my problem:
When I sum one hour, I'm using the UTC date. So the "turn of the day" occurs in a different moment. For example, in Brazil the TimeZone is -3 hours, so when the day changes it will be 9 o'clock in Brazil. (because it's 0h UTC, so the Brazil's local time will be 9h PM of the day before...)
Anyone can help me?
I've been thinking about it for weeks and I couldn't find a good solution...
Thanks in advance.
This should work. I've used the difference between the GetUTCDate and GetDate functions to get the difference between the local timezone and UTC. Here is my suggested approach:
Code Snippet
--This variable contains the last saved date
Declare @.LastDisplay as Datetime
--Retrieve the value from the 'Schedule' table
--Retrieve the number of hours difference between the local timezone and UTC
Declare @.HourDiff as Int
Select @.HourDiff = DateDiff(hour, GetUTCDate(), GetDate())
--Calculate the next schedule date taking into consideration the timezone difference and adding an hour
Declare @.NextDisplay as Datetime
Set @.NextDisplay = DateAdd(hour, 1 + @.HourDiff, @.LastDisplay)
Select @.NextDisplay
--Check the difference between the last date (in UTC) and the new date (local time)
If DateDiff(day, @.LastDisplay, @.NextDisplay) = 1
Begin
--The day has changed
End
Else
Begin
-- The day has not changed
End
The DateDiff at the end, compares the last date the schedule ran which is saved in UTC with the new calculated date which is in local time. If the difference is not 0 then this is a new date and you can perform the logic that you want.
I hope this answers your question.
Best regards,
Sami Samir
|||Hmmm. Good idea..
I'll try to pass a parameter with the time difference, since my SQL Server does not know which country is calling..
Thank you!!!!
Scheduled weekly reports
I am trying to create a Job that will run a stored procedure i have scripted
and the email the the results to certain managers in my company for analysis
purposes. The procedure is fine i was just wondering how i would get the
results formatted and into an email. Do i use the SQL Mail?hi
did u see xp_sendmail
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"A.B." wrote:
> I am trying to create a Job that will run a stored procedure i have script
ed
> and the email the the results to certain managers in my company for analys
is
> purposes. The procedure is fine i was just wondering how i would get the
> results formatted and into an email. Do i use the SQL Mail?|||"A.B." <AB@.discussions.microsoft.com> wrote in message
news:4C34B83A-C86E-4031-ACEF-05B4F02B0326@.microsoft.com...
>I am trying to create a Job that will run a stored procedure i have
>scripted
> and the email the the results to certain managers in my company for
> analysis
> purposes. The procedure is fine i was just wondering how i would get the
> results formatted and into an email. Do i use the SQL Mail?
No, you use reporting services.|||Do both: use SQL Mail to send a message that the report is ready, but use SQ
L
Reporting Services for the actual report.
Why would you need to send a large amount of data through e-mail? Don't they
get enough spam anyway? ;)
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:7303024C-CB3B-4273-9D76-24D9C962D9BC@.microsoft.com...
> Do both: use SQL Mail to send a message that the report is ready, but use
> SQL
> Reporting Services for the actual report.
Or you could use Reporting Services to both render and send notification via
email.
and the email the the results to certain managers in my company for analysis
purposes. The procedure is fine i was just wondering how i would get the
results formatted and into an email. Do i use the SQL Mail?hi
did u see xp_sendmail
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"A.B." wrote:
> I am trying to create a Job that will run a stored procedure i have script
ed
> and the email the the results to certain managers in my company for analys
is
> purposes. The procedure is fine i was just wondering how i would get the
> results formatted and into an email. Do i use the SQL Mail?|||"A.B." <AB@.discussions.microsoft.com> wrote in message
news:4C34B83A-C86E-4031-ACEF-05B4F02B0326@.microsoft.com...
>I am trying to create a Job that will run a stored procedure i have
>scripted
> and the email the the results to certain managers in my company for
> analysis
> purposes. The procedure is fine i was just wondering how i would get the
> results formatted and into an email. Do i use the SQL Mail?
No, you use reporting services.|||Do both: use SQL Mail to send a message that the report is ready, but use SQ
L
Reporting Services for the actual report.
Why would you need to send a large amount of data through e-mail? Don't they
get enough spam anyway? ;)
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:7303024C-CB3B-4273-9D76-24D9C962D9BC@.microsoft.com...
> Do both: use SQL Mail to send a message that the report is ready, but use
> SQL
> Reporting Services for the actual report.
Or you could use Reporting Services to both render and send notification via
email.
Scheduled tasks
Is it possible to schedule tasks in SQL Express, for example a database backup, or to periodically execute a stored procedure?
hi Allan,
yes, it is possible, but not using the SQL Server Agent as SQLExpress does not provide the relative bits.. you have so to rely on the native OS scheduler, third party (or self written alternative) scheduler, or rely on the Service Broker features..
personally I usually go for the native OS scheduler, where you can define a list of xx.sql scripts to be executed by a xx.cmd file.. then the xx.cmd file, connecting to SqlCMD.exe and executing each defined xx.sql script, has to be scheduled... quiete simple and not expensive... results are output to a text file similar to:
[cmdfile.cmd]
ECHO. >>Backing up databases
SqlCmd -E -S(Local)\InstanceName -i"c:\somefolder\ScheduledBackup.sql" >c:\SomeFolder\ScheduledBackupLog.txt
ECHO. >>Sending results via mail to aministrators
SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing log of - ' + CONVERT(varchar, GETDATE());" >E:\e-VbHot\Files\Log\MailingLogBCKschedulato.txt
SqlCmd -E -S(Local)\InstanceName -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [applicationDB].[dbo].[amSMTPmail] @.Server = N'smtp server name', @.Sender = N'scheduledbackup@.my_company.com', @.AddressesTO = N'admin_name@.my_company.com', @.AddressesCC = N'me@.me.com', @.AddressesCCN = NULL, @.AttachFiles = N'c:\SomeFolder\ScheduledBackupLog.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed', @.UserName = NULL, @.UserPassword = NULL; SELECT @.ret AS [Execution result];" >>c:\SomeFolder\MailingLog.txt
[/cmdfile.cmd]
as you can see, I do personally even send the c:\SomeFolder\ScheduledBackupLog.txt resulting file via e-mail to a "list" of addresses.. this is performed via a (free) CLR stored procedure of mine, amDBObj, where the .Net code implements a poor's man SMTP mail feature to "replace" (ok... it is not comparable with) Database Mail missing feature of SQLExpress..
regards
|||Thanks for the info Andrea. I will look into it.
Thanks, too, for the offer of the CLR email proc - but I already did one myself :)
sqlWednesday, March 21, 2012
Scheduled Stored Procedure Causes SQL Server to stop responding.
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.codecomments.com ***
Can you use performance monitor to see what actually is happening to the
system when that stored proc was running? Check to see if the CPU is maxing
out or the disks are being thrashed? May be there's scope for optimising
that stored proc. Could you post the code?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Austin Rathe" <austin#REMOVETHIS#@.gametart.com> wrote in message
news:O3Fkl8WfFHA.3316@.TK2MSFTNGP14.phx.gbl...
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.codecomments.com ***
sql
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.codecomments.com ***
Can you use performance monitor to see what actually is happening to the
system when that stored proc was running? Check to see if the CPU is maxing
out or the disks are being thrashed? May be there's scope for optimising
that stored proc. Could you post the code?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Austin Rathe" <austin#REMOVETHIS#@.gametart.com> wrote in message
news:O3Fkl8WfFHA.3316@.TK2MSFTNGP14.phx.gbl...
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.codecomments.com ***
sql
Scheduled Stored Procedure Causes SQL Server to stop responding.
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.developersdex.com ***Can you use performance monitor to see what actually is happening to the
system when that stored proc was running? Check to see if the CPU is maxing
out or the disks are being thrashed? May be there's scope for optimising
that stored proc. Could you post the code?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Austin Rathe" <austin#REMOVETHIS#@.gametart.com> wrote in message
news:O3Fkl8WfFHA.3316@.TK2MSFTNGP14.phx.gbl...
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.developersdex.com ***
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.developersdex.com ***Can you use performance monitor to see what actually is happening to the
system when that stored proc was running? Check to see if the CPU is maxing
out or the disks are being thrashed? May be there's scope for optimising
that stored proc. Could you post the code?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Austin Rathe" <austin#REMOVETHIS#@.gametart.com> wrote in message
news:O3Fkl8WfFHA.3316@.TK2MSFTNGP14.phx.gbl...
Hi,
We have a scheduled stored procedure running on SQL Server (enterprise
server I believe). This stored proc is one of a set of procs run by one
job, it takes about 11 minutes to do everything.
Sometimes, during the execution of the Job, the server seems to not
respond to connection requests from our website, giving the following
error:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
I didn't think that running a stored procedure (even if it is a bit of a
beast) would bring down SQL server. Does anyone have any idea what we
are doing wrong?
Thanks for your help & time,
Austin Rathe
*** Sent via Developersdex http://www.developersdex.com ***
Subscribe to:
Posts (Atom)