Sql Server Agent producing different results then execution of a sp

Question

Post reply

Sql Server Agent producing different results then execution of a sp

debrarowens

Mr or Mrs. 500

Points: 503
More actions
September 25, 2011 at 12:09 am

#244396

sql 2008
The sp has been running via a scheduled agent job for several years with out an issue as of this week it's producing incorrect numbers.
When the sp is ran via Query analyzer it works. I've tried coping the code in the agent with same results. I've tried putting the sp in an SSIS package this works however when I place the package in a sql server job it produces the wrong totals.
Does any one have a clue, I'm pulling my hair out?
Query - works 'execute dbo.dwspNetWkAmts'
In agent create a job - does not work
in properties type Transact-sql script (t-sql) 'execute dbo.dwspNetWkAmts'

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Usually, stuff like this is related to missing permissions, e.g. access to the file system.

But without knowing what the sproc and SSIS package do, it's hard to tell.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

debrarowens Mr or Mrs. 500 Points: 503 More actions · Answer 2

Lutz thanks for your time.

The job owner is sa, so that cannot be the issue.

I'll post the code and sample of data.

The sp should calculate AcDedAmtIn as:

MaID Lob AcDedAmtIn

207223018A 343.68

Table 1 dbo.dw_MemAccum_DedBen_Ac

Using one example where MaId= '207223' (prior years removed as they are not relevant).

MaIdSeqNoDedBenAcDedYrAcDedAmtAcMetFlg

207223 3718MD1101.20100.00 1

207223 3818MD1102.20100.00 1

207223 3918MD1103.2010 350.00 1

207223 4018MD1104.20100.00 1

207223 4118MD1105.20100.00 1

207223 4218MD1106.20100.00 1

207223 4318MD1107.20100.00 1

207223 4418MD1108.20100.00 1

207223 4518MD1109.20100.00 1

207223 4618MD1110.20100.00 1

207223 4718MD1111.20100.00 1

207223 4818MD1112.20100.00 1

207223 118MD2101.20100.00 NULL

207223 218MD2102.20100.00 NULL

207223 318MD2103.2010 277.20 NULL

207223 418MD2104.20100.00 NULL

207223 518MD2105.20100.00 NULL

207223 618MD2106.20100.00 NULL

207223 718MD2107.20100.00 NULL

207223 818MD2108.20100.00 NULL

207223 918MD2109.20100.00 NULL

207223 1018MD2110.20100.00 NULL

207223 1118MD2111.20100.00 NULL

207223 1218MD2112.20100.00 NULL

207223 7718MDE101.20110.00 NULL

207223 7818MDE102.20110.00 NULL

207223 7918MDE103.20110.00 NULL

207223 7618MDI101.20110.00 NULL

207223 7318MDI201.2011 271.14 NULL

207223 7418MDI202.20110.00 NULL

207223 7518MDI203.2011 72.54 NULL

Table 2 dw_v_LaserLob

BenCodeLOBBenNetWBenTypecoded

18MD11018A I DED1

18MD21018A I DED1

18MDE1018A I DED1

18MDI1018A I DED1

18MDI2018A I DED1

SP:

UPDATE dbo.dw_Benefit_Limit_Ded_Netwk

SET AcDedAmtIN =

(SELECT COALESCE(SUM(AcDedAmt),0.00)

FROM dbo.dw_MemAccum_DedBen_Ac MD (NOLOCK)

INNER JOIN dbo.dw_v_LaserLob LL ON MD.DedBen = LL.BenCode

AND (LL.BenNetW ='OT' or LL.BenNetW ='I') AND (LL.BenType = 'DED')

WHERE dbo.dw_Benefit_Limit_Ded_Netwk.MaID = MD.MAID AND dbo.dw_Benefit_Limit_Ded_Netwk.lob = LL.Lob

AND ((LL.DedFamTp = 'AC' AND CAST(right(MD.AcDedYr,4)AS NUMERIC )=YEAR(GETDATE()) and LL.CoDed = '1')

OR(LL.DedFamTp <> 'AC' AND CAST(right(MD.AcDedYr,4)AS NUMERIC )=(YEAR(GETDATE())-1) and LL.CoDed = '1'

AND CAST(left(MD.AcDedYr,2)AS NUMERIC) > 9 ))

HAVING SUM(AcDedAmt) > 0)

debrarowens Mr or Mrs. 500 Points: 503 More actions · Answer 3

debrarowens

Mr or Mrs. 500

Points: 503

September 25, 2011 at 6:04 pm

#1386255

Does anyone have a suggestion?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 4

Yep... if the privs are the same and the code is the same, there's only one thing left... the data.

Check and make double sure that the job is running against both the same database and on the same server as your "manual" attempts. It's a very common problem where there's a Dev, UAT, and Prod environment for people to simply be pointed to the wrong place. Assume nothing here... carefully double check.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)