1 / 25

Connecting to Microsoft SQL Server

Connecting to Microsoft SQL Server. Tuc Goodwin. Introduction . We are going to discuss establishing an connection to a SQL Server Database. You will learn that there is an easy way and a hard way. Agenda. Overview – What do you have to know… Security Modes Database Interfaces

fawn
Download Presentation

Connecting to Microsoft SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Connecting to Microsoft SQL Server Tuc Goodwin

  2. Introduction • We are going to discuss establishing an connection to a SQL Server Database. • You will learn that there is an easy way and a hard way.

  3. Agenda • Overview – What do you have to know… • Security Modes • Database Interfaces • Connecting to a SQL Server Data Source

  4. Overview – What do you have to know… • Server Name • Security Information • Database name • Data Interface / API to use • Steps to connect…

  5. Security Modes There are two types of modes: • Standard Mode (default) • Integrated Mode

  6. Database Interfaces

  7. Connecting to a SQL Server Data Source • Connecting with Data Control • Connecting with ADO Data Control (ADO) • Connecting with ADO Data Control (DSN) • Connecting with ADO Data Control (UDL) • Connecting with ADO (Programmatically)

  8. Connecting with Data Control • Natively it can’t be done… • It uses DAO. • However… • If a Jet Database has already connected to a SQL Server database, then we can connect to that. • Downside • We’re indirectly connecting (through JET) • Probably not the most efficient way. • DEMO

  9. Connecting with ADO Data Control (ADO) • Natively it connects fine • It’s ADO • Set the Connection string property • Set the RecordSource property • DEMO

  10. Connecting with ADO Data Control (DSN) • Natively it connects fine • It’s ODBC • Set the Connection string property • Set the RecordSource property

  11. Data Source Name (DSN) A DSN is a registry entry (Machine DSN) or text file (File DSN) that contains information about a database and the SQL Server it resides on. Control Panel -> Administrative Tools -> Data Sources (ODBC) Start Menu -> Administrative Tools -> Data Sources (ODBC)

  12. Demo – Setting up a Data Source Name

  13. Example of a File DSN (Standard) [ODBC] DRIVER=SQL Server UID=sa DATABASE=StateU WSID=DALGOODWIN3 APP=Microsoft® Access SERVER=dalgoodwin3

  14. Example of a File DSN (Integrated) [ODBC] DRIVER=SQL Server UID=tgoodwin Trusted_Connection=Yes DATABASE=pubs WSID=DALGOODWIN3 APP=Microsoft Open Database Connectivity SERVER=DALGOODWIN3\DALGOODWIN32000

  15. DEMO – ADO Control with DSN

  16. Connecting with ADO Data Control (UDL) • Natively it connects fine • It’s ADO • Set the Connection string property • Set the RecordSource property

  17. Creating a UDL • Create a file with a .UDL extention. • Double-click on the icon.

  18. Set the Provider

  19. Set the Connection and Test

  20. Examine the UDL [oledb] ; Everything after this line is an OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=dalgoodwin3\dalgoodwin32000 Look familiar? Can you say “Connection string”

  21. DEMO – ADO Control with UDL

  22. Connecting with ADO (Programmatically) • Declare an ADO connection object • Set the Connection String • Open the Connection • Instantiate the recordset etc….

  23. DEMO – Connect with ADO

  24. Other ways • RDO – Similar to ADO. Use DSN or DSN-less connection strings • ODBCDirect – Too much for this lesson • ODBC – lots of API calls. Better off with the ADO abstraction.

  25. Summary • We discussed establishing an connection to a SQL Server Database. • We learned that there is an easy way and a hard way • Questions?

More Related