Not applicable

SQL Server Service SmartObject only exposing Execute

I've written a stored procedure that creates a temp table, loads the temp table from another stored procedure and then performs SUM on the data in the temp table. It then sets a variable, drops the temp table, and returns the variable to the caller.

 

When I run the stored procedure I'm getting a number in SSMS. When I SmartObjectize the stored procedure the only method it's coming up with is Execute. I am not getting List or Read.

 

Any ideas how to trouble shoot this?

0 Kudos
Reply
5 Replies
ScottPK2
Apprentice

Re: SQL Server Service SmartObject only exposing Execute

The broker will only ever expose a list or execute method.

 

List is exposed when a return value is discovered and execute if no return is found.

 

The behavior you describe tells me that k2 cannot discover the return value. Try adding a select statement to the end of your stored procedure to return your variable such as SELECT yourvarable AS somecolumnname. Should clear it up for you.

0 Kudos
Reply
Prajwal_Shambhu
Apprentice

Re: SQL Server Service SmartObject only exposing Execute

Hi,

 

 

This reminds me one of the limitation of the SQL server broker. Please note K2 SQL server broker as follwing limiations;

 SQL borker doesn't support;

  • Dynamic SQL
  • Cursors
  • Temparory tables.

 

As a work around i would suggest you to tryout using table varialble which is supported by the SQL broker.!

 

Hope it helps!

 

Cheers,

Prajwal Shambhu

0 Kudos
Reply
ScottPK2
Apprentice

Re: SQL Server Service SmartObject only exposing Execute

Prajwal is correct which is why you have to define what essentially amounts to an implicit cursor to return your variable so that K2 will be able to discover it. I have implemented this approach in both SQL and ORACLE and works well.

0 Kudos
Reply
Asfand
Novice

Re: SQL Server Service SmartObject only exposing Execute

table varialble does not work too
0 Kudos
Reply
Latte
Apprentice

Re: SQL Server Service SmartObject only exposing Execute

Hi, 

 

I've used a table variable with K2 and it works very well. Temp tables though do not work.

 

So instead of a Create #temptable ([id] bigint, text NVARCHAR(200)), you can do a Declare @ParameterTable ([id] bigint, text NVARCHAR(200)).

 

0 Kudos
Reply