Talend community forum / problems getting output from MSSql stored procedure (pass parameters)

strpdsnk · 2008-08-26 00:15:23

Tags: [database, java, mssql, tis2.4.1, tmap, tMSSqlInput, trowgenerator]

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)

Code:

EXECUTE [DMA].[dbo].[spAnalyticDataExtractForODY] '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.
[tLogRow_1] |
Job AnalysisInstrumentList ended at 17:00 25/08/2008. [exit code=0]

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. [exit code=1]

tRowGenerator --> tMSSqlSP --> RECORD SET
-I get no output from tMSSqlSP
-see pics 5 & 6
-output:

Starting job AnalysisInstrumentList at 18:03 25/08/2008.
[tLogRow_1] 08-25-08||
Job AnalysisInstrumentList ended at 18:03 25/08/2008. [exit code=0]

Related to topics:
[Forum, topic 2706] Some information about SP component
[Forum, topic 1579] Using Stored Procedures for Parameterized Queries

Uploaded Images

Last edited by strpdsnk (2010-11-30 17:01:47)

strpdsnk · 2008-08-26 00:27:42

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:

Code:

"EXECUTE [DMA].[dbo].[spAnalyticDataExtractForODY] '" + TalendDate.getDate("MM-DD-YY") + "'"

-output:

Starting job AnalysisInstrumentList at 18:22 25/08/2008.
[statistics] connecting to socket on port 3926
[statistics] connected
[tLogRow_2] SET DATEFORMAT MM-DD-YYYY
[tLogRow_2] SET DATAFORMAT DELIMITED
[tLogRow_2] SET SEPARATOR ;
[tLogRow_2] SET THOUSAND ,
[tLogRow_2] SET AUTOMATIC instrument ON
[tLogRow_2] SET CODIFICATION instrument RESEARCH_TICKER
[tLogRow_2]
[tLogRow_2] REM DMA Analytic Data Native Import File
[tLogRow_2]
[tLogRow_2] CMD UPDINS instr_chrono
[tLogRow_2] ATT instr nature_e third_party sub_nat_type validity_d value_n currency

[statistics] disconnected
Job AnalysisInstrumentList ended at 18:22 25/08/2008. [exit code=0]

Uploaded Images

Last edited by strpdsnk (2008-08-26 00:28:41)

bthomson · 2010-11-30 15:41:51

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

Announcement

Unanswered posts

#1 2008-08-26 00:15:23

problems getting output from MSSql stored procedure (pass parameters)

Code:

#2 2008-08-26 00:27:42

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

Code:

#3 2010-11-30 15:41:51

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

Board footer