Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Using a Variable in a SQL Script

  1. #1
    Join Date
    Jul 2010
    Posts
    12

    Question Using a Variable in a SQL Script

    I really want to use a variable as a parameter to a stored procedure in a SQL Script object, but since I cannot get that to work, I am starting simple with just trying to get A variable to be used in an insert statement.

    So, in my SQL Script object I have this:

    Insert into common.dbo.JobHistory (JobName, StartDate) values (?, '1/1/2011')

    Down in the bottom left, Field name to be used as argument has one row called JobName.

    JobName is loaded in a GetVariables widget immediately prior to the Execute SQL Script.

    However, when I run the overall job, I get this:


    2011/09/30 14:56:30 - Job UserInfo - Starting entry [StageEMR Set Start]
    2011/09/30 14:56:30 - StageEMR Set Start - Loading transformation from repository [StageEMR Set Start] in directory [/EMRdm/Jim/Incremental]
    2011/09/30 14:56:30 - StageEMR Set Start - Dispatching started for transformation [StageEMR Set Start]
    2011/09/30 14:56:30 - StageEMR Set Start - This transformation can be replayed with replay date: 2011/09/30 14:56:30
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : An error occurred, processing will be stopped:
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : Couldn't execute SQL: Insert into common.dbo.JobHistory (JobName, StartDate) values (?, '1/1/2011')
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) :
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) :
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) :
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : Unexpected parameter marker at position 64.
    2011/09/30 14:56:30 - Execute SQL script.0 - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : Error initializing step [Execute SQL script]
    2011/09/30 14:56:30 - StageEMR Set Start - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : Step [Execute SQL script.0] failed to initialize!
    2011/09/30 14:56:30 - Execute SQL script.0 - Finished reading query, closing connection.
    2011/09/30 14:56:30 - StageEMR Set Start - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : Unable to prepare for execution of the transformation
    2011/09/30 14:56:30 - StageEMR Set Start - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2011/09/30 14:56:30 - StageEMR Set Start - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) : We failed to initialize at least one step. Execution can not begin!
    2011/09/30 14:56:30 - StageEMR Set Start - ERROR (version 4.1.2.1-GA, build 14760 from 2011-01-26 13.31.23 by buildguy) :

    I got similar errors when I was trying to call a stored procedure such as:
    exec dbo.sp_InsertJobRecord ?, '1/1/2011'

    Why is it telling me unexpected parameter?

  2. #2

    Default

    jcrash: are you sure you have checked "Variable Substitution" check box that is located just above parameters box (on Edit step screen of SQL Script step)?

    - Rakesh

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.