One Star

problems getting output from MSSql stored procedure (pass parameters)

I am trying to execute a MSSql SP from Talend, that needs a parameter declared, but I am having some trouble with it.
What I want to do is send the, needed, date parameter with the SP to SQL and then return the results of that SP to Talend

This is what the command looks like that I run in MS SQL Server Management Studio (see pic #1)
EXECUTE .. '08-22-08'

tMap --> tMSSqlSP
-I get no output from tMSSqlSP
-see pics 2 & 3
-output:
Starting job AnalysisInstrumentList at 17:00 25/08/2008.
|
Job AnalysisInstrumentList ended at 17:00 25/08/2008.

tRowGenerator --> tMSSqlSP --> OUT
-see pics 4 & 6
-output:
Starting job AnalysisInstrumentList at 18:05 25/08/2008.
Exception in component tMSSqlSP_1
java.sql.SQLException: Procedure or function spAnalyticDataExtractForODY has too many arguments specified.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:478)
at batches_and_scripts.analysisinstrumentlist_0_1.AnalysisInstrumentList.tRowGenerator_1Process(AnalysisInstrumentList.java:567)
at batches_and_scripts.analysisinstrumentlist_0_1.AnalysisInstrumentList.runJobInTOS(AnalysisInstrumentList.java:3268)
at batches_and_scripts.analysisinstrumentlist_0_1.AnalysisInstrumentList.main(AnalysisInstrumentList.java:3176)
Job AnalysisInstrumentList ended at 18:05 25/08/2008.

tRowGenerator --> tMSSqlSP --> RECORD SET
-I get no output from tMSSqlSP
-see pics 5 & 6
-output:
Starting job AnalysisInstrumentList at 18:03 25/08/2008.
08-25-08||
Job AnalysisInstrumentList ended at 18:03 25/08/2008.

Related to topics:
2706
1579
2 REPLIES
One Star

Re: problems getting output from MSSql stored procedure (pass parameters)

I figured out how to do it with another component, BUT I'd still like to know how to use the tMSSqlSP component to get the same results

tMSSqlInput
-this worked, returning the data, row by row
-query statement:
"EXECUTE .. '" + TalendDate.getDate("MM-DD-YY") + "'"

-output:
Starting job AnalysisInstrumentList at 18:22 25/08/2008.
connecting to socket on port 3926
connected
SET DATEFORMAT MM-DD-YYYY
SET DATAFORMAT DELIMITED
SET SEPARATOR ;
SET THOUSAND ,
SET AUTOMATIC instrument ON
SET CODIFICATION instrument RESEARCH_TICKER

REM DMA Analytic Data Native Import File

CMD UPDINS instr_chrono
ATT instr nature_e third_party sub_nat_type validity_d value_n currency

disconnected
Job AnalysisInstrumentList ended at 18:22 25/08/2008.
One Star

Re: problems getting output from MSSql stored procedure (pass parameters)

strpdsnk, I see this is an old post, but your image 4 has your full connection details in the screenshot.