Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Execute Row SQL Script using MSSQL connection in Transformation

  1. #1
    Join Date
    Dec 2014
    Posts
    10

    Default Execute Row SQL Script using MSSQL connection in Transformation

    Hello all,

    I'm trying to run a particular set of scripts using an MSSQL connection. I have the "Read SQL from file" checked and I'm successfully inserting dynamic file paths into the "SQL field name" field.

    My issue comes in when one of the scripts actually runs. This particular script has two different statements (a drop statement and create statement). The error that I get when I try to run this is: "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch." Any advice?

    -Tim

  2. #2
    Join Date
    Dec 2014
    Posts
    10

    Default

    Also, the version I'm running is 5.2.0.

  3. #3
    Join Date
    Dec 2014
    Posts
    10

    Default

    Sorry, I'm new to the Kettle forum here. I should have also mentioned that there is a semi-colon after my drop statement. The two statements do work individually, as tested in Microsoft SQL Server 2008 R2. This script does work in a job using the SQL from file step. It's only in a transformation in the Execute Row SQL Script step that this does not work. The more I look at it, the more I'm thinking that this is a bug. Please advise if I should submit this as one.

  4. #4
    Join Date
    Dec 2014
    Posts
    10

    Default

    Can anyone else confirm/replicate the issue or offer any advice? I tried submitting a bug report in Jira, but it didn't get posted.
    Last edited by timstoffel; 01-08-2015 at 10:22 AM.

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Wouldn't you use GO to separate MSSQL batch statements?
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Dec 2014
    Posts
    10

    Default

    Thanks for the suggestion! I get a syntax error with the GO in. "Incorrect syntax near 'go'." I ran it in Management Studio to make sure the syntax was correct and it is. I think the GO separator is specific to Management Studio. I've never been able to put GO in statements being ran by Spoon. Other thoughts?

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You're right, GO is only understood by the MSSQL tools.

    What's the rationale behind your choice of using step "Execute Row SQL Script"?
    I would execute DDL statements on the job level, not in a transformation.

    BTW: "Execute Row SQL Script" isn't capable to split batches like "Execute SQL Script" is.
    The step expects a single statement if I read the sources right.
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Dec 2014
    Posts
    10

    Default

    The reason that I'm using it is because I'm passing several paths into it. I'm reading the paths from a table in a different transformation and then passing those paths into this transformation through the parameters. I cannot, to my knowledge, run every script that I have in the table without using transformation method, but I've also only been using this software for about 7 months or so. Is there a way that I just can't think of?

    To your next comment, I had wondered about that, but it's only an issue with running off of an MSSQL connection. The step can run multiple statements in the same file for a Postgresql connection. That's the part that baffles me. If it just is the way it is then I guess I'll just have to accept that.

  9. #9
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I see your reasoning, so don't worry about my mentioning of the job level for DDL statements.
    And welcome to database reality: Yes, it's definitely possible to find two SQL engines behaving differently.
    Perhaps you can separate the DROP statement from the CREATE statement?
    So long, and thanks for all the fish.

  10. #10
    Join Date
    Dec 2014
    Posts
    10

    Default

    Unfortunately, according to the requirements of the project, I cannot. Every other table / view / procedure / function file has the drop statement and the create statement in the same file. Separating these two statements in any way would break the consistency of our file system and would require an entire overhaul of the current system to make work.

  11. #11
    Join Date
    Dec 2014
    Posts
    10

    Default

    For the record, in the end my supervisor and I decided on handling each MSSQL file in the job by calling each specific one. It's not ideal, but we don't think we'll need to add anything to the job for a while. Perhaps later implementations will be able to support multiple statements in a single file using an MSSQL connection in the Execute Row SQL Script step in a transformation and we can move back to what the project was intended to be.

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.