Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Dataset: Variable "sql_nid.CurrentRow" could not be set
Topic Summary: Looping through a dataset
Created On: 01/12/2018 06:22 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by Devin Cannon, on Wednesday, January 24, 2018 2:07 AM

Answer:
Hi,

In the task it's looping the "sql_nid" dataset (Step 14) but then before the end of the loop it's repopulating "sql_nid" with different information (Step 38). That logic seems strange - not sure if that's intended - but I'm able to reproduce this error if the query on Step 38 returns Null.
 01/12/2018 06:22 AM
User is offline View Users Profile Print this message

Author Icon
TimoCordes
Apprentice (20-49)

Posts: 35
Joined: 04/12/2016

Hi Guys,

i have a little problem. I modified an older job. After that modification there is a looping through a dataset which contains a loop through another dataset (both from SQL querys). The job runs fine thorugh the first pass and after he complete the first run and got back to the start of the "big" loop i got following error: Variable "sql_nid.CurrentRow" could not be set.

Any one a hint?

Regards
Timo

 Category Survey
AutoMate 10 version: 10.7.0
Windows version: Windows 10
 01/12/2018 06:26 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Timo,

Unfortunately that is not enough information to point you in the right direction. It appears that the task worked properly before the last modification, correct? What modifications were made within the task? Are you able to provide the task for our review?

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 01/12/2018 07:07 AM
User is offline View Users Profile Print this message

Author Icon
TimoCordes
Apprentice (20-49)

Posts: 35
Joined: 04/12/2016

Hi Leonard,

Yes the Task worked fine before. Original the second dataset loop did'nt exist (or better it was a file Loop that Loops through a csv file). The old Task produced sometimes Errors since the created csv had too much columns. So i removed that part an worked directly with the dataset and Loop through that. I will check if a can provide that Task.

 Category Survey
AutoMate 10 version: 10.7.0
Windows version: Windows 10
 01/15/2018 02:43 AM
User is offline View Users Profile Print this message

Author Icon
TimoCordes
Apprentice (20-49)

Posts: 35
Joined: 04/12/2016

Hi Leonard,

Attached the AML Code. I removed names/emailadresses and Passwords.

<AMVARIABLE NAME="nid" VALUE="" />
<AMVARIABLE NAME="Ziele" VALUE="" />
<AMVARIABLE NAME="Link" VALUE="" />
<AMVARIABLE NAME="google" VALUE="" />
<AMVARIABLE NAME="url" VALUE="" />
<AMVARIABLE NAME="Einleitung" VALUE="" DESCRIPTION="Einleitung" />
<AMVARIABLE NAME="Leistung" VALUE="" />
<AMVARIABLE NAME="Fahrziele" VALUE="" />
<AMVARIABLE NAME="Preise" VALUE="" />
<AMVARIABLE NAME="Schiff" VALUE="" />
<AMVARIABLE NAME="FAQ" VALUE="Seit ihr das erste mal auf Kreuzfahrt? Dann schaut einmal in unser Reise FAQ. Dort haben wir alles wissenswerte für Neulinge und einige interessante Infos für Reisen erfahrene gesammelt!" />
<AMDATABASE SESSION="sql" CONNECTION="0AM41HrgW5KAj8/QFTjn7CQTbkH/nFnHotBUB84QIP/0C/la2JM7h1vTFxHbhNZasFTuq3RYmOUH4Df1oZbXSvVWgPzs7YZUNHCN9o93XzZ/5BmTSMhu+gt6ryKnC93EpSv95uE8R9PasCidsJRFoeEIV72YpIwmP3jNRydHeXETj5aEpidgT6Q4XA==aME" />
<AMDATABASE ACTIVITY="sql_query" CONNECTION="0AM427/KZ68VZmY6WY355iYekSk6RyhiLxwznFU/aHU+iL3lgP/stpAKxcuB64wb489m/PQN6KBmuL3PHxVKTDep3wHZjXEp17Gn8KiQ1MP81pdqYkfRGtT/UsH48M+wd7UK4Hwy+pROMLav3R2QgoVfmolIIbgytaMKN3Q6bou90jpQa8GNvSQcbg==aME" RESULTDATASET="sql_nid">use kreuzfahrt
select distinct s.sails_nid from dbo.dreamlines_sails as s
join dbo.kreuzfahrt_statistik as d
on s.zone = d.zone and s.company = d.company and s.cabintype = d.cabintype
where discountPercentage > 50 and s.discountPrice <= 1500 and s.exported is null;</AMDATABASE>
<AMLOOP ACTIVITY="dataset" DATASET="sql_nid">
<AMDATABASE ACTIVITY="sql_query" CONNECTION="0AM4HBmuPy9zVNyY0Xs7B6PMsl4yAASmfwNevSl/1aygyW7pCYoQAuhgTyCxXfrKVGC5mTgMn6bZdvTOuzRz5yT1KIy0eundydYyQ6lf33FbyD/j6AZ0Ke/CgY2sG5pCKOKuzH08Z51AwniJIrs9VCp44pP+BhzSE7Cj4i0V/s3uEBTaHxWDcVIl/w==aME" RESULTDATASET="export">use Kreuzfahrt
select distinct s.nid, s.nights, s.zone, s.catalogPrice, s.discountPrice, s.currency, s.discountPercentage, s.cabinname, s.title, s.arrival, s.departure, s.url, c.ship, c.company, c.waypoints, c.ship_logo, c.route_teaser, s.includesservices, s.schiffsbeschreibung, c.startpoint, c.endpoint
from dbo.dreamlines_sails as s
join dbo.dreamlines_cruises as c
on s.nid = c.nid
where s.sails_nid = %sql_nid.sails_nid%
order by s.discountPrice asc
</AMDATABASE>
<AMFILESYSTEM ACTIVITY="delete" AM_ONERROR="CONTINUE" SOURCE="C:\Export\Dreamlines\body.txt" />
<AMFILESYSTEM ACTIVITY="delete" AM_ONERROR="CONTINUE" SOURCE="C:\Export\Dreamlines\urlshort.txt" />
<AMFILESYSTEM ACTIVITY="delete" AM_ONERROR="CONTINUE" SOURCE="C:\Export\Dreamlines\url.txt" />
<AMHTTP AM_RETRY="3" AM_RETRYDELAY="500" AM_ONERROR="CONTINUE" URL="%export.ship_logo%" FILE="C:\Export\Dreamlines\dreamlines_ship_%export.nid%.jpg" />
<AMHTTP AM_RETRY="3" AM_RETRYDELAY="500" AM_ONERROR="CONTINUE" URL="%export.route_teaser%" FILE="C:\Export\Dreamlines\dreamlines_route_%export.nid%.jpg" />
<AMHTTP ACTIVITY="post" AM_RETRY="3" AM_RETRYDELAY="500" AM_ONERROR="CONTINUE" SSLVERSION="SSL3,TLS1" CONTENTTYPE="application/json" URL="https://www.googleapis.com/urlshortener/v1/url?key=xxx" DATA="{"longUrl": "%export.url%"}" RESULTVARIABLE="google" />
<AMFILESYSTEM ACTIVITY="write_file" FILE="C:\Export\Dreamlines\url.txt">%google%</AMFILESYSTEM>
<AMPOWERSHELL SCRIPT="C:\export\dreamlines\jq.exe -r .id C:\export\dreamlines\url.txt | out-file -filepath C:\export\dreamlines\urlshort.txt" />
<AMFILESYSTEM ACTIVITY="read_file" FILE="C:\Export\Dreamlines\urlshort.txt" RESULTVARIABLE="url" />
<AMVARIABLE ACTIVITY="set" VARIABLENAME="Einleitung">Hallo Trip Rocker,

Ihr habt noch Urlaub?
Wie wäre es mit einer Kreuzfahrt Schnäppchen on the rocks? <br>
Wir haben hier eine schöne %export.nights% tägige Kreuzfahrt mit der %export.ship% (%export.company% Reederei).<br>
Los geht es ab dem %export.departure% in %export.startpoint% und endet am %export.arrival% in %export.endpoint%.

<p><!--more--></p></AMVARIABLE>
<AMVARIABLE ACTIVITY="set" VARIABLENAME="Fahrziele"></p><h1>Die Ziele</h1><p>

Folgende Fahrziele steuert die %export.ship% auf dieser Kreuzfahrt an:

<li>%export.waypoints%</li>
<br>
</br>

#img2#
</br></AMVARIABLE>
<AMVARIABLE ACTIVITY="set" VARIABLENAME="Leistung"></p><h1>Die Kreuzfahrt Leistungen</h1><p>
Folgende Leistungen sind bei dieser Kreuzfahrt inkludiert:
%export.includesservices%</AMVARIABLE>
<AMVARIABLE ACTIVITY="set" VARIABLENAME="Schiff"></p><h1>Das Schiff</h1><p>

Hier ein paar Eckdaten zur %export.ship%:
%export.schiffsbeschreibung%</AMVARIABLE>
<AMVARIABLE ACTIVITY="set" VARIABLENAME="Preise"></p><h1>Die Preise</h1><p>

Bei einer zweier Belegung werden folgende Preise für diese Kreuzfahrt der %export.company% Reederei fällig:
<br />
</AMVARIABLE>
<AMFILESYSTEM ACTIVITY="write_file" FILE="C:\Export\Dreamlines\body.txt">%Einleitung%

%Fahrziele%

%Schiff%

%Leistung%

%Preise%

</AMFILESYSTEM>
<AMLOOP ACTIVITY="dataset" DATASET="export">
<AMFILESYSTEM ACTIVITY="write_file" FILE="C:\Export\Dreamlines\body.txt"><li>%export.cabinname% + %export.discountPrice% +%export.currency%</li></AMFILESYSTEM>
</AMLOOP>
<AMFILESYSTEM ACTIVITY="write_file" FILE="C:\Export\Dreamlines\body.txt"><br>
</br>

Im günstigsten Fall spart ihr zum Katalogpreis von %export(1).catalogPrice% %export.currency% etwa %export(1).discountPercentage% Prozent.

Ist euer Interesse geweckt? Dann klickt auf den Link zum Anbieter, wählt das entsprechende Datum aus und macht eine unverbindliche Anfrage.
<br>
</br>
<a href="%url%" target="_blank" rel="nofollow noopener">Link zum Anbieter</a>
<br>
</br>

[mc4wp_form id="363"]</AMFILESYSTEM>
<AMEMAIL ACTIVITY="send" AM_RETRY="3" AM_RETRYDELAY="500" AM_ONERROR="CONTINUE" SERVER="smtp.gmail.com" PORT="465" USERNAME="xxx" PASSWORD="xxx" SECURITY="implicit" FROM="test@gmail.com" TO="poster@test" SUBJECT="[Kreuzfahrt] [%export.company%] %export.nights% Nächte %export.zone% Kreuzfahrt Schnäppchen mit der %export.ship% ab %export(1).discountPrice% %export.currency%" BODYFILE="C:\Export\Dreamlines\body.txt" HTML="YES" ATTACH="C:\Export\Dreamlines\dreamlines_ship_%export.nid%.jpg;C:\Export\Dreamlines\dreamlines_route_%export.nid%.jpg" />
<AMFILESYSTEM ACTIVITY="delete" SOURCE="C:\Export\Dreamlines\dreamlines_route_%export.nid%.jpg" />
<AMFILESYSTEM ACTIVITY="delete" SOURCE="C:\Export\Dreamlines\dreamlines_ship_%export.nid%.jpg" />
<AMDATABASE ACTIVITY="sql_query" CONNECTION="0AM4ajXawYH/sx+TT/F/0qsb4ZzTHuCeMqqt1/uTXEnZYwoJxZvRkB07oUOm3Dxs1LGZsWF8/uNnU4ZF4bK1sMBb+IKQYmRQezLZGlvGVQv8QjWskGuVi2XlX9531QL86IqbN2nAArynOUzVLdCKQD/6lD/GBtVeXE/JwRZfNkqcQ4a09eHMAeUHlQ==aME" RESULTDATASET="sql_nid">use Kreuzfahrt
update dbo.dreamlines_sails
set exported = 1 where sails_nid = %sql_nid.sails_nid%</AMDATABASE>
</AMLOOP>

 Category Survey
AutoMate 10 version: 10.7.0
Windows version: Windows 10
 01/16/2018 02:10 PM
User is offline View Users Profile Print this message

Author Icon
Mary Manzano
HelpSystems

Posts: 280
Joined: 03/10/2014

Hello Tim,

Can you export the task from and attach it to the forum post?
Also, what version of 10 are you using, 10.7.0?

 Category Survey
AutoMate 10 version:
Windows version:


-------------------------
Mary Manzano | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
 01/18/2018 05:19 AM
User is offline View Users Profile Print this message

Author Icon
TimoCordes
Apprentice (20-49)

Posts: 35
Joined: 04/12/2016

Hallo Mary, i will try that. But i will remove there also the user/Password informations for the Task. The Version that is used is 10.7.3

Regards
Timo

 Category Survey
AutoMate 10 version: 10.7.0
Windows version: Windows 10





Edited: 01/18/2018 at 07:15 AM by TimoCordes
 01/19/2018 11:44 AM
User is offline View Users Profile Print this message

Author Icon
Devin Cannon
HelpSystems

Posts: 398
Joined: 03/17/2016

Answer Answer
Hi,

In the task it's looping the "sql_nid" dataset (Step 14) but then before the end of the loop it's repopulating "sql_nid" with different information (Step 38). That logic seems strange - not sure if that's intended - but I'm able to reproduce this error if the query on Step 38 returns Null.

 Category Survey


-------------------------

Devin Cannon | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 533 guests browsing this forum, which makes a total of 533 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2019 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog