(9000 - convert Details to CSV) Invalid Input. Input must be of type Rowset

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

stimetb

Posts: 4
Joined: Wed Jan 11, 2017 9:57 am

Post by stimetb » Wed Jan 25, 2017 9:30 am
Looking for help on this error. I'm setting up a CSV report using SQL. my report will look something like the attached file: I know the report works as I simulated it on management studio. But once it tries to put it into the output variable and/or placing it in the input rowset variable on the convert to csv, this error comes up:

Project '/Finance/TEA Placement Staffing Detail WA Smart Export copy' failed. Job number is '1373818465579'.
[9000 - Convert TEA Details to CSV] Invalid input. Input must be of type RowSet.
Attachments
goanywhere csv error.csv
(634 Bytes) Downloaded 301 times

Support_Rick

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

Post by Support_Rick » Wed Jan 25, 2017 10:45 am
You will need to post your actual project and what you're trying to accomplish. The selection of SQL data from a table generates a Rowset and that Rowset can be used as input into creating the CSV file easily.

As an example:
Code: Select all
<sql label="Connect to DB" resourceId="Pwr744" version="1.0">
	<query label="Retrieve Data" outputVariable="MyData">
		<statement>Select * from GADemo.employee</statement>
	</query>
</sql>

<setVariable label="setVar: FileName" name="FileName" value="${ Concat( &apos;Temp.&apos;, currentDate(&apos;yyyyMMdd&apos;), &apos;.&apos;, system.job.id, &apos;.XML&apos; ) }" version="2.0" />

<writeCSV label="Convert to CSV" inputRowSetVariable="${MyData}" outputFile="${FileName}" whenFileExists="overwrite" fieldDelimiter="comma" includeHeadings="true" outputFileVariable="csvFile" version="1.0" />
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

stimetb

Posts: 4
Joined: Wed Jan 11, 2017 9:57 am

Post by stimetb » Wed Jan 25, 2017 3:36 pm
the following is the sql code used to run the project. If i run this on management studio, it returns 771 rows of data. However running it on goanywhere results in 0 rows of data generated. (is there certain sql commands that goanywhere does not handle like cross apply?

Code: Select all
select Value, Description 
into #CyGlobalPayCodeSetXHR2
from LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet 
     where 	 Codeset = 'XHR2'

select Value, Description 
into #CyGlobalPayCodeSetHR59
       FROM LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet cs59
       where CODESET LIKE 'HR59'

 
select Social, SchoolKey, Subject, Fte, SchoolCode, row_number() 
   over (partition by Social order by Fte desc) rownumber
into #TempRepAssign
from RepAssign
 
--delete from #TempRepAssign where rownumber <> 1
 
--select * from  #TempRepAssign
select Social, SchoolKey, Subject, Fte, SchoolCode, rownumber
into #TempRepAssign2
from #TempRepAssign
where rownumber = 1

CREATE TABLE #TempRepTable
(
 EmployeeNumber char(10) NULL,
 Subject       char(05) NULL,
    Fte           decimal(5,2) NULL,
    RepSchoolCode    char(10) NULL,
    RepSubjectAssign  char(50) NULL
);
insert into #TempRepTable (EmployeeNumber, Subject, Fte, 
            RepSchoolCode, RepSubjectAssign) 
select ra.Social, ra.Subject, ra.Fte, ra.SchoolCode, 
/*use subselect for this field to get into paycodeset*/
  (select Description from #CyGlobalPayCodeSetXHR2 cs02
   where ra.Subject = cs02.Value) RepSubjectAssign
--cs02.Description

from #TempRepAssign2 ra


  UPDATE #TempRepTable
  -- take out ',' from RepSubjectAssign
set RepSubjectAssign  =  replace(RepSubjectAssign, ',', '')
--  select * from #TempRepTable
---------------------------------------

CREATE TABLE #TempEndorsements
(
 CredentialIdent         int NULL,
 Endorsements            varchar(MAX) NULL
    
);
insert into #TempEndorsements (CredentialIdent, Endorsements)
select ec.CredentialIdent, edx.list

from EmpCredentials ec
--from #EmpCredentials ec
cross apply (
  select ee.Endorsement + ',' 
  from EmpEndorsements ee
  where ec.CredentialIdent = ee.CredentialIdent
  order by  DateIssued desc, REPOverride asc
  for XML path('') ) edx (list)
 

  UPDATE #TempEndorsements
  -- take out ',' from Endorsements
set Endorsements  =  replace(Endorsements, ',', '/')
  --select * from #TempEndorsements
  -------------------------------------- 

