《外文翻译--SQL-伺候器数据库》由会员分享，可在线阅读，更多相关《外文翻译--SQL-伺候器数据库（8页珍藏版）》请在金锄头文库上搜索。
1、外文原文Have you ever had to deploy a SQL Server database to a customer? If you have, then you will know that it is certainly not as simple as deploying an application that uses an MS Access database.With SQL Server databases, you have 3 deployment options that I know of:1. Distribute your .mdf and .ldf
2、 files to your customer and attach the database to their SQL Server instance using the system stored procedure sp_attach_db (or sp_attach_single_file_db). The downside is that the SQL Server must reside on the same machine as the setup application, which is not always the case. 2. Use Enterprise Man
3、ager to generate your database scripts and BCP to export all of your data to files and import them back in during your setup process. 3. Script out your entire database including SQL Insert statements for importing data into the database. With options 2 and 3, you can deploy a SQL Server database lo
4、cally or remotely. This article focuses on option number 3.BackgroundThere are a number of commercial programs that will script out an entire SQL Server database for you, but due to current financial reasons, I couldnt afford to buy one and so I had to write my own program which only took a few days
5、.The source code for this article uses the SQLDMO library extensively to interrogate a SQL Server database. SQLDMO is a COM library and is installed with SQL Server 7/2000. (Note: the code for this article will only work for SQL Server 2000 databases).Using the code(See figure 1 above)1. When you ru
6、n the project, enter the connection information for the database you would like to generate scripts for. 2. Enter an existing directory for the output. A subdirectory for the database will be placed in the output directory. Underneath the database folder, there is another folder which is used to sto
7、re all of the BLOB values as .txt files for Text columns, or .dat files for Image columns. 3. If you do not want to generate SQL Insert scripts for the data in the selected database, then clear this option. 4. Press the Generate button to start the process. A progress screen will be displayed. Press
8、 the Cancel button at any time to cancel the process. 5. After the generation process has finished, browse to the database folder in the output directory to see a list of files: osql.exeSQL utility to send batch statements to a SQL Server instancetextcopy.exeUnsupported SQL utility used to copy text
9、 and image values into a SQL Server databasecreatedatabase.sqlScript to create the databasebuildobjects.sqlScript to build all of the objects in the databasepopulatedata.sqlScript containing SQL Insert statements to populate the database with dataimportblobvalues.batBatch file to import the blob val
10、ues into the database.Parameters required are server name, database, username, password, authentication method.setup.batBatch file to invoke the createobject.sql, buildobjects.sql and populatedata.sql scripts. This batch file is generic to any database. Ability to invoke osql using either Windows Au
11、thentication or SQL Server Authentication.Parameters required are server name, database, username, password, authentication method. install.batBatch file to test the installation.Normally, your Setup Project would invoke the setup.bat and importblobvalues.bat files directly passing the appropriate v
12、alues gathered from a custom user interface.6. Before you test the installation:Please follow steps 6 and 7 carefully to avoid dropping the original database!7. Open createdatabase.sql in Notepad and change all occurrences (3) of the original database name to something different. 8. Open install.bat
13、 and change the database name to that chosen in step 6 above. 9. To install the database, double-click on install.bat. Points of InterestScripting out Dependent objects firstEven if you use SQL Server Enterprise Manager to script out all of the objects in your database, you will still be left to sor
14、t out the order in which objects should be created, as some tables/views/functions and stored procedures may depend on others. This process can be time consuming and tedious.However, in the article code, the EnumDependencies method was called for each of the objects that may potentially have depende
15、ncies and the dependent objects are scripted out first. This should eliminate receiving error messages when the scripts run. Circular references in tables when both tables have foreign keys to the other table would still generate errors though.Generating a batch file to import BLOB values into a dat
16、abaseWhen I first started out, I included all BLOB values in the actual INSERT statements. However, when I ran the scripts they produced errors, so I assumed there was a length limit for an INSERT statement.After a bit of browsing on , I learnt that SQL Server already had sample code to import BLOB values, installed in the C:Program FilesMicrosoft SQL ServerMSSQLInstall di
