Reading from SQL Database

Automation Studio
1

HI, I’m new to assistedge platform.
I want to read data from an SQL server on my machine and write the data to an excel file.
Specifics of data to be read should be taken as input from the user. How can I perform this task?

2

Hi,

You can use execute DB query activity to read from SQL db
You can export result set in excel

Regards,
Amit Anand

3

Hi Anand,

I have a single DB query which returns multiple tables. Can all these tables be recorded in an excel sheet?
When I tried executing the query, only a single table is being recorded in the datatable. Is there a solution?

4

Hi,

It is expected from a DB query only one result set is returned.

Regards,
Amit Anand

5

So it is not possible to record multiple tables…

Ok. Thanks Amit

6

Is there any workaround for this Amit?

Thanks

7

Hi,

I am not sure how you have formulated your query, because when we run a sql query only one result set is returned . In case you are running a loop for multiple result set then i can suggest a workaround

Regards,
Amit Anand

8

Yes Amit, I’m running a loop using cursors concept to get multiple result sets. I’ll share the query below

“”

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
SET @MyCursor = CURSOR FOR
select top 1000 YourField from dbo.table
where StatusID = 7

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @MyField

WHILE @@FETCH_STATUS = 0
BEGIN
  /*
     YOUR ALGORITHM GOES HERE   
  */
  FETCH NEXT FROM @MyCursor 
  INTO @MyField 
END; 

CLOSE @MyCursor ;
DEALLOCATE @MyCursor;

END;

“”
I picked it up from stackoverflow. Any help would be appreciated.

Thanks Amit