Back devices: step

Showing posts with label step. Show all posts

Monday, March 26, 2012

How can I programatically run the snapshot agent?

How can I run the snapshot agent of a publication from within a custom job in
one step?
I have a snapshot publication that will execute on demand. A custom job will
initiate this process, but the custom job needs to be able to wait until the
snapshot agent is finished as it will do many other things after. I know I
can start the snapshot agent by using the sp_start_job sproc but this will
simply start the process and will not wait until it finishes.
One option I know I have is to copy the 3 steps below which are inside the
auto-generated snapshot job and add them to my custom job as 3 steps.
Step Name
---
1. Snapshot Agent startup message.
2. Run agent.
3. Detect nonlogged agent shutdown.
However, I would prefer to perform all of this in one step to simplify
things which is why I am asking if any of you know if this is even possible.
Thanks for any assistance you can provide.
P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
- JohnnyYou can use the commands within run agent. You will not get a start up
message, and if it gpf's you will not know your agent has died.
--
Hilary Cotter
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
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> How can I run the snapshot agent of a publication from within a custom job
in
> one step?
> I have a snapshot publication that will execute on demand. A custom job
will
> initiate this process, but the custom job needs to be able to wait until
the
> snapshot agent is finished as it will do many other things after. I know I
> can start the snapshot agent by using the sp_start_job sproc but this will
> simply start the process and will not wait until it finishes.
> One option I know I have is to copy the 3 steps below which are inside the
> auto-generated snapshot job and add them to my custom job as 3 steps.
> Step Name
> ---
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
>
> However, I would prefer to perform all of this in one step to simplify
> things which is why I am asking if any of you know if this is even
possible.
> Thanks for any assistance you can provide.
> P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> - Johnny
>|||Thanks Hilary.
What does "gpf's" mean? I havent seen that before. Do you instead recommend
I copy all 3 steps for a more solid solution?
P.S - We bought your book already btw. It's great!
Johnny
"Hilary Cotter" wrote:
> You can use the commands within run agent. You will not get a start up
> message, and if it gpf's you will not know your agent has died.
> --
> Hilary Cotter
> 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
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:853CA188-69E7-437D-AE57-548F5DDDF7AD@.microsoft.com...
> > How can I run the snapshot agent of a publication from within a custom job
> in
> > one step?
> >
> > I have a snapshot publication that will execute on demand. A custom job
> will
> > initiate this process, but the custom job needs to be able to wait until
> the
> > snapshot agent is finished as it will do many other things after. I know I
> > can start the snapshot agent by using the sp_start_job sproc but this will
> > simply start the process and will not wait until it finishes.
> >
> > One option I know I have is to copy the 3 steps below which are inside the
> > auto-generated snapshot job and add them to my custom job as 3 steps.
> >
> > Step Name
> > ---
> > 1. Snapshot Agent startup message.
> > 2. Run agent.
> > 3. Detect nonlogged agent shutdown.
> >
> >
> > However, I would prefer to perform all of this in one step to simplify
> > things which is why I am asking if any of you know if this is even
> possible.
> >
> > Thanks for any assistance you can provide.
> >
> > P.S - FYI, I am using snapshot replication for this w/ SQL Server 2005.
> >
> > - Johnny
> >
>
>

Friday, March 9, 2012

How can i insert a default row if it doenst exists already?

Hi,

I am currently loading dimensions using a Sql Server Destination and i was wondering if i can create a middle step to insert a certain row so i would identify in my dimension the undefined records... I know i can do it with sql statement but i was wondering if there is a better way.

Best Regards,

Luis Sim?es

Would this not be part of the fact load, you wish to infer the dim member from the fact data when there is not already a matching dim member? You would use either the lookup or join to detect the rows in the fact that do not have a corresponding dim member. Normally I would expect a lookup to be better for the job as there you have many more fact rows than dim rows, so lookups are normally quite effective. The lookup would be used to get the dim key (surrogate key value) and this is then inserted. You could use the error output of the lookup, or even ignore lookup failures and then use a conditional split to get a path with only rows that having missing values. You would then insert the missing Dim members then insert the fact. You will need to ensure only you do only one insert as you could have several facts missing the same dim member in one buffer.

|||

I think that was not the desired output...

What i really want to do is to insert just one row with a specific caracteristic and add it to the dimension if it doens't exists already...

That record will be used to identify "undefined" records of dimension X.

Best Regards,

|||You can very easily use a custom script component in "Source" mode to create rows.

The rest depends on your architecture, but perhaps you could union your new "unknown" member with the rest of your incoming dimension data. I assume you already have a mechanism to discard/update members that already exist.

