Author: Andrew Clinick
Published Date: January 10, 2000
I have talked in "If IT Moves, Script IT" article, how to use "Windows Script Host (Windows Script Host) Manage Windows and Windows applications. Most examples in the text are based on the management of the Windows operating system itself, not based on applications running under this operating system. In order to welcome the new millennium, I think I should talk about how to use the script in many applications that reveal the scripted interface. This time only involves "SQL Server". In the next few months, I will focus on Exchange, Office, and System Management Server.
You can use the "Distributed Management Object", "Data Conversion Service" and the new "SQL Server XML" implementation, use the script for "SQL Server".
Many people have access to the database through "Active Data Object (ADO) technology. ADO has made a lot of work in helping you to query and update the database - but what is the performance of the data between the backup (Y2K question) or the data between the database? And at this time, it is not available to XML.
Here, I will tell you how to use ADO's accompanying technology-especially "distributed management object", "Data Conversion Service" implementation, "SQL Server" is used for "SQL Server".
Distributed management object
"Distributed Management Object (DMO)" is a set of COM objects that encapsulate "SQL Server" database and replication management. This means that you can write a WSH script that copies all the data in a particular table to a file separated by a table, which helps move a lot of data. The reason why I chose this example is because it is written, but DMO allows you to get every object in SQL Server, so that you can write some very good and meaningful management scripts.
The key to DMO is SqldMo.sqlServer object, which is a basic object that allows you to connect to the server and get all available objects. In this case, I will use the Database collection to select the database, and then access the table to be dumped to the file from the Table collection. If the database is not provided, an error message will appear and the script is over. If you do not provide a table name, the script will loop in all tables of the database and export non-system tables. If a database is provided, it exports to the table. This example is limited, but it provides a simple export utility based on command line for "SQL Server", which you can use it.
Now look at a code first:
'Declaring variables used when talking to SQL
DIM OSERVER 'SQL Server object
Dim Odatabase 'To User Target Database
DIM OBCP 'BCP object
DIM NROWS 'number of rows returned from BCP
DIM TABLE 'Table Object
ON Error ResMe next
'The first parameter must be a database
IF wscript.Arguments (0) = "" THEN
WScript.echo "You did not provide a database to be connected"
WScript.quit
END IF
'Instances of creating SQL DMO
Set oserver = createObject ("sqldmo.sqlserver")
'Creating an instance of SQL DMO BULKCOPY object
Set Obcp = CreateObject ("SqldMo.bulkcopy" oserver.enablebcp = true
'Log in to the local server
'I hope that you have changed the SA password!!
Oserver.connect ".", "sa"
'Connect to the supplied database
Set odatabase = oserver.databases (wscript.arguments (0))
'Set separator to comma
Obcp.columndelimiter = vbcomma
'Set the file type to separate
Obcp.DataFileType = SqldModataFile_CommadelimitedChar
Obcp.importrowsperbatch = 1000
Obcp.maximuMerrorSbeForeAbort = 1
BCP.ROWDELIMITER = VBCRLF
Obcp.serverbcpdatafiletype = sqldmobcpdatafile_char
Obcp.useexistingConnection = TRUE
'If you don't provide a table, dump all tables
F wscript.arguments (1) = "" "
For Each Table In Odatabase.tables
'Make sure the table is not a system table
if Table.systemObject = false
Obcp.DataFilePath = Table.Name & ".csv"
NROWS = Table.ExportData (OBCP)
Wscript.echo nrows & "rows exported from" & Table.Name
END IF
NEXT
Else
'Setting the output file
Obcp.DataFilePath = WScript.Arguments (1) & ".csv"
NROWS = ODatabase.tables (WScript.Arguments (1)). ExportData (OBCP)
Wscript.echo nrows & "rows exported from" & wscript.arguments (1)
END IF
DMO's full content is much more than this article, but I hope this article can give you some sense of understanding: what can be done with some simple WSH scripts and DMO. You can download my example (English). For more information on DMO, please visit http://msdn.microsoft.com/library/psdk/sql/9_dmoc01.htm (English).
Data conversion service
Export to the COMMA SEPARATED file (CSV), can be used as a starting point to export data to SQL and import from SQL, but this is not an cutting-edge technology (too much in 20 centuries, don't you think?). "SQL Server 7.0" provides a fairly complete import and export mechanism in the form of "Data Conversion Service (DTS)". Fortunately, the script still has a place in this new field of "SQL Server", so you can use "Visual Basic (R) script editing (VBScript)", "JScript (r)" or "Perl" technology Extend the ability of DTS.
The setting of DTS is very simple, especially when using "SQL ServeRenterprise Manager". By default, "SQL Server" has a defined folder that stores any conversion, and "Enterprise Manager" provides a large graphical user interface (GUI) that creates and edits DTS packages. In the example below, I chose a new table in the Employees.csv file created by the SQLEXPORT.WSF file and "SQL Server". The DTA package will create the table, load it into the Text file, then run a script, convert the data to the table of SQL Server. The conversion in the DTS package allows the script to remain in the entire conversion process. SQL Enterprise Manager provides a simple script editor with a syntax analysis script button. The button will warn your script in your script before running the script. During the conversion process, the script uses the CINT function of VBScript, converts the input of EmployeeID and DEPTID to int and converts all email addresses to lowercase letters.
'********************************************************** ********************
'Visual Basic Conversion Script
'Copy each source to
'Target Column
'********************************************************** *********************
Function main ()
DTSDestination ("EmployeeID") = CINT (DTSSource ("COL001"))
DTSDESTINATION ("firstname") = dtssource ("col002")
DTSDESTINATION ("Lastname") = dtssource ("col003")
DTSDestination ("email") = LCase (DTSSource ("COL004"))
DTSDestination ("extension") = dtssource ("COL005")
DTSDestination ("office") = dtssource ("col006")
DTSDestination ("deptid") = CINT (DTSSource ("COL007"))
Main = DTSTRANSFORMSTAT_OK
END FUNCTION
With DTS, some perfect import / export mechanism can be used, and you can use scripts in any step of the conversion. It is important to pay attention to the script may not be the best way to operate data - especially if your data set is large. If you have a lot of data to be converted, and performance is also important, you may need to consider using Visual Basic or C to create a COM component, then call the component from the DTS. That is, if performance is not important, and to convert it when data import / export, the script provides you with the flexible mechanism for this, so you can store all code to the SQL Server database. In, the deployment becomes quite simple.
About XML
Currently, XML looks like the best tool to share data between applications, so all management of "SQL Server" has got an XML benefit. Writing scripts for extracting all data in the database and writing scripts that create an XML document with a programming method are possible. However, if you only need to query "SQL Server", it is best to return the data in XML and then operate the XML with a script. The "SQL Server" group recently issued an informal version of "SQL Server XML" technology, which is completely available. The new XML technology is very simple. In fact, the server issues an HTTP request and passes the query to the server, and the server returns to XML. For example, I created a simple WSH script that queried all the data in the employee table of the North Wind database to the local machine. To access XML, the script uses the XML analyzer included with Internet Explorer 5.01. The wonderful place of this object is that it handles all your HTTP requests and allows you to call synchronously. Because you don't have to deal with any event-driven programs, it is very helpful to write scripts.
This script is very simple. It creates an instance of an XML analysis program, using the analyst to open the URL, and save the results as a .xml file. Just five sketches of scripts, good!
'Setting the requested URL
XMLPath = "http: // localhost / northwind? SQL = SELECT * FROM EMPLOYEES FOR XML AUTO"
'Creating an instance of "XML Analysis Program"
Set myXmldoc = creteObject ("Microsoft.xmLDom")
'Don't need asynchronous
MyXmldoc.async = false
'Loading this URL
MyXmldoc.Load XMLPath
'Save the document
MYXMLDoc.save "Employees.xml"
Its power is that it is now easy to establish a remote connection with the server and dump data; as long as you change the URL, you have already done it. This example tells you how to export, but you can easily write the import routine, repeatedly using the XML analysis program in XML and insert the value into the database.
Summary
"SQL Server" provides a comprehensive mechanism for writable scripts for local or remote management databases. DMO and DTS have been listed (in fact, DMO has released many versions), so you can use them directly to automate your database management tasks. Windows 2000 has been launched with "Windows Script Host 2.0", so all the above scripts are running, which is not subject to the conditional restrictions of Windows 2000 servers with "SQL Server". New XML technology in "SQL Server" makes access data easily, so that the script written "SQL Server" is easier. For more information on "SQL Server", please visit SQL Server Developer Enter.
Andrew Clinick is the program manager in the Microsoft Script Technology group, the opportunity, as long as the script is involved, he may have a big show. Most of his spare time flowers watching the wonderful football game for US TV broadcasts, and explains with his US colleagues.
