topic RE: Generating Table script from SQL server in Product Forum

Generating Table script from SQL server

Soumya21 — Tue, 06 Jun 2023 05:57:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

SimonCooke1 — Tue, 06 Jun 2023 12:32:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

Soumya21 — Fri, 09 Jun 2023 11:00:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

SimonCooke1 — Fri, 09 Jun 2023 12:53:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

SoniyaNag — Sat, 10 Jun 2023 09:54:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

Soumya21 — Sat, 10 Jun 2023 14:38:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

Soumya21 — Sat, 10 Jun 2023 14:40:00 GMT

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
------------------------------

RE: Generating Table script from SQL server

CamilaNagg — Wed, 26 Jul 2023 09:56:00 GMT

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
------------------------------

Re: RE: Generating Table script from SQL server

Charlie77 — Thu, 29 Aug 2024 09:14:03 GMT

When generating a table script from SQL Server, the process involves extracting the schema definition of a table, which includes its structure, constraints, and relationships. This script is essential for recreating the table in a different database or environment. To generate the script, use SQL Server Management Studio (SSMS) and navigate to the desired table. Right-click on the table, select Script Table as, then choose CREATE To and select your preferred format (e.g., file or clipboard). Review the generated script to ensure it accurately represents the table’s structure before executing it in the target environment.