Database activities - Insert datatable

Help Studio
, , , , ,
1

Using the package UiPath.Database.Activities, I am connecting to a database with an account that is only able to insert data, but does not have SELECT permission.

I am using the ‘DatabaseConnect’ activitie to configure the connection.

After that, im using the InsertDataTable to insert a datatable to the database, but Iam having this error:

message: Microsoft.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object ‘', database '’, schema ‘dbo’. at UiPath.Database.Activities.InsertDataTable.HandleException(Exception ex, Boolean continueOnError) at UiPath.Database.Activities.InsertDataTable.ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken) at UiPath.Database.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager) ClientConnectionId:4baefa76-8bf4-4bed-9d95-f25db1a790fc Error Number:229,State:5,Class:14

The account exists only to write data, not to read, this activity did not have to only insert? Why I have this Select permission denied? How can I fix this?

2

Get the Select permission granted to you in the database.

3

can you run query activity and executing there the Insert… SQL

4

And how can I insert the datatable i have with the ‘run query’ activitie? I use as a string?

5

This is not a possibility

6

to clear the task more in detail:

Data Table exists already in the DataBase
Data is reflected as rows within a System.Data.Datatable variable

So you can try to insert a test row by:

7

That’s just strange. If you have insert, you should have select. If they won’t give you select, then you can’t use the Insert activity because after inserting it does a select to return the number of rows added.

8

You’d have to loop through your datatable and run one insert statement for each row.

9

Yes, I already inserted a test row in SQL, its working.

My datatable has the same formact, when tha account used to have the select access, it was working perfectly.

I think I will export the datatable to a CSV file, and run a query inserting the csv file to the database table

10

I will try to remove the output and see if it works, I can have the number of affected counting the datatables rows. Tks

11

I don’t think removing the output stops it from doing the select, it’ll still try to do it you just won’t have it in a variable.

12

We do feel that easy the Datatable can be transformed into the SQL Code lines, when this is preferred.

However we do feel that a workaround was found and the topic can be closed
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum

13

How would you do the insert with the datatable?

The data is already fomatted.

It would be like

INSERT INTO table_name
VALUES (datatable);