ERROR: state IM004 - Issues with odbcDriverConnect in RStudio

[shawn2x4]

Good morning, everyone.

Been running this script for 6+ years now and suddenly it no longer works. Cannot get any help from my IT department, so hoping someone here might have some insights. The issue seems to be around ODBC drivers in RStudio, as I am able to connect to this specific database using tools other than RStudio. Also, I am able to establish connections to other databases within RStudio other than the one that is causing me issues.

Here is the code that was running fine until a week ago:

channel <- odbcDriverConnect(paste("DRIVER={IBM DB2 ODBC DRIVER}",
";Database=database_name",
";Hostname=host_name",
";Port=Port#",
";PROTOCOL=PROTOCOL",
";UID=uID",
";PWD=password123", sep=""))

And here is the error I am receiving:

Warning messages:
1: In odbcDriverConnect(paste("DRIVER={IBM DB2 ODBC DRIVER}", ";Database=database_name", :
[RODBC] ERROR: state IM004, code 0, message [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
2: In odbcDriverConnect(paste("DRIVER={IBM DB2 ODBC DRIVER}", ";Database=database_name", :
ODBC connection failed

Any help would be greatly appreciated. I am a complete novice when it comes to IT-related issues.

[andresrcs]

Does this work on the R GUI but not on RStudio? Because it doesn't seem to be related to RStudio since you are connecting programmatically, maybe your problem is with the odbc R package?

[shawn2x4]

Thank you for the response. How exactly would I test this on the R GUI? Sorry if that is a silly question.

Is there a newer version of the odbc package that needs to be installed? I am running R 4.2.0, btw.

[andresrcs]

@shawn2x4

Thank you for the response. How exactly would I test this on the R GUI?

Open the R GUI, (the one with the R icon) and run your code there, RStudio is an IDE for the R programming language but it is not an R interpreter, the code gets run on R under the hood but with some environment variables loaded.

[shawn2x4]

Thank you for the clarification. I got the same error when running it through the R GUI.

Warning messages:
1: In odbcDriverConnect(paste("DRIVER={IBM DB2 ODBC DRIVER}", ";Database=database_name", :
[RODBC] ERROR: state IM004, code 0, message [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
2: In odbcDriverConnect(paste("DRIVER={IBM DB2 ODBC DRIVER}", ";Database=database_name", :
ODBC connection failed

[andresrcs]

Then your problem is not with RStudio but most likely with the odbc library. Have you recently upgraded your R version? If so, Have you recompiled your old packages for the new version?

update.packages(checkBuilt = TRUE)

[shawn2x4]

I ran update.packages and I received several prompts for updates, which I executed. Unfortunately, I'm still getting the same error.

Thank you for giving me so many suggestions.