Some kind of permissions on stored procedure?
-
Hi,
I have a problem with my application (ACCESS 2000 front end and SQL server backend) where I can generate a report (I'm the one developed the report) but not other developers/users.
The report recordsource is set to a stored procedure in SQL server via pass through query.
The stored procedure contains three set of EXEC command to insert values into each local temporary table. There are no permissions or anything set on the SP nor temp tables.
My application will create the temp tables automatically when it doesnt exists. I have tried to drop those tables so the application will create a fresh one when other developers run it. But still no luck.
The funny thing is it runs properly if I run SP codes (copy and paste the codes inside the SP) in Query Analyzer.
But if I call the SP using Query Analyzer, it wouldnt work.
Is there anything I might have missed? This is the first time I use SP for my application.
-
In what way 'does it not work' for others than you? Errors? Messages?
Who owns the proc? You or dbo?
In any case 'others' must have execute granted to them on the proc, else they'd be denied execute on it
/Kenneth
-
It shows me some records in the report but others received "No record available" message. Which means that the code that inserts records into the temp tables doesnt work.
I placed some print statements in the SP, it prints them without error messages but no records inserted.
dbo is the owner of the SP.
Any other SP or views used by this SP are owned by dbo.
Here is part of the SP codes.
-- Create a unique ID for this process
SET @uid = CONVERT (VARCHAR(255),NEWID())
SELECT @uid
-- Ensure there are no records with the newly generated ID in these tables
DELETE TempSummaryReport_Referrals WHERE uid = @uid
DELETE TempSummaryReport_Completed WHERE uid = @uid
DELETE TempSummaryReport_HomeVisit WHERE uid = @uid
print 'AAAA'
SET @sSQL = 'SELECT ''' + @uid + ''', getdate() , vwReportReferrals.PRRefByID,
([EmpLstNam] + '', '' + [EmpFstNam]) AS SignedBy,
dbo.getWeekNumber([PRRefDat]) AS Week,
CAST(dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat)) AS VARCHAR(11)) + '' - '' + CAST(dateadd(day,4,dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat))) AS VARCHAR(11)) AS RangeDt,
Count(vwReportReferrals.PRProgRefID) AS Total_Referrals
FROM vwReportReferrals INNER JOIN tblEmployee
ON vwReportReferrals.PRRefByID = tblEmployee.EmpID
WHERE (((DateDiff(day,[vwReportReferrals].[PRRefDat],''' + @startDate + '''))0))
AND PRProgRefID In (''3'',''6'',''9'',''11'')
GROUP BY
vwReportReferrals.PRRefByID,
([EmpLstNam] + '', '' + [EmpFstNam]),
dbo.getWeekNumber([PRRefDat]),
CAST(dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat)) AS VARCHAR(11)) + '' - '' + CAST(dateadd(day,4,dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat))) AS VARCHAR(11))'
SET DATEFIRST 1
INSERT TempSummaryReport_Referrals (UID,[Date], PRRefByID, SignedBy, [Week],RangeDt, Total_Referrals, [3],[6],[9],[11])
EXECUTE CrossTab @sSQL, 'Count(PRProgRefID)','PRProgRefID','',' AND PRProgRefID In (''3'',''6'',''9'',''11'')', 'vwReportReferrals' , ''
-
Hi,
My first thought is that you have all the necessary permissions in the temp table, but your other users don't. As a first port of call, that's what I'd check.
All Best
Conway
-
Hi Conway and Ken
Thanks for your input.
I finally found the cause of the problem.
It was as simple as I parse in date in 10 characters long and when parsed in through the SP, it truncate it to varchar(8). oppps.
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply