Microsoft SQL Server 2005 Developer’s Guide- P23

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

0
39
lượt xem
8
download

Microsoft SQL Server 2005 Developer’s Guide- P23

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

Microsoft SQL Server 2005 Developer’s Guide- P23:This book is the successor to the SQL Server 2000 Developer’s Guide, which was extremely successful thanks to all of the supportive SQL Server developers who bought that edition of the book. Our first thanks go to all of the people who encouraged us to write another book about Microsoft’s incredible new relational database server: SQL Server 2005.

Chủ đề:
Lưu

Nội dung Text: Microsoft SQL Server 2005 Developer’s Guide- P23

  1. Chapter 12: Developing with SMO 459 TargetServerGroups Returns a Microsoft.SqlServer.Management.Smo .Agent.TargetServerGroupCollection that represents the target server groups defined on SQL Server Agent. TargetServers Returns a Microsoft.SqlServer.Management.Smo.Agent .TargetServerCollection that represents the target server defined on SQL Server Agent. NOTE This list represents only the major objects in the SMO JobServer object hierarchy. Many of these objects also contain their own objects, properties, and collections. Building the SMO Sample Application In the first part of this chapter, you had an overview of SMO objects, followed by an explanation of some of the most important SMO collections, methods, and properties. In the next part of the chapter, you learn how you can put SMO to work in a sample VB.NET program. Figure 12-3 presents the main screen of an example SMO application name SMOSample.exe that was built using VB.NET. Figure 12-3 A sample VB.NET SMO application
  2. 460 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e This sample application demonstrates many of the essential techniques required to use SMO, including: Creating the Server object Connecting to a SQL Server system Using collections Getting specific objects from a collection Creating new database objects Copying existing database objects Disconnecting from a SQL Server system The sample application lists the registered SQL Server systems in a drop-down box, enabling the user to select the desired system to connect to. After selecting the desired SQL Server system, the user selects the appropriate authentication type. If SQL Server Authentication is selected, then the user is prompted to enter the Login ID and Password. If Windows Authentication is chosen, the user simply clicks the Connect button. Following a successful connection, the sample application lists all the databases the user is authorized to use. Lists showing the tables and columns for the first database in the list are filled in automatically. The Columns list is automatically filled in with the column names from the first table. Likewise, the Attributes list is filled in with the attributes of the first column in the list. Clicking any of the list items updates all the dependent lists. For instance, clicking a different database name causes the list of tables, columns, and column attributes to be updated with information from the newly selected database. And clicking a different item in the Tables list causes the Columns and Attributes lists to be updated with the column names and attributes of the selected table. Likewise, clicking a different entry in the column list updates the Attributes list with the attributes of the newly selected column. In addition to listing the SQL Server system’s databases schema, the sample SMO application also illustrates how SMO is capable of performing basic database management functions. Clicking Create Database prompts the user to create a new database. Clicking Copy Table lets the user create a copy of an existing table in the selected database. Likewise, clicking Show Keys displays the keys where a given column is used. Creating the Server Object The first thing the sample application needs to do is create an instance of the Server object. In the sample application, the Server object is shared by all the objects on the
  3. Chapter 12: Developing with SMO 461 form, so it has been created using the following code in the Declarations section of the main Visual Basic form: Dim oSQLServer As New Server() Listing the Registered SQL Systems After a new global instance of the SMO object is instantiated in the project’s Declarations section, the list of registered SQL Server systems is retrieved during the initial Form_Load subroutine and displayed in a combo box enabling the user to select a specific SQL Server system. The code illustrating how to use SMO to retrieve the list of registered SQL Server systems is shown in the following listing: Private Sub Form_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Dim oRegisteredServers As RegisteredServers.RegisteredServer() = _ SmoApplication.SqlServerRegistrations.EnumRegisteredServers() ' Add each name to the combobox For Each oRegisteredServer As RegisteredServers.RegisteredServer _ In oRegisteredServers cboServer.Items.Add(oRegisteredServer.ServerInstance) Next End Sub In the beginning of the Form_Load subroutine, you can see where an instance of the RegisteredServer object is created and filled using the EnumRegisteredServers method of the SqlServerRegistrations class. Using this method is a vast improvement over SQL-DMO, as there is no longer any need to use a recursive loop through the servers and server groups to list the registered servers. Next, you see the For Each Loop read through all the individual SQL Server system names contained in the RegisteredServers collection. The registered systems objects are assigned to the oRegisteredServer object, and the Items.Add method of the cboServer combo box is used to add the value of the oRegisteredServer .ServerInstance property to the combo box’s drop-down list. Connecting to the Selected SQL Server System After an oSQLServer object is created, you can use the ServerConnection object’s connect method to establish a connection to a SQL Server system. In the sample application, the SQL Server connection is started after the user selects the name
  4. 462 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e of the appropriate SQL Server system, then enters the appropriate authentication information, and then clicks Connect. Clicking Connect executes the following code in the cmdConnect_Click subroutine: Private Sub cmdConnect_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles cmdConnect.Click ResetScript() Dim oServerConn As New ServerConnection() oServerConn.ServerInstance = cboServer.Text oServerConn.LoginSecure = False Dim oDatabase As Database Try ' Setup a secure login for NT security If Me.optNTSecurity.Checked = True Then oServerConn.LoginSecure = True Else oServerConn.Login = txtLogin.Text oServerConn.Password = txtPwd.Text End If ' Connect to the selected SQL Server system oServerConn.Connect() oSQLServer = New Server(oServerConn) ' Disable the Connect button cmdConnect.Enabled = False ' List the databases For Each oDatabase In oSQLServer.Databases If oDatabase.Status DatabaseStatus.Inaccessible Then lstDatabases.Items.Add(oDatabase.Name) End If Next oDatabase ' Populate the other list boxes lstDatabases_Click(sender, e) ' Enable all of the other buttons cmdCreateDB.Enabled = True cmdTransferTables.Enabled = True cmdShowScript.Enabled = True Catch SQLSMOError() End Try End Sub
  5. Chapter 12: Developing with SMO 463 In the beginning of this routine, an instance of Database is declared and named oDatabase. Next, the SMO ServerConnection object is declared and the server name from the cboServer drop-down box is put to the ServerInstance property. The ServerConnection object is also set with the LoginSecure type and Login and Password properties based on the security type selected by the user. Next, the oServerConn object is then passed as a parameter to the oSQLServer object’s constructor. If the user chose to use Windows Authentication, then the LoginSecure property of the oServerConn object is set to True, indicating that a trusted connection between SQL Server and NT will be used to authenticate the client. The Connect method could fail if the user enters an invalid SQL Server name or other invalid login information. As you can see in the cmdConnect_Click subroutine, a Try/Catch loop is used around the Connect method of the SMO objects. If the Connect method fails, a run-time error is generated and Visual Basic’s error handler is invoked. This causes control of the cmdConnect_Click subroutine to jump to the Catch portion of the Try/Catch loop, where the SQLSMOError function is executed. Handling SMO errors and the SQLSMOError subroutine is discussed later in this chapter. Listing Databases If the Connect method is successful, the cmdConnect_Click subroutine continues and the cmdConnect button is disabled, preventing the user from attempting to connect a second time. Next, the For Each loop is executed, which fills the list of databases on the SMO example program with the database names from the connected SQL Server system. The Databases property of the oSQLServer object contains a collection of the database names for the connected SQL Server. In the cmdConnect_Click subroutine shown previously, you can see how Visual Basic’s For Each operation is used to loop through the collection of database names and add each name to the list of databases. Each iteration of the For Each loop addresses a different Database object in the Databases collection. For instance, the first time the For Each loop is executed, the first Database object in the Databases collection is the current object. The second time the For Each loop is executed, the second Database object in the Databases collection is the current object. Within the For Each loop, first the database is tested to ensure that it’s accessible. For instance, the user running the application might not have permission to access the database. Next, the database name contained in the Name property of the current oDatabase object is added to the lstDatabases ListBox object using the Items.Add method. The For Each loop executes once for each object contained in the Databases collection.
  6. 464 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e To fill out the Tables list, the Columns list, and the Columns attributes automatically, the cmdConnect_Click subroutine then executes the lstDatabases_Click subroutine, which retrieves a list of tables contained in the first database in the oDatabases collection. Listing Tables The code that retrieves the database table information for the sample application is executed either automatically at the end of the cmdConnect_Click subroutine or when the user clicks one of the database names in the list of databases. Both cases execute the lstDatabases_Click subroutine shown here: Private Sub lstDatabases_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles lstDatabases.Click ResetScript() Dim oCurDatabase As Database Dim oTable As Table Try ' Get the selected database name If lstDatabases.SelectedIndex >= 0 Then oCurDatabase = oSQLServer.Databases(lstDatabases.SelectedIndex) Else ' otherwise pick the first database oCurDatabase = oSQLServer.Databases(0) lstDatabases.SetSelected(0, True) End If ' Clear the dependant objects lstTables.Items.Clear() lstColumns.Items.Clear() txtAttributes.Text = vbNullString ' Add the table names to the list For Each oTable In oCurDatabase.Tables lstTables.Items.Add(oTable.Name) Next oTable ' Populate the dependant objects lstTables_Click(sender, e) Catch SQLSMOError() End Try End Sub At the top of the lstDatabases_Click subroutine, you can see where instances of the SMO Database object and the Table object are created. Next, the Try/Catch
  7. Chapter 12: Developing with SMO 465 loop is set up to transfer control to the Catch tag and the SQLSMOError function to trap any run-time errors. Next, the SelectedIndex property of the lstDatabases list is checked to determine if this subroutine was evoked automatically by the cmdConnect_Click subroutine or by the user’s clicking one of the database names in the list of databases. If the value of the lstDatabases.SelectedIndex property is 0 or greater, then the user clicked one of the database list items and the program should display the tables for the selected database. Otherwise, if the lstDatabases .SelectedIndex property value is less than 0 (actually –1), then no user selection was made. The oCurDatabase object is set to the SelectedIndex property value of the Database objects contained in the oSQLServer.Databases collection. In other words, if the user clicks the fifth item in the lstDatabases ListBox, the lstDatabase .SelectedIndex property value will be used to set the oCurDatabase object to the fifth Database object in the oSQLServer.Databases collection. If no selection was made, the oCurDatabase object is set to the first Database object in the collection. Next, all the information currently displayed in the dependent interface objects is cleared. Then, Visual Basic’s For Each operation is used to list the members of the Tables collection. The For Each operation loops through the collection of SQL Server table names contained in the oCurDatabase object. Inside the For Each loop, the name of each member of the Tables collection is added to the lstTables list using the Items.Add method. Listing Columns After all the table names have been added to the list of tables, the lstTables_Click subroutine is executed to refresh the list of column names. The code that retrieves the names of the columns contained in a given table is executed either automatically at the end of the lstTables_Click subroutine or when the user clicks one of the table items displayed in the Tables list. In both instances, the lstTables_Click subroutine shown here is executed: Private Sub lstTables_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles lstTables.Click ResetScript() Dim oCurDatabase As Database Dim oCurTable As Table Dim oColumn As Column Try ' Get the selected table name If lstTables.SelectedIndex >= 0 Then oCurDatabase = oSQLServer.Databases(lstDatabases.SelectedIndex) oCurTable = oCurDatabase.Tables(lstTables.SelectedIndex) Else ' otherwise pick the first table
  8. 466 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e oCurDatabase = oSQLServer.Databases(0) oCurTable = oCurDatabase.Tables(0) lstTables.SetSelected(0, True) End If ' Clear the dependant objects lstColumns.Items.Clear() txtAttributes.Text = vbNullString ' Add the column names to the list For Each oColumn In oCurTable.Columns lstColumns.Items.Add(oColumn.Name) Next oColumn ' Populate the dependant objects lstColumns_Click(sender, e) Catch SQLSMOError() End Try End Sub The lstTables_Click subroutine is structured much like the lstDatabases_Click subroutine. The first thing the lstTables_Click subroutine does is to make instances of the SMO Database, Table, and Column objects named oCurDatabase, oCurTable, and oColumn. Next, the lstTables_Click subroutine sets up the Try/Catch loop used to catch any errors generated and, subsequently, execute the SQLSMOError function. Then the lstTables.SelectedIndex property is tested to determine if this subroutine has been evoked automatically from the lstDatabases_Click subroutine or if this subroutine has been evoked by the user’s clicking one of the items in the lstTables list. If the lstTables_Click subroutine was called by the user’s clicking an entry in the lstTables list, then the oCurDatabase and oCurTable objects are assigned the value of the selected list items. Otherwise, the oCurDatabase and oCurTable variables are set to the first entry in each of their respective lists. After the variables containing the parent Database and Table names have been set, the old values in the lstColumns list and txtAttributes text box interface objects are cleared. This ensures that the lstColumns list and the txtAttributes text box will contain the values from the user’s new selection. Like the Databases and Tables collections, a For Each loop is used to iterate through the collection of Column objects. As the For Each loop progresses, the name of each current Column object is added to the lstColumns ListBox object. After all the names from the Columns collection have been added to the lstColumns list, the lstColumns_Click subroutine is called to retrieve a subset of the specific attributes of a selected column.
  9. Chapter 12: Developing with SMO 467 Retrieving Column Attributes The code that retrieves the attributes of a specific column is executed either automatically at the end of the lstColumn_Click subroutine or when the user clicks an item in the Columns list. The lstColumns_Click subroutine shown here is executed for both of these actions: Private Sub lstColumns_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles lstColumns.Click ResetScript() Dim oCurDatabase As Database Dim oCurTable As Table Dim oColumn As Column Try ' Get the selected column name If lstColumns.SelectedIndex >= 0 Then oCurDatabase = oSQLServer.Databases(lstDatabases.SelectedIndex) oCurTable = oCurDatabase.Tables(lstTables.SelectedIndex) oColumn = oCurTable.Columns(lstColumns.SelectedIndex) Else ' otherwise pick the first column oCurDatabase = oSQLServer.Databases(0) oCurTable = oCurDatabase.Tables(0) oColumn = oCurTable.Columns(0) lstColumns.SetSelected(0, True) End If ' Clear the dependant objects txtAttributes.Text = vbNullString ' Add the attributes to the textbox txtAttributes.Text = oColumn.Name.ToString() _ & vbCrLf & vbCrLf _ & "Data Type:" & vbCrLf _ & oColumn.DataType.ToString() & vbCrLf & vbCrLf _ & "Length:" & vbCrLf _ & oColumn.Properties.Item("Length").Value.ToString() _ & vbCrLf & vbCrLf _ & "Precision:" & vbCrLf _ & oColumn.Properties.Item("NumericPrecision").Value.ToString() _ & vbCrLf & vbCrLf _ & "Scale:" & vbCrLf _ & oColumn.Properties.Item("NumericScale").Value.ToString() Catch ex As Exception SQLSMOError() End Try End Sub
  10. 468 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Again, the lstColumns_Click subroutine starts by declaring instances of the SMO Database, Table, and Column objects. Like the other subroutines, the lstColumns_Click subroutine then determines if the user has clicked an entry in the lstColumns list or if the lstColumns_Click subroutine was called automatically from the lstTables_Click subroutine. If the user has clicked one of the lstColumn items, then the oCurDatabase, oCurTable, and oColumn variables are assigned the select list items. Otherwise, the oCurDatabase, oCurTable, and oColumn variables are assigned the first item in each of the respective lists. Next, the txtAttributes.Text property is cleared. You might notice that unlike the other subroutines, the lstTables_Click subroutine doesn’t use a list box or iterate through a collection. Instead, the lstColumns_Click subroutine displays a selection of properties from one specific member of the Columns collection. The following properties of the Column object are displayed: Name DataType Length NumericPrecision NumericScale The txtAttributes text box has the Multiline property enabled, which allows multiple lines to be displayed in the text box. Each line is separated by CR + LF characters, which are represented by the vbCrLf constant. Creating Databases The earlier examples in this chapter illustrated how to list the various SQL Server database objects using SMO collections. SMO is capable of far more than listing databases or tables, however. SMO’s extensive object framework lets you perform virtually any management function that can be performed using the SQL Server Enterprise Manager. The following example illustrates how SMO can be used to create a new SQL Server database programmatically. The cmdCreateDB_Click subroutine is executed when the user clicks the Create Database button provided by the SMOSample application, shown earlier in Figure 12-3. The code for the cmdCreateDB_Click subroutine is shown in the following listing:
  11. Chapter 12: Developing with SMO 469 Private Sub cmdCreateDB_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles cmdCreateDB.Click ResetScript() Dim sDatabaseName As String 'Input the database name sDatabaseName = InputBox("Enter the new database name", _ "New Database") Try Dim oDatabase As New Database(oSQLServer, sDatabaseName) oDatabase.Create() 'Add the DB name to the list lstDatabases.Items.Add(oDatabase.Name) Catch SQLSMOError() End Try End Sub This subroutine begins by declaring a string object called sDatabaseName. The Visual Basic InputBox method is then called to prompt the user to enter a new database name to create, and the result is placed into the newly created string sDatabaseName. A new Database object is then instantiated using the current SQL Server instance and sDatabaseName string as parameters. To create the new database on the server, the database object’s Create method is called. The Create method creates the new database using default property settings; however, you can also set the database object properties before calling the Create method. Last, the list of databases displayed by the SMOSample application is updated with the new name by using the lstDatabases object’s Items.Add method to add the name of the new database to the list of databases. Transferring Tables In addition to creating and manipulating databases, SMO is capable of creating and managing tables and other databases objects. Using the SMOSample application, a user can copy tables in the selected database to another database on the server by clicking the Transfer Tables button. Clicking the Transfer Tables button executes the cmdTransferTables_Click event subroutine that you can see in the following listing. NOTE To use the Transfer utility class functions from a client system, the DTS run time needs to be installed on the client system.
  12. 470 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e The code in the cmdTransferTables_Click subroutine shows how the SMO Transfer utility class can be used to copy tables from one database to another database within a SQL Server system. Private Sub cmdTransferTables_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles cmdTransferTables.Click ResetScript() Dim oCurDatabase As Database Dim oTrans As New Transfer() Try ' Set the database object from the current list selection If lstDatabases.SelectedIndex >= 0 Then oCurDatabase = oSQLServer.Databases(lstDatabases.SelectedIndex) Else oCurDatabase = oSQLServer.Databases(0) End If oTrans.Database = oCurDatabase ' Prompt for new table name oTrans.DestinationDatabase = InputBox( _ "Enter the destination database name:", "Destination Database") oTrans.DestinationServer = cboServer.Text oTrans.DestinationLoginSecure = False ' Setup a secure login for NT security If Me.optNTSecurity.Checked = True Then oTrans.DestinationLoginSecure = True Else oTrans.DestinationLogin = txtLogin.Text oTrans.DestinationPassword = txtPwd.Text End If oTrans.CopyAllTables = True oTrans.CopyData = True oTrans.CopySchema = True oTrans.TemporaryPackageDirectory = "c:\temp" oTrans.TransferData() Catch SQLSMOError() End Try End Sub At the beginning of the cmdTransferTables_Click subroutine, you can see where the Database object oCurDatabase is declared, which represents the current database
  13. Chapter 12: Developing with SMO 471 selected on the SMOSample dialog. A new oTrans object is then instantiated. The VB Try/Catch loop is enabled, and then the oCurDatabase object is assigned to correspond to the database name that’s currently selected on the dialog. If the user has previously clicked a database, then that database is used. Otherwise, the first database in the list is assigned as the current database. The oTrans.Database property is then set with the selected database. The Database property of the Transfer object is the source database from which the schema and/or data is transferred to the target location. Next, the user is prompted to input a destination database name using the InputBox function, and the value keyed by the user is assigned to the oTrans.DestinationDatabase property. The cmdTransferTables_Click subroutine then continues to set the other destination properties—DestinationServer, DestinationLoginSecure, DestinationLogin, and DestinationPassword—with the appropriate values displayed on the SMOSample application dialog. In the SMOSample application, all tables, including data and the schema, are to be transferred to the destination database; therefore, the oTrans object properties of CopyAllTables, CopyData, and CopySchema are all set to True. The next line in the subroutine sets the TemporaryPackageDirectory property to the c:\temp directory on the local hard drive. When the oTrans.TransferData method is called, several files will be generated and placed into the temporary directory. The following files and file types are generated: Prologue SQL file NonTransactable SQL file Epilogue SQL file CompensatingAction SQL file TransferMetadata XML document InnerPackage SSIS package file Each of the SQL files contains T-SQL statements that are generated in accordance with the Transfer object’s properties, the XML document contains the metadata information based on the Transfer object’s properties, and the SSIS package file is used to execute the transfer of the schema and/or data to the destination location. An example of the TransferMetadata file is shown in Figure 12-4. The oTrans.TransferData method is then called and the tables are copied from the selected database to the destination database. After the transfer completes, the files in the temporary directory are automatically deleted.
  14. 472 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Figure 12-4 A sample TransferMetadata file Showing T-SQL Script for Tables Another one of the handy utility classes found in SMO is the Scripter class. The Scripter class allows you to programmatically create a hierarchical tree object that represents the parent or child dependent relationships of SQL Server objects. You can also generate Transact-SQL scripts that can be used to re-create SQL Server objects. In the example SMOSample application, selecting a table name from the tables list and clicking the Show Script button displays a text box showing the T-SQL script that can be used to re-create the table object. The cmdShowScript_ Click subroutine is shown in the following listing: Private Sub cmdShowScript_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles cmdShowScript.Click Dim oCurDatabase As Database Dim oCurTable As Table Dim sScript As String
  15. Chapter 12: Developing with SMO 473 ' Clear the dependant objects, reset the textbox txtAttributes.Text = vbNullString Label4.Text = "Script" Label5.Visible = False txtAttributes.Width = 220 txtAttributes.Left = 379 Try ' Set the database object from the current list selection If lstDatabases.SelectedIndex >= 0 Then oCurDatabase = oSQLServer.Databases(lstDatabases.SelectedIndex) oCurTable = oCurDatabase.Tables(lstTables.SelectedIndex) Else ' otherwise pick the first table oCurDatabase = oSQLServer.Databases(0) oCurTable = oCurDatabase.Tables(0) lstTables.SetSelected(0, True) End If For Each sScript In oCurTable.Script() txtAttributes.Text += sScript & vbCrLf Next sScript Catch SQLSMOError() End Try End Sub At the top of the cmdShowScript_Click subroutine, you can see where two SMO objects are declared. The oCurDatabase object contains an instance of the current database object selected in the database list. Likewise, the oCurTable object can contain an instance of the current table object selected in the list of tables displayed by the SMOSample application. Next, a string variable named sScript is created. The next few lines of code reset the labels and text box that are displayed on the VB Windows form so that the generated script can be easily read. After all the working objects and variables have been declared, objects that represent the current database and table selected by the user are assigned. Essentially, this code determines the item in the lstDatabases and lstTables list boxes the user clicked, or it selects the first item if no selection has been made. Next, you can see where the Script function of the oCurTable object is called. The Script function returns a collection of string objects representing the T-SQL that is used to create the current table. A For Each loop is used to read each of the string items contained in the collection and then add each string object to the text box. Appending a vbCrLf character to each string object causes the contents of the text box to be presented to the end user as a list.
  16. 474 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Figure 12-5 Showing a T-SQL script for a table After the list of T-SQL strings has been added to the text box, it is displayed to the end user. Figure 12-5 shows the SMOSample application displayed by the cmdShowScript_Click subroutine. SMO Error Handling Errors that the SMO methods generate can be trapped using Visual Basic’s standard error handling. If an error is raised by one of the SMO methods and Visual Basic’s error handling is not implemented, the user sees a Visual Basic run-time error and the application is terminated. TIP Make use of Visual Basic’s built-in error handler to trap any SMO errors and prevent unexpected application errors from terminating the application. All the subroutines presented in the sample SMO application make use of the common SQLSMOError subroutine shown here:
  17. Chapter 12: Developing with SMO 475 Public Function SQLSMOError() Dim sErrorMsg As String sErrorMsg = Err.Source & " Error: " & _ Err.Number - vbObjectError & ": " & Err.Description SQLSMOError = MsgBox(sErrorMsg, vbOKOnly, "SMO Error") End Function The SQLSMOError function is a relatively simple function, which displays a message box to the user that provides more information about any error conditions encountered. SMO errors can be displayed using the Visual Basic Err object. The Err.Source property contains the name of the SMO component that raised the error. Subtracting the vbObjectError constant from the Err.Number property results in the SMO error number. The Err.Description property contains a text description of the error. Summary As you can see from the code examples, SMO opens the power of SQL Server’s management functions to Visual Basic, and its .NET implementation makes it easy to use from Visual Basic and other .NET programming languages. The examples in this chapter illustrated a small section of the capabilities provided by SMO, which is capable of performing virtually every function you can manually perform using the SQL Server Management Studio.
  18. This page intentionally left blank
  19. CHAPTER Using sqlcmd 13 IN THIS CHAPTER sqlcmd Components Developing sqlcmd Scripts 477 Copyright © 2006 by The McGraw-Hill Companies. Click here for terms of use.
  20. 478 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e A nother new development tool that’s provided with SQL Server 2005 is the new sqlcmd utility, which is essentially a replacement for the older command-line osql and isql utilities found in the earlier releases of SQL Server. The old isql program used the now-deprecated SQL Server DB-library to connect to SQL Server, while the osql program used an ODBC interface. For backward compatibility, the osql utility is still shipped with SQL Server 2005; however, isql has been dropped. Like those tools, the sqlcmd utility is run from the command prompt and enables you to enter and execute T-SQL statements, stored procedures, and T-SQL batches. However, it connects to SQL Server using OLE DB and extends the power that was provided in those earlier command-line tools by adding support for variables and extended commands. sqlcmd Components The sqlcmd utility consists of four primary components: the sqlcmd command shell, its command-line parameters, and the built-in commands and variables that are supported by the sqlcmd shell, which enables to you build and execute batches of T-SQL commands. The command-line parameters enable you to pass in run-time information to the SqlCmd shell as well as run T-SQL commands and other sqlcmd scripts. The built-in variables enable your sqlcmd scripts to access system and environment information, while the built-in commands enable you to add extended control flow to your scripts. Command Shell Much like its earlier counterparts, isql and osql, sqlcmd uses a command shell to submit T-SQL commands to SQL Server. Entering sqlcmd at the command prompt starts the sqlcmd command shell. When the command shell is active, each line in the batch will be numbered until the batch is executed. The first line in the batch will be labeled with 1> prompt. You build the batch by entering T-SQL commands and then pressing the enter key following each command. The sqlcmd shell will add the line to the batch and increment the line number for the next command. Entering the T-SQL Go command will execute all of the commands in the current batch. You can see a simple example of using the sqlcmd shell in the following listing: C:\temp>sqlcmd 1> use adventureworks 2> select DepartmentID, Name from HumanResources.Department 3> go Changed database context to 'AdventureWorks'.DepartmentID Name ------------ --------------------------------------------------
Đồng bộ tài khoản