You are not logged in.

#1 2008-08-25 23:15:23

strpdsnk
Member
328 posts

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 [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:
http://www.talendforge.org/forum/viewtopic.php?id=2706
http://www.talendforge.org/forum/viewtopic.php?id=1579

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

Offline

#2 2008-08-25 23:27:42

strpdsnk
Member
328 posts

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 [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]

Last edited by strpdsnk (2008-08-25 23:28:41)

Offline

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

bthomson
Member
10 posts

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.

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy