SQL query -> export results

Discuss RoboTask here

SQL query -> export results

Postby Rukbunker » Thu Feb 28, 2019 2:41 pm

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!
Using version 7.3.0.964
Rukbunker
 
Posts: 90
Joined: Mon Feb 22, 2016 4:06 pm

Re: SQL query -> export results

Postby Oleg » Thu Feb 28, 2019 3:32 pm

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
Oleg
Site Admin
 
Posts: 2393
Joined: Thu Jan 01, 1970 1:00 am

Re: SQL query -> export results

Postby Rukbunker » Thu Feb 28, 2019 4:03 pm

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!
Using version 7.3.0.964
Rukbunker
 
Posts: 90
Joined: Mon Feb 22, 2016 4:06 pm

Re: SQL query -> export results

Postby Oleg » Thu Feb 28, 2019 4:48 pm

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
Oleg
Site Admin
 
Posts: 2393
Joined: Thu Jan 01, 1970 1:00 am

Re: SQL query -> export results

Postby Rukbunker » Fri Mar 01, 2019 8:40 am

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 164 times
02.PNG
02.PNG (11.69 KiB) Viewed 164 times
01.PNG
01.PNG (12.15 KiB) Viewed 164 times
Using version 7.3.0.964
Rukbunker
 
Posts: 90
Joined: Mon Feb 22, 2016 4:06 pm

Re: SQL query -> export results

Postby Oleg » Fri Mar 01, 2019 9:01 am

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: 2393
Joined: Thu Jan 01, 1970 1:00 am

Re: SQL query -> export results

Postby Oleg » Fri Mar 01, 2019 9:08 am

...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
Oleg
Site Admin
 
Posts: 2393
Joined: Thu Jan 01, 1970 1:00 am

Re: SQL query -> export results

Postby Rukbunker » Tue Mar 05, 2019 2:16 pm

I agree with you. Will be continued, I will post the result here.
Using version 7.3.0.964
Rukbunker
 
Posts: 90
Joined: Mon Feb 22, 2016 4:06 pm


Return to General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 11 guests

cron