Search code examples
sqlsql-serverrrodbc

Execute SQL-functions in R, Microsoft SQL Server

I have a number of functions written on our Microsoft SQL servers.

I can easily access and query all data normally, but I cannot execute functions on the server using RODBC.

How can I execute sql-functions using R? Are there other packages that can do this?

Or do I need to switch strategies completely?

Example: 

require(RODBC)
db <- odbcConnect("db")

df <- sqlQuery(channel = db, query = "USE [Prognosis] 
GO 
SELECT * FROM [dbo].[Functionname] ("information_variable")
GO" ) 

Error message:
"42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'."                                                                                                               
[2] "[RODBC] ERROR: Could not SQLExecDirect 'USE... "
Solution

  • This turned out to work: 

    df <- sqlQuery(channel = db, 
query = "SELECT * FROM [dbo].[Functionname] ("information_variable")" )

    So I dropped USE [The_SQL_TABLE] and GO