Thanks
Guess I couldn't see the tree for the forest. Here is the solution in case anyone else has the issue. Get the NextRunDate property of the Job object in the Microsoft.SqlServer.Management.Smo.Agent namespace.
Showing posts with label agent. Show all posts
Showing posts with label agent. Show all posts
Wednesday, March 28, 2012
How to determine when and if SQL Agent job will run again?
I need to determine programmatically if and when a SQL Agent job will run next. (a date and time) Is this possible and if so, any suggestions on how? I need to maintain a table of the next pending execution for each job so I can allow the user to cancel the next pending job, but I have to show them the date and time on which it will occur. Since I'm using most of the scheduling capabilities of SQL Agent, I really need to get the next execution time that SQL Agent thinks the job should run rather than trying to calculate when I think it should run based on all the various scheduling scenarios. I'm using SMO in SQL Server 20005.
How to determine when and if SQL Agent job will run again?
I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?
Thanks
Hello, your question is really SQL agent related so you likely want to post in the mgt tools forum but I can say what I know. SQL agent jobs and job steps can be manipulate from TSQL/Stored procedures so in theory you could call those from an SSIS pacakge. For example, using the SSIS Execute SQL task.
The following looks like a good reference to SQL Agent SPs.
http://msdn2.microsoft.com/en-us/library/ms187763.aspx
Hope that helps
|||Here is the solution in case anyone else has the issue. Get the NextRunDate property of the Job object in the Microsoft.SqlServer.Management.Smo.Agent namespace.
How to determine what was inserted
Hi,
Can anyone tell me how I can locate what was inserted into the publisher?
The merge agent on the publisher says Last Action (Merged 3 data chnages (3
inserts, 0 updates, 0 deletes, 0 resolved conflicts) Further along the
merge agent details reveal that it was 3 Publisher inserts.
I want to know what was inserted (At least what table was affected).
Is this possible.
PS: I only have access to the Publisher.
Can anyone help?
Hi Warren,
From your descriptions, I understood that you would like to find out what
was done by Replication when it reminds you 3 inserts etc., Have I
understood you? If there is anything I misunderstood, please feel free to
let me know
Unfortunately, I don't think there are any views or tables will record this
kind of modifications. Only possible way is using SQL Profiler to trace the
operation of Replication so that I believe you will find them.
Hope this helps and if you have any questions or concerns, please feel free
to let me know. I will be glad of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
Thats exactly what i want to know (What tables were affected by the inserts,
updates etc)
I will try the profiler if I can, but we are concerned by the extra time
Replication will now take.
Many thanks
Warren
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:YGlFkACUEHA.2988@.cpmsftngxa10.phx.gbl...
> Hi Warren,
> From your descriptions, I understood that you would like to find out what
> was done by Replication when it reminds you 3 inserts etc., Have I
> understood you? If there is anything I misunderstood, please feel free to
> let me know
> Unfortunately, I don't think there are any views or tables will record
this
> kind of modifications. Only possible way is using SQL Profiler to trace
the
> operation of Replication so that I believe you will find them.
> Hope this helps and if you have any questions or concerns, please feel
free
> to let me know. I will be glad of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
Can anyone tell me how I can locate what was inserted into the publisher?
The merge agent on the publisher says Last Action (Merged 3 data chnages (3
inserts, 0 updates, 0 deletes, 0 resolved conflicts) Further along the
merge agent details reveal that it was 3 Publisher inserts.
I want to know what was inserted (At least what table was affected).
Is this possible.
PS: I only have access to the Publisher.
Can anyone help?
Hi Warren,
From your descriptions, I understood that you would like to find out what
was done by Replication when it reminds you 3 inserts etc., Have I
understood you? If there is anything I misunderstood, please feel free to
let me know
Unfortunately, I don't think there are any views or tables will record this
kind of modifications. Only possible way is using SQL Profiler to trace the
operation of Replication so that I believe you will find them.
Hope this helps and if you have any questions or concerns, please feel free
to let me know. I will be glad of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
Thats exactly what i want to know (What tables were affected by the inserts,
updates etc)
I will try the profiler if I can, but we are concerned by the extra time
Replication will now take.
Many thanks
Warren
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:YGlFkACUEHA.2988@.cpmsftngxa10.phx.gbl...
> Hi Warren,
> From your descriptions, I understood that you would like to find out what
> was done by Replication when it reminds you 3 inserts etc., Have I
> understood you? If there is anything I misunderstood, please feel free to
> let me know
> Unfortunately, I don't think there are any views or tables will record
this
> kind of modifications. Only possible way is using SQL Profiler to trace
the
> operation of Replication so that I believe you will find them.
> Hope this helps and if you have any questions or concerns, please feel
free
> to let me know. I will be glad of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
Wednesday, March 21, 2012
how to detect transactional replication agent failure programmatic
Hello,
Right now, I have an alert set up in the SQL Server but is it possible for a
C#/C/C++/VB program to detect the replication agent failure? If so, how can
it be done?
Thanks in advance,
Frank
query the repl_errors table in the distirbution database.
select name, Publisher=p.srvname, publisher_db, publication,
Subscriber=s.srvname, subscriber_db, MSdistribution_history.time, comments,
error_type_id,source_type_id, source_name, error_code,error_text from
MSdistribution_history
join msrepl_errors on MSdistribution_history.error_id=msrepl_errors.id
join msdistribution_agents on
msdistribution_agents.id=MSdistribution_history.ag ent_id
join master.dbo.sysservers as p on p.srvid=publisher_id
join master.dbo.sysservers as s on s.srvid=publisher_id
order by 1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJY" <FJY@.discussions.microsoft.com> wrote in message
news:1F219C21-2032-422F-AC54-C0D5BBC15CAA@.microsoft.com...
> Hello,
> Right now, I have an alert set up in the SQL Server but is it possible for
> a
> C#/C/C++/VB program to detect the replication agent failure? If so, how
> can
> it be done?
> Thanks in advance,
> Frank
Right now, I have an alert set up in the SQL Server but is it possible for a
C#/C/C++/VB program to detect the replication agent failure? If so, how can
it be done?
Thanks in advance,
Frank
query the repl_errors table in the distirbution database.
select name, Publisher=p.srvname, publisher_db, publication,
Subscriber=s.srvname, subscriber_db, MSdistribution_history.time, comments,
error_type_id,source_type_id, source_name, error_code,error_text from
MSdistribution_history
join msrepl_errors on MSdistribution_history.error_id=msrepl_errors.id
join msdistribution_agents on
msdistribution_agents.id=MSdistribution_history.ag ent_id
join master.dbo.sysservers as p on p.srvid=publisher_id
join master.dbo.sysservers as s on s.srvid=publisher_id
order by 1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJY" <FJY@.discussions.microsoft.com> wrote in message
news:1F219C21-2032-422F-AC54-C0D5BBC15CAA@.microsoft.com...
> Hello,
> Right now, I have an alert set up in the SQL Server but is it possible for
> a
> C#/C/C++/VB program to detect the replication agent failure? If so, how
> can
> it be done?
> Thanks in advance,
> Frank
Sunday, February 19, 2012
How to define token syntax in MSSQL2005 sp1?
There is one token in my Agent Job $WMI(DatabaseName)
Now,I defined this using $(ESCAPE_NONE(WMI(DatabaseName)))
but failed and prompted: Variable WMI(DatabaseName) not found
What should i do for this? thanks
From the updated books online:
For jobs that run in response to WMI alerts, the value of the property specified by property. For example, $(WMI(DatabaseName)) provides the value of the DatabaseName property for the WMI event that caused the alert to run.
So I believe you don't need the ESCAPE_NONE...
|||If do not add ESCAPE_NONE,prompt "For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run"
Discover In SQL Server 2005 SP1, the SQL Server Agent job step token syntax has changed
url：http://support.microsoft.com/kb/915845
I want to do something for each database just was created,So I could not specifiy the value of database, how to deal with that? thanks
|||Perhaps the tokens remain disabled?
Because access to Eventlog is not always secured, the alerts are disabled by default. To get the substitutions to work, you should ensure that only members of trusted groups have write permissions to Eventlog, then enable these tokens on the Agent Properties Dialog Alert System tab, or you can set the AlertReplaceRuntimeTokens reg key.
jkh
How to define token syntax in MSSQL2005 sp1?
There is one token in my Agent Job $WMI(DatabaseName)
Now,I defined this using $(ESCAPE_NONE(WMI(DatabaseName)))
but failed and prompted: Variable WMI(DatabaseName) not found
What should i do for this? thanks
From the updated books online:
For jobs that run in response to WMI alerts, the value of the property specified by property. For example, $(WMI(DatabaseName)) provides the value of the DatabaseName property for the WMI event that caused the alert to run.
So I believe you don't need the ESCAPE_NONE...
|||If do not add ESCAPE_NONE,prompt "For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run"
Discover In SQL Server 2005 SP1, the SQL Server Agent job step token syntax has changed
url：http://support.microsoft.com/kb/915845
I want to do something for each database just was created,So I could not specifiy the value of database, how to deal with that? thanks
|||Perhaps the tokens remain disabled?
Because access to Eventlog is not always secured, the alerts are disabled by default. To get the substitutions to work, you should ensure that only members of trusted groups have write permissions to Eventlog, then enable these tokens on the Agent Properties Dialog Alert System tab, or you can set the AlertReplaceRuntimeTokens reg key.
jkh
Subscribe to:
Posts (Atom)