• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

problem creating sql script using SMO

I am using SMO to create and write a sql file.
I am getting the following error:
Thanks in advance for any help given.
Use of unassigned local variable 'Tables' Line 208
foreach (var tbl in Tables) // error line 208
                    {
                        db.Tables[tbl].EnumScript(options);
                    }

Open in new window

Cannot implicitly convert type 'Microsoft.SqlServer.Management.Smo.TableCollection' to 'string[]' Line 185
Tables = tables;// error line 185

Open in new window

For clarity, the full code is listed below:
Response.Write(MyAppCaaaMisc2.divStart2);
            try
            {       
                if (!IsPostBack)
                {
                    //<!-- Start Page Content -->
                    string Dbase = Request.QueryString["database"];// aaaMisc2.aspx?database=Cams2
                    string Tbase = Request.QueryString["table"];
                    string database_tb = null;
                    string server_tb = null;
                    string user_tb = null;
                    string pswrd_tb = null;
                    string[] Tables;
                    string FileName = @"C:\TableScriptWithDependencies.sql";
                    connectionString = Session["strConnection"].ToString();// line 168 Connection details etc
                    Database database = SQLTableDatabaseClass.SinglDatabase(connectionString, Dbase.ToString());
                    string connstr = "@" + database; 
                    MyAppCaaaMisc2.errorLabel1 = "<font color='red'>" + "174:FL:" + "</font>" + connstr + ";  connectionString:" + connectionString + "#";
                    MyAppdivEL1();
                    string[] str1 = connstr.Split(']');
                    database_tb = str1[0].Split('[')[1];
                    connstr = "@" + connectionString; // detailsLabel.Text;
                    str1 = connstr.Split(';');
                    server_tb = str1[0].Split('=')[1];
                    user_tb = str1[1].Split('=')[1];
                    pswrd_tb = str1[2].Split('=')[1];
                    MyAppCaaaMisc2.errorLabel2 = "<font color='red'>" + "183:FL:" + "</font>" + "database_tb:" + database_tb + "    server_tb:" + server_tb + "    user_tb:" + user_tb + "    pswrd_tb:" + pswrd_tb + "    connectionString:" + connectionString + "#";
                    MyAppdivEL2();
                    TableCollection tables = SQLTableDatabaseClass.GetTablesFromDatabase(connectionString, database_tb);
                    //public static void Backup(string FileName, string[] Tables)
                    //<!-- Start Page Content -->
                    Tables = tables;// error line 185
                    StringBuilder sb = new StringBuilder();
                    Server srv = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(server_tb, user_tb, pswrd_tb));    
                    Database db = srv.Databases[database_tb];  
                    Response.Write("<br/><br/><br/><br/>");
                    foreach (User user in db.Users)
                    {
                        Response.Write("User: " + user.Name);
                        Response.Write("<br/>");
                        Response.Write("Login: " + user.Login);
                        Response.Write("<br/>");
                        Response.Write("Type: " + user.UserType);
                    }
                    ScriptingOptions options = new ScriptingOptions();
                    options.ScriptData = true;
                    options.ScriptDrops = false;
                    options.FileName = FileName;
                    options.EnforceScriptingOptions = true;
                    options.ScriptSchema = true;
                    options.IncludeHeaders = true;
                    options.AppendToFile = true;
                    options.Indexes = true;
                    options.WithDependencies = true;
                    foreach (var tbl in Tables) // error line 208
                    {
                        db.Tables[tbl].EnumScript(options);
                    }
                    //<!-- End Page Content -->
                }// End !IsPostBack
                else
                {
                //<!-- Start Page Content -->
     
                //<!-- End Page Content -->
                }
            } // End try
            catch (Exception ex)
            {
                MyAppCaaaMisc2.errorLabel4 = "<font color='red'>" + "err:220:PL:" + "</font>" + MyAppCaaaMisc2.errLabel + "&nbsp;&nbsp;" + ex.ToString();
                MyAppdivEL4();
            } // End catch
            Response.Write(MyAppCaaaMisc2.divEnd);

Open in new window

0
homeshopper
Asked:
homeshopper
  • 3
1 Solution
 
homeshopperAuthor Commented:
I have tried the following:
for (int i = 0; i < tables.Count; i++)
                    {
                        Response.Write("(" + i + ")" + tables[i].ToString() + "#<br/>");
                        Tables[i] = tables[i].ToString();
                    }

Get following error at line 190 Tables[i] = tables[i].ToString();
System.NullReferenceException: Object reference not set to an instance of an object.
Response.Write("(" + i + ")" + tables[i].ToString() + "#<br/>");

Open in new window

does display table names.
0
 
homeshopperAuthor Commented:
Get following error at line 190:
System.NullReferenceException: Object reference not set to an instance of an object.
line 190 Tables[i] = tables[i].ToString();

Open in new window

Complete code below:
for (int i = 0; i < tables.Count; i++)
                    {
                        Response.Write("(" + i + ")" + tables[i].ToString() + "#<br/>");
                        Tables[i] = tables[i].ToString();
                    }

Open in new window

0
 
homeshopperAuthor Commented:
I have now solved the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now