Problem scheduling processing

When I manually run the IS packages to load the data warehouse and then process the dimensions and cubes, everything works fine.

When I schedule the packages as jobs, eventually the data goes wrong. The jobs are scheduled in the correct order.

Any ideas?

Thanks.

If I get your description right, the SSIS packages are not executed in the correct order. Is that so?

For one you can use serveral other ways to process your dimensions and cubes: You can use ascmd command line utility to process exactly the cubes and dimensions. Then wou can schedule a SQL agent job to run ascmd.

You can also use SQL Agent jobs directly to send processing commands to Analysis Serivces.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

eventually the data goes wrong.

What do you mean by "goes wrong" is it possible that it is just not updated, or not fully updated?

One common trap when scheduling jobs is that usually the scheduled instance runs under a different user account. You should double check that this account has all the appropriate priviledges. Sometimes logging on to your workstation with the same account that the SQL Agent is running under and running the packages interactively can help to highlight any issues. You might find things like network drive mappings are not the same, or privileges to access other databases are not the same your own account, or even that the account in question does not have rights to process the SSAS database.

Problem of running sp through VB

Hi,
I have a sp running well manually in SQL analyzer. This sp is using BCP to
archive data from SQL tables to datafiles.
The problem is if the sp is run through VB and a table has lot of data, the
process will stop after exec bcp(datafile is created) and doesn't go through
other part of the sp. VB part doesn't get any error. If all the tables dealt
with don't have too many data, it works well.
Any suggesting will be appreciatedYou might want to increase your CommandTimeout to greater than 30 seconds.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:7E219AF2-2036-4D10-8FCD-029D22A9B367@.microsoft.com...
> Hi,
> I have a sp running well manually in SQL analyzer. This sp is using BCP to
> archive data from SQL tables to datafiles.
> The problem is if the sp is run through VB and a table has lot of data,
> the
> process will stop after exec bcp(datafile is created) and doesn't go
> through
> other part of the sp. VB part doesn't get any error. If all the tables
> dealt
> with don't have too many data, it works well.
> Any suggesting will be appreciated|||I set CommandTimeout = 0. If it's timeout error, VB will get error message,
right?
"oj" wrote:

> You might want to increase your CommandTimeout to greater than 30 seconds.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:7E219AF2-2036-4D10-8FCD-029D22A9B367@.microsoft.com...
>
>|||Yes. "0" means indefinitely. Also, be aware that SQL will wait indefinitely
for any external calls via xp_cmdshell until it returns. If there is
something wrong at the system level and xp_cmdshell hangs, SQL would not
know about it.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E7298BD1-4236-42CC-9245-34BBA05FA5B0@.microsoft.com...
>I set CommandTimeout = 0. If it's timeout error, VB will get error message,
> right?
> "oj" wrote:
>|||xp_cmdshell works fine because datafile was created. However it stop after
that without any error.
"oj" wrote:

> Yes. "0" means indefinitely. Also, be aware that SQL will wait indefinitel
y
> for any external calls via xp_cmdshell until it returns. If there is
> something wrong at the system level and xp_cmdshell hangs, SQL would not
> know about it.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:E7298BD1-4236-42CC-9245-34BBA05FA5B0@.microsoft.com...
>
>|||Can you use BULK INSERT instead BCP?
AMB
"Tom" wrote:
> xp_cmdshell works fine because datafile was created. However it stop after
> that without any error.
> "oj" wrote:
>|||Tom,
Try downloading ntpmon/ntfilmon from sysinternals to see what's going. Also,
use "-e" flag of bcp to capture any eror.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:4BCB7A0C-25F7-4FEE-9495-09D58D796E5F@.microsoft.com...
> xp_cmdshell works fine because datafile was created. However it stop after
> that without any error.
>|||Can I use BULK INSERT to copy data from SQL table to datafile?
"Alejandro Mesa" wrote:
> Can you use BULK INSERT instead BCP?
>
> AMB
> "Tom" wrote:
>|||Sorry, thought you were importing.
AMB
"Tom" wrote:
> Can I use BULK INSERT to copy data from SQL table to datafile?
> "Alejandro Mesa" wrote:
>|||There is no any error in error file. I wander if there is any difference
between running a sp in SQL query analyzer and through VB.
"oj" wrote:

> Tom,
> Try downloading ntpmon/ntfilmon from sysinternals to see what's going. Als
o,
> use "-e" flag of bcp to capture any eror.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:4BCB7A0C-25F7-4FEE-9495-09D58D796E5F@.microsoft.com...
>
>

Problem Jobs with send mails

hello people,
i have a stored procedure that send mails masive to users, this stored
procedures succesfully execute manually, but programming job for execute
this stored procedurre not send mail, but histoyr job its succesfully.
what its happening?Hi Jesus
You don't say how you are sending the emails, but if the procedure does not
raise an error and only returns an status code then you would need to check
this status code asnd raise your own error to make the step fail.
HTH
John
"Jesus Suarez" wrote:

