Select Statement Output Variable to Write to CSV

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
7 posts Page 1 of 1

jlee1

Posts: 12
Joined: Mon Jun 22, 2015 12:41 pm

Post by jlee1 » Tue Jun 30, 2015 4:17 pm
Hi everyone,

I'm running a test that writes data to a database table. I successfully do so and then I want to run a select query so that I put the data into an output variable to be written to a csv file. When I get to the Select statement I receive this error: ERROR [8044 - Write Invoice Total to File] Last known record near row 0

There is 1 row and 1 column in the table and I just want to write it to a CSV file. From my google research it sounds like the data is being truncated. Does anyone have thoughts or suggestions? Thanks!

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Jun 30, 2015 4:31 pm
Make sure the variable you are setting the value to after the SQL|Query is version 1

as in ...

SQL ResourceID
Query "Select * from Table ..." - OutputRowsetVar="MyRowValue"

<setVariable label="SetVar: MyValue" name="MyValue" value="${MyRowValue[1]" version="1.0" />
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

jlee1

Posts: 12
Joined: Mon Jun 22, 2015 12:41 pm

Post by jlee1 » Wed Jul 01, 2015 8:10 am
Hi Rick,

Thanks for the input! I never actually declared a variable outside of the Select Task. Would you suggest I do that? I didn't see anywhere that I could put "Version 1.0" into the code and when I manually did it it gave me an error. Here is the code that I am using. This is my select task followed by my write to CSV task. Any thoughts?

Code: Select all
<sql label="Select Rows and Invoice Total" resourceId="DCP-NT9" version="1.0">
				<query label="total control" outputVariable="totalControl">
					<statement>select top 2 * FROM [master].[dbo].[fileSummary]</statement>
				</query>
			</sql>


			<writeCSV label="Write Invoice Total to File" inputRowSetVariable="${totalControl}" outputFile="unzipped/Control.csv" whenFileExists="overwrite" fieldDelimiter="comma" version="1.0" />

jlee1

Posts: 12
Joined: Mon Jun 22, 2015 12:41 pm

Post by jlee1 » Wed Jul 01, 2015 8:32 am
OK so I adjusted my Write to CSV task so that it writes one portion of the rowset at a time, as in: ${totalControl[1] and ${totalControl[2]

However, this time I received error ERROR The result set has no current row.

So somehow it seems like my Select statement isn't pulling data and assigning it to the "out" variable called totalControl.

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Wed Jul 01, 2015 8:42 am
jlee1,

There is an option while selecting tasks at the top of the page. Click on the check box and it will show you earlier versions of the task. That's where you'll find the Version 1 of the SetVariable task.

The code you have should work for multiple row selection and input into CSV files.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

jlee1

Posts: 12
Joined: Mon Jun 22, 2015 12:41 pm

Post by jlee1 » Wed Jul 01, 2015 11:12 am
Hi Rick,

I just can't seem to figure this one out. I keep geting this error when I write the database rowset variable to a csv: ERROR [8044 - Write Invoice Total to File] Last known record near row 0

So far I've written to the database, Select from the database(select statement works when I manually do it), put the Select output to a variable, and then I fail at the write to CSV. Any thoughts? Thanks again!

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Mon Jul 06, 2015 6:27 pm
JLee,

Based on our conversation, have you figured out what you need to do with this?

Please let us know...
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
7 posts Page 1 of 1