(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
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.
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 248 times
- Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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:
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( 'Temp.', currentDate('yyyyMMdd'), '.', system.job.id, '.XML' ) }" 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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
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?
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
Code: Select all
the following is the log report: and results from goanywhere are attached.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
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 242 times
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)
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 = '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
</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 235 times
- Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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.
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
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)
-- with above so it was separated into a single update.
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
I also tried a left join over a subselect and that didn’t work either so I broke it up to the following: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
Code: Select all
--goanywhere was NOT handling this on a left join or subselectCREATE 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-- 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