> hello people,
> i have a stored procedure that send mails masive to users, this stored
> procedures succesfully execute manually, but programming job for execute
> this stored procedurre not send mail, but histoyr job its succesfully.
> what its happening?
>
>|||hello
send mail using xpsmtp, but i dont undertand you, the storede procedure not
error, only if execute this stored procedured using jobs
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...[vbcol=seagreen]
> Hi Jesus
> You don't say how you are sending the emails, but if the procedure does
> not
> raise an error and only returns an status code then you would need to
> check
> this status code asnd raise your own error to make the step fail.
> HTH
> John
> "Jesus Suarez" wrote:
>|||Did you specify an output file for the job step and check for messages in th
at output file?
According to http://sqldev.net/xp/xpsmtp.htm, any error from xp_smtp_sendmai
l should return a return
code, also accompanied by an error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jesus Suarez" <suarez.lopezjesus@.gmail.com> wrote in message
news:%230LivVP$GHA.4980@.TK2MSFTNGP04.phx.gbl...
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure no
t error, only if execute
> this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>|||Hi Jesus
xp_smtp_sendmail will return 1 if there is a problem, you may want to create
a test job that just sends a test email and selects the return code.
e.g.
DECLARE @.rc int
EXEC @.rc = xp_smtp_sendmail....
SELECT @.rc
Add a log file in the advanced tab of the step so that you have a copy of
the output from the step, this may give you some indication of any error. Yo
u
may also need to change the SQL Agent service account to be a domain account
if network access is an issue.
John
"Jesus Suarez" wrote:

> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure no
t
> error, only if execute this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>
>

Problem Jobs with send mails

hello people,
i have a stored procedure that send mails masive to users, this stored
procedures succesfully execute manually, but programming job for execute
this stored procedurre not send mail, but histoyr job its succesfully.
what its happening?Hi Jesus
You don't say how you are sending the emails, but if the procedure does not
raise an error and only returns an status code then you would need to check
this status code asnd raise your own error to make the step fail.
HTH
John
"Jesus Suarez" wrote:
> hello people,
> i have a stored procedure that send mails masive to users, this stored
> procedures succesfully execute manually, but programming job for execute
> this stored procedurre not send mail, but histoyr job its succesfully.
> what its happening?
>
>|||hello
send mail using xpsmtp, but i dont undertand you, the storede procedure not
error, only if execute this stored procedured using jobs
"John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
> Hi Jesus
> You don't say how you are sending the emails, but if the procedure does
> not
> raise an error and only returns an status code then you would need to
> check
> this status code asnd raise your own error to make the step fail.
> HTH
> John
> "Jesus Suarez" wrote:
>> hello people,
>> i have a stored procedure that send mails masive to users, this stored
>> procedures succesfully execute manually, but programming job for execute
>> this stored procedurre not send mail, but histoyr job its succesfully.
>> what its happening?
>>|||Did you specify an output file for the job step and check for messages in that output file?
According to http://sqldev.net/xp/xpsmtp.htm, any error from xp_smtp_sendmail should return a return
code, also accompanied by an error message.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jesus Suarez" <suarez.lopezjesus@.gmail.com> wrote in message
news:%230LivVP$GHA.4980@.TK2MSFTNGP04.phx.gbl...
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure not error, only if execute
> this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>> Hi Jesus
>> You don't say how you are sending the emails, but if the procedure does not
>> raise an error and only returns an status code then you would need to check
>> this status code asnd raise your own error to make the step fail.
>> HTH
>> John
>> "Jesus Suarez" wrote:
>> hello people,
>> i have a stored procedure that send mails masive to users, this stored
>> procedures succesfully execute manually, but programming job for execute
>> this stored procedurre not send mail, but histoyr job its succesfully.
>> what its happening?
>>
>|||Hi Jesus
xp_smtp_sendmail will return 1 if there is a problem, you may want to create
a test job that just sends a test email and selects the return code.
e.g.
DECLARE @.rc int
EXEC @.rc = xp_smtp_sendmail....
SELECT @.rc
Add a log file in the advanced tab of the step so that you have a copy of
the output from the step, this may give you some indication of any error. You
may also need to change the SQL Agent service account to be a domain account
if network access is an issue.
John
"Jesus Suarez" wrote:
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure not
> error, only if execute this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
> > Hi Jesus
> >
> > You don't say how you are sending the emails, but if the procedure does
> > not
> > raise an error and only returns an status code then you would need to
> > check
> > this status code asnd raise your own error to make the step fail.
> >
> > HTH
> >
> > John
> >
> > "Jesus Suarez" wrote:
> >
> >> hello people,
> >>
> >> i have a stored procedure that send mails masive to users, this stored
> >> procedures succesfully execute manually, but programming job for execute
> >> this stored procedurre not send mail, but histoyr job its succesfully.
> >>
> >> what its happening?
> >>
> >>
> >>
>
>