Alternatively, you could have a separate Data Flow that tries to load unknowns for all dimensions. Each dimension would have a separate custom source to create the row, a lookup to detect it if already exists, and a destination to insert it if it doesn't.
|||

If you want to do this in the data-flow that populates the dimension table then use a script transformation to create your "Unknown" row and use a UNION transformaiton to put it together with the est of the incoming data. Very simple.

-Jamie

|||

Script and Unions will do the trick, but how often do you populate a Dim table from scratch? Would it not be easier to include the "unknown" record as part of your create table script? You only create a Dim table once, and therefore only need to do this once, so it would make more sense to me just do it manually, and avoid complicating the packge.

It also makes it easier to break that cardinal rule and hard code a surrogate key value. Code a -1 for example as the unknown, and then you can use that as a default value assigned in your pipeline if the regular lookup fails. People may complain about this logic, but it can perform rather well.

Friday, February 24, 2012

How can I get the current job's name or job ID from a job step within the job

How can I get the current job's name or job ID from a job step within
the job in sql server?In MSDB, start checking out the sysjobs, sysjobssteps, etc. tables.
"tram" <tram_e@.hotmail.com> wrote in message
news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> How can I get the current job's name or job ID from a job step within
> the job in sql server?
>|||Chris,
I know the the tablenames, but we need to give either job ID or name to
get the output file name. After finishing step1, I should be able to
get the output fielname of step1 for current job without hard coding
...
ChrisR wrote:
> In MSDB, start checking out the sysjobs, sysjobssteps, etc. tables.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> > How can I get the current job's name or job ID from a job step
within
> > the job in sql server?
> >|||Chris,
I know the the tablenames, but we need to give either job ID or name to
get the output file name. After finishing step1, I should be able to
get the output fielname of step1 for current job without hard coding
...
ChrisR wrote:
> In MSDB, start checking out the sysjobs, sysjobssteps, etc. tables.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> > How can I get the current job's name or job ID from a job step
within
> > the job in sql server?
> >|||Perhaps this is of help?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"tram" <tram_e@.hotmail.com> wrote in message
news:1104252327.066933.268140@.f14g2000cwb.googlegroups.com...
> Chris,
> I know the the tablenames, but we need to give either job ID or name to
> get the output file name. After finishing step1, I should be able to
> get the output fielname of step1 for current job without hard coding
> ...
>
> ChrisR wrote:
>> In MSDB, start checking out the sysjobs, sysjobssteps, etc. tables.
>>
>> "tram" <tram_e@.hotmail.com> wrote in message
>> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
>> > How can I get the current job's name or job ID from a job step
> within
>> > the job in sql server?
>> >
>|||Use the [JOBID] job step token. See sp_add_job_step in BOL for details. Also
look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"tram" <tram_e@.hotmail.com> wrote in message
news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> How can I get the current job's name or job ID from a job step within
> the job in sql server?
>|||Jasper,
I've tried using token. But didn't work. Would you give an example?
Thanks
Jasper Smith wrote:
> Use the [JOBID] job step token. See sp_add_job_step in BOL for
details. Also
> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "tram" <tram_e@.hotmail.com> wrote in message
> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> > How can I get the current job's name or job ID from a job step
within
> > the job in sql server?
> >|||Did you check the samples at the web page that Jasper and me posted?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"tram" <tram_e@.hotmail.com> wrote in message
news:1104265806.678523.298020@.z14g2000cwz.googlegroups.com...
> Jasper,
> I've tried using token. But didn't work. Would you give an example?
> Thanks
>
> Jasper Smith wrote:
>> Use the [JOBID] job step token. See sp_add_job_step in BOL for
> details. Also
>> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "tram" <tram_e@.hotmail.com> wrote in message
>> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
>> > How can I get the current job's name or job ID from a job step
> within
>> > the job in sql server?
>> >
>|||Tibor,
I appreciate your reply. I've seen the examples , but is not working. I
was trying to know the JOB id of current job in the job step so that I
can get the output file name from sysjobsteps.
Anyway, thanks for your reply.
Tibor Karaszi wrote:
> Did you check the samples at the web page that Jasper and me posted?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:1104265806.678523.298020@.z14g2000cwz.googlegroups.com...
> > Jasper,
> >
> > I've tried using token. But didn't work. Would you give an example?
> >
> > Thanks
> >
> >
> >
> > Jasper Smith wrote:
> >> Use the [JOBID] job step token. See sp_add_job_step in BOL for
> > details. Also
> >> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "tram" <tram_e@.hotmail.com> wrote in message
> >> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> >> > How can I get the current job's name or job ID from a job step
> > within
> >> > the job in sql server?
> >> >
> >|||tram,
"Doesn't work" is not a very good problem description. I created a job with one jobstep and added
below SQL to the jobstep. Work just fine on my machine:
SELECT 'The output file name is "' + output_file_name + '"'
FROM msdb..sysjobsteps
WHERE job_id = [JOBID]
AND step_id = [STEPID]
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"tram" <tram_e@.hotmail.com> wrote in message
news:1104323486.443287.4420@.z14g2000cwz.googlegroups.com...
> Tibor,
> I appreciate your reply. I've seen the examples , but is not working. I
> was trying to know the JOB id of current job in the job step so that I
> can get the output file name from sysjobsteps.
> Anyway, thanks for your reply.
>
> Tibor Karaszi wrote:
>> Did you check the samples at the web page that Jasper and me posted?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "tram" <tram_e@.hotmail.com> wrote in message
>> news:1104265806.678523.298020@.z14g2000cwz.googlegroups.com...
>> > Jasper,
>> >
>> > I've tried using token. But didn't work. Would you give an example?
>> >
>> > Thanks
>> >
>> >
>> >
>> > Jasper Smith wrote:
>> >> Use the [JOBID] job step token. See sp_add_job_step in BOL for
>> > details. Also
>> >> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "tram" <tram_e@.hotmail.com> wrote in message
>> >> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
>> >> > How can I get the current job's name or job ID from a job step
>> > within
>> >> > the job in sql server?
>> >> >
>> >
>|||Tibor,
Thanks for quick reply. In my machine, I was getting invalid column
name JOBID and STEPID.
Tibor Karaszi wrote:
> tram,
> "Doesn't work" is not a very good problem description. I created a
job with one jobstep and added
> below SQL to the jobstep. Work just fine on my machine:
> SELECT 'The output file name is "' + output_file_name + '"'
> FROM msdb..sysjobsteps
> WHERE job_id = [JOBID]
> AND step_id = [STEPID]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:1104323486.443287.4420@.z14g2000cwz.googlegroups.com...
> > Tibor,
> >
> > I appreciate your reply. I've seen the examples , but is not
working. I
> > was trying to know the JOB id of current job in the job step so
that I
> > can get the output file name from sysjobsteps.
> > Anyway, thanks for your reply.
> >
> >
> > Tibor Karaszi wrote:
> >> Did you check the samples at the web page that Jasper and me
posted?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "tram" <tram_e@.hotmail.com> wrote in message
> >> news:1104265806.678523.298020@.z14g2000cwz.googlegroups.com...
> >> > Jasper,
> >> >
> >> > I've tried using token. But didn't work. Would you give an
example?
> >> >
> >> > Thanks
> >> >
> >> >
> >> >
> >> > Jasper Smith wrote:
> >> >> Use the [JOBID] job step token. See sp_add_job_step in BOL for
> >> > details. Also
> >> >> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >> http://www.sqldbatips.com
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "tram" <tram_e@.hotmail.com> wrote in message
> >> >> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> >> >> > How can I get the current job's name or job ID from a job
step
> >> > within
> >> >> > the job in sql server?
> >> >> >
> >> >
> >|||Thanks, Tibor. I've tried with sp_add_jobstep, it worked fine.
Initially I've just added it as transact sql command in the Enterprise
manager create job, step 1 and it did give an error.
tram wrote:
> Tibor,
> Thanks for quick reply. In my machine, I was getting invalid column
> name JOBID and STEPID.
> Tibor Karaszi wrote:
> > tram,
> >
> > "Doesn't work" is not a very good problem description. I created a
> job with one jobstep and added
> > below SQL to the jobstep. Work just fine on my machine:
> >
> > SELECT 'The output file name is "' + output_file_name + '"'
> > FROM msdb..sysjobsteps
> > WHERE job_id = [JOBID]
> > AND step_id = [STEPID]
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "tram" <tram_e@.hotmail.com> wrote in message
> > news:1104323486.443287.4420@.z14g2000cwz.googlegroups.com...
> > > Tibor,
> > >
> > > I appreciate your reply. I've seen the examples , but is not
> working. I
> > > was trying to know the JOB id of current job in the job step so
> that I
> > > can get the output file name from sysjobsteps.
> > > Anyway, thanks for your reply.
> > >
> > >
> > > Tibor Karaszi wrote:
> > >> Did you check the samples at the web page that Jasper and me
> posted?
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "tram" <tram_e@.hotmail.com> wrote in message
> > >> news:1104265806.678523.298020@.z14g2000cwz.googlegroups.com...
> > >> > Jasper,
> > >> >
> > >> > I've tried using token. But didn't work. Would you give an
> example?
> > >> >
> > >> > Thanks
> > >> >
> > >> >
> > >> >
> > >> > Jasper Smith wrote:
> > >> >> Use the [JOBID] job step token. See sp_add_job_step in BOL
for
> > >> > details. Also
> > >> >> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
> > >> >>
> > >> >> --
> > >> >> HTH
> > >> >>
> > >> >> Jasper Smith (SQL Server MVP)
> > >> >> http://www.sqldbatips.com
> > >> >> I support PASS - the definitive, global
> > >> >> community for SQL Server professionals -
> > >> >> http://www.sqlpass.org
> > >> >>
> > >> >> "tram" <tram_e@.hotmail.com> wrote in message
> > >> >> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
> > >> >> > How can I get the current job's name or job ID from a job
> step
> > >> > within
> > >> >> > the job in sql server?
> > >> >> >
> > >> >
> > >|||Yes, EM parses the SQL command and it doesn't recognize these tokens. Just ignore the warning from
EM and all is fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"tram" <tram_e@.hotmail.com> wrote in message
news:1104328701.411855.87570@.z14g2000cwz.googlegroups.com...
> Thanks, Tibor. I've tried with sp_add_jobstep, it worked fine.
> Initially I've just added it as transact sql command in the Enterprise
> manager create job, step 1 and it did give an error.
> tram wrote:
>> Tibor,
>> Thanks for quick reply. In my machine, I was getting invalid column
>> name JOBID and STEPID.
>> Tibor Karaszi wrote:
>> > tram,
>> >
>> > "Doesn't work" is not a very good problem description. I created a
>> job with one jobstep and added
>> > below SQL to the jobstep. Work just fine on my machine:
>> >
>> > SELECT 'The output file name is "' + output_file_name + '"'
>> > FROM msdb..sysjobsteps
>> > WHERE job_id = [JOBID]
>> > AND step_id = [STEPID]
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > http://www.sqlug.se/
>> >
>> >
>> > "tram" <tram_e@.hotmail.com> wrote in message
>> > news:1104323486.443287.4420@.z14g2000cwz.googlegroups.com...
>> > > Tibor,
>> > >
>> > > I appreciate your reply. I've seen the examples , but is not
>> working. I
>> > > was trying to know the JOB id of current job in the job step so
>> that I
>> > > can get the output file name from sysjobsteps.
>> > > Anyway, thanks for your reply.
>> > >
>> > >
>> > > Tibor Karaszi wrote:
>> > >> Did you check the samples at the web page that Jasper and me
>> posted?
>> > >>
>> > >> --
>> > >> Tibor Karaszi, SQL Server MVP
>> > >> http://www.karaszi.com/sqlserver/default.asp
>> > >> http://www.solidqualitylearning.com/
>> > >> http://www.sqlug.se/
>> > >>
>> > >>
>> > >> "tram" <tram_e@.hotmail.com> wrote in message
>> > >> news:1104265806.678523.298020@.z14g2000cwz.googlegroups.com...
>> > >> > Jasper,
>> > >> >
>> > >> > I've tried using token. But didn't work. Would you give an
>> example?
>> > >> >
>> > >> > Thanks
>> > >> >
>> > >> >
>> > >> >
>> > >> > Jasper Smith wrote:
>> > >> >> Use the [JOBID] job step token. See sp_add_job_step in BOL
> for
>> > >> > details. Also
>> > >> >> look at http://sqldev.net/sqlagent/SQLAgentStepTokens.htm
>> > >> >>
>> > >> >> --
>> > >> >> HTH
>> > >> >>
>> > >> >> Jasper Smith (SQL Server MVP)
>> > >> >> http://www.sqldbatips.com
>> > >> >> I support PASS - the definitive, global
>> > >> >> community for SQL Server professionals -
>> > >> >> http://www.sqlpass.org
>> > >> >>
>> > >> >> "tram" <tram_e@.hotmail.com> wrote in message
>> > >> >> news:1104250803.810521.121410@.z14g2000cwz.googlegroups.com...
>> > >> >> > How can I get the current job's name or job ID from a job
>> step
>> > >> > within
>> > >> >> > the job in sql server?
>> > >> >> >
>> > >> >
>> > >
>

Monday, March 26, 2012

How can I programatically run the snapshot agent?

Friday, March 9, 2012

How can i insert a default row if it doenst exists already?

Friday, February 24, 2012

How can I get the current job's name or job ID from a job step within the job

How can I get the current job's name or job ID from a job step within the job

How can I get the current job's name or job ID from a job step within the job

Back devices

Blog Archive

About Me