Connecting to microsoft sql server l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 25

Connecting to Microsoft SQL Server PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Connecting to microsoft sql server l.jpg

Connecting to Microsoft SQL Server

Tuc Goodwin


Introduction l.jpg

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 l.jpg

Agenda

  • Overview – What do you have to know…

  • Security Modes

  • Database Interfaces

  • Connecting to a SQL Server Data Source


Overview what do you have to know l.jpg

Overview – What do you have to know…

  • Server Name

  • Security Information

  • Database name

  • Data Interface / API to use

  • Steps to connect…


Security modes l.jpg

Security Modes

There are two types of modes:

  • Standard Mode (default)

  • Integrated Mode


Database interfaces l.jpg

Database Interfaces


Connecting to a sql server data source l.jpg

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)


Connecting with data control l.jpg

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


Connecting with ado data control ado l.jpg

Connecting with ADO Data Control (ADO)

  • Natively it connects fine

    • It’s ADO

  • Set the Connection string property

  • Set the RecordSource property

  • DEMO


Connecting with ado data control dsn l.jpg

Connecting with ADO Data Control (DSN)

  • Natively it connects fine

    • It’s ODBC

  • Set the Connection string property

  • Set the RecordSource property


Data source name dsn l.jpg

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)


Demo setting up a data source name l.jpg

Demo – Setting up a Data Source Name


Example of a file dsn standard l.jpg

Example of a File DSN (Standard)

[ODBC]

DRIVER=SQL Server

UID=sa

DATABASE=StateU

WSID=DALGOODWIN3

APP=Microsoft® Access

SERVER=dalgoodwin3


Example of a file dsn integrated l.jpg

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


Demo ado control with dsn l.jpg

DEMO – ADO Control with DSN


Connecting with ado data control udl l.jpg

Connecting with ADO Data Control (UDL)

  • Natively it connects fine

    • It’s ADO

  • Set the Connection string property

  • Set the RecordSource property


Creating a udl l.jpg

Creating a UDL

  • Create a file with a .UDL extention.

  • Double-click on the icon.


Set the provider l.jpg

Set the Provider


Set the connection and test l.jpg

Set the Connection and Test


Examine the udl l.jpg

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”


Demo ado control with udl l.jpg

DEMO – ADO Control with UDL


Connecting with ado programmatically l.jpg

Connecting with ADO (Programmatically)

  • Declare an ADO connection object

  • Set the Connection String

  • Open the Connection

  • Instantiate the recordset etc….


Demo connect with ado l.jpg

DEMO – Connect with ADO


Other ways l.jpg

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.


Summary l.jpg

Summary

  • We discussed establishing an connection to a SQL Server Database.

  • We learned that there is an easy way and a hard way

  • Questions?


  • Login