# Connecting Tidier with MSSQL

**URL:** https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018
**Category:** Data
**Tags:** question, package
**Created:** [October 7, 2024, 4:39pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018 "2024-10-07T16:39:43Z")
**Posts on this page:** 13
**Page:** 1

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 4:39pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/1 "2024-10-07T16:39:43Z")

</div>

Hi, all. I’m hoping to get some Tidier officiandos here to help me out. I’m trying to connect to a MSSQL database using Tidier because I want to test out that package. I have followed all the info in the docs as far as I can tell but I can’t get the connect method to work.

Here’s my code, a simple connection is erroring out:

```julia
using Tidier
using ODBC
db = DB.connect(:mssql, "Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")

```

and here’s the error message:

```julia
MethodError: no method matching connect(::Symbol, ::String)

Closest candidates are:

  connect(::Symbol, ::String, !Matched::String, !Matched::String, !Matched::String, !Matched::String)

   @ TidierDB C:\Users\[userid]\.julia\packages\TidierDB\XJFly\src\parsing_snowflake.jl:160

  connect(::Symbol, ::String, !Matched::String)

   @ TidierDB C:\Users\[userid]\.julia\packages\TidierDB\XJFly\src\parsing_gbq.jl:9

  connect(::Symbol; kwargs...)

   @ TidierDB C:\Users\[userid]\.julia\packages\TidierDB\XJFly\src\TidierDB.jl:331

```

Any help is appreciated!

---

<div class="post-metadata">

### Author: ![Palli](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/palli/32/3380_2.png) [@Palli](https://discourse.julialang.org/u/Palli)
#### Post date: [October 7, 2024, 5:19pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/2 "2024-10-07T17:19:31Z")

</div>

I would first try to connect some way without Tidier (and then you might only need TiderDB):

