Connecting to Microsoft SQL Server - PowerPoint PPT Presentation

1 / 25
About This Presentation
Title:

Connecting to Microsoft SQL Server

Description:

Security Information. Database name. Data Interface / API to use. Steps to connect... Better off with the ADO abstraction. Summary ... – PowerPoint PPT presentation

Number of Views:75
Avg rating:3.0/5.0
Slides: 26
Provided by: spNt
Category:

less

Transcript and Presenter's Notes

Title: Connecting to Microsoft SQL Server


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
  1. Server Name
  2. Security Information
  3. Database name
  4. Data Interface / API to use
  5. 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 cant 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
  • Were indirectly connecting (through JET)
  • Probably not the most efficient way.
  • DEMO

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

10
Connecting with ADO Data Control (DSN)
  • Natively it connects fine
  • Its 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 -gt Administrative Tools -gt Data
Sources (ODBC) Start Menu -gt Administrative Tools
-gt Data Sources (ODBC)
12
Demo Setting up a Data Source Name
13
Example of a File DSN (Standard)
ODBC DRIVERSQL Server UIDsa DATABASEStateU WS
IDDALGOODWIN3 APPMicrosoft Access SERVERdalgoo
dwin3
14
Example of a File DSN (Integrated)
ODBC DRIVERSQL Server UIDtgoodwin Trusted_Conn
ectionYes DATABASEpubs WSIDDALGOODWIN3 APPMicr
osoft Open Database Connectivity SERVERDALGOODWIN
3\DALGOODWIN32000
15
DEMO ADO Control with DSN
16
Connecting with ADO Data Control (UDL)
  • Natively it connects fine
  • Its 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 ProviderSQLOLEDB.1Integrated
SecuritySSPIPersist Security InfoFalseUser
IDsaInitial CatalogpubsData
Sourcedalgoodwin3\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?
Write a Comment
User Comments (0)
About PowerShow.com