Connect to SQL Database using ODBC connection

Connect to SQL Database using ODBC connection 

Prerequisites
  1. Ensure the user has the access privileges.  Check for membership of the right Active Directory Group
  2. Ensure the user has required privileges to configure the tool.  (Some may require local Admin rights)


Step By Step Process

This method is used by the business when typically generating internal analytics through third-party tools (like MS Access)




Screenshot


1. Open ODBC.  If using MS Access this can be done via the External Data ribbon.


2. Select Machine Data Source and New


3. Accept warning re non-Admin privileges

4. Select Next (Note, we are creating a Machine DSN)

5. Select SQL Server



6. Accept next alert

7. Enter details as below:
  • Name: must be exact in order to permit other users (with correct DSN setup) to share database queries
  • Description:  (non-critical)
  • Server: (mandatory field)

8. Accept defaults on the next screen

9. Change the default database if required

10. Accept the defaults on the next screen

11. Select Test Data Source ...

12. Test should be successful.  If not start again (may need IT support to delete the Machine DSN)

13. Select OK to finish.  Now newly created ODBC files should now be available.

Comments

Post a Comment

Popular posts from this blog

Calculating Age of the person in T-SQL

One of common calculations, we have to do in ETL is calculating age of the person from the date attribute And this is how I have calculated Age of the person. Select BirthDate , CASE             WHEN Month ( GetDate ()) < Month ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             WHEN Month ( GetDate ()) = Month ( c . [BirthDate] )             AND Day ( GetDate ()) < Day ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             ELSE DateDiff ( yy , c . [BirthDate] , GetDate ())  ...
Read more

How to Troubleshoot Subscription issue in Reporting Services

Tips on troubleshooting Subscription issue in Reporting Services 1 – Look at the Reporting Services Log File Error Message from Log File: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   INFO: Notification 20f7b482-eb74-4b51-9e53-9f0609034e72 completed.  Success: False, Status: Failure writing file Startup :  The report server has encountered a configuration error. , DeliveryExtension: Report Server FileShare, Report: Startup, .......      2 - Run below query agains...
Read more