Solved

Execute Stored Procedure - Can't Finish creating Smart Object

  • 31 January 2017
  • 2 replies
  • 8 views

Badge +8

Hi there. 

 

I'm brand new to writing stored procedures, so please forgive if I appear ignorant. 

 

I built a workflow process to add items to my database using a create reference and get next to have it compare for duplicates and then add non-duplicate items.  Althought his works well, it's kind of slow.

 

So, I did more research and learned that the SQL command "merge" will do what I want at a data level much much much much quicker. 

 

So, I am attempting to write a stored procedure that I can use in my workflow to accomplish this.  What I wrote I have pasted below. 

 

I am now trying to create a smart object to execute the stored procedure, but it doesn't create any properties and when I drag the execute method over to the methods tab, it won't let me map anything nor hit "next" or "finish" thus I can't complete the smart object. 

 

What I was thinking would happen is that it would just allow me to execute the procedure via the smart object...that's all I want it to do.  There are no variables or anything like that...everything is already stored in the tables for the data I want to manipulate. 

 

What am I missing?  I am not finding a lot of great info in the K2 documentation on this.

 

Rob

 

 

USE [ProductionControl]

GO

/****** Object: StoredProcedure [dbo].[MergeCREST] Script Date: 1/31/2017 7:19:46 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Nicholas, Robert>

-- Create date: <27-Jan-2017>

-- Description: <Merge ASO Items into PROD Database Table>

-- =============================================

ALTER PROCEDURE [dbo].[MergeCREST]

 

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

merge work_items wi

using loader_staging cs

on wi.reference = cs.reference

when not matched then

insert

(reference, type, group_id, sub_group_id, crest_proposed_eff_date, record_date_whenload, report_or_entry_date, target_date, unitid, work_source, current_status, ep_aso_current_stat_date

, created_by, created_date)

values

(cs.reference, cs.type, cs.[group], cs.subgroup, cs.[eff date], getdate(), date, getdate(), cs.unit, 25, 1, getdate(), 'Uploader System', getdate());

END

 

icon

Best answer by tin 31 January 2017, 23:02

View original

2 replies

Userlevel 5
Badge +18


 


1.  Do you see this method under the correspond Service Object with the 'Execute' type?


2.  Do you see the same behavior as above?  I do not believe properties are needed.


 


I believe this Smartobject can also be created via the SmartObject servicer tester tool via right clicking on the Service Object and selecting the Create SmartObject option.


Badge +8

Thank you for the info.  It turns out I was able to make it work by simply identifying a property on the smart object so I could save it.  Once I saved it, I was able to add it as an SO and use it in the workflow. 

 

It's working perfectly!

 

 

Reply