CHAPTER 20 • SQL-DMO
774
TABLE 20.2: PROPERTIES OF THE SQLSERVER OBJECT (CONTINUED)
Property Extended Description
TrueName The value of @@SERVERNAME from this server
UserProfile Returns a series of bitflags indicating user privi-
leges on the server
VersionMajor Major version number
VersionMinor Minor version number
VersionString Complete version information
Methods
Table 20.3 lists the methods of the SQLServer and SQLServer2 objects.
TABLE 20.3: METHODS OF THE SQLSERVER OBJECT
Method Extended Description
AddStartParameter Appends a startup option for this server
AttachDB Attaches a database file to the current
server
AttachDBWithSingleFile Attaches a database stored in a single
file to the current server
AttachDBWithSingleFile2 ✔ Attaches a database stored in a single
file to the current server
BeginTransaction Starts a T-SQL transaction
Close Closes the connection with the server
CommandShellImmediate Executes an operating system command
Executes an operating system command
and returns the results
CommitTransaction Commits a T-SQL transaction
Connect Connects to a particular SQL Server
Continue Restarts a paused server
DetachDB Detaches a database from the server
DetachedDBInfo ✔ Returns a result set containing informa-
tion about a detached database
CommandShell-
ImmediateWithResults
2627ch20.qxd 8/22/00 11:13 AM Page 774
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
775
TABLE 20.3: METHODS OF THE SQLSERVER OBJECT (CONTINUED)
Method Extended Description
DisConnect Breaks the connection with a server
EnumAccountInfo Enumerates the Windows NT accounts
with access to the server
EnumAvailableMedia Enumerates the drives visible to the
server
EnumCollations ✔ Enumerates the valid collations for this
server
EnumDirectories Enumerates the child directories of a
directory on the server
EnumErrorLogs Enumerates the error logs on the current
server
EnumLocks Enumerates the locks currently held on
the server
EnumLoginMappings Enumerates the security mappings on
the current server
EnumNTDomainGroups Enumerates the groups in the server’s
domain
EnumProcesses Enumerates the SQL Server processes on
the current server
EnumServerAttributes Returns a list of the properties of the
current server
EnumVersionInfo Returns the complete VERSIONINFO
resource from the current server
ExecuteImmediate Submits a T-SQL batch for immediate
execution
ExecuteWithResults Submits a T-SQL batch and returns the
results
IsDetachedPrimaryFile ✔ Returns True if a specified disk file is a
primary database file
IsLogin Returns True if a specified name is a
valid login
IsNTGroupMember Returns True if a specified user is in a
specified NT group
Submits a T-SQL batch and returns the
results along with any messages from
the server
ExecuteWithResultsAnd-
Messages
SQL-DMO OBJECT MODEL
Development with
SQL server
PART
V
2627ch20.qxd 8/22/00 11:13 AM Page 775
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 20 • SQL-DMO
776
TABLE 20.3: METHODS OF THE SQLSERVER OBJECT (CONTINUED)
Method Extended Description
IsOS Returns True if this server is running on
a specific operating system
IsPackage Returns an integer indicating the version
of SQL Server that this object refers to
KillDatabase Drops a database
KillProcess Terminates a process
ListCollations ✔ Returns a list of all valid collation names
ListCompatibilityLevels ✔ Returns a list of all valid compatibility
levels
ListDetachedDBFiles ✔ Returns a list of all database files refer-
enced by a specified primary database file
ListDetachedLogFiles ✔ Returns a list of all log files referenced
by a specified primary database file
ListInstalledInstances ✔ Returns a list of all named instances of
SQL Server on a specified computer
ListMembers Returns a list of the database roles that
a particular login belongs to
ListStartupProcedures Returns a list of the stored procedures
that execute when the server is started
Pause Pauses the server
PingSQLServerVersion Returns an integer corresponding to the
version of a specified server
ReadBackupHeader Lists the contents of a backup device
or file
ReadErrorLog Returns the contents of an error log
ReConnect Reconnects a disconnected server
RollbackTransaction Rolls back a T-SQL batch
SaveTransaction Sets a checkpoint within a T-SQL batch
ServerLoginMode ✔ Returns the default login mode for the
specified server
Shutdown Stops the server
Start Starts the server
Stop Stops the server
2627ch20.qxd 8/22/00 11:13 AM Page 776
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
777
TABLE 20.3: METHODS OF THE SQLSERVER OBJECT (CONTINUED)
Method Extended Description
UnloadODSDLL Unloads a DLL containing extended
stored procedures
VerifyConnection Checks whether the current server is still
connected
Note that although methods and properties can both return information to the
user, there are differences between them. SQL-DMO uses methods for three distinct
situations:
• When the SQLServer object is being told to perform an action (such as dropping
a database)
• When retrieving information requires supplying other information (such as
checking whether a user ID belongs to a particular Windows NT group)
• When the return value consists of multiple pieces of information (such as the
list of all available drives on a system)
These rules for distinguishing methods from properties are consistent across all the
SQL-DMO objects.
Events
Table 20.4 lists the events that the SQLServer object makes available. All of these
events are available on the original SQLServer object. There are no additional events
on the extended SQLServer2 object.
TABLE 20.4: EVENTS OF THE SQLSERVER OBJECT
Event Occurs when…
CommandSent SQL-DMO submits a T-SQL batch to be executed
ConnectionBroken SQL-DMO loses its connection to the server
QueryTimeout A T-SQL batch times out
RemoteLoginFailed An attempt to connect to a remote server fails
ServerMessage A success-with-information message is returned by the server
SQL-DMO OBJECT MODEL
Development with
SQL server
PART
V
2627ch20.qxd 8/22/00 11:13 AM Page 777
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 20 • SQL-DMO
778
The Configuration Object
The Configuration object and its child collection of ConfigValue objects are another
important part of the SQL-DMO object model. With these objects, you can retrieve or
set the same configuration options for a server that you can set with the sp_configure
stored procedure or the configuration options of SQL Server Enterprise Manager.
The Configuration object itself has only one property, the ShowAdvancedOptions
property. Setting this property to True includes the advanced configuration options in
the ConfigValues collection. The Configuration object has two methods: Reconfigure-
CurrentValues and ReconfigureWithOverride. Either method applies changes made to
ConfigValue objects back to the server. The difference is that the ReconfigureWith-
Override method bypasses SQL Server’s validity checking.
The Configuration object has a child collection of ConfigValue objects. Each of
these objects represents a single configuration option for SQL Server. The properties of
the ConfigValue object include:
Name: The name of the option
Description: A lengthier description of the option
CurrentValue: The current value of the option
MinimumValue: The minimum allowed value of the option
MaximumValue: The maximum allowed value of the option
RunningValue: The value currently used by the server (this can differ from
the CurrentValue property if the CurrentValue property has been changed and
the change has not yet been committed to the server)
You’ll see an example of using the Configuration and ConfigValue objects later in
this chapter in the section “Changing a Configuration Option.”
The Database Object
One of the principle objects in the SQL-DMO object model is the Database object.
This object represents an entire database, and it provides a way to both manipulate
databasewide properties and get to other objects stored in a database.
Like the SQLServer object, the Database object has been extended for SQL Server 2000,
so there are both Database and Database2 object types.
Table 20.5 shows some of the principle properties (P) and methods (M) of the Data-
base object. This is not an exhaustive listing. For the full details of these objects, refer
to the SQL-DMO reference in SQL Server Books Online.
2627ch20.qxd 8/22/00 11:13 AM Page 778
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
779
TABLE 20.5: SELECTED DETAILS OF THE DATABASE OBJECT
Name Type Extended Description
Checkpoint M Forces a write of dirty pages back to the disk
CheckTables M Checks the integrity of tables in this database
CheckTablesWithResult M ✔ Checks the integrity of tables in this database
and returns the results as a table
CurrentCompatibility P ✔ Specifies the compatibility level of this database
DboLogin P True if the current login has DBO privileges on
this database
ExecuteImmediate M Executes a T-SQL batch within this database
IsFullTextEnabled P True if full-text searching is available for this
database
Name P Name of the database
Permissions P A set of bitflags that indicate the privileges of
the current SQL-DMO session in this database
PrimaryFilePath P Path to the primary data file for this database
Script M Creates a T-SQL script that re-creates this
database
Shrink M Reduces the space of the files holding this
database
SpaceAvailable P Amount of free space in the database
Status P Current state of the database (suspect, recovery,
loading, and so on)
You’ll see one use for the Database object in the section “Creating a Database” later
in this chapter.
The DBOption Object
The DBOption object is SQL-DMO’s way of allowing you to set the overall options
that control a database. Each Database object has one DBOption object as a child. As
you change the properties of this object, SQL Server changes the options of the refer-
enced database to match. The properties of this object include:
AssignmentDiag: True to enable SQL-92 null behavior
AutoClose: True to close the database when the last user exits
AutoCreateStat: True to automatically create statistics as required
SQL-DMO OBJECT MODEL
Development with
SQL server
PART
V
2627ch20.qxd 8/22/00 11:13 AM Page 779
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 20 • SQL-DMO
780
AutoShrink: True to periodically attempt to shrink the database
AutoUpdateState: True to automatically update statistics as required
ColumnsNullByDefault: True to default newly created columns to
nullable
CompareNull: True to evaluate NULL=NULL as NULL
ContactNull: True to propagate nulls in string concatenation
CursorCloseOnCommit: True to close cursors when changes are committed
DBOUseOnly: True to limit access to the database to the database owner
DefaultCursor: True to give cursors created in a batch local scope
Offline: True to place the database offline
QuoteDelimiter: True to allow quoted delimiters
ReadOnly: True to make the database read-only
RecursiveTriggers: True to allow triggers to fire other triggers
SelectIntoBulkCopy: True to allow SELECT INTO and bulkcopy operations
SingleUser: True to limit the database to one user at a time
TornPageDetection: True to force SQL Server to automatically scan for
torn pages
TruncateLogOnCheckpoint: True to truncate the log on each checkpoint
You’ll see an example of using the DBOption object later in the chapter in the sec-
tion “Changing a Configuration Option.”
The StoredProcedure Object
The StoredProcedure object, as you can probably guess by now, represents a single
SQL Server stored procedure. This can be either a system stored procedure or a user-
defined stored procedure. You can use the methods and properties of this object to
create stored procedures, set their properties, execute them, and so on.
Table 20.6 shows the methods (M) and properties (P) of the StoredProcedure
object. This is a complete list, because this object does not have the overwhelming
complexity of some of the other objects that represent larger parts of SQL Server. Note
that SQL Server 2000 also exposes an extended StoredProcedure2 object.
2627ch20.qxd 8/22/00 11:13 AM Page 780
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
781
TABLE 20.6: DETAILS OF THE STOREDPROCEDURE OBJECT
Name Type Extended Description
Alter M Assigns new text to the stored procedure
AnsiNullsStatus M ✔ True when this stored procedure refers to a
table defined with ANSI null behavior
CreateDate P Date and time this stored procedure was
created
Deny M Denies permission to a specific user
EnumDependencies M Returns a list of objects that depend on this
stored procedure or objects that this stored
procedure depends on
EnumParameters M Returns a list of parameters for this stored
procedure
Grant M Grants permissions to a specific user
ID P Unique identifier that SQL Server uses to track
this stored procedure
IsDeleted P ✔ True if this stored procedure has been deleted
by another session
ListPermissions M Lists implicit and explicit permissions for a
specified user
ListUserPermissions M Lists explicit permissions for a specified user
Name P Name of the stored procedure
Owner P Owner of the stored procedure
QuotedIdentifierStatus P True if this stored procedure depends on a
table that uses quoted identifiers
Remove M Drops the stored procedure
Revoke M Reverses the effect of Grant or Deny
Script M Generates a T-SQL script for this stored
procedure
Startup P True if this stored procedure runs at server
startup
SystemObject P True if this is a system stored procedure
Text P Actual T-SQL text of the stored procedure
Type P Indicates whether this is a regular or extended
stored procedure
SQL-DMO OBJECT MODEL
Development with
SQL server
PART
V
2627ch20.qxd 8/22/00 11:13 AM Page 781
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 20 • SQL-DMO
782
You’ll learn more about the StoredProcedure object in the section “Creating and
Executing a Stored Procedure” later in this chapter.
The Table Object
The Table object (along with the extended Table2 object in SQL Server 2000) repre-
sents a single table within a database. Other child objects of the Table object let you
work with all the other things that go into a table: columns, indexes, keys, con-
straints, and so on. Figure 20.1 shows the other objects that are descendants of the
Table object. Later in this chapter, in the section “Creating a Table,” you’ll see how to
use some of these objects together in code.
FIGURE 20.1
The Table object and
its descendants
The Table object is quite complex, with many methods and properties. Table 20.7
lists some of the more important methods (M) and properties (P) of this object.
Table
Checks ClusteredIndex Columns
Indexes
Keys PrimaryKey Triggers
Key
Trigger
KeyColumns
ReferencedColumns
Check Column
Index
DRIDefault
IndexedColumns
2627ch20.qxd 8/22/00 11:13 AM Page 782
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
783
TABLE 20.7: SELECTED DETAILS OF THE TABLE OBJECT
Name Type Extended Description
AnsiNullsStatus P ✔ True if the table uses ANSI null handling
DataSpaceUsed P Actual storage space used (in KB) for the
table’s data
EnumDependencies M Lists all the objects that this table depends on
or all the objects that depend on this table
EnumReferencedTables M Lists all the tables that this table references
via DRI
EnumReferencingTables M Lists all the tables that reference this table
via DRI
FullTextIndexActive P True if this table is participating in full-text
indexing
FullTextPopulation M ✔ Builds the full-text index for the table
GenerateSQL M Creates a SQL statement that will create this
table
HasClusteredIndex P True if the table has a clustered index
HasIndex P True if the table has any index
ImportData M Imports data via bulkcopy
Name P Name of the table
Owner P Owner of the table
RebuildIndexes M Rebuilds the indexes for the table
Rows P Number of rows stored in the table
TruncateData M Deletes all rows from the table without logging
UpdateStatistics M Updates the information used for determining
optimum query plans
The Column Object
The Column object (together with the extended Column2 object) is a subsidiary of
the Table object. The Table object contains a Columns collection, which in turn con-
tains one Column object for each column in the table. Of course, you can use the
Columns collection to iterate through all of the columns in a table:
Dim objTable as SQLDMO.Table
Dim objColumn As SQLDMO.Column
…
SQL-DMO OBJECT MODEL
Development with
SQL server
PART
V
2627ch20.qxd 8/22/00 11:13 AM Page 783
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét