February 25, 2019 at 1:49 am
Hi Experts,
I am running a query below in ssms and it returns data,but when i put the same query on stored proc it returns nothing.The query must return yesterday's data
Please help
Create proc dbo.p_RS_Created_Bonuses
AS(
SELECT u.UserLoginName as Admin
, p.UsAccountNo as CreditedTo
, p.Currency_Code as Currency
, b.BonusName as Bonus
, f.Orig_Currency_Comp_Amount as BonusAmt
, f.Comp_Amount AS WageringAmt
, f.Orig_Comp_Date as CreatedAt
FROM DW.dbo.Fact_Comps AS f WITH (NOLOCK)
INNER JOIN DW.dbo.Dim_User AS u WITH (NOLOCK) ON u.UserSK = f.UserSK
INNER JOIN DW.dbo.Dim_Player AS p WITH (NOLOCK) ON p.Acct_Surr_Key = f.Acct_Surr_Key
INNER JOIN DW.dbo.Dim_Bonus AS b WITH (NOLOCK) ON b.BonusSK = f.BonusSK
WHERE f.Orig_Comp_Date >= CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, -1, SYSDATETIME())))
AND f.Orig_Comp_Date < CONVERT(DATETIME, CONVERT(DATE, SYSDATETIME()))
Many thanks
TS
February 25, 2019 at 2:49 am
The only reason the query would return a different result is if the query isn't the same, the permissions differ (meaning the user running the report can't see the data),or you're not connecting to the same instance/database.
When you say in SSRS, do you mean a deployed report or within Visual Studio? If the latter, have you report cached the data and you haven't cleared it? If the former, I suggest having a look at a trace and extended event and find out what SQL the report is really running; you'll very find it isn't the same.
As a final question, any reason for all the NOLOCKs?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 25, 2019 at 6:57 am
Thanks so much
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply