Home • About • Contact
Welcome • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

There are several basic ways of interfacing with SQL Server.

• Manager
• Embedded SQL
• CLI (Call Level Interfaces)
• Object Interfaces
• Shortcuts

Manager

Microsoft provides some sort of visual app to manage many of its server products. This Windows Explorer style interface is quite intuitive and provides multiple utilities and wizards.

Here are the rough structures of the "folder list" in two older versions of SQL Server.

SQL Server 7.0	SQL Server 2000 (8.0) with Enterprise Manager	SQL Server 2005 (9.0) with Management Studio
SQL Server Groups Servers Databases Diagrams Tables Views Stored Procedures Users Roles Rules Defaults User Defined Data Types Data Transformation Services Local Packages Repository Packages Metadata Management SQL Server Agent Backup Current Activity Database Maintenance Plans SQL Server Logs Web Publishing Security Logins Server Roles Linked Servers Remote Servers Support Services Distributed Transaction Coordinator SQL Mail	ServerType SQLServerGroup Server Databases Database Diagrams Tables Views Stored Procedures Users Roles Rules Defaults User Defined Data Types User Defined Functions Full-Text Catalogs Data Transformation Services Local Packages Meta Data Services Packages Meta Data Management SQL Server Agent Alerts Operators Jobs Backup Current Activity Process Info Locks / Process ID Locks / Object Database Maintenance Plans SQL Server Logs Replication Publications Subscriptions Replication Monitor Publishers Agents Snapshot Agents Log Reader Agents Queue Reader Agents Distribution Agents Merge Agents Miscellaneous Agents Replication Alerts Security Logins Server Roles Linked Servers Remote Servers Support Services Distributed Transaction Coordinator Full-Text Search SQL Mail Meta Data Services Contents Microsoft Data Warehousing Framework Microsoft Analysis Services Schema	Server Databases System Databases master model msdb tempdb Database Snapshots Database Database Diagrams Tables System Tables Table Columns Keys Constraints Triggers Indexes Statistics Views System Views View Synonyms Programmability Stored Procedures System Stored Procedures Stored Procedures Parameters Functions Table-valued Functions Function Parameters Scalar-valued Functions Aggregate Functions System Functions Database Triggers Assemblies Types System Data Types Exact Numerics Approximate Numerics Date and Time Character Strings Unicode Character Strings Binary Strings Other Data Types User-defined Data Types User-defined Types XML Schema Collections Rules Defaults Service Broker Message Types Contracts Queues Services Routs Remote Service Binding Storage Full Text Catalogs Partition Schemes Partition Functions Security Users Roles Database Roles Application Roles Schemas Asymmetric Keys Certificates Symmetric Keys Security Logins Server Roles Credentials Server Objects Backup Devices Endpoints Database Mirroring Service Broker SOAP TSQL Linked Servers Providers Triggers Replication Local Publications Local Subsriptions Management Maintenance Plans SQL SErver Logs Activity Monitor Database Mail Distributed Transaction Coordinator Full-Text Search Legacy Database Maintenance Plans Data Transformation Services SQL Mail Notification Services SQL Server Agent Jobs Job Activity Monitor Alerts Operators Proxies ActiveX Script Operating System (CmdExec) Replication Distributor Replication Queue Reader Replication Snapshot Replication Transaction-Log Reader Analysis Services Command Analysis Services Query SSIS Package Execution UnassignedProxies Error Logs

There are additional ways to manage and interface with SQL Server.

• The most common and most powerful way to interact with SQL Server is by using plain old SQL (or T-SQL)
  • In SQL Server 2000, the SQL Server Query Analyzer is a great for entering SQL directly.
  • In SQL Server 2005, queries can be done right in Management Studio.
• Here are other interfaces for SQL Server 2000
  • From time to time use SQL Server's SQL Server Profiler to check data usage.
  • From time to time use NT's Performance Monitor to check SQL Server performance via traces, etc. Performance Monitor is  conveniently accessed through SQL Server Profiler as well. You may have to use SQL Server Client Configuration Utility first.
• Here are other interfaces for SQL Server 2005
  • Configuration Tools
    • Notification Services Command Prompt
    • Reporting Services Configuration
    • SQL Server Configuration Manager
    • SQL Server Error and Usage Reporting
    • SQL Server Surface Area Configuration. SQL Server starts out secure and you have to poke holes into the "surface" area, install stuff, activate stuff, open stuff, etc. as needed.
  • Performance Tools
    • Database Engine Tuning Advisor
    • SQL Server Profiler
  • Not quite an interface but something new in SQL Server 2005 is that nearly every resource has extended properties, custom name & value pairs that are very flexible.

Embedded SQL

An ANSI standard interface by which SQL statements are incorporated into source code. The code is framed by EXEC SQL tags. Here are some of the languages that can use Embedded SQL:

• Oracle
• DB2
• COBOL
• c

CLI (Call Level Interfaces)

There are two CLIs that SQL Server supports:

• ODBC (Open DataBase Connectivity)
• DB Library

Both are very powerful but the former is easier to learn, more applicable industry-wide, and utilizes some of SQL Server's features more automatically. CLI is only practical for situations where a company already has a large body of legacy DB Library applications.

Object Interfaces

This is the modern way to go. Object interfaces use a model of objects, complete with methods and properties, to access SQL Server. This includes:

• SQL-DMO (Distributed Management Objects). This is an object interface specifically for performing SQL Server administrative tasks. The SQL Enterprise Manager itself is layered on top of SQL-DMO. This can be used to programmatically administer SQL Server.
• DAO (Data Access Objects) over ODBC. An older object interface closely tied to JET (Joint Engine Technology).
• RDO (Remote Data Objects) over ODBC. An older object interface closely tied to ODBC.
• ADO (ActiveX Data Objects) over OLE DB (Object Linking and Embedding DataBase). An object interface closely tied to OLE DB. ADO is the recommended SQL Server interface method, especially for Web applications.

Shortcuts

CTRL+:

Enters today's date.

CTRL+0

Enters NULL.

Page Modified: (Hand noted: 2007-10-13 15:51:33Z) (Auto noted: 2010-12-24 22:47:36Z)