Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedure
Didn't want it to fall to far - anyone have ideas?
|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?
|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:

>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure
sql

Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedureDidn't want it to fall to far - anyone have ideas?|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:

>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure

Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedureDidn't want it to fall to far - anyone have ideas?|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:
>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure

Calling an .EXE from a SQL Job?

I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?
One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:

> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:

> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calling an .EXE from a SQL Job?

I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:

> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EX
E
> from the command line and it works but I don't know how to call it from wi
th
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by Use
r
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:

> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calling an .EXE from a SQL Job?

I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> > I'm trying to call a .exe as a Job Step that pulls some data and
> > sends out custom emails that will execute after a DTS package runs.
> > I tested the EXE from the command line and it works but I don't know
> > how to call it from with the Job Step.
> >
> > I have the step called ''Example of calling an EXE" and set an
> > Operating System Command (CmdExec) with:
> >
> > xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> >
> > When it runs, I check the Windows event and it says:
> >
> > SQL Server Scheduled Job 'Example of calling an EXE'
> > (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> > 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> > by User sa. The last step to run was step 1 (Calling the EXE).
> >
> > I have no idea why it's failing when called as a job?
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calculating Luhn Digit in Crystal Reports

I have an 18 digit number and i need a SQL formula in Crystal Reports to calculate the Luhn digit.

Below is how Luhn is calculated.

Step 1: Double the value of alternate digits beginning with the first right hand digit.

e.g 8 6 3 7 5 = 8 12 3 14 5

Step 2: Add the individual digits compromising the products obtained in Step 1 to each of the unaffected digits in the original number.

e.g 8 12 3 14 5 = 8+1+2+3+1+4+5 (total=24)

Step 3: Subtract the total obtained in Step 2 from the next higher number ending in 0.

e.g 24 = 30-24 = 6 (Luhn = 6!)

Any help here would be fantastic!!This should do it...

Local StringVar sValue := {test.NUMBER};

Local NumberVar nSubtotal;
Local NumberVar i;

For i := (Length(sValue) - 1) To 1 Step -2 Do
(nSubtotal := nSubtotal + CDbl(sValue[i]));

For i := Length(sValue) To 1 Step -2 Do
nSubtotal := nSubTotal + (If CDbl(sValue[i]) < 5
Then CDbl(sValue[i]) * 2
Else
CDbl(sValue[i]) - (9 - CDbl(sValue[i])));

nSubtotal Mod 10;