Solved

Execute Stored Procedure - Can't Finish creating Smart Object

  • 31 January 2017
  • 2 replies
  • 3 views

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


 


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.


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