CREATE TABLE #EmpCredentials
( CredentialIdent         int not null,
 EmployeeNumber          char(10) NULL,
 CertificateNumber       char(15) NULL,
    CertificateTypeCode     char(4) NULL,
    RepOverride             char(1) NULL,
    DateIssued              smalldatetime NULL,
    DateExpired             smalldatetime NULL,
    Endorsements            varchar(MAX) NULL
  )
 insert into #EmpCredentials (CredentialIdent, EmployeeNumber,
   CertificateNumber, CertificateTypeCode, RepOverride,   
   DateIssued, DateExpired, Endorsements)
 
 select --*
  ec.CredentialIdent, ec.EmployeeNumber, ec.CertificateNumber, 
  ec.CertificateTypeCode, 
  ec.RepOverride, ec.DateIssued, ec.DateExpired, ee.Endorsements 
 
 from EmpCredentials ec
left join #TempEndorsements ee
 on ec.CredentialIdent = ee.CredentialIdent
  
 WHERE RepOverride IN ('R', 'S')
 
-------------------------------------------above----- certificate retrieval

CREATE TABLE #TempEmpCredentials
(
 
 EmployeeNumber          char(10) NULL,
 CertificateNumber       char(15) NULL,
    CertificateTypeCode     char(4) NULL,
    RepOverride             char(1) NULL,
    DateIssued              smalldatetime NULL,
    DateExpired             smalldatetime NULL,
    CertificateTypeDesc     char(50) NULL,
    Endorsements            varchar(MAX) NULL
 
); 
insert into #TempEmpCredentials (EmployeeNumber, CertificateNumber, 
            CertificateTypeCode, RepOverride, DateIssued,
            DateExpired, CertificateTypeDesc, Endorsements ) 
SELECT ec.EmployeeNumber, ec.CertificateNumber, 
       ec.CertificateTypeCode, ec.RepOverride, ec.DateIssued, 
       ec.DateExpired, 
	   /*use subselect for this field to get into paycodeset*/
  (select Description from #CyGlobalPayCodeSetHR59 cs59
   where ec.CertificateTypeCode = cs59.Value) RepSubjectAssign,
	   --cs59.Description, 
       ec.Endorsements
       from #EmpCredentials ec
	   --left join #CyGlobalPayCodeSetHR59 cs59
      -- ON ec.CertificateTypeCode = cs59.Value 
    
  UPDATE #TempEmpCredentials
  -- take out ',' from CertificateTypeDesc
set CertificateTypeDesc  =  replace(CertificateTypeDesc, ',', '')
 -- select * from #TempEmpCredentials
------------------------------------------------------

select ed.EmployeeNumber, ed.Lastname, ed.FirstName, 
pc.Description as CheckLocDesc, 
ej.JobTitle,
tr.RepSubjectAssign,
ec.CertificateTypeDesc,
 ec.Endorsements,
 convert(decimal(8,4), ROUND(ex.RateValue4 * .01, 4))  AS RateValue4,
 convert(decimal(8,4), ROUND(ex.RateValue5 * .01, 4))  AS RateValue5,
ed.UnionCode
from EmpDemo ED
 
left join dbo.EmpJobAssignment ej
     on ed.EmployeeNumber = ej.EmployeeNumber 
        and ej.currentflag = '1'
     
LEFT join #TempRepTable tr
     on ed.EmployeeNumber = tr.EmployeeNumber
     
inner join #TempEmpCredentials ec
        on ed.EmployeeNumber = ec.EmployeeNumber
     
LEFT join dbo.EmpExtraNumeric ex
     on ed.EmployeeNumber = ex.EmployeeNumber 
     AND ex.RateValue4 > 0
     
left JOIN CyPayCodeset pc
ON    ed.CheckLocation = pc.Value
AND pc.codeset = 'BARL' 

INNER JOIN EmpHire eh ON ed.EmployeeNumber = eh.EmployeeNumber and eh.CurrentFlag = 1
    and SUBSTRING(eh.ResultingStatusCode,1,1) not in ('1', '5', '9') 
  --   and SUBSTRING(eh.ResultingStatusCode,1,1) = '0' 
where ed.UnionCode in ('0A', '0D', '0E', '0K') and
RateValue4 is not NULL
order by ex.RateValue4 DESC, ex.RateValue5 


 
 

GO
the following is the log report: and results from goanywhere are attached.
1/25/17 3:27:32 PM INFO Start Date and Time: 1/25/17 3:27:32 PM
1/25/17 3:27:32 PM INFO Job Number: 1373818465599
1/25/17 3:27:32 PM INFO Project Name: /Finance/TEA Placement Staffing Detail WA Smart Export
1/25/17 3:27:32 PM INFO Submitted By: stimetb
1/25/17 3:27:32 PM INFO Submitted From: Administrator UI
1/25/17 3:27:32 PM INFO GoAnywhere 5.4.1 running on Windows Server 2012 R2 6.3 (amd64)
1/25/17 3:27:32 PM INFO Executing project 'TEA Placement Staffing Detail WA Smart Export'
1/25/17 3:27:32 PM INFO Project location: C:\Program Files\Linoma Software\GoAnywhere\userdata\projects\Finance\TEA Placement Staffing Detail WA Smart Export.xml
1/25/17 3:27:32 PM INFO Executing module 'Main'
1/25/17 3:27:32 PM INFO Executing task 'sql 1.0 (Connect to DB)'
1/25/17 3:27:32 PM INFO Executing sub-task 'query'
1/25/17 3:27:32 PM INFO Executing statement - exec spxLocal_TEAPlacementStaffingDetails
1/25/17 3:27:33 PM INFO Query completed successfully and 0 row(s) were affected
1/25/17 3:27:33 PM INFO Finished sub-task 'query'
1/25/17 3:27:33 PM INFO Finished task 'sql 1.0 (Connect to DB)'
1/25/17 3:27:33 PM INFO Executing task 'writeCSV 1.0 (Convert TEA Details to CSV)'
1/25/17 3:27:33 PM ERROR [9000 - Convert TEA Details to CSV] Invalid input. Input must be of type RowSet. Full stack trace written to '1373818465599_error_1.log'
1/25/17 3:27:33 PM INFO Finished project 'TEA Placement Staffing Detail WA Smart Export'
1/25/17 3:27:33 PM ERROR [9000 - Convert TEA Details to CSV] Invalid input. Input must be of type RowSet.
1/25/17 3:27:33 PM INFO End Date and Time: 1/25/17 3:27:33 PM
Attachments
goanywhere error info.docx
(137.9 KiB) Downloaded 294 times

stimetb

Posts: 4
Joined: Wed Jan 11, 2017 9:57 am

Post by stimetb » Thu Jan 26, 2017 8:50 am
its a series of sql commands saving temp tables to set up a table that i attached a couple of rows of results in the original post

exec spxLocal_TEAPlacementStaffingDetails

i brought the sql into the project for a minute to show the xml (wasnt sure how you wanted to see it)
Code: Select all
<project name="TEA Placement Staffing Detail WA Smart Export" mainModule="Main" version="2.0" logLevel="debug">
	<description>TEA Placement Staffing Detail</description>

	<module name="Main">

		<sql label="Connect to DB" resourceId="FinSql4 WASmart" version="1.0" logLevel="debug">
			<query label="Extract TEA Data" outputVariable="data">
				<statement>

select Value, Description 
into #CyGlobalPayCodeSetXHR2
from LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet 
     where 	 Codeset = &apos;XHR2&apos;

select Value, Description 
into #CyGlobalPayCodeSetHR59
       FROM LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet cs59
       where CODESET LIKE &apos;HR59&apos;

 
select Social, SchoolKey, Subject, Fte, SchoolCode, row_number() 
   over (partition by Social order by Fte desc) rownumber
into #TempRepAssign
from RepAssign
 
--delete from #TempRepAssign where rownumber <> 1
 
--select * from  #TempRepAssign
select Social, SchoolKey, Subject, Fte, SchoolCode, rownumber
into #TempRepAssign2
from #TempRepAssign
where rownumber = 1

CREATE TABLE #TempRepTable
(
 EmployeeNumber char(10) NULL,
 Subject       char(05) NULL,
    Fte           decimal(5,2) NULL,
    RepSchoolCode    char(10) NULL,
    RepSubjectAssign  char(50) NULL
);
insert into #TempRepTable (EmployeeNumber, Subject, Fte, 
            RepSchoolCode, RepSubjectAssign) 
select ra.Social, ra.Subject, ra.Fte, ra.SchoolCode, 
/*use subselect for this field to get into paycodeset*/
  (select Description from #CyGlobalPayCodeSetXHR2 cs02
   where ra.Subject = cs02.Value) RepSubjectAssign
--cs02.Description

from #TempRepAssign2 ra


  UPDATE #TempRepTable
  -- take out &apos;,&apos; from RepSubjectAssign
set RepSubjectAssign  =  replace(RepSubjectAssign, &apos;,&apos;, &apos;&apos;)
--  select * from #TempRepTable
---------------------------------------

CREATE TABLE #TempEndorsements
(
 CredentialIdent         int NULL,
 Endorsements            varchar(MAX) NULL
    
);
insert into #TempEndorsements (CredentialIdent, Endorsements)
select ec.CredentialIdent, edx.list

from EmpCredentials ec
--from #EmpCredentials ec
cross apply (
  select ee.Endorsement + &apos;,&apos; 
  from EmpEndorsements ee
  where ec.CredentialIdent = ee.CredentialIdent
  order by  DateIssued desc, REPOverride asc
  for XML path(&apos;&apos;) ) edx (list)
 

  UPDATE #TempEndorsements
  -- take out &apos;,&apos; from Endorsements
set Endorsements  =  replace(Endorsements, &apos;,&apos;, &apos;/&apos;)
  --select * from #TempEndorsements
  -------------------------------------- 

CREATE TABLE #EmpCredentials
( CredentialIdent         int not null,
 EmployeeNumber          char(10) NULL,
 CertificateNumber       char(15) NULL,
    CertificateTypeCode     char(4) NULL,
    RepOverride             char(1) NULL,
    DateIssued              smalldatetime NULL,
    DateExpired             smalldatetime NULL,
    Endorsements            varchar(MAX) NULL
  )
 insert into #EmpCredentials (CredentialIdent, EmployeeNumber,
   CertificateNumber, CertificateTypeCode, RepOverride,   
   DateIssued, DateExpired, Endorsements)
 
 select --*
  ec.CredentialIdent, ec.EmployeeNumber, ec.CertificateNumber, 
  ec.CertificateTypeCode, 
  ec.RepOverride, ec.DateIssued, ec.DateExpired, ee.Endorsements 
 
 from EmpCredentials ec
left join #TempEndorsements ee
 on ec.CredentialIdent = ee.CredentialIdent
  
 WHERE RepOverride IN (&apos;R&apos;, &apos;S&apos;)
 
-------------------------------------------above----- certificate retrieval

CREATE TABLE #TempEmpCredentials
(
 
 EmployeeNumber          char(10) NULL,
 CertificateNumber       char(15) NULL,
    CertificateTypeCode     char(4) NULL,
    RepOverride             char(1) NULL,
    DateIssued              smalldatetime NULL,
    DateExpired             smalldatetime NULL,
    CertificateTypeDesc     char(50) NULL,
    Endorsements            varchar(MAX) NULL
 
); 
insert into #TempEmpCredentials (EmployeeNumber, CertificateNumber, 
            CertificateTypeCode, RepOverride, DateIssued,
            DateExpired, CertificateTypeDesc, Endorsements ) 
SELECT ec.EmployeeNumber, ec.CertificateNumber, 
       ec.CertificateTypeCode, ec.RepOverride, ec.DateIssued, 
       ec.DateExpired, 
	   /*use subselect for this field to get into paycodeset*/
  (select Description from #CyGlobalPayCodeSetHR59 cs59
   where ec.CertificateTypeCode = cs59.Value) RepSubjectAssign,
	   --cs59.Description, 
       ec.Endorsements
       from #EmpCredentials ec
	   --left join #CyGlobalPayCodeSetHR59 cs59
      -- ON ec.CertificateTypeCode = cs59.Value 
    
  UPDATE #TempEmpCredentials
  -- take out &apos;,&apos; from CertificateTypeDesc
set CertificateTypeDesc  =  replace(CertificateTypeDesc, &apos;,&apos;, &apos;&apos;)
 -- select * from #TempEmpCredentials
------------------------------------------------------

select ed.EmployeeNumber, ed.Lastname, ed.FirstName, 
pc.Description as CheckLocDesc, 
ej.JobTitle,
tr.RepSubjectAssign,
ec.CertificateTypeDesc,
 ec.Endorsements,
 convert(decimal(8,4), ROUND(ex.RateValue4 * .01, 4))  AS RateValue4,
 convert(decimal(8,4), ROUND(ex.RateValue5 * .01, 4))  AS RateValue5,
ed.UnionCode
from EmpDemo ED
 
left join dbo.EmpJobAssignment ej
     on ed.EmployeeNumber = ej.EmployeeNumber 
        and ej.currentflag = &apos;1&apos;
     
LEFT join #TempRepTable tr
     on ed.EmployeeNumber = tr.EmployeeNumber
     
inner join #TempEmpCredentials ec
        on ed.EmployeeNumber = ec.EmployeeNumber
     
LEFT join dbo.EmpExtraNumeric ex
     on ed.EmployeeNumber = ex.EmployeeNumber 
     AND ex.RateValue4 > 0
     
left JOIN CyPayCodeset pc
ON    ed.CheckLocation = pc.Value
AND pc.codeset = &apos;BARL&apos; 

INNER JOIN EmpHire eh ON ed.EmployeeNumber = eh.EmployeeNumber and eh.CurrentFlag = 1
    and SUBSTRING(eh.ResultingStatusCode,1,1) not in (&apos;1&apos;, &apos;5&apos;, &apos;9&apos;) 
  --   and SUBSTRING(eh.ResultingStatusCode,1,1) = &apos;0&apos; 
where ed.UnionCode in (&apos;0A&apos;, &apos;0D&apos;, &apos;0E&apos;, &apos;0K&apos;) and
RateValue4 is not NULL
order by ex.RateValue4 DESC, ex.RateValue5 
</statement>
			</query>
		</sql>


		<writeCSV label="Convert TEA Details to CSV" inputRowSetVariable="${data}" outputFile="resource:smb://webapps1/wa/WATEAdata${CurrentDate()}.csv" whenFileExists="overwrite" fieldDelimiter="tab" includeHeadings="true" version="1.0">
			<data trim="right" dateFormat="MM/dd/yyyy" timeFormat="HH:mm:ss" timestampFormat="MM/dd/yyyy HH:mm:ss.SSS" />
		</writeCSV>


		<sendEmail label="Send reminder report is ready" resourceId="SMTP server" toList="stimetb@resa.net" version="2.0">
			<from address="stimetb@resa.net" />
			<subject>
				<![CDATA[TEA Placement Staffing reports]]>
			</subject>
			<message>
				<![CDATA[Your TEA file(s) has been uploaded to RESA's secure file. 
]]>
			</message>
		</sendEmail>

	</module>

</project>
Attachments
goanywhere error info.docx
(137.9 KiB) Downloaded 287 times

Support_Rick

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

Post by Support_Rick » Thu Jan 26, 2017 5:53 pm
Try writing your results out to a Temp Table and create a 2nd Query in the SQL Task you are executing the Stored Procedure in to select the results from the Temp Table.

You'll get better processing from the Stored Procedure, then generating the Temp output table for GAMFT to read.

Let us know how this works for you.. or if you have any additional questions.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

stimetb

Posts: 4
Joined: Wed Jan 11, 2017 9:57 am

Post by stimetb » Wed Feb 01, 2017 9:15 am
Rick - thanks for your help. I was using an execute statement inside of goanywhere. My first issue was i didnt have authority to update on the fly sp's in production databases, so after I read your note, I set up a test database I was able to update on the fly and use goanywhere.

What i did find out is the following statement (subselect), I couldnt get to work in goanywhere (but was able to get it to work in management studio)
Code: Select all
CREATE TABLE #TempRepTable
(
 EmployeeNumber char(10) NULL,
 Subject       char(05) NULL,
    Fte           decimal(5,2) NULL,
    RepSchoolCode    char(10) NULL,
    RepSubjectAssign  char(50) NULL
);
insert into #TempRepTable (EmployeeNumber, Subject, Fte, 
            RepSchoolCode, RepSubjectAssign) 
select ra.Social, ra.Subject, ra.Fte, ra.SchoolCode, 
/*use subselect for this field to get into paycodeset*/
  (select Description from #CyGlobalPayCodeSetXHR2 cs02
   where ra.Subject = cs02.Value) 
from #TempRepAssign ra
I also tried a left join over a subselect and that didn’t work either so I broke it up to the following:
Code: Select all
CREATE TABLE #TempRepTable
(
 EmployeeNumber char(10) NULL,
 Subject       char(05) NULL,
    Fte           decimal(5,2) NULL,
    RepSchoolCode    char(10) NULL,
    RepSubjectAssign  char(50) NULL
);
insert into #TempRepTable (EmployeeNumber, Subject, Fte, 
            RepSchoolCode
                		) 
select ra.Social, ra.Subject, ra.Fte, ra.SchoolCode
from #TempRepAssign2 ra
--goanywhere was NOT handling this on a left join or subselect
-- with above so it was separated into a single update.
Code: Select all
UPDATE #TempRepTable
  set RepSubjectAssign  = 
  (select Description from #CyGlobalPayCodeSetXHR2
    where #TempRepTable.Subject = #CyGlobalPayCodeSetXHR2.Value)
6 posts Page 1 of 1