外文翻译--SQL伺候器数据库

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 .ld

2、f 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 Ma

3、nager 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 l

4、ocally 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 couldn't afford to buy one and so I had to write my own program which only took a fe

5、w days.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

6、you run 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

7、to store 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.

8、 Press 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 cop

9、y 'text' 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 f

10、ile to import the blob values 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 os

11、ql using either Windows Authentication 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

12、 passing the appropriate values 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 diff

13、erent. 8. Open install.bat 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

14、 will still be left to sort 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 ma

15、y potentially have dependencies 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 imp

16、ort BLOB values into a databaseWhen 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 s

17、ample code to import BLOB values, installed in the C:Program FilesMicrosoft SQL ServerMSSQLInstall directory.The files of interest are: pubimage.bat, pubtext.bat and instpubs.sql.The example uses TEXTCOPY.EXE to insert 'text' and 'image' values into the pubs database.pubtext.bat repr

18、inted below:echo offIf”%1”=”goto usageEcho.echo Inserting images into pubs database on servber %1textcopy I-Vsa p%2 S%1 Dpubs Tpub_info ckogo -W”where pub_id =1389”-Falgodata.giftextcopy I-Vsa p%2 S%1 Dpubs Tpub_info ckogo -W”where pub_id =0877 ”-Fbinnet.giftextcopy I-Vsa p%2 S%1 Dpubs Tpub_info cko

19、go -W”where pub_id =9901 ”-Fgggg.giftextcopy I-Vsa p%2 S%1 Dpubs Tpub_info ckogo -W”where pub_id =1622 ”-F5lake.giftextcopy I-Vsa p%2 S%1 Dpubs Tpub_info ckogo -W”where pub_id =0736 ”-Fnowmoon.giftextcopy I-Vsa p%2 S%1 Dpubs Tpub_info ckogo -W”where pub_id =9999 ”-Fluce.giftextcopy I-Vsa p%2 S%1 Dpu

20、bs Tpub_info ckogo -W”where pub_id =1756 ”-Framona.giftextcopy I-Vsa p%2 S%1 Dpubs Tpub_info ckogo -W”where pub_id =9952 ”-Fscootney.gifEcho Image update complere!Echo.Goto done:usageEcho.Echo Vsage:pubimage serverNameSAPasswordEcho.:doneIt was only after I was successfully able to script out the pu

21、bs database that I learnt that TEXTCOPY.EXE cannot import 'ntext' values. I was very disheartened when I realized this and I still know of no good way to import 'ntext' data into a database. BCP needs format files and file length sizes and can only insert data, not update data.If any

22、one knows a good way to import 'ntext' data into a SQL Server database, then please let me know so that I can update this article and provide a complete solution for scripting out an entire SQL Server database.ConclusionI've been a fan of CodeProject for some time now and this is my very

23、 first article. I hope it will help many of you out when it comes to deploying your SQL Server databases. If you have any suggestions for improvements, please let me know.HistoryVersion 1. Submitted on 14th April 2004.窗体顶端LicenseThis article has no explicit license attached to it but may contain usa

24、ge terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.A list of licenses authors might use can be found here中文译文曾经部署对一个客户的一个 SQL 伺候器数据库吗? 如果你有, 那么你将会知道它是当然不是像部署一个使用一个小姐通路数据库的申请那样简单藉由 SQL 伺候器数据库，你有 3 配置选项我知道1. 分配你的。mdf 和。ld

25、f 对你的客户申请而且使用被储存程序 sp_attach_db 的系统把数据库附在他们的 SQL 伺候器例证。 (或 sp_attach_single_file_db) 缺点是那 SQL 伺候器一定在与装备申请相同的机器,不总是情形上住。2. 使用企业经理产生你的数据库手写体和 BCP 输出你的全部数据到文件而且把他们输入回来在你的装备程序期间3. 手写体出自你的包括 SQL 的整个数据库插入输入数据进入数据库的陈述。藉由选项 2 和 3 ，你能地方性地或很远地部署一个 SQL 伺候器数据库。 这一个文章把重心集中在 3号选项。背景有一些商业的计画哪一将会手写体出自一个整个的 SQL 伺候器数

26、据库为你，但是由于现在的财务理由，我无法负担买一而且因此我必须写只花了数天的我自己的计画.给这一个文章的原始码广泛地使用 SQLDMO 图书馆质问一个 SQL 伺候器数据库。 SQLDMO 是一间 COM 图书馆而且与 SQL 伺候器 7/2000 一起安装。 (笔记: 给这一个文章的密码只将会为 SQL 伺候器 2000 数据库工作)。使用密码当你跑计画的时候，为你想要产生手写体的数据库进入连接数据。为输出进入一个现有的目录。 给数据库的一个子目录将会被放在之内输出目录。 在数据库文件夹的下面，有用来储存所有的一滴价值的另外的一个文件夹当做 . 文件为本文专栏, 或。dat 申请图像专栏。如

27、果你不想要为挑选的数据库的数据产生 SQL 插入物手写体, 然后清除这选项。按那 产生 扣住开始程序。 一个进步荧屏将会被显示。按那 取消 随时扣住取消程序。在程序已经完成的世代之后，对在输出目录中的数据库文件夹浏览见到一连串的文件：6. 在你之前测试安装：7. 请小心地遵从第 6 和 7 步骤避免降低最初的数据库!8. 在笔记本打开 createdatabase.sql 而且将最初数据库名字的所有发生 (3) 换成不同的事物。 9. 打开 install.bat 而且将数据库名字换成那在上面的第 6 步骤被选择。 10. 为了要安装数据库, 加倍-按下 install.bat重要点首先手写体

28、出依赖的物体即使你出自你的数据库的所有物体对手写体使用 SQL 伺候器企业经理,你将会仍然被留下在哪些物体应该被产生挑出次序, 如一些桌子/视野/功能而且储存程序可能仰赖其他。 这一个程序可能是耗费时间的和沈闷的。然而，在文章密码中， EnumDependencies 方法被要求每一个可能可能地有属国的物体，而且依赖的物体被首先手写体出。 当手写体跑的时候，这应该除去接受错误信息。 在桌子中的圆形叁考当两者的桌子有的时候，另一个桌子的外国关键会仍然虽然产生错误。产生一个一届申请输入一滴价值进入一个数据库当我最初开始的时候，我在真实的插入物陈述中包含了所有的一滴价值。 然而, 当我跑他们生产了错

29、误的手写体，因此，我假定，有一个长度界限的时候为一份插入物陈述。在一点浏览之后，在 上，我学习 SQL 伺候器已经对 import BLOB valuesthat is installed in the C:Program FilesMicrosoft SQL Server MSSQLInstall directory 有了样品编码。重要文件是: pubimage.bat 、 pubtext.bat 和 instpubs.sql。例子使用 TEXTCOPY 。EXE 插入 '本文' 和 '图像' 进入酒馆数据库之内的价值。pubtext.bat 在下面再版:echo offIf”%1”=”goto usageEcho.echo Inserting images into pubs database on servber %1textcopy I-Vsa p%2 S%1 Dpubs T