Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Script task

Hi,

I am new in SSIS development. In the script task of the Control Flow, how to use my custom classes (that use SMO and DTS for building a package and are written in C##?). If not the Script Task then which tool I have to use?

Natali Rozin wrote:

Hi,

I am new in SSIS development. In the script task of the Control Flow, how to use my custom classes (that use SMO and DTS for building a package and are written in C##?). If not the Script Task then which tool I have to use?

If you have compiled them into assemblies then you can reference them.

You should read this though:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx)

-Jamie

|||

thank you very much Jamie.

Did you put your DDLs into the root of the Microsoft.Net folder? Through "Add Reference", I cannot find my DDLs I just put into C:\WINDOWS\Microsoft.NET\Framework

|||

Natali Rozin wrote:

thank you very much Jamie.

Did you put your DDLs into the root of the Microsoft.Net folder? Through "Add Reference", I cannot find my DDLs I just put into C:\WINDOWS\Microsoft.NET\Framework

Natali,

You should put it into the appropriate .Net Framework version folder (I always just pick the highest). e.g.:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

My apologies, the post I linked to above does not explain this does it?

-Jamie

|||

Thank you - all are working!

The problem was I put mine into C:\WINDOWS\Microsoft.NET\Framework\v3.0 and do not have VS SDK yet.

In C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 works well!

thanks again