Configure Auto-start option in SQL agent

Is there a way I can set the Auto-start option in SQL Server agent to
true from my program. This can be done through a DOS command, SQL
statement, or anything else I can execute through my program. I know
how to start the agent from my software, I just don't know how to set
it to auto-start.
Any suggestions?You could use the sc.exe command prompt utility (sc config). Check
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/windowsserver2003/proddocs/entserver/sc.asp.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0309061823.6f44952a@.posting.google.com...
> Is there a way I can set the Auto-start option in SQL Server agent to
> true from my program. This can be done through a DOS command, SQL
> statement, or anything else I can execute through my program. I know
> how to start the agent from my software, I just don't know how to set
> it to auto-start.
> Any suggestions?|||Other options include using Service Control Manager e.g.
scm -Silent 1 -Action 7 -Service SQLServerAgent -SvcStartType 2
or from TSQL
exec xp_cmdshell 'scm -Silent 1 -Action 7 -Service
SQLServerAgent -SvcStartType 2'
You could also use SQLDMO from VBScript
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0309061823.6f44952a@.posting.google.com...
Is there a way I can set the Auto-start option in SQL Server agent to
true from my program. This can be done through a DOS command, SQL
statement, or anything else I can execute through my program. I know
how to start the agent from my software, I just don't know how to set
it to auto-start.
Any suggestions?|||That worked great, thanks!!!!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<uRJvexUdDHA.2508@.tk2msftngp13.phx.gbl>...
> Other options include using Service Control Manager e.g.
> scm -Silent 1 -Action 7 -Service SQLServerAgent -SvcStartType 2
> or from TSQL
> exec xp_cmdshell 'scm -Silent 1 -Action 7 -Service
> SQLServerAgent -SvcStartType 2'
> You could also use SQLDMO from VBScript
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
> news:d8653140.0309061823.6f44952a@.posting.google.com...
> Is there a way I can set the Auto-start option in SQL Server agent to
> true from my program. This can be done through a DOS command, SQL
> statement, or anything else I can execute through my program. I know
> how to start the agent from my software, I just don't know how to set
> it to auto-start.
> Any suggestions?

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:

Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie

Conditional -- ?

What does "conditional" mean as a command? sp_who2 reports this for
some sessions, along with "insert" etc.

Thanks,
Jim Geissman(jim_geissman@.countrywide.com) writes:
> What does "conditional" mean as a command? sp_who2 reports this for
> some sessions, along with "insert" etc.

An IF or WHILE statement. Typically with a query in the condition, like
IF EXISTS(SELECT ...). If this query is long-running, or the longest
running in a stuck loop, CONDITIONAL is likely to turn up.

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

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

Erland Sommarskog wrote:
> (jim_geissman@.countrywide.com) writes:
> > What does "conditional" mean as a command? sp_who2 reports this for
> > some sessions, along with "insert" etc.
> An IF or WHILE statement. Typically with a query in the condition, like
> IF EXISTS(SELECT ...). If this query is long-running, or the longest
> running in a stuck loop, CONDITIONAL is likely to turn up.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

condensing log files

I need to set up an automated way to condense log files in a database. I
successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
was thinking that if I can create a stored proc that runs through all the
tables in a dabase, I could schedule that to run and it would always keep my
log file sizes down. So I wrote code, but of course the first parameter of
the "dbcc shrinkdatabase" command requires a 'table object' and that is my
problem. How can I write the code to use the table names as table objects
instead of varchar vairables? Here is the code that I have so far:
declare @.name as varchar(50)
create table #tableNames
(name varchar(50))
insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
while select count(*) from #tableNames > 0
BEGIN
@.name = select top 1 from #tableNames
dbcc shrinkdatabase (@.name, truncateonly)
delete from #tableNames where name = @.name
END
drop table #tableNames
Scott Schuman
Medical AssociatesThere are undocumented stored procedures designed to execute dynamic SQL
against databases and tables:
Undocumented sp_MSforeachdb and sp_MSforeachtable Stored Procedures
http://www.dbazine.com/sql/sql-articles/larsen5
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OnHXUDD$FHA.1548@.TK2MSFTNGP10.phx.gbl...
>I need to set up an automated way to condense log files in a database. I
> successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
> was thinking that if I can create a stored proc that runs through all the
> tables in a dabase, I could schedule that to run and it would always keep
> my
> log file sizes down. So I wrote code, but of course the first parameter
> of
> the "dbcc shrinkdatabase" command requires a 'table object' and that is my
> problem. How can I write the code to use the table names as table objects
> instead of varchar vairables? Here is the code that I have so far:
> declare @.name as varchar(50)
> create table #tableNames
> (name varchar(50))
> insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
> while select count(*) from #tableNames > 0
> BEGIN
> @.name = select top 1 from #tableNames
> dbcc shrinkdatabase (@.name, truncateonly)
> delete from #tableNames where name = @.name
> END
> drop table #tableNames
> Scott Schuman
> Medical Associates
>|||Schoo (scott.schuman@.nospam.ma-hc.com) writes:
> I need to set up an automated way to condense log files in a database.
> I successfully got a "dbcc shrinkdatabase" command to run in TranSQL,
> but I was thinking that if I can create a stored proc that runs through
> all the tables in a dabase, I could schedule that to run and it would
> always keep my log file sizes down. So I wrote code, but of course the
> first parameter of the "dbcc shrinkdatabase" command requires a 'table
> object' and that is my problem. How can I write the code to use the
> table names as table objects instead of varchar vairables? Here is the
> code that I have so far:
It seems to me to be a poor idea. Shrinking is good if a file has
exploded because a of some special operation. But shrinking the log
regularly is meaningless, the log will grow again, and that will cost
you performance.
Also read this article from Tibor Karazsi's site:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx