Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem


Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p16', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Nội dung Text: MASTERING SQL SERVER 2000- P16

  1. 770 CHAPTER 20 • SQL-DMO using properties of the SQLServer object. For example, once you’ve instantiated a SQLServer object and connected it to a particular server, you can use the Databases property of the SQLServer object to retrieve a Database object referring to a particular database: Set objDatabase = objSQLServer.Databases(“Northwind”) In this example, objSQLServer is a SQLServer object. When this line of code is exe- cuted, the objDatabase object will be set to point to the Northwind database owned by the specified SQL Server. You can also use the SQLServer object to perform operations that affect an entire server. For example, you could use properties and methods of this object to drop a database, set serverwide options, or manipulate the default timeout for SQL Server logins. NOTE All of the sample code in this chapter was written with Visual Basic. Of course, because SQL-DMO is a COM server, you can use the objects, methods, and properties it exposes from any COM client language. You need to set a reference to the Microsoft SQLDMO Object Library in your client code to use these objects. In the following pages, we’ll list the properties, methods, and events of the SQLServer object. These lists will give you an overview of the tasks that you can per- form directly with this object. But first, we need to explain why there are two differ- ent SQLServer objects in SQL Server 2000. Later in this chapter (in the section “Creating and Connecting a SQLServer Object”), we’ll show you an example of work- ing with these objects. SQLServer and SQLServer2 SQL Server 2000 has two different objects to represent the entire SQL Server. The SQL- Server object can be used with both SQL Server 2000 and earlier versions of SQL Server. The SQLServer2 object can be used only with SQL Server 2000. The SQLServer2 object includes all of the methods, properties, and events of the SQLServer object. In addition, it exposes some new methods and properties that pertain only to SQL Server 2000. The SQLServer2 object is an example of an extended SQL-DMO object. If you’re sure that your code will be working with the latest version of SQL Server, you should use the extended objects. Otherwise, you should use the earlier versions of the objects for portability.
  2. SQL-DMO OBJECT MODEL 771 You can’t create the extended objects directly. Rather, you create the original object and then retrieve the extended object from the interface of the original object. In Visual Basic or VBA, this is as simple as assigning one object to another, as this example shows: Dim objSQLServer As SQLDMO.SQLServer2 Dim objOldSQLServer As SQLDMO.SQLServer Set objOldSQLServer = New SQLDMO.SQLServer objOldSQLServer.LoginSecure = True objOldSQLServer.Connect “HENHOUSE” On Error Resume Next Set objSQLServer = objOldSQLServer PA R T If Err = 0 Then Debug.Print objSQLServer.StartupAccount V Else Debug.Print “This function is not supported.” End If Development with Here, the purpose is to retrieve the Windows NT account used by the SQLServer- Agent by querying the StartupAccount property of the SQLServer object. This prop- SQL server erty is available from only the extended version of the object. The code first connects to a particular SQL Server (in this case, one named HENHOUSE) using the original SQLServer object. This will work for any version of SQL Server from 6.5 forward. The code then assigns this object to the new extended object. If the assignment succeeds, the code can retrieve the value of the StartupAccount property; if the assignment fails, you know that this is an older server and that the requested information isn’t available. Properties Table 20.2 lists the properties of the SQLServer and SQLServer2 objects. Although in general we’re not going to list all the properties of objects in this chapter, we wanted to give you a feel for the richness of the SQL-DMO object model. Properties with a check mark in the Extended column are available on only the SQLServer2 object.
  3. 772 CHAPTER 20 • SQL-DMO TABLE 20.2: PROPERTIES OF THE SQLSERVER OBJECT Property Extended Description AnsiNulls True if ANSI null compatibility is enabled Application The SQL-DMO Application object AutoReConnect True if the SQLServer object automatically tries to reconnect in case of any problem AutoStart ✔ True if the SQLServerAgent starts automatically when the operating system starts BlockingTimeout Timeout interval in milliseconds when waiting for a blocked resource CodePage Code page of the server Collation ✔ Collation name for this server CommandTerminator T-SQL batch delimiter (defaults to GO) ConnectionID Unique identifier for a connected SQLServer object EnableBcp True if bulkcopy operations are enabled Hostname Network name of the client where this object is running InstanceName ✔ Name of the current instance of SQL Server IsClustered ✔ True if this server is part of a cluster Isdbcreator True if the login for this object is a member of the dbcreator role Isdiskadmin True if the login for this object is a member of the diskadmin role Isprocessadmin True if the login for this object is a member of the processadmin role Issecurityadmin True if the login for this object is a member of the securityadmin role Isserveradmin True if the login for this object is a member of the serveradmin role Issetupadmin True if the login for this object is a member of the setupadmin role Issysadmin True if the login for this object is a member of the sysadmin role Language Language ID for this server Login Username used for this connection
  4. SQL-DMO OBJECT MODEL 773 TABLE 20.2: PROPERTIES OF THE SQLSERVER OBJECT (CONTINUED) Property Extended Description LoginSecure True if using integrated security LoginTimeout Milliseconds to wait for a connection MaxNumericPrecision Maximum precision of floating-point numbers on this server Name Name of the SQL Server NetName Network name of the server NetPacketSize Packet size used on the network by this server NextDeviceNumber Next device ID (this property is obsolete) ODBCPrefix True if error sources are returned with error messages Password Password used for this connection PA R T PID ✔ Process ID for this instance of SQL Server V ProcessID Process ID for this connection ProcessInputBuffer Contents of the current input buffer ProcessOutputBuffer Contents of the current output buffer Development with ProductLevel ✔ Product level (Beta or RTM) QueryTimeout Milliseconds to wait for query results SQL server QuotedIdentifier True if quoted identifiers are enabled on this server RegionalSetting True if SQL Server uses the client locale for dis- playing data SaLogin True if the login for this object is a member of the sysadmin role ServiceName ✔ Name of the computer where this server is running StartupAccount ✔ Name of the login account used by the SQLServerAgent service Status Status (running, paused, stopped) of the server StatusInfoRefetchInterval Sets the interval used to automatically refetch status information TranslateChar True if high-order characters are translated to the client locale TrueLogin SQL Server login used for the current connec- tion (even if integrated security was specified)
  5. 774 CHAPTER 20 • SQL-DMO 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 CommandShell- Executes an operating system command ImmediateWithResults 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
  6. SQL-DMO OBJECT MODEL 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 PA R T the server V EnumLoginMappings Enumerates the security mappings on the current server EnumNTDomainGroups Enumerates the groups in the server’s Development with domain EnumProcesses Enumerates the SQL Server processes on the current server SQL 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 ExecuteWithResultsAnd- Submits a T-SQL batch and returns the Messages results along with any messages from the server 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
  7. 776 CHAPTER 20 • SQL-DMO 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
  8. SQL-DMO OBJECT MODEL 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) PA R T • When retrieving information requires supplying other information (such as V 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) Development with These rules for distinguishing methods from properties are consistent across all the SQL-DMO objects. SQL server 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
  9. 778 CHAPTER 20 • SQL-DMO 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.
  10. SQL-DMO OBJECT MODEL 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 PA R T Permissions P A set of bitflags that indicate the privileges of the current SQL-DMO session in this database V PrimaryFilePath P Path to the primary data file for this database Script M Creates a T-SQL script that re-creates this Development with database Shrink M Reduces the space of the files holding this database SQL server 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
  11. 780 CHAPTER 20 • SQL-DMO 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.
  12. SQL-DMO OBJECT MODEL 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 PA R T ID P Unique identifier that SQL Server uses to track V this stored procedure IsDeleted P ✔ True if this stored procedure has been deleted by another session Development with ListPermissions M Lists implicit and explicit permissions for a specified user ListUserPermissions M Lists explicit permissions for a specified user SQL server 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
  13. 782 CHAPTER 20 • SQL-DMO 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 Table its descendants Checks ClusteredIndex Columns Indexes Check Column Index DRIDefault IndexedColumns Keys PrimaryKey Triggers Key Trigger KeyColumns ReferencedColumns 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.
  14. SQL-DMO OBJECT MODEL 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 PA R T GenerateSQL M Creates a SQL statement that will create this V table HasClusteredIndex P True if the table has a clustered index HasIndex P True if the table has any index Development with ImportData M Imports data via bulkcopy Name P Name of the table SQL server 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 …
  15. 784 CHAPTER 20 • SQL-DMO ‘ Table must be instantiated before this looping code is called For Each objColumn in objTable.Columns ‘ Do something with each column here Next objColumn The Column object has more properties than methods. You’ll find that this is com- mon as you get to the more specific objects. In code, you can use properties to describe these objects, but manipulating objects via methods is normally left to the larger objects. Table 20.8 shows some of the methods (M) and properties (P) of the Column object. TABLE 20.8: SELECTED DETAILS OF THE COLUMN OBJECT Name Type Extended Description AllowNulls P True if the column is nullable AlterDataType M ✔ Changes the datatype of a column BindDefault M Associates a default with this column Collation P ✔ Collation for this column ComputedText P T-SQL statement used to generate the value of a computed column DataType P Name of the datatype for this column Identity P True if this is an identity column IdentityIncrement P Increment for an identity column IdentitySeed P Starting value for an identity column InPrimaryKey P True if this column is part of the primary key IsComputed P True if this is a computed column Length P Maximum data length for the column Name P Name of the column NumericPrecision P Precision for a numeric column NumericScale P Scale for a numeric column Remove M Drops this column from the table
  16. SAMPLE SQL-DMO CODE 785 The Alert Object Not all of the objects within SQL-DMO are directly related to data. A good example of one of these helper objects is the Alert object. The Alert object corresponds to a single SQL Server alert. If you’re working in SQL Server Enterprise Manager, you’ll find alerts in the Management folder under the SQLServerAgent node. NOTE Alerts are covered in more detail in Chapter 17. You can use the Alert object to create a new alert or modify the properties of an existing alert. The AddNotification method is used to associate operators (who can be represented by Operator objects) with an alert. Table 20.9 shows some of the methods (M) and properties (P) of the Alert object. PA R T V TABLE 20.9: SELECTED DETAILS OF THE ALERT OBJECT Name Type Description Development with AddNotification M Associates an operator with this alert Category P Category that this alert belongs to SQL server DatabaseName P Database that this alert monitors Enabled P True if this alert is active EnumNotifications M Lists all the notifications for this alert JobName P Job to run when this alert is activated MessageID P Error number that activates this alert Name P Name of the alert Severity P Error severity that activates this alert Sample SQL-DMO Code Now that you have some idea that SQL-DMO objects exist, and know the sort of prop- erties and methods that they implement, it’s time to see some examples of their use.
  17. 786 CHAPTER 20 • SQL-DMO In this section, we’ll show you seven techniques that are representative of the kinds of things you can do with SQL-DMO: • Creating and connecting a SQLServer object • Creating a database • Changing a configuration option • Creating a table • Dropping a table • Creating and executing a stored procedure • Creating an alert However, before we dig into the code, we’d like to talk just a bit about why you would write this sort of application. SQL-DMO is mainly useful for two sorts of programs: general-purpose management utilities and limited-use utilities that are safe for users. Some developers make their living enhancing and extending applications like SQL Server. Suppose, for example, you have an idea for a better way to design tables. Per- haps instead of the standard grid metaphor, you’re envisioning a drag-and-drop envi- ronment, where you can grab predefined fields and stick them together to form tables. Well, once your application has progressed to the point where the user inter- face works, you’ll need to tell SQL Server what objects to create and which properties to assign to those objects. SQL-DMO is the obvious choice for this interface to SQL Server, because it encompasses all of the things one normally needs to do with objects. On the other end of the spectrum, you might have users who occasionally need to perform an administrative task on your SQL Server. Perhaps the personnel depart- ment is responsible for adding new hires in a specific job position to the list of autho- rized SQL Server operators. You wouldn’t necessarily want to train your personnel people in the complete use of SQL Server Enterprise Manager. Instead, you could use SQL-DMO in conjunction with Visual Basic to create a specialized front-end program that could be used only for creating operators. This would be easier to train your per- sonnel folks to use and safer for the server. We hope that those two illustrations, combined with the code in the rest of the chapter, will inspire you to use SQL-DMO in your own applications. Creating and Connecting a SQLServer Object Before you can do anything else with SQL-DMO, you need to establish a connection to the SQL Server with which you want to work. This involves three basic steps: 1. Create the SQLServer object. 2. Set security properties. 3. Connect to the server.
  18. SAMPLE SQL-DMO CODE 787 The simplest way to connect to a SQL Server is to use Windows NT integrated secu- rity. You can do this by setting the LoginSecure property of the SQLServer object to True, as in the following code fragment: Dim objSQLServer As SQLDMO.SQLServer Set objSQLServer = New SQLDMO.SQLServer objSQLServer.LoginSecure = True objSQLServer.Connect “HENHOUSE” Debug.Print objSQLServer.TrueLogin This code first creates and instantiates (with the New keyword) a SQLServer object. The code then sets the LoginSecure property and attempts to connect with a server named HENHOUSE. If this works, the code will print out the name of the Windows PA R T NT security account that was used for the connection. V TI P The code in this chapter uses a server named HENHOUSE for all the examples. Of course, you’ll need to change this to the name of your own SQL Server if you want to try Development with the code on your own network. SQL server You can also log in to a server by supplying a SQL Server username and password, as in this example: Dim objSQLServer As SQLDMO.SQLServer Set objSQLServer = New SQLDMO.SQLServer objSQLServer.LoginSecure = False objSQLServer.Login = “sa” objSQLServer.Password = “” objSQLServer.Connect “HENHOUSE” Debug.Print objSQLServer.TrueLogin In this case, the code attempts to log in as the sa user with a blank password. If this works, the TrueLogin property will return the name of the SQL Server security account that was used (in this case, “sa”).
  19. 788 CHAPTER 20 • SQL-DMO Finally, you can simplify this code by supplying the login and password directly as parameters to the Connect method. Also, the LoginSecure property defaults to False, so you can omit setting this property if you’re using SQL Server security. Note that this won’t work if you’re using integrated security: Dim objSQLServer As SQLDMO.SQLServer Set objSQLServer = New SQLDMO.SQLServer objSQLServer.Connect “HENHOUSE”, “sa”, “” Debug.Print objSQLServer.TrueLogin Creating a Database One task that SQL-DMO is well suited for is the creation of new objects. For example, you can use SQL-DMO to create a database entirely through code, without using the UI or explicitly executing a CREATE DATABASE statement. The code for creating a new database starts out by defining variables and connect- ing to a SQL Server, just like any other SQL-DMO procedure: Dim objDatabase As SQLDMO.Database Dim objDBFile As SQLDMO.DBFile Dim objLogFile As SQLDMO.LogFile Dim objSQLServer As SQLDMO.SQLServer ‘ Connect to the server using integrated security Set objSQLServer = New SQLDMO.SQLServer objSQLServer.LoginSecure = True objSQLServer.Connect “HENHOUSE” The next task is to instantiate the SQL-DMO database object using the New opera- tor and give the database a name: ‘ Create the database object Set objDatabase = New SQLDMO.Database ‘ Give it a name objDatabase.Name = “NewDB” At this point, the Database object exists, but SQL-DMO doesn’t yet have enough information to create the database on disk. The essential missing piece of information is where to store the database. You can fill this gap by creating a DBFile object (which
  20. SAMPLE SQL-DMO CODE 789 represents a single physical storage file) and giving it an internal name and a physical storage name: ‘ Now for the physical storage Set objDBFile = New SQLDMO.DBFile objDBFile.Name = “NewDBFile” objDBFile.PhysicalName = “c:\Temp\NewDB.mdf” ‘ Size is in megabytes objDBFile.Size = 4 WARN I NG In production code, you wouldn’t want to include the actual filename in the code. You might use a common dialog to prompt the user for a file location, or look at the DBFile object belonging to the master database to determine where other SQL Server databases on this server are stored. PA R T Once the DBFile object has been created, you can associate it with the database by V adding it to the PRIMARY filegroup of the Database object. There must be at least one DBFile added to this filegroup before you can save the database. You can also add as Development with many additional files as you’d like with the same technique. ‘ Add this file to the primary filegroup objDatabase.FileGroups(“PRIMARY”).DBFiles.Add objDBFile SQL server Optionally, you can add a log file for the database with a similar technique. If you skip this step, SQL Server will use the server defaults to create a log file. ‘ Now for a log file Set objLogFile = New SQLDMO.LogFile objLogFile.Name = “NewDBLog” objLogFile.PhysicalName = “c:\Temp\NewDB.ldf” objLogFile.Size = 2 ‘ Add this log file to the database objDatabase.TransactionLog.LogFiles.Add objLogFile Once the database has a name and a storage location, you can cause SQL-DMO to create the database by adding the Database object to the server’s Databases collection: ‘ And finally add the database to the server objSQLServer.Databases.Add objDatabase That’s all you need to do to create a new database with SQL-DMO. As with any other method of creating a new database, the database will initially be a copy of the
Đồng bộ tài khoản