Page 1 of 1
SQL query -> export results
Posted: Thu Feb 28, 2019 2:41 pm
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!
Re: SQL query -> export results
Posted: Thu Feb 28, 2019 3:32 pm
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.
Re: SQL query -> export results
Posted: Thu Feb 28, 2019 4:03 pm
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!
Re: SQL query -> export results
Posted: Thu Feb 28, 2019 4:48 pm
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.
Re: SQL query -> export results
Posted: Fri Mar 01, 2019 8:40 am
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....
Re: SQL query -> export results
Posted: Fri Mar 01, 2019 9:01 am
by Oleg
I see in the log that the query returns nothing.
The loop ends immediately. The step #3 is not performed at all.
Re: SQL query -> export results
Posted: Fri Mar 01, 2019 9:08 am
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.
Re: SQL query -> export results
Posted: Tue Mar 05, 2019 2:16 pm
by Rukbunker
I agree with you. Will be continued, I will post the result here.