SQL query -> export results

Discuss RoboTask here
Post Reply
Rukbunker
Posts: 192
Joined: Mon Feb 22, 2016 4:06 pm
Location: Netherlands

SQL query -> export results

Post by Rukbunker »

Hey Oleg.

I'm struggling with exporting stuff from SQL. I'm really a n00b with this. My query seems OK according to Robotask, but I have no clue how to export my results (around 1181 lines).
However, "Rows effected" are 0.

Code: Select all

SELECT DISTINCT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(account0.telephone1, ' ', ''),'-',''),'(',''),')',''),'/',''),'\',''),'+490','+49'),'+49+49','+49') AS phone
FROM        FilteredAccount AS account0 WHERE     (account0.telephone1 IS NOT NULL)
Can you make just a simple task which exports the results to a file? Thanks!
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: SQL query -> export results

Post by Oleg »

Look at my example, please (see below)

You can collect data in some variable and the write it to text file
Of course you have to specify your own connection string in the step #2 (dataset loop)

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1040932817
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task325"
Hide=INTEGER|0
ID=INTEGER|-586330382
LogOnAsUser=INTEGER|1
Name=STRING|"export SQL"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value """""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"TXT"

[Actions\Action2]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER

[Actions\Action2\Params]
commatext=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=mytest"
override=STRING|"0"
paramcount=STRING|"0"
password=STRING|"2005621131211342036418828"
sql=STRING|"""SELECT DISTINCT "",""REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(account0.telephone1, ' ', ''),'-',''),'(',''),')',''),'/',''),'\',''),'+490','+49'),'+49+49','+49') AS phone"",FROM,""FilteredAccount AS account0 "",""WHERE     "",""(account0.telephone1 IS NOT NULL)"""
timeout=STRING|"60"
var2=STRING|"ROW"
withmemo=STRING|"0"

[Actions\Action3]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TXT"" with value ""{txt}{row}{eol}"""
Params=FOLDER

[Actions\Action3\Params]
expand=STRING|"1"
varname=STRING|"TXT"
varvalue=STRING|"{txt}{row}{eol}"

[Actions\Action4]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

[Actions\Action5]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file C:\temp\export.txt"
Params=FOLDER

[Actions\Action5\Params]
encode=STRING|"0"
fileexists=STRING|"0"
filname=STRING|"C:\temp\export.txt"
line0=STRING|"{txt}"
linecount=STRING|"1"
suppress=STRING|"0"
Save the text of the task to a file and use menu Task->Import to import the task into RoboTask.
Oleg Yershov
Rukbunker
Posts: 192
Joined: Mon Feb 22, 2016 4:06 pm
Location: Netherlands

Re: SQL query -> export results

Post by Rukbunker »

Thanks for your quick answer. The file is being created but without any results. I think that issue has to do with permissions on the SQL server itself.

Again, thanks!
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: SQL query -> export results

Post by Oleg »

I keep your SQL query without any changes. Also I don't know your database. Maybe the SQL returns nothing...
If SQL syntax OK this does not guarantee that the SQL returns correct data.
Send the whole log file of this task, I want to see it.
Oleg Yershov
Rukbunker
Posts: 192
Joined: Mon Feb 22, 2016 4:06 pm
Location: Netherlands

Re: SQL query -> export results

Post by Rukbunker »

I'd really like to, but there is nothing to tell. My next approach is to enter user credentials from someone who has admin rights in Robotask and see if the result is the same.
Other approach is to login with the Robotask account to the SQL machine and execute the same query there.

Code: Select all

I 1-3-2019 08:29:41: ****** Starting task: [CRM] 05 - SQL ******************
I 1-3-2019 08:29:41: Executing "1.Set variable "TXT" with value """
I 1-3-2019 08:29:41: Executing "2.Dataset Loop"
I 1-3-2019 08:29:42: Executing "5.Create text file C:\temp\export.txt"
I 1-3-2019 08:29:42: Task executed successfully
To be continued....
Attachments
03.PNG
03.PNG (8.71 KiB) Viewed 11215 times
02.PNG
02.PNG (11.69 KiB) Viewed 11215 times
01.PNG
01.PNG (12.15 KiB) Viewed 11215 times
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: SQL query -> export results

Post by Oleg »

I see in the log that the query returns nothing.
The loop ends immediately. The step #3 is not performed at all.
Oleg Yershov
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: SQL query -> export results

Post by Oleg »

...to enter user credentials from someone who has admin rights...
I don't think that this helps you.
You will get error when you have not enough permissions to do SELECT query on this table/view.
But I see that query performed successfully. No rows means that the condition is incorrect.
Oleg Yershov
Rukbunker
Posts: 192
Joined: Mon Feb 22, 2016 4:06 pm
Location: Netherlands

Re: SQL query -> export results

Post by Rukbunker »

I agree with you. Will be continued, I will post the result here.
Post Reply