• Index
  •  » Talend Open Studio for Data Integration » Usage, Operation
  •  » problems getting output from MSSql stored procedure (pass parameters)

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

strpdsnk
Member
Registered: 2008-03-25
Posts: 328

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)

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)

Offline

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

strpdsnk
Member
Registered: 2008-03-25
Posts: 328

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:

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)

Offline

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

bthomson
Member
Registered: 2010-10-18
Posts: 10

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

  • Index
  •  » Talend Open Studio for Data Integration » Usage, Operation
  •  » problems getting output from MSSql stored procedure (pass parameters)

Board footer

Powered by FluxBB