Solved

problem creating sql script using SMO

Posted on 2013-06-24
3
395 Views
Last Modified: 2013-10-07
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
Comment
Question by:homeshopper
  • 3
3 Comments
 

Author Comment

by:homeshopper
ID: 39271057
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
 

Accepted Solution

by:
homeshopper earned 0 total points
ID: 39272462
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
 

Author Closing Comment

by:homeshopper
ID: 39551619
I have now solved the problem
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now