October 23, 2009 at 8:29 am
I have a stored procedure which is working fine, it's quite simple, calculating lots of number and then insert them into a table.
I need the sp to run every day, so I created/scheduled a maintenance plan and add a "Execute T-SQL Statement Task" in the plan, that's the only thing in the plan.
The problem is: although everything works fine when just run the sp, but the plan will insert 0 to all the fields in the table.
I don't know what to check: I already gave insert and select permission to the table.
Please help........this is crazy!
The content of my "Execute T-SQL Statement Task" in my maintenance plan is:
use Pergamum
GO
exec dbo.spUpdateSummaryStatistic
GO
For your convenient, here is my sp: I doubt the problem is with my code because it is working when I execute it.
ALTER proc [dbo].[spUpdateSummaryStatistic]
AS
declare @total int
declare @component int
declare @obsolete int
declare @active int
declare @infrastructure int
declare @application int
declare @notassigned int
declare @assigned int
declare @notclassified int
declare @classified int
declare @nonsox int
declare @sox int
declare @withServerInfo int
declare @withnotServerInfo int --This is the number that you can see in Reporting Services' data, but not included in AOR's infosys table, neither in EPM
select @total = count(1) from infosys where dbo.fnUserAccess(SysID) = 1--total
select @obsolete = count(1) from infosys where status = 'retired' and dbo.fnUserAccess(SysID) = 1--obsolete
select @active = count(1) from infosys where status = 'active' and dbo.fnUserAccess(SysID) = 1--active
select @component = count(1) from infosys where status = 'active' and [type] = 'component' and dbo.fnUserAccess(SysID) = 1--component
select @infrastructure = count(1) from infosys where status = 'active' and type = 'Infrastructure' and dbo.fnUserAccess(SysID) = 1--infrastructure
select @application = count(1) from infosys where status = 'active' and type = 'application' and dbo.fnUserAccess(SysID) = 1--application
select @notassigned = count(1) from infosys where status = 'active' and type = 'application' and orgunitid is null and dbo.fnUserAccess(SysID) = 1--not assigned
select @assigned = count(1) from infosys where status = 'active' and type = 'application' and orgunitid is not null and dbo.fnUserAccess(SysID) = 1 --assigned
select @notclassified = count(1) from infosys where status = 'active' and type = 'application' and orgunitid is not null and (integrity = 0 or confidentiality = 0 or availability = 0 or continuity = 0) and dbo.fnUserAccess(SysID) = 1 --not classified
select @classified = count(1) from infosys where status = 'active' and type = 'application' and orgunitid is not null and (integrity <> 0 and confidentiality <> 0 and availability <> 0 and continuity <> 0) and dbo.fnUserAccess(SysID) = 1--classified
select @nonsox = count(1) from infosys where status = 'active' and type = 'application' and orgunitid is not null and (integrity <> 0 and confidentiality <> 0 and availability <> 0 and continuity <> 0) and sox = 0 and dbo.fnUserAccess(SysID) = 1--non sox
select @sox = count(1) from infosys where status = 'active' and type = 'application' and orgunitid is not null and (integrity <> 0 and confidentiality <> 0 and availability <> 0 and continuity <> 0) and sox = 1 and dbo.fnUserAccess(SysID) = 1--sox
--select @in_infosys = count(distinct [appl id]) from mappedserverwithip where [appl id] not in (select code from infosys)
--select @not_in_infosys = count(distinct [appl id]) from mappedserverwithip where [appl id] not in (select code from infosys)
select @withServerInfo = count(1) from infosys where code in (select distinct [appl id] as code
from mappedserverwithip) and code <> '' and type = 'application' and status = 'active' and dbo.fnUserAccess(SysID) = 1
set @withnotServerInfo = @application - @withServerInfo
insert into Summary
(Total,
obsolete,
active,
component,
Infrastructure,
[Application],
notassigned,
assigned,
notclassified,
classified,
nonsox,
sox,
withServerInfo,
withnotServerInfo
)
values(@total,@obsolete,
@active, @component, @infrastructure, @application ,
@notassigned, @assigned, @notclassified,
@classified, @nonsox, @sox,
@withServerInfo,
@withnotServerInfo)
October 23, 2009 at 8:30 am
What does the proc do if you run it outside the job?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 8:36 am
Thanks for quick response.
the sp will calculate correct number and insert them into the table Summary.
I don't have any problem running the sp outside the maintenance plan.
October 23, 2009 at 8:41 am
Then it's almost certainly something to do with the UDF dbo.fnUserAccess.
Guessing by the name, that function will return a 1 or a 0 based on user permissions. It's possible that the account the maintenance plan job is running under returns a 0. If so, then you'd get a count of 0 for all of those, because of the Where clause.
Whatever account the job is running under, try setting a connection to that account, and selecting the UDF. If it doesn't come back as a 1, then that's why you're getting counts of 0.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 8:59 am
Good catch!
However, I checked the owner of the job in the maintenance plan, it is me and it's dbo and it has access to the fn.
October 23, 2009 at 9:02 am
I also just changed the owner to sa, it returns same 0 to all number - that's not right.
October 23, 2009 at 9:52 am
Have you attempted to use EXECUTE AS and impersonating the execution context of the job to see what results you get?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
October 23, 2009 at 9:55 am
Are you sure your function is returning one for the SQL Server Agent service account (or proxy account)?
If you remove that function call, does it work?
October 23, 2009 at 1:20 pm
halifaxdal (10/23/2009)
I also just changed the owner to sa, it returns same 0 to all number - that's not right.
The owner of the job is not the user that is going to run that job. The job is going to run in the context of the user that is running SQL Server Agent.
Either make sure the user running Agent has access - or, use EXECUTE AS to set the specific user context.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 23, 2009 at 1:39 pm
halifaxdal (10/23/2009)
Good catch!However, I checked the owner of the job in the maintenance plan, it is me and it's dbo and it has access to the fn.
Change the line in the query to "and 1 = 1", without the function call, and run that. See what that does. (Just take out the function, don't change any of the rest of the Where clause or anything else.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply