I have been using R to connect to a remote MS SQL Server database, from Ubuntu Linux 21.10 and it works quite well. I’d like to do the same thing from Julia but can not make the connection using the ODBC package. Does anyone have a script that does make a “con”, and would share it with me? I have the “R” equivalent of what I’m trying to achieve. Thx. J.
con <- dbConnect(odbc::odbc(),
Driver="ODBC Driver 17 for SQL Server",
Server="ip_number",
UID="User.Name",
PWD=decrypt_gk_pw("key1 word1 key2 word2"),
Database="DBName")
So it seems the driver is there, what is “comprehension syntax” ? Should I not be using double quotes ? What about commas vs semicolons? Any working example would be greatly appreciated. Thx.
Thanks Jeff, getting closer! It still won’t connect, although these same settings work from R!
julia> ODBC.drivers()
Dict{String, String} with 2 entries:
"ODBC Drivers" => ""
"ODBC Driver 17 for SQL Server" => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1\0UsageCount=6\0"
julia> con = ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=ip#,DATABASE=dbname,UID=username,PWD=passwrod")
ERROR: HYT00: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired08001: [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Connection string is not valid [87]. 08001: [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Stacktrace:
[1] error(s::String)
@ Base ./error.jl:33
[2] driverconnect(connstr::String)
@ ODBC.API ~/.julia/packages/ODBC/qhwMX/src/API.jl:114
[3] connect
@ ~/.julia/packages/ODBC/qhwMX/src/API.jl:351 [inlined]
[4] ODBC.Connection(dsn::String; user::Nothing, password::Nothing, extraauth::Nothing)
@ ODBC ~/.julia/packages/ODBC/qhwMX/src/dbinterface.jl:57
[5] ODBC.Connection(dsn::String)
@ ODBC ~/.julia/packages/ODBC/qhwMX/src/dbinterface.jl:55
[6] top-level scope
@ REPL[27]:1
Thanks again, that does get closer but still getting wrong string errors:
`julia> con = ODBC.Connection(“Driver={ODBC Driver 17 for SQL Server};Server=ip#,Database=dbname,UID=username,PWD=pwd”)
ERROR: HYT00: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired08001: [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Connection string is not valid [87]. 08001: [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Stacktrace:
[1] error(s::String)
@ Base ./error.jl:33
[2] driverconnect(connstr::String)
@ ODBC.API ~/.julia/packages/ODBC/qhwMX/src/API.jl:114
[3] connect
@ ~/.julia/packages/ODBC/qhwMX/src/API.jl:351 [inlined]
[4] ODBC.Connection(dsn::String; user::Nothing, password::Nothing, extraauth::Nothing)
@ ODBC ~/.julia/packages/ODBC/qhwMX/src/dbinterface.jl:57
[5] ODBC.Connection(dsn::String)
@ ODBC ~/.julia/packages/ODBC/qhwMX/src/dbinterface.jl:55
[6] top-level scope
@ REPL[23]:1
Look at the “SQL Server Import and Export” window in the link I sent earlier. It displays a connection string. Maybe you can connect through that tool (or some other MS-Sql tool) and copy and paste the connection string from there. Probably a red herring, but server in that connection string is lowercase, and yours is uppercase, but somewhere else on that page it is all uppercase.
I finally got a working solution by cobbling pieces together. There is not much documentation on any of this. If anyone knows a more elegant solution I’d be happy to try it.