Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Data Management Objects: Running SQL Statements
Last updated Mar 28, 2003.
It might seem strange that in the previous articles on DMO programming, I've shown you how to get a great deal of information about SQL Server databases and their objects, how to perform maintenance on a system, and even how to use the Filesystem object to create a log file for the process. However, we haven't seen a single T-SQL query yet. It's great to be able to manipulate various DMO objects, but you just can't beat T-SQL for power and flexibility.
In this article, I show you one way you can run a T-SQL statement against SQL Server. I say one way since there are a few methods you can use to run a query, each with its own advantages and disadvantages.
The requirement for this code is pretty simple. I want to run a T-SQL query and return one result. This type of return is called scalar. (Later, we'll learn ways to get lots of data from the server.)
The method I use today uses the Databases object. In DMO programming, you work with objects and collections of objects. Most of the time, those things are separate, but in some cases the distinction isn't so clear. Using a particular database is one of those times.
To work with a database, you create a SQL Server object, and then access the Databases collection it holds. From there, you create a variable that stores a particular database from that collection. I do that here; once I have a handle to the database I use a method to run a query.
But I get a little bit ahead of myself. The way I code is to detail all the requirements, create comments, and then code the comments. For brevity's sake (and to stay on task) the requirements we concern ourselves with here consist of getting a single result back from the database. That's as much detail as we need for now.
The comments you'll see right in the code:
' dmoQuery.vbs ' Created: 01/27/05 - Buck Woody ' Change Log: ' Variable to hold Server Name Dim ServerNameVar ' Variable for the text boxes Dim MsgTxtVar ' Variables for objects Dim oServer Dim oDatabase Dim oQeryResults ' Create the server object using SQL-DMO Set oServer = CreateObject("SQLDMO.SQLServer2") ' Login with current Windows account oServer.LoginSecure = True oServer.Connect "(local)" ' Connect to the database Set oDatabase = oServer.Databases("pubs") Set oQueryResults = oDatabase.ExecuteWithResults("SELECT au_fname FROM authors ORDER BY au_fname") MsgTxtVar = oQueryResults.GetColumnString(1, 1) ' Get the message box MsgBox MsgTxtVar ' Clean up set oDatabase = Nothing Set oServer = Nothing
Most of this code you've seen before, but with slight differences. I left the variables in place, even though I left out the other sections I mentioned. That makes it easier to correct mistakes, even in this small example.
Connecting to the server object is just as we've seen before. The part that begins to get interesting is when I set up the database object:
' Connect to the database Set oDatabase = oServer.Databases("pubs") Set oQueryResults = oDatabase.ExecuteWithResults("SELECT au_fname FROM authors ORDER BY au_fname")
I set a static reference to a database (pubs) and then used a new object variable, called oQueryResutls, to store the query. This creates a single object that represents the results of a query, called by a method on the oDatabase object.
There are a couple of methods you can use to call a query, and here I use one called ExecuteWithResults. This method does just what it sounds like: it executes a query, and then returns a result set.
Notice that the parameter it takes is a query (in quotes). In this case, I asked for the first name of all the authors, ordered by the first name, from the authors table. This example is simple, but just about any valid SQL Server query can be used here.
I've run a query and stored the result in an object, and now I want to display that result in a message box. The result, however, isn't simply a string of text. It's actually still stored as a dataset, consisting of rows and columns, like a spreadsheet. I can't display all of the text by simply referring to the single variable where I stored the data, since the system isn't sure which specific row or column I'm looking for.
The code that takes care of that specification is here:
MsgTxtVar = oQueryResults.GetColumnString(1, 1)
What I've done in this part of the code is to set a text variable to only a part of the oQueryResults object, using yet another method. This seems strange at first, since the oQueryResults object was itself filled with a method! As Alice in Wonderland observed, things sometimes refer back to themselves in the strangest ways. Programming is like that; you sometimes have to work back through the code to understand how some variable suddenly has methods and properties.
It is this ability of the oQueryResults object to inherit all the properties and methods of a QueryReults object that allows us to get at a particular column and row which is precisely what the (1,1) part does. Since I asked for column 1, row 1, and the query got the au_fname column sorted by au_fname, I get the first alphabetical, single item from the table.
Now that I have that information stored in a string, I can use it for whatever I want. In this example, I display the data in a message box, but I could also use it in a comparison, to trigger another method, and more.
In this article, I've shown a scalar (single value, in the physics sense) result. In the next article in this series, I'll show you how to deal with more data, iterating through the entire dataset, regardless of length or width.
The full documentation for the SQL Server Object Library can be found here.
InformIT Tutorials and Sample Chapters
Safari has literally hundreds of books that explain object oriented programming. Make sure you check out this incredible resource.