The EXEC SQL construct or statement is not supported

• tt-615680

  SSCertifiable

  Points: 7835

  More actions

  March 6, 2012 at 6:18 am

  #265540

  Hi All,

  I'm trying to use one procedure for two different Datasets, by passing a parameters (one of the parameters is an id and the second one is used to switch between the two Datasets).

  But when I try to run the stored procedure in the Dataset properties eg. EXEC procedureName @ID, 1, I get the following error "The EXEC SQL construct or statement is not supported.". Would anyone let me know how to resolve this issue please?

  Thank you!

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  March 6, 2012 at 7:44 am

  #1455313

  Rather than use a text based query with EXEC spname, just set the dataset to use a stored procedure directly. There is an option in the dataset dialog box (not the query builder) that allows you to use a stored procedure.

• tt-615680

  SSCertifiable

  Points: 7835

  More actions

  March 6, 2012 at 8:11 am

  #1455345

  Daniel Bowlin (3/6/2012)

  ---

  Rather than use a text based query with EXEC spname, just set the dataset to use a stored procedure directly. There is an option in the dataset dialog box (not the query builder) that allows you to use a stored procedure.

  Thanks for your reply!

  The reson why I chose to use the text based query is because I need the two options to be switched, so I've put a default Parameter for each Dataset, for example; for Dataset1 it is: exec spname @id 1(which is a defult value, if the user chooses Dataset1) and it would be the same for Dataset2 eg. exec spname @id 2 and it will populate all the columns for Dataset2. If thats any clear?

  Thank you!

• ansuman2006

  SSC Eights!

  Points: 987

  More actions

  March 10, 2012 at 1:48 pm

  #1457522

  In the text builder itself write the sp name only without using exec statement and put question mark "?" for the parameters.

  stored procedure ?,?,?,?... (no of parameters = no of inputs for the sp)

  then go to the parameters option in the dataset properties itself and set the parameters against the "?" in the same order.

  Hope this helps.