SMO Scripter problem with dependencies

Question

Hi, I have used an example like this one http://msdn.microsoft.com/en-us/library/ms162153.aspx but the Schema.sql file generated have many CREATE TABLE entries for the same table when "WithDependencies" property is activated!? If I turn off this flag the script generated doesn't have the dependencies in order, giving errors on the FK! On the other hand, the "ScriptData" property generates a huge file (i can understand this because its an INSERT clause for each row). Questions: 1. How can I generate a proper script for schema (whithout using the IFNOTEXISTS)? 2. Which is the best way to make a complete database data dump programmatically for huge databases? Thanks in advance.

---

Zé

Answer 1

Hello,

I am not sure to have understood fully your problem ( but as english is not my mother language, i have often problems to understand a post written in english ).So, to be sure that i will not miss anything which could compell me to help you, please, could you provide some informations which are important ( according to me ) ?

- the version ( 2005, 2008, 2008 R2 ) and the edition of your SQL Server

- the language (VC#/VB) you are using

About your 1st question ( generation of a script without IFNOTEXISTS ) you should have a look at the ScriptingOptions class  and its property IncludeIfNotExists  http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.includeifnotexists.aspx

For the copy of the data, i would suggest to use bcp and to create a file for each table.

I will have a deeper look at your problem this morning ( it is 00:25 Paris hour ).

I have tested the link that you have provided ( but as i have only SQL Server 2008 and no 2008 R2, i have to do some modifications as the classes and namespaces are different between 2008 and 2008 R2.Moreover , i want to create a script file and not only to display the result in the console.

I hope you will excuse me not to help more efficiently for the moment.

Have a nice day

 

 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Bonjour Mark,

I presume that your mother language is French, right? If you have problems with the english you can write in french. Mine is portuguese.

About your questions:

I'm using SQL-Server 2008 Express and Visual Basic 2010.

I know that property IncludeIfNotExists. Waht I wanted to know is how to generate a "clean" script whithout using this property. I have a working script with this property already working but the generated file is to big because it has a lot of duplicate "Create Table" for the same table.

 

Merci beaucoup.

À bientot.

---

Zé

Answer 3

Hello,

I am sorry not to have replied you before , but i got a problem of memory ( it had to replace 1 GB of memory in emergency and it is not evident to find that in August especially around the Assumption , surely the same problem in France or Portugal ).

About the use of IncludeIfNoExist property, you have the same problem than me, and you will meet this problem if you try to create a script with SSMSE ( SQL Server Management Studio Express ), but i have no workaround.

The problem seems to come from the fact that Scripter can be used to create a script used to generate additionnal objects ( tables,foreign keys, schemas and so on ) which have to be added to an already existing database. In this case, the IncludeIfNoExists property is useful as it permits not to create an object ( for example a schema or a user-defined datatype ) which could already exist in the database where the generated script must be applied.But how can we be sure that an object is already existing in the to database ?

With the WithDependencies property , Scripter is trying to generate a script for each object appearing in each table. I think that the SQL Server Management Team has chosen the simplest way. Even if a schema is used by several tables, for each table , Scripter checks whether the schema has already generated, if yes, the schema generation is skipped thanks to the IncludeIfNoExists, else it is created. I think that it is the easier way to implement the code inside Scripter.

When i have to create a new database, i have 2 possibilities :

- the 1st one , i am using a script generated by SSMS(E) thru the ExecuteNoQuery() method from SMO or SqlConnection

- the 2nd one, i create a SMO program which will create every object in the correct way ( in fact SMO will generate a script which will be executed , and it is possible to get this script to check whether i have done no error or forgotten something )

I am using sometimes another way :

1- i create ( with possible IncludeIfNotExists ) the whole schemas,user-defined-datatypes, XML SchemasCollections and similar objects not depending from a specific table.

2- i create my tables ( only the columns, extendedproperties , indexes and primary keys )

3- when all the tables have been created, i create the foreign keys for each table

Complicated , long to do , but it is working. I am only relying on the collections of schema,uddt, XMLSchemasCollections of the database.For the tables, i am relying on the tables collections (DataBase.Tables ) and so on.

I am sorry not to give you a better solution.

Don't hesitate to post again for more help or explanations

Have a nice day

PS : Why not to use a collection of Tables that you fill each time you create a new Table ? I give you this idea, but i have not diggen it, so i have no idea whether it is possible to implement

 

 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Let us know if this power shell script sample helps you  http://blogs.msdn.com/b/sqlagent/archive/2012/08/28/using-powershell-script-to-script-out-tables-and-procedures-with-dependencies-and-permissions.aspx

Thanks

Sethu Srinivasan {MSFT]
SQL Server