This post is part of Ed Leighton-Dick’s SQL New Blogger Challenge. Please follow and support these new (or reborn) bloggers.
I’m working with a number of SQLite databases as extra data sources in addition to the SQL Server database I’m primarily using for a project. Brian Davis (b|t) wrote a blog post a few years ago that covers setting up the connection quite well. In my case, I’ve got nine SQLite databases to connect to, and that gets tedious. PowerShell to the rescue!
I started by installing the SQLite ODBC Drivers and creating one ODBC connection for reference. Brian’s post linked above covers that well. But I don’t want to do it eight more times, so I’ll use the first DSN as a template so I can script the creation of the rest.
I named my DSN GSAKMyFinds
. To inspect the DSN, I can use the Get-OdbcDsn
cmdlet.
Get-OdbcDsn -Name GSAKMyFinds;
Name : GSAKMyFinds DsnType : System Platform : 64-bit DriverName : SQLite3 ODBC Driver Attribute : {Database, Description, NoTXN, LongNames...}
This looks pretty simple, but there’s a collection of Attribute
s I need to look at too. I’ll do this by expanding that property with Select-Object
.
Get-OdbcDsn -Name GSAKMyFinds | Select-Object -ExpandProperty Attribute |Format-Table -AutoSize;
Name Value ---- ----- Database C:\Users\andy\Dropbox\GSAK8\data\My Finds\sqlite.db3 Description NoTXN 0 LongNames 0 Timeout FKSupport 0 JDConv 0 StepAPI 0 BigInt 0 NoWCHAR 0 SyncPragma LoadExt OEMCP 0 NoCreat 0 ShortNames 0
Now I have everything I need to create a new DSN with Add-OdbcDsn
. All of my SQLite databases are stored in a directory structure under C:\Users\andy\Dropbox\GSAK8\data\
, with each one in a different subdirectory. For now, I’ll just create one to make sure that I’m doing it right, then use Get-OdbcDsn
to see if it matches with my GUI-created DSN.
Add-OdbcDsn -Name GSAKPuzzles -DriverName "SQLite3 ODBC Driver" -Platform 64-bit -DsnType System -SetPropertyValue "Database=C:\Users\andy\Dropbox\GSAK8\data\Far-off puzzles\sqlite.db3"; Get-OdbcDsn -Name GSAKPuzzles; Get-OdbcDsn -Name GSAKPuzzles | Select-Object -ExpandProperty Attribute |Format-Table -AutoSize;
Results:
Name : GSAKPuzzles DsnType : System Platform : 64-bit DriverName : SQLite3 ODBC Driver Attribute : {Database} Name Value ---- ----- Database C:\Users\andy\Dropbox\GSAK8\data\Far-off puzzles\sqlite.db3
Looks pretty good! Note that not all of the Attribute
s seen above are here; those are default values that are set when creating the DSN through the GUI. After deleting my first two test DSN, I can move on to looping through all of my SQLite databases and creating DSNs for all of them. SQLite databases are just files on your filesystem, so by iterating over all of the db3
files in the parent directory I can build the list of files to point my DSNs at.
Get-ChildItem -Path C:\users\andy\Dropbox\gsak8\data -Recurse -Filter sqlite.db3 | Select-Object -ExpandProperty FullName | ForEach-Object { $DSNName = $_.split("\")[6]; Add-OdbcDsn -Name $DSNName -DriverName "SQLite3 ODBC Driver" -Platform 64-bit -DsnType System -SetPropertyValue "Database=$_"; }; Get-OdbcDsn -DriverName "SQLite3 ODBC Driver";
In a few seconds, the DSNs are created.
Name DsnType Platform DriverName Attribute ---- ------- -------- ---------- --------- GSAKMain System 64-bit SQLite3 ODBC Driver {Database, Description, NoTXN, LongNames...} Far-off puzzles System 64-bit SQLite3 ODBC Driver {Database} Home200 System 64-bit SQLite3 ODBC Driver {Database} My Finds System 64-bit SQLite3 ODBC Driver {Database} My Hides System 64-bit SQLite3 ODBC Driver {Database} New England System 64-bit SQLite3 ODBC Driver {Database} Niagara Falls System 64-bit SQLite3 ODBC Driver {Database} NJ System 64-bit SQLite3 ODBC Driver {Database} Seattle System 64-bit SQLite3 ODBC Driver {Database}
Next up is creating the linked servers in SQL Server. I created one with Management Studio using all the defaults, then scripted it to see what I need to do. The only parts I really need are sp_addlinkedserver
and sp_addlinkedsrvlogin
; the defaults for the other options are good enough for what I’m doing here (this may not be true for you, so be sure to check!).
EXEC master.dbo.sp_addlinkedserver @server = N'GSAKMAIN', @srvproduct=N'GSAKMain', @provider=N'MSDASQL', @datasrc=N'GSAKMain' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GSAKMAIN',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
Now I can put this into a PowerShell loop and run it for all of my other DSNs.
$AllDSNs = Get-OdbcDsn -DriverName "SQLite3 ODBC Driver"; foreach ($DSN in $AllDSNs) { $CreateLinkedServerSP =@" EXEC master.dbo.sp_addlinkedserver @server = N'$($DSN.Name)', @srvproduct=N'$($DSN.Name)', @provider=N'MSDASQL', @datasrc=N'$($DSN.Name)'; EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'$($DSN.Name)',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL; "@; invoke-sqlcmd -query $CreateLinkedServerSP -serverinstance sql2014 -database master; }
I let this run and when it’s finished, all my DSNs are linked servers ready to be queried.
Because I’m going to be querying all of these linked servers together, I wrote some additional code to give me a skeleton query performing a UNION
across all of my linked servers which I can use as a starting point.
Here’s the final script:
#require -version 3.0 #requires -module sqlps set-strictmode -Version latest; set-location c:; Get-ChildItem -Path C:\users\andy\Dropbox\gsak8\data -Recurse -Filter sqlite.db3|Select-Object -ExpandProperty FullName | ForEach-Object { $DSNName = $_.split("\")[6]; Add-OdbcDsn -Name $DSNName -DriverName "SQLite3 ODBC Driver" -Platform 64-bit -DsnType System -SetPropertyValue "Database=$_"; }; $AllDSNs = Get-OdbcDsn -DriverName "SQLite3 ODBC Driver"; foreach ($DSN in $AllDSNs) { $CreateLinkedServerSP =@" EXEC master.dbo.sp_addlinkedserver @server = N'$($DSN.Name)', @srvproduct=N'$($DSN.Name)', @provider=N'MSDASQL', @datasrc=N'$($DSN.Name)'; EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'$($DSN.Name)',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL; "@; invoke-sqlcmd -query $CreateLinkedServerSP -serverinstance sql2014 -database master; } $AllDSNs = Get-OdbcDsn -DriverName "SQLite3 ODBC Driver"; $AllDatabasesUnion = ""; foreach ($DSN in $AllDSNs) { $AllDatabasesUnion += "SELECT * FROM OPENQUERY([$($DSN.Name)], 'select * from caches') UNION ALL`n"; } $AllDatabasesUnion = $AllDatabasesUnion.Substring(0,$AllDatabasesUnion.Length - 10); Write-Output $AllDatabasesUnion;
And the query that it generated for me:
SELECT * FROM OPENQUERY([GSAKMain], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([Far-off puzzles], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([Home200], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([My Finds], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([My Hides], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([New England], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([Niagara Falls], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([NJ], 'select * from caches') UNION ALL SELECT * FROM OPENQUERY([Seattle], 'select * from caches')
With a little exploration of the PowerShell OdbcDsn
cmdlets, I’ve eliminated a tedious process and prevented any accidental mouse clicks in a GUI.