Generating Table script from SQL server

[Soumya21]

Hi Everyone

I want to generate table script from SQL Server.

I am using below code and added in global code of object

    public static class TableScriptGenerator

    {

        public static string GenerateTableScript(string serverInstance,string databaseName, string tableName, out string errorMessage)

        {

            try

            {

                //Server srv = new Server();

                //srv.ConnectionContext.Login = login;

                //srv.ConnectionContext.Password = password;

Server srv;

srv=new Server(serverInstance);

                Database db= new Database();

                db = srv.Databases[databaseName];

StringBuilder sb = new StringBuilder();

                Table tbl = db.Tables[tableName];

                ScriptingOptions options = new ScriptingOptions();

                options.ClusteredIndexes = true;

                options.Default = true;

                options.DriAll = true;

                options.Indexes = true;

                options.IncludeHeaders = true;

                StringCollection coll = tbl.Script(options);

                string script = string.Join(Environment.NewLine, coll);

                errorMessage = string.Empty;  // Clear any previous error message

                return script;

            }

            catch (Exception ex)

            {

                errorMessage = ex.Message;  // Assign the error message to the output variable

                return null;

            }

        }

    }

and I am calling function Generate Table script in code stage of object

string serverInstance = "abc\\SQLEXPRESS";

string databaseName = "Demo";
string tableName = "humanresources";

string script;
string errorMessage;

script = TableScriptGenerator.GenerateTableScript(serverInstance,databaseName, tableName, out errorMessage);

OutputVariable = script;
ErrorMessageOutputVariable = errorMessage;

In this I have added OutputVariable and ErrorMessageOutputVariable are output variables

I have added required dll's and namespace

I am getting error like cant connect to server

If anyone have idea on this, please help me on this

Thanks,

Soumya

------------------------------
Soumya k RPA Developer
RPA Developer
Qualesce India Private Limited
Bangalore
------------------------------

[SimonCooke1]

Hi Soumya,

Is your connection error saying something like 'server cannot be located' or is it more of an 'unauthorised' error. 

If the server cannot be located it suggests you either have the server name wrong or there is some network related issues i.e. internal firewall that your infrastructure team will be able to help you with. 

If it is unauthorised that means you've hit the server OK but either the credentials are wrong or they don't have the permissions you need. This would probably be the DB owner who can help you

Simon

------------------------------
Simon Cooke
------------------------------

[Soumya21]

Hi @Simon Cooke 

It doesn't show like server cannot be located and server name is correct. when I run this code in Visual studio Its working when I run this in Blueprism its throwing error

Does anyone have worked on this, If yes kindly help on this

------------------------------
Soumya k RPA Developer
RPA Developer
Qualesce India Private Limited
Bangalore
------------------------------

[SimonCooke1]

What's the exact error message you're getting? 

I've not used the SQL Server Management Objects in Blue Prism before but have been successful with the SQL Client before. Have you tried that, something like :

    public static class TableScriptGenerator
    {
        public static string GenerateTableScript(string connectionString, string tableName, out string errorMessage)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string scriptQuery = $"SELECT definition FROM sys.all_sql_modules WHERE object_id = OBJECT_ID('{tableName}')";
                    using (SqlCommand command = new SqlCommand(scriptQuery, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                sb.AppendLine(reader.GetString(0));
                            }
                        }
                    }
                }
                errorMessage = string.Empty;
                return sb.ToString();
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
                return null;
            }
        }
    }

You'd need to add in the assemblies for System, System.Data.SqlClient and System.Text

Simon

------------------------------
Simon Cooke
------------------------------

[SoniyaNag]

Hi Friend, To generate a table script from SQL Server, you can use the "Generate Scripts" feature in SQL Server Management Studio (SSMS). Right-click on the database, go to Tasks, and select "Generate Scripts." Choose the specific table you want to script and customize the options as needed.

------------------------------
Soniya Nag
------------------------------

[Soumya21]

Hi @Simon Cooke

This code only works for other than table objects. so only I preferred the above-mentioned code to integrate with blueprism.

and that code works in Visual studio not in blueprism.

------------------------------
Soumya k RPA Developer
RPA Developer
Qualesce India Private Limited
Bangalore
------------------------------

[Soumya21]

Hi @Soniya Nag 

Yes, we can generate scripts like as u mentioned but its manual work. 

I want to develop a bot to generate script for tables.

------------------------------
Soumya k RPA Developer
RPA Developer
Qualesce India Private Limited
Bangalore
------------------------------

[CamilaNagg]

This script generates a table in SQL Server. 
   The table can be used to store data with defined columns, 
   data types, and constraints to ensure data integrity and consistency. 
   Modify the column names, data types, and constraints as needed 
   to suit the specific requirements of the application. */

------------------------------
Camila Nagg
------------------------------