How can I reduce the error of SQL query in R?

Question

Here is my function

         getSQL <- function(server="server name", database="database name", Uid=" 
        user name", Pwd="password", Query){
        conlink <- paste('driver={SQL Server};server=', server,';database=',database,';Uid=', Uid, 
        ';Pwd=', Pwd,';Encrypt=True;TrustServerCertificate=False', sep="")
        conn <- odbcDriverConnect(conlink)
       dat <- sqlQuery(channel= conn, Query, stringsAsFactors = F)
       odbcCloseAll()
       return(dat)
           }

When I call the function using

  query.cut = "SELECT [measurename] 
       ,[OrgType]
     ,[year_session]
       ,[Star]
      ,[cutvalue]
      ,[Date]
      ,[File]
       FROM [database name].[dbo].[DST_Merged_Cutpoint]
       ORDER BY [year_session] DESC
          " 
        getSQL(Query=query.cut)

I get this error:

Error in sqlQuery(conn, Query, stringsAsFactors = F) :
first argument is not an open RODBC channel

In addition: Warning messages:
1: In odbcDriverConnect(conlink) :
[RODBC] ERROR: state 28000, code 18456, message [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ' insightm8'.
2: In odbcDriverConnect(conlink) :
[RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
3: In odbcDriverConnect(conlink) :
Error in sqlQuery(conn, Query, stringsAsFactors = F) :
first argument is not an open RODBC channel

How can I fix these errors? Thanks in advance

Great @Waldi, it works. But I just think about how much foolish I am! — SORIF HOSSAIN

Waldi Waldi · Accepted Answer · 2021-03-15T11:21:29

Take care not to add spaces to UID:

Server]Login failed for user ' insightm8'.

Reproducing this on an SQL Server connection creates the same error.

Try using paste0 instead of paste :

conlink <- paste0('driver={SQL Server};server=', server,';database=',database,';Uid=', Uid, 
        ';Pwd=', Pwd,';Encrypt=True;TrustServerCertificate=False', sep="")

How can I reduce the error of SQL query in R?

1 Answers