locked
help needed with execute SQL Task and user defined function RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a execute sql task that calls a function, the function requires a paramater and returns a int

    i have tried setting up the sql task as follows

    Connection type ADO.Net

    SQLStatement  SELECT dbo.CheckIsNumeric(?)

    Resultset Singlerow

    Result Set parameter mapped to variable, Int32

    input parameter set to variable , direction Input, datatype string,

     

    I keep getting the following error

    [Execute SQL Task] Error: Executing the query "SELECT dbo.CheckIsNumeric(?) " failed with the following error: "Incorrect syntax near '?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    If I change the SQL Statement to SELECT dbo.CheckIsNumeric(123456)  it runs fine

     

     

     

     

     

    Dont ask me .. i dont know
    Thursday, December 22, 2011 3:21 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Can you modify the syntax to include

    AS result so it would look like:

    SELECT dbo.CheckIsNumeric(?) AS Result


    See if this thread is of help:

    http://social.msdn.microsoft.com/Forums/en-AU/sqlintegrationservices/thread/c094f008-c104-4adf-8697-b9ed5cdd0527

    Arthur My Blog
    • Marked as answer by Eileen Zhao Wednesday, December 28, 2011 6:34 AM
    Thursday, December 22, 2011 3:40 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Can you modify the syntax to include

    AS result so it would look like:

    SELECT dbo.CheckIsNumeric(?) AS Result


    See if this thread is of help:

    http://social.msdn.microsoft.com/Forums/en-AU/sqlintegrationservices/thread/c094f008-c104-4adf-8697-b9ed5cdd0527

    Arthur My Blog
    • Marked as answer by Eileen Zhao Wednesday, December 28, 2011 6:34 AM
    Thursday, December 22, 2011 3:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    follow Arthur's suggestion,

    also in Result Set tab, set result name to: Result

    http://www.rad.pasfu.com
    Friday, December 23, 2011 6:26 AM