> **[Working with MS SQL Server in Julia](https://towardsdatascience.com/working-with-ms-sql-server-in-julia-fc6fcd55d69a)**
>
> Time to supercharge your data analysis workflow

> [@Julia, ODBC connection to a remote MS SQL Server](https://discourse.julialang.org/t/julia-odbc-connection-to-a-remote-ms-sql-server/75374/2):
>
> Ok, this is what I’m trying, and I know generally that the variables are correct. 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\> ODBC.Connection(Driver={ODBC Driver 17 for SQL Server};Server="server\_ip#";Database="DBaseName";UID="User.Name";PWD="passwor") ERROR: syntax: invalid comprehension syntax Stacktrace: [1] to…

Also you’re doing something more than me, since I get less specific error message:

```julia
julia> using Tidier

julia> using ODBC

julia> db = DB.connect(:mssql, "Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")
ERROR: UndefVarError: `DB` not defined

```

Something like this errors for me (expectedly) but should work for you:

```julia
julia> julia> con = ODBC.Connection("Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;") # or similar to ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=ip#,DATABASE=dbname,UID=username,PWD=passwrod")
ERROR: 01000: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

```

You might want to make sure if you have latest versions with `st` (though I doubt it was the problem), I noticed I didn’t have latest (and ironically trying to fix downgraded):

```julia
pkg> add Tidier@v1.4.0

⌅ [4acbeb90] ↓ Stipple v0.30.7 ⇒ v0.28.14
  [2b41e42f] ↑ StippleMarkdown v0.0.1 ⇒ v0.2.0
  [f0413319] ↑ Tidier v1.3.0 ⇒ v1.4.0
⌅ [86993f9b] ↓ TidierDB v0.4.1 ⇒ v0.1.9
    Updating `~/Manifest.toml`
...
⌅ [458c3c95] ↓ OpenSSL_jll v3.0.15+1 ⇒ v1.1.23+1 [and many more than this, what I don't like about Julia's defaults]

pkg> add TidierDB@v0.4.1
   Resolving package versions...
   Installed JuliaFormatter ─ v1.0.61
    Updating `~/Project.toml`
⌃ [f0413319] ↓ Tidier v1.4.0 ⇒ v1.3.0
  [86993f9b] ↑ TidierDB v0.1.9 ⇒ v0.4.1
...

```

I’m not exactly sure what’s blocking, I see in “[compat]” (and should be ok?): TidierDB = “0.1, 1”:

```julia
(pharaldsson) pkg> add TidierDB@v0.4.1 Tidier@v1.4.0
   Resolving package versions...
ERROR: Unsatisfiable requirements detected for package TidierDB [86993f9b]:
 TidierDB [86993f9b] log:
 ├─possible versions are: 0.1.0-0.4.1 or uninstalled
 ├─restricted to versions 0.4.1 by an explicit requirement, leaving only versions: 0.4.1
 └─restricted by compatibility requirements with Tidier [f0413319] to versions: 0.1.0-0.1.9 — no versions left
   └─Tidier [f0413319] log:
     ├─possible versions are: 0.3.0-1.4.0 or uninstalled
     └─restricted to versions 1.4.0 by an explicit requirement, leaving only versions: 1.4.0

```

I think on Windows you have ODBC preinstalled (the non-Julia support), elsewhere you also likely need to do something like (I even gave up trying to set up in R…):

```julia
$ apt-get install unixodbc-dev

```

> <https://github.com/r-dbi/odbc/issues/236#issuecomment-434463291>
>
> \### Issue Description and Expected Result
> I call install.packages("odbc") and t…he odbc package to be installed
> 
> \### Database
> 
> 
> \### Reproducible Example
> \<!--
> If possible include a \_small\_ dump of the table with the error and the R code
> that generates the error. In your reprex please use \`con\` as the name of the
> connection, this makes it easier to run locally.
> 
> The reprex package can be helpful in doing this.
> 
> install.packages("reprex")
> reprex::reprex()
> 
> Example:
> \`\`\`r
> library(odbc)
> library(DBI)
> con \<- dbConnect(odbc::odbc(), dsn = "PostgreSQL")
> dbWriteTable(con, "iris", iris)
> dbReadTable(con, "iris")
> \`\`\`
> \--\>
> \<summary\>This happens both on RStudio Server (R version 3.5.1) and on Ubuntu 16.04 with R version 3.2.3\</summary\>
> 
> \`\`\`r
> install.packages("odbc")
> 
> Installing package into ‘/usr/local/lib/R/site-library’
> (as ‘lib’ is unspecified)
> trying URL 'https://cran.rstudio.com/src/contrib/odbc\_1.1.6.tar.gz'
> Content type 'application/x-gzip' length 288033 bytes (281 KB)
> ==================================================
> downloaded 281 KB
> 
> \* installing \*source\* package ‘odbc’ ...
> \*\* package ‘odbc’ successfully unpacked and MD5 sums checked
> PKG\_CFLAGS=
> PKG\_LIBS=-lodbc
> \<stdin\>:1:17: fatal error: sql.h: No such file or directory
> compilation terminated.
> \------------------------- ANTICONF ERROR ---------------------------
> Configuration failed because odbc was not found. Try installing:
> \* deb: unixodbc-dev (Debian, Ubuntu, etc)
> \* rpm: unixODBC-devel (Fedora, CentOS, RHEL)
> \* csw: unixodbc\_dev (Solaris)
> \* brew: unixodbc (Mac OSX)
> To use a custom odbc set INCLUDE\_DIR and LIB\_DIR manually via:
> R CMD INSTALL --configure-vars='INCLUDE\_DIR=... LIB\_DIR=...'
> \--------------------------------------------------------------------
> ERROR: configuration failed for package ‘odbc’
> \* removing ‘/usr/local/lib/R/site-library/odbc’
> Warning in install.packages :
> installation of package ‘odbc’ had non-zero exit status
> 
> The downloaded source packages are in
> ‘/tmp/RtmpDXgKkI/downloaded\_packages’
> \`\`\`

---

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 6:46pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/3 "2024-10-07T18:46:42Z")

</div>

Thanks for taking a look. I’ve used ODBC before and was able to connect and run queries. I was just attempted to get Tidier to work without using and explicit ODBC connection on my end. The docs show an mssql example but it doesn’t seem work with just importing Tidier as I expected.

Regarding what I did that gave more info than the UndefVarError for DB, those 3 lines of code in a Pluto notebook are all that I used. I am starting from a project space created by the DrWatson package, not sure if that is doing anything.

If I don’t get any answers on fixing Tidier, I’ll probably do as you suggested and connect using ODBC, possibly pull data like that, and then using Tidier on a dataframe or something.

---

<div class="post-metadata">

### Author: ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [October 7, 2024, 7:04pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/4 "2024-10-07T19:04:16Z")

</div>

@jjAtNxtPt do you know what version you are using? based on the error message, I would guess \>v.3 (which is good!). however this means, i moved most of the backends to [package extensions](https://tidierorg.github.io/TidierDB.jl/latest/examples/generated/UserGuide/getting_started/#package-extensions) to reduce direct dependencies in tidierdb. this means that to use MsSQL, you will have to do the following

```julia
using Tidier
using ODBC

```

this will load the MsSQL extension.

From there to connect  
it will look like this (the `:mssql` is now `mssql()`

```julia
db = DB.connect(DB.mssql(), "Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")

```

I did not introduce a `connect` for MsSQL [until v.3.2](https://github.com/TidierOrg/TidierDB.jl/blob/main/NEWS.md) tho so can always set up your connection as you historically have and then pass that to `DB.db_table()` for you connection.

finally, I would rec using .3.4 or greater (ideally \>.4) as I fixed an MsSQL issue join in v.3.4.

please let me know if that fixes your connection issue.

as far as the dependency issue with a downgrade. im not sure why that happened, but we will hopefully release an updated version of tidier soon to avoid that.

---

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 7:09pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/5 "2024-10-07T19:09:45Z")

</div>

Well, I was playing around a bit more and tried using a “connection\_string” kwarg and received a different error that let me to try using a dsn name instead of a connection string like this :

```julia
db = DB.connect(:mssql; dsn="ODBCNAME")

```

---

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 7:15pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/6 "2024-10-07T19:15:03Z")

</div>

I’m using Tidier v 1.4, so whatever TidierDB version that comes with that meta package.

I tried using the code you specified, fixing the connection string which was probably my fault, and here’s what I got from the REPL:

```julia
julia> using Tidier

julia> using ODBC

julia> db = DB.connect(DB.mssql(), "Driver={ODBC Driver 17 for SQL Server};Server=<my db>;Database=Reporting;Trusted_Connection=yes;")
ERROR: UndefVarError: `mssql` not defined
Stacktrace:
 [1] getproperty(x::Module, f::Symbol)
   @ Base .\Base.jl:31
 [2] top-level scope
   @ REPL[13]:1

```

---

<div class="post-metadata">

### Author: ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [October 7, 2024, 7:21pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/7 "2024-10-07T19:21:12Z")

</div>

hmm , can you try just isolating TidierDB and seeing that? I would love to get this to work for you

so

```julia
import TidierDB as DB 
using ODBC # you should get an ext is loaded message in repel
] status TidierDB

```

and then can you try connecting via just ODBC the way you used to and then passing that to `DB.db_table(conn, "table_name")`.

The `connect` for MsSQL was not made to be super robust yet, so that could be the block

---

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 7:30pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/8 "2024-10-07T19:30:57Z")

</div>

Ok, I added TidierDB by itself from the REPL and then tried your code.

Here’s the REPL after I restarted it to give it a clean slate:

```julia
julia> import TidierDB as DB

julia> import ODBC

(@v1.10) pkg> status TidierDB
Status `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
⌅ [86993f9b] TidierDB v0.1.9
Info Packages marked with ⌅ have new versions available but compatibility constraints restrict them from upgrading. To see why use `status --outdated`

```

---

<div class="post-metadata">

### Author: ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [October 7, 2024, 7:34pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/9 "2024-10-07T19:34:01Z")

</div>

Ok, so  
`mssql()` is only in .3.0 and above which is what I would recommend using and why you got that alternate error message about `mssql()` not existing

Can you do

```julia
]up TidierDB

```

and the make sure its \> .3.0 ideally \> .3.3 and then try again with

```julia
import TidierDB as DB
using ODBC

```

---

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 7:39pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/10 "2024-10-07T19:39:37Z")

</div>

I tried, but some compatability issue is causing it to fail:

```julia
(@v1.10) pkg> up TidierDB
    Updating registry at `C:\Users\<userid>\.julia\registries\General.toml`
  No Changes to `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
  No Changes to `C:\Users\<userid>\.julia\environments\v1.10\Manifest.toml`

(@v1.10) pkg> st TidierDB
Status `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
⌅ [86993f9b] TidierDB v0.1.9
Info Packages marked with ⌅ have new versions available but compatibility constraints restrict them from upgrading. To see why use `status --outdated`

```

I tried the `status --outdated` command and all it says is:

```julia
(@v1.10) pkg> status --outdated TidierDB
Status `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
⌅ [86993f9b] TidierDB v0.1.9 (<v0.4.1): Tidier

```

I’m not sure what that actually means, though, or how to get around it.

---

<div class="post-metadata">

### Author: ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [October 7, 2024, 7:45pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/11 "2024-10-07T19:45:59Z")

</div>

ahh, ok, until we can get a newer version of Tidier released, you may have to uninstall Tidier and install the individual packages you need.

You can also try

```julia
using Pkg; Pkg.add(url = "https://github.com/TidierOrg/TidierDB.jl")

```

or use an old version of tidierdb which does not have extensions so you would have use ODBC to create your own connection, and then  
`set_sql_mode(:mssql)`

and then use tidierdb like that.

---

<div class="post-metadata">

### Author: ![jjAtNxtPt](https://avatars.discourse-cdn.com/v4/letter/j/e56c9b/32.png) [@jjAtNxtPt](https://discourse.julialang.org/u/jjAtNxtPt)
#### Post date: [October 7, 2024, 7:47pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/12 "2024-10-07T19:47:53Z")

</div>

Thanks!

---

<div class="post-metadata">

### Author: ![JoshuaTaylor](https://avatars.discourse-cdn.com/v4/letter/j/eada6e/32.png) [@JoshuaTaylor](https://discourse.julialang.org/u/JoshuaTaylor)
#### Post date: [November 19, 2024, 1:10pm UTC](https://discourse.julialang.org/t/connecting-tidier-with-mssql/121018/13 "2024-11-19T13:10:52Z")

</div>

Thank you, you saved my day.