cancel
Showing results for 
Search instead for 
Did you mean: 

Fixed Query - Not returning selected statement with exec SP

swaroop_anasane
Active Contributor
0 Kudos

Dear All,

Something strange happening with my query template, not sure why. I guess i am missing something here.

Problem statement:

SP with following logic:

          Begin

               Set @a = (Select.....);

               Insert statement;

               Select B from xyz;

          End

In SQL QT with fixed mode:

          Exec SP

I am not able to see result of Select B from.....query, it just gives me a blank Rowsets node. Any pointers??

Thanks in advance.

Best Regards,

Swaroop

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Swaroop,

are you getting the column definitions in the rowset?

If yes then it means your select query is returning blank resultset.

Regards,

Rohit Negi.

swaroop_anasane
Active Contributor
0 Kudos

Hi Rohit,

Nope, I am not getting the defs. As mentioned, it's returning a blank Rowsets node.

Thanks,

Swaroop

swaroop_anasane
Active Contributor
0 Kudos

Hi All,

Got the solution. 

Use "Set NOCOUNT on;" in the procedure and set it back to off after begin and before last select.

Thanks for replying.

Regards,

Swaroop

swaroop_anasane
Active Contributor
0 Kudos

Hi Rohit,

Just wanted to check if my understanding is correct here.

While we have an insert statement followed by a select one, sql returns

================

"" no of rows affected

================

B

1

--------------------------------

So, it shows the result in SQL but not in MII test query.

I think while creating a Rowsets/Rowset/Row format, it is unable to fit in "No. of rows affected" and hence as the output is not properly formed in mii's understandable format, it gives a blank Rowsets node.

Please let me know if the understanding is correct.

Thanks in advance.

Best Regards,

Swaroop

Answers (0)