June 16, 2015 at 8:32 am
Hello all,we have a couple of databases that have constant data change capture on them,without going in much detail it is allmost impossible storage wise to keep a consisten databackup on these & we don't really need one as the source system already has those if needed.
However we need to maintain the structure so each week we want to run something that will generate an sql script for all the objects in the database.
I have a script that does that,tested out very nicely,only got 2 issues left to resolve,getting the users & getting the schema.
For instance we can have tables abc.def or def.abc
I need to have the create script for abc. & def.
I followed most of the stuff I could find about smo but when I try the schema's I get the following error
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Discover dependencies failed. ---> Microsoft.SqlServer.Management.Smo.InternalEnumeratorException: Schema is not supported in dependency discovery. Only objects of the following types are supported: UserDefinedFunction, View, Table, StoredProcedure, Default, Rule, Trigger, UserDefinedAggregate, Synonym, UserDefinedDataType, XmlSchemaCollection, UserDefinedType, PartitionScheme, PartitionFunction, SqlAssembly.
Is their a way around this issue.
PS
I'm making this in C# a language I had 0 experience with yesterday.
June 17, 2015 at 2:18 am
OK I kind of solved by doing this
var schemaList = new List<string>();
string dbUse = "USE [" + dbName + "]";
foreach (Schema sch in db.Schemas)
{
string schName = sch.Name.ToString();
if (schName != "db_accessadmin")
{
if (schName != "db_backupoperator")
{
if (schName != "db_datareader")
{
if (schName != "db_datawriter")
{
if (schName != "db_ddladmin")
{
if (schName != "db_denydatareader")
{
if (schName != "db_denydatawriter")
{
if (schName != "db_owner")
{
if (schName != "db_securityadmin")
{
if (schName != "guest")
{
if (schName != "INFORMATION_SCHEMA")
{
if (schName != "sys")
{
if (schName != "dbo")
{
string schCreate = "CREATE SCHEMA " + sch.ToString() + " AUTHORIZATION [" + sch.Owner.ToString() + "]";
schemaList.Add(dbUse);
schemaList.Add("GO");
schemaList.Add(schCreate);
schemaList.Add("GO");
//string schn = sch.ToString();
//string schO = sch.Owner.ToString();
}
}
}
}
}
}
}
}
}
}
}
}
}
}
Now the next challenge is to get the database create script including datafiles
July 31, 2015 at 9:00 am
Wow that is alot of work
Why arent you using the SMO option. I have other options but I would suggest that you research what you need.
ScriptingOptions scropt = new ScriptingOptions();
scropt.ScriptSchema = true;
scropt.AllowSystemObjects = false;
SchemaCollection schemacoll;
schemacoll = db.Schemas;
foreach (Schema schemas in schemacoll)
{
schemas.Script(scropt);
}
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply