0

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")))
Patthebug
  • 3,933
  • 8
  • 39
  • 77
  • Most likely the newline characters are causing the problem; try using `gsub("\\n, "", paste(...))`. – nrussell Oct 05 '15 at 18:57
  • Thanks for your suggestion, I have updated the question. I still get the same error. I have tried executing similar queries (numerous times) in the past from `RStudio` on different databases and I've never had such a problem (I've never had to use `gsub` to replace `\n`). In other words, I don't think '\n' would be a problem. – Patthebug Oct 05 '15 at 19:06
  • This works fine for me: library(RODBC) dbconnection – ASH Oct 10 '15 at 16:27
  • See this for additional info: http://stackoverflow.com/questions/15420999/rodbc-odbcdriverconnect-connection-error – ASH Oct 10 '15 at 16:28

0 Answers0