All tables involved are select, update, delete, insert granted. I am trying to execute a procedure and getting that error.
I used odbcDriverConnect because I guess any connection parameter will solve my problem (I tried trusted_connection and autocommit).
cc = odbcDriverConnect(connection = paste0('DRIVER={SQL Server};SERVER=localhost;DATABASE=XX;UID=user;PWD=pass;autocommit=True;trusted_connection=true'))
rst = sqlQuery(cc, 'exec dbo.usp_mm_xx')
print(rst)
[1] "[RODBC] ERROR: Could not SQLExecDirect 'exec dbo.usp_mm_xx'"
If I retire the insert from the procedure it runs ok.
I get the same error when I try to run the insert.
query=paste0('insert INTO dbo.X select * FROM dbo.Y a ',
'where not exists (select 1 from dbo.X b where a.c1=b.c1 and a.c2=b.c2)')
rst=sqlQuery(cc, query)
print(rst)
[1] "[RODBC] ERROR: Could not SQLExecDirect 'insert INTO dbo.X SELECT * FROM dbo.Y a where not exists (select 1 from dbo.X b where a.c1=b.c2 and a.c2=b.c2)'"
The SQL statement runs ok on database, with same user.
Your procedure needs the instruction "Set nocount on" in its first line.
The insert instruction inside the procedures has a result, a row count, that is returned to the client. When the insert appears before a select instruction, we are, for sure, expecting the select result in the client, however the client receives multiple results and the insert row count comes first.
Usually, when this happens, we receive empty resultsets. It appears RODBC is having a bigger trouble with multiple resultsets.