ODBC Data Sourcs

Help
1

Hi,

I want to configure ODBC connection in uipath but before that i want to check if ODBC data source is present in the system or not. I have no idea and dont know anything about ODBC data sources.
BTW I tried to using connect activity and used a connect string to connect and test connection but I am getting the below shared error every time I run. Any help in this.

TIA

image
image1366×768 71.2 KB

1 Like
2

Which SQL version are you using or want to connect to?

3

Hey @rameezimtiaz

Can you try this link…

1 Like
4

@rameezimtiaz
I have a test example of an SQL connection laying around. Will update this topic when i have checked it. and will explain a bit. That would be a good start from there for you to explore the rest yourself

1 Like
5

I have no idea about it. How to check?

6

This would be a great help
Thanks

7

When i open ODBC Data source I get this screen

image
image1366×768 102 KB

8

I will post an example xaml. And will explain a bit in it. Don’t need any info from you at the moment. I wasn’t sure if the SQL version was needed, so i asked just in case. I will update the topic and quote you so you will get notified.

1 Like
9

thanks

10

@rameezimtiaz

edit: you don’t need the local ODBC connector on your PC.

edit2: of course edit the credentials with your own database settings and credentials :wink:

Ok, i might went a bit over the top with it, but i think you will a good impression on the SQL connection and how to use it. I added some notations to the objects. If something isn’t clear, just let me know.
Example: see attachment: UI_SQL_example.zip (20.7 KB)

I have setup 2 examples in it.

  • single usage. if you need to run just 1 query.
  • multi usage. If you need to run multiple queries you don’t want to set the connection everytime. You can use the Connection acitvity and just output the connection string into a variable, so you can reusage the connection string again.
  • note: the credentials used in this example are fake. But it will give the impression
  • note: please don’t use credentials in plain text. This is only used just to give the example.
    Secure them by using the Windows credential manager or when using orchestrator set them as
    Credentials in Your Assets!!!

what do you need?:
SQL server (+ instance if present) > ask your IT department
SQL databaseName
SQL databaeUserName
SQL databaseUserPassword
SQL tables to query on

examples used:

create table WKV_TEST1 (
id int identity(1,1),
name varchar(200) not null,
primary key (id)
);
create table WKV_TEST2 (
id int identity(1,1),
name varchar(200) not null,
primary key (id);
)

Some testdata:

insert into WKV_TEST1 (name) select ‘Table 1 test’;
insert into WKV_TEST2 (name) select ‘Table 2 test’;

UiPath Activity:
UiPath Activity: UiPath.Database.Activities

in my example. i have an SQL server in my homelab.
Server (DataSource): sql.homelab.lan (no separate database instance present)
databaseName (Initial Catalog): WKV
databaeUserName (User ID): wkv_user (role: db_owner)
databaseUserPassword (Password): Hbn3h46wuReX

I made 2 xaml files of the below options.
SQL_MultiUse.xaml
SQL_SingleUse.xaml

**1. [SQL_MultiUse] Manual setup (multi use).
I don’t like wizards, so i always use the manual option. Set it once and output it to a variable, so i can use it in the
whole project

That would result in this connectionString
“Data Source=sql.homelab.lan;Initial Catalog=WKV;Persist Security Info=True;User ID=wkv_user;Password=Hbn3h46wuReX”

2. [SQL_SingleUse] use the wizard.
This option can be used when you only need to run 1 query. Quick and dirty ;). See the screenshots on how to use this:

sql-wizard-1
sql-wizard-11029×546 36 KB

Fig 1.

sql-wizard-2
sql-wizard-2379×532 6.35 KB

Fig 2.

sql-wizard-3
sql-wizard-3376×520 12.3 KB

Fig 3.

sql-wizard-4
Fig 4.

Test the setup
In the Main.xaml i attached both that they will run after eachother. Just test/play with it. Or run the other xaml files separate (Run File)

Some result outputs
sql-single-result | sql-multi-result1 | sql-multi-result2

and what does SQL management studio say:
sql-result-check

Troubleshooting:

  • connection refused? > Check with SQL Management Studio if your credentials are correct and the firewall is open for incoming connections to the SQL database
  • anything else? Give as much information as possible. Comments like “doesn’t work” i don’t take serious;)

You can set the connection string manually, or use the wizard

1 Like