Consider this mouthful: you have a file on a network server, that you want to put into SQL server, that you want to put into Spotfire! I’ll show you this process below along with the common issues that enjoin it.
Create a Linked Server
We need a way to connect to your Excel file from SQL Server. For this script, change the URL below:
DECLARE @RC int DECLARE @server nvarchar(128) DECLARE @srvproduct nvarchar(128) DECLARE @provider nvarchar(128) DECLARE @datasrc nvarchar(4000) DECLARE @location nvarchar(4000) DECLARE @provstr nvarchar(4000) DECLARE @catalog nvarchar(128) -- Set parameter values SET @server = 'COMPLETIONSLINK' SET @srvproduct = 'Excel' SET @provider = 'Microsoft.ACE.OLEDB.12.0' SET @datasrc = 'C:\Users\Administrator\Desktop\CompletionData.xlsx' SET @provstr = 'Excel 12.0 Xml' EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog
The gotchas/solutions are outlined below:
Gotcha | Solution |
Can’t connect to the file | You need to close the file. |
Still can’t connect. | You need to give the SQL server access to that location. |
Missing Microsoft.ACE.OLEDB.12.0 | You need to install the Access Database Engine driver, located here: https://www.microsoft.com/en-us/download/details.aspx?id=54920 |
Still missing Microsoft.ACE.OLEDB.12.0! | Update the SET @provider to ‘Microsoft.ACE.OLEDB.16.0’ |
Configure the Linked Server
Once you’re setup, you need to log into SQL Management Studio and change the following settings:
- Open Server Objects > Linked Servers > Providers and then select Microsoft.ACE.OLEDB.12.0.
- Right-click and select Properties.
- Check Allow inprocess.
- Press OK.
Create the Stored Procedure
Now create the stored procedure to upload the file:
CREATE PROCEDURE [dbo].[LoadCompletions] AS BEGIN drop table if exists CompletionWellHeader; SELECT * INTO CompletionWellHeader FROM COMPLETIONSLINK...[Data$]; END
Imported! Now all you need to do is run the stored procedure to import the file.
Icing on the Cake
You can setup a SQL Agent Job to run the stored procedure as a nightly job, importing the file at night. It will be important for everyone interacting with the file to be closed out before the day is over.
Technical Director at Ruths.ai