Click here to Skip to main content
15,896,726 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i get this error when i run this code :

Database db = sv.Databases["oldDatabase"];

Database newDatbase = new Database(sv, "newDatabase");
newDatbase.Create();

ScriptingOptions options = new ScriptingOptions();
StringBuilder sb = new StringBuilder();
options.ScriptData = true;
options.ScriptDrops = false;
options.ScriptSchema = true;
options.EnforceScriptingOptions = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.WithDependencies = true;

TableCollection tables = db.Tables;

//conn.Open();
foreach (Microsoft.SqlServer.Management.Smo.Table mytable in tables)
{
foreach (string line in db.Tables[mytable.Name ].EnumScript(options))
{
sb.Append(line + "\r\n");
}
}

string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = sb.ToString().Split(splitter, StringSplitOptions.RemoveEmptyEntries);
int indexx = 0;
foreach (string command in commandTexts)
{

indexx = command.IndexOf("SET ");
string command2 = command.Insert(indexx, "use " + newDatabase + "\n");
SqlCommand comm = new SqlCommand(command2, conn);
comm.ExecuteNonQuery();
}
//return true ;
string scriptresult1 = commandTexts[0].ToString();
int index = scriptresult1.IndexOf("SET ");
string scriptresult12 = scriptresult1.Insert(index, "use " + newDatabase + "\n");

int res=sv.ConnectionContext.ExecuteNonQuery(scriptresult12);

return scriptresult12;
}
the error happen when sp(string builder) store more data so how can i fix it ??
Posted

1 solution

So...let me see if I can understand this.
You are accessing a database of unknown size, and getting SQL to create a script of each and every table in the DB, complete with all data, headings, dependencies, and indexes? And then you are building this into one, single StringBuilder?

And then you are surprised when you run out of memory?
You do realize that any single object in .NET is limited to 2GB, don't you?
Start by checking what is happening: use the debugger to look at how big the StringBuilder is getting as you go round that nested loop...
 
Share this answer
 
Comments
zainab77 20-Jul-14 4:11am    
yes u r right .. but how can i solve it ??
OriginalGriff 20-Jul-14 4:14am    
Don't generate such *HUGE* objects! :laugh:

What are you trying to achieve? Not "what are you doing?" - I can see that - but why are you doing it? What effect on the outside world are you trying to get? Because SQL DB's can get very, very big indeed!
zainab77 20-Jul-14 4:13am    
can i use any datatype like string as clop?
OriginalGriff 20-Jul-14 4:24am    
"clop"? What is "clop"?

But probably not - when I said "any single object in .NET" that is what I meant: you cannot create any single object that is bigger than that.
zainab77 20-Jul-14 4:46am    
i am trying to get dynamically script for my db (schema and data) .. i know that i can do it from db itself but it will not be dynamic .. so i used this sol ?? have you any idea to do that task without getting this error ??

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900