US - Wisconsin (Green Bay)

Steve Erbach's Excel template for making direct SQL connections 

Nov 24, 2020 12:22 PM

The Excel template I mentioned during my presentation on Thursday, Nov. 19th. It's a tiny file (11 KB) that opens up a world of SQL connectivity very easily.

What you'll need is for your SQL database Administrator (DBA) to ensure that you have permission to access the SQL server directly rather than through GP. Those two methods of access are different and separate in the SQL Server security scheme. Make sure that your DBA is on board.

  1. Open the workbook and click the "Enable Content" button
  2. Go to Data >> Queries & Connections
  3. right-click on the "Placeholder Query" that appears in the "Queries & Connections" sidebar
  4. Select "Properties..." from the context menu
  5. In the "Connection Properties" dialog box, change the "Connection name:" to whatever you'd like it to read.
  6. Check whichever "Refresh control" options you'd like
  7. Click on the "Definition" tab
  8. You'll see this:
  9. Change the "SERVER" from "YourServer" to the actual name of your SQL Server; change the "DATABASE" from "YourDatabase" to the actual name of your database.
  10. Then click "OK"
Now you can save the Excel workbook with your "live" SQL Connection, and it's ready to be used with any SQL query you paste into the "Command text:" box on the Definition tab.

0 Favorited
1 Files
xlsx file
PlaceHolder.xlsx   10 KB   1 version
Uploaded - Nov 24, 2020


Dec 03, 2020 11:58 AM

Excellent!!! Thank you Steve

Related Entries and Links

No Related Resource entered.