I'm facing a strange issue when I'm trying to connect my RStudio
environment to SQL Server
.
CASE 1
I execute the following commands and they tend to work fine:
dbhandleRSREPORTINGV1 <- odbcDriverConnect('driver={SQL Server};server=RS-REPORTING-V1;database=Loyalty;trusted_connection=true')
badData <- sqlQuery(dbhandleRSREPORTINGV1, paste("select * from loyalty where loyaltyplusstartdate = '10/1/2014'"))
goodData <- sqlQuery(dbhandleRSREPORTINGV1, paste("select * from loyalty where loyaltyplusstartdate <> '10/1/2014'"))
CASE 2
When I execute the following command, I get a logon error:
dbhandleRSREPORTINGV1 <- odbcDriverConnect('driver={SQL Server};server=RS-REPORTING-V1;database=Loyalty;trusted_connection=true')
goodDataSixMonthBeforeAfter <- sqlQuery(dbhandleRSREPORTINGV1, paste("select
l.email,
(sum(ISNULL(o.Revenue,0))) as Revenue,
((DATEDIFF(day, l.loyaltyplusstartdate, o.created) + 179) / 30) - 6 as DeltaMonth
from [<linked_server>].<database_name>.dbo.ORDERS o
join (select distinct email,loyaltyplusstartdate,created from loyalty where loyaltyplusstartdate <> '10/1/2014' and loyaltyplusstartdate < '4/1/2015' group by email,loyaltyplusstartdate,created) l
on o.contactemail = l.email
where o.created
between DATEADD(day, -179, l.loyaltyplusstartdate) and DATEADD(day, 180, l.loyaltyplusstartdate)
group by
l.email,l.loyaltyplusstartdate,
((DATEDIFF(day, l.loyaltyplusstartdate, o.created) + 179) / 30) - 6"))
Here's the error that I get:
[1] "28000 18456 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select \nl.email,\n(sum(ISNULL(o.Revenue,0))) as Revenue,\n((DATEDIFF(day, l.loyaltyplusstartdate, o.created) + 179) / 30) - 6 as DeltaMonth\nfrom [<linked_server>].<database_name>.dbo.ORDERS o\njoin (select distinct email,loyaltyplusstartdate,created from loyalty where loyaltyplusstartdate <> '10/1/2014' and loyaltyplusstartdate < '4/1/2015' group by email,loyaltyplusstartdate,created) l\non o.contactemail = l.email\nwhere o.created\nbetween DATEADD(day, -179, l.loyaltyplusstartdate) and DATEADD(day, 180, l.loyaltyplusstartdate)\ngroup by \nl.email,l.loyaltyplusstartdate, \n((DATEDIFF(day, l.loyaltyplusstartdate, o.created) + 179) / 30) - 6'"
This is really strange as all I am changing from Case 1 to Case 2 is the query. I have executed the query separately and it works perfectly fine. Then why the logon error when tried from RStudio
?
Any help would be greatly appreciated.
UPDATE
As per suggestion from @nrussell, I executed the following command, but I still get the same error:
goodDataSixMonthBeforeAfter <- sqlQuery(dbhandleRSREPORTINGV1, gsub("\\n", " ", paste("select
l.email,
(sum(ISNULL(o.Revenue,0))) as Revenue,
((DATEDIFF(day, l.loyaltyplusstartdate, o.created) + 179) / 30) - 6 as DeltaMonth
from [10.24.50.182].OneLegalDataAnalysis.dbo.ORDERS o
join (select distinct email,loyaltyplusstartdate,created from loyalty where loyaltyplusstartdate <> '10/1/2014' and loyaltyplusstartdate < '4/1/2015' group by email,loyaltyplusstartdate,created) l
on o.contactemail = l.email
where o.created
between DATEADD(day, -179, l.loyaltyplusstartdate) and DATEADD(day, 180, l.loyaltyplusstartdate)
group by
l.email,l.loyaltyplusstartdate,
((DATEDIFF(day, l.loyaltyplusstartdate, o.created) + 179) / 30) - 6")))