database generation script

Question

database generation script

lenyn

SSC Enthusiast

Points: 134
More actions
August 25, 2003 at 1:11 am

#160559

HI,
We have a database that we would like to distribute using a SQl script.
We've tried to use the SQLserver script generation tool, with no success.
The database is not so big but each time we attache the data to the script, the script fails.
Today we do it with a database backup, but we have problems with users...
What is the best way of doing it ?
Best regards,
Nicolas

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Nicolas,

quote:
We've tried to use the SQLserver script generation tool, with no success.
The database is not so big but each time we attache the data to the script, the script fails.

???

Are you trying to script the structure of the db, and /or the data ?

Or both?

quote:
What is the best way of doing it ?

If the db is not that big, what about using sp_detach and sp_attach at the client.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lenyn SSC Enthusiast Points: 134 More actions · Answer 2

We write a software using .net. The script will be part of the install program and will create the database, and insert some data.

Best regards,

nicolas

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

And what's the error you get from scripting the db?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lenyn SSC Enthusiast Points: 134 More actions · Answer 4

It looks like the script is to long (because of all the inserts we have for the data), and it is truncated in the query analyser when we launch the script.

(to execute the script we copy/paste it in the query analyser and then execute it. There is maybe an other way to execute it ?)

Thank you for your help

Nicolas

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
It looks like the script is to long (because of all the inserts we have for the data), and it is truncated in the query analyser when we launch the script.
(to execute the script we copy/paste it in the query analyser and then execute it. There is maybe an other way to execute it ?)

if you already have some kind of 'model' database available with sample data....I think it is most easy to copy it on the server and run a sp_attach via QA or code.

Another way would be to script the db and execute the files via ISQL

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lenyn SSC Enthusiast Points: 134 More actions · Answer 6

lenyn

SSC Enthusiast

Points: 134

August 25, 2003 at 3:05 am

#471176

Thank you, I'll try that

Nicolas

jbelci SSC Journeyman Points: 75 More actions · Answer 7

have you tried using a combination of SQLDMO and ADO.

i.e. use SQL DMO to create the structure of the database, and then use ADO to insert the data.

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

Hi Nicolas,

What I would do is generate the DDL script with EM including create database.

That one you can run on the client server.

After you can BCP out all the data you want to transfer to the client location. There you have to BCP in.

This is the way I'm usually doing. And it works well and fast.

Bye

Gabor

Bye
Gabor

stubob SSCommitted Points: 1839 More actions · Answer 9

I've had problems where page breaks were inserted, screwing up the code.

I have no idea if this is your problem, but it's something to look for.

Data: Easy to spill, hard to clean up!