Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / Visual Basic
Article

Generate SQL Insert statements for your SQL Server 2000 Database

Rate me:
Please Sign up or sign in to vote.
4.77/5 (98 votes)
13 Apr 20045 min read 510.9K   8.3K   138   110
Deploy your SQL Server 2000 database with scripts.

Enter Settings on main form

Introduction

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 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 Manager 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 locally or remotely. This article focuses on option number 3.

Background

There 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 few 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 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 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. 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 instance
    textcopy.exeUnsupported SQL utility used to copy 'text' and 'image' values into a SQL Server database
    createdatabase.sqlScript to create the database
    buildobjects.sqlScript to build all of the objects in the database
    populatedata.sqlScript containing SQL Insert statements to populate the database with data
    importblobvalues.bat

    Batch file to import the blob values into the database.

    Parameters required are server name, database, username, password, authentication method.

    setup.bat

    Batch 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 Authentication or SQL Server Authentication.

    Parameters required are server name, database, username, password, authentication method.

    install.bat

    Batch file to test the installation.

    Normally, your Setup Project would invoke the setup.bat and importblobvalues.bat files directly passing the appropriate values gathered from a custom user interface.

    Before you test the installation:

    Please follow steps 6 and 7 carefully to avoid dropping the original database!!

  6. Open createdatabase.sql in Notepad and change all occurrences (3) of the original database name to something different.
  7. Open install.bat and change the database name to that chosen in step 6 above.
  8. To install the database, double-click on install.bat.

Points of Interest

Scripting out Dependent objects first

Even if you use SQL Server Enterprise Manager to script out all of the objects in your database, you 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 may 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 import BLOB values into a database

When 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 groups.google.com, I learnt that SQL Server already had sample code to import BLOB values, installed in the C:\Program Files\Microsoft SQL Server\MSSQL\Install 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 reprinted below:

@echo off
if "%1" == "" goto usage
echo.
echo Inserting images into pubs database on server %1
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1389'" -Falgodata.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '0877'" -Fbinnet.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9901'" -Fgggg.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1622'" -F5lakes.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '0736'" -Fnewmoon.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9999'" -Flucerne.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '1756'" -Framona.gif
textcopy -I -Usa -P%2 -S%1 -Dpubs -Tpub_info -Clogo 
                 -W"where pub_id = '9952'" -Fscootney.gif
echo Image update complete!
echo.
goto done
:usage
echo.
echo Usage: pubimage ServerName [SAPassword]
echo.
:done

It was only after I was successfully able to script out the pubs 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 anyone 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.

Conclusion

I've been a fan of CodeProject for some time now and this is my very 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.

History

Version 1. Submitted on 14th April 2004.

License

This article has no explicit license attached to it but may contain usage 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


Written By
Software Developer (Senior) Simbient
Australia Australia
Darren Weir is a senior .NET developer working for Simbient in North Sydney, Australia.

My blog is located at dotnetdarren.wordpress.com

Comments and Discussions

 
GeneralGreat Tool Pin
OmegaSupreme8-Feb-05 4:45
OmegaSupreme8-Feb-05 4:45 
GeneralIdeas Pin
Anonymous31-Jan-05 10:59
Anonymous31-Jan-05 10:59 
GeneralTwo small bugs Pin
Sire40431-Jan-05 4:41
Sire40431-Jan-05 4:41 
GeneralRe: Two small bugs Pin
Darren Weir31-Jan-05 10:55
Darren Weir31-Jan-05 10:55 
GeneralRe: Two small bugs Pin
jshallard15-Jul-05 15:42
jshallard15-Jul-05 15:42 
GeneralGreat tool Pin
Kant6-Jan-05 4:00
Kant6-Jan-05 4:00 
GeneralRe: Great tool Pin
Darren Weir6-Jan-05 7:56
Darren Weir6-Jan-05 7:56 
GeneralIndexes and Permissions Pin
ddaiker13-Dec-04 10:15
ddaiker13-Dec-04 10:15 
GeneralRe: Indexes and Permissions Pin
Darren Weir13-Dec-04 10:52
Darren Weir13-Dec-04 10:52 
GeneralRe: Indexes and Permissions Pin
Lord of Scripts13-Mar-05 3:35
Lord of Scripts13-Mar-05 3:35 
GeneralRe: Indexes and Permissions Pin
ddaiker28-Mar-05 2:13
ddaiker28-Mar-05 2:13 
Generalcan't open file. It doesn't appear to be a valid archive Pin
fcote9-Dec-04 10:11
fcote9-Dec-04 10:11 
Generalcomputed columns Pin
Member 153746822-Nov-04 10:38
Member 153746822-Nov-04 10:38 
GeneralRe: computed columns Pin
Darren Weir22-Nov-04 10:46
Darren Weir22-Nov-04 10:46 
GeneralInsert Statements Tip Pin
SergioCosta17-Sep-04 1:26
SergioCosta17-Sep-04 1:26 
Generalerror for running the projetc Pin
hichem3224-Aug-04 2:45
hichem3224-Aug-04 2:45 
GeneralRe: error for running the projetc Pin
mav.northwind3-Sep-04 4:30
mav.northwind3-Sep-04 4:30 
GeneralGenDBScripts.exe does not show any progress Pin
gmathijssen5-Aug-04 23:37
gmathijssen5-Aug-04 23:37 
GeneralRe: GenDBScripts.exe does not show any progress Pin
cheezfud7-Jan-05 5:20
cheezfud7-Jan-05 5:20 
GeneralError in create Database from script Pin
Hso9-Jun-04 23:13
Hso9-Jun-04 23:13 
GeneralnText a problem Pin
Chris Keeble11-May-04 23:38
Chris Keeble11-May-04 23:38 
GeneralRe: nText a problem Pin
Darren Weir12-May-04 14:40
Darren Weir12-May-04 14:40 
GeneralRe: nText a problem Pin
jlb4-Nov-04 0:11
jlb4-Nov-04 0:11 
GeneralRe: nText a problem Pin
Darren Weir4-Nov-04 1:08
Darren Weir4-Nov-04 1:08 
GeneralDatabase name tip Pin
renevanberkel25-Apr-04 22:32
renevanberkel25-Apr-04 22:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.