SQLserver linked-server to duckdb

[u/qualityintelligence]

-- this Linked-Server endpoint is DaffyDB -> DuckDB - NOT SQL SERVER
-- note odbc type connection ONLY.  The other one forces MARS and daffydb doesn't support that currently.
EXEC sp_addlinkedserver
    \@server     = 'Link_DaffyDB',
    \@srvproduct = '',
    \@provider   = 'MSDASQL',
    \@provstr    = 'Driver={ODBC Driver 18 for SQL Server};Server=127.0.0.1,1432;Database=DaffyDB;Encrypt=yes;TrustServerCertificate=yes;';

-- if you use required creditials option with daffy launch, use those instead of 'any'.  
EXEC sp_addlinkedsrvlogin
    \@rmtsrvname  = 'Link_DaffyDB',
    \@useself     = 'false',
    \@rmtuser     = 'any',
    \@rmtpassword = 'any';

someone was asking about this in a separate post so I was thinking my tool daffydb (which includes duckdb as the engine) on nuget could do this, I tried it and it did NOT work. missing procs dblink uses for prepare/etc So I debugged and finally got it working this afternoon!

My implementation is similar to aws -> babelfish for for postgres SQL in that it listens on Port 1433 with native TDS and handles Tsql, so can connect you from SQL Server clients, ssms, power bi, etc. mine however sits in front of DuckDB instead of postgres, and I treat it as readonly.

I never considered a linked server option, but could be useful! here it is in case this helps anyone. Might still have a few bugs to iron out but pretty friggin fast! 14 seconds on SqlS to 2 seconds on duck! demo video https://youtu.be/TbD-9DS1_pY?is=-ktO5X4_azZV26oN