Set Private Data to SQL result

[kheslin]

Using Switch 2018.

I'd like to do a SQL query and store the result in Private Data.

This is my SQL statement

EXEC SW_GetDeadlineDate_r1
'[Job.PrivateData:Key="p1"]' ,
'[Job.PrivateData:Key="deadline"]'
____________________________________________________________________
Here is my SQL Stored Procedure

CREATE PROCEDURE [dbo].[SW_GetDeadlineDate_r1]
@PubID char(6), @Deadline int output
AS
BEGIN

Set @Deadline = (SELECT [Deadline]
FROM [dbo].[Publications] where [Publication_ID] = @PubID)

END

GO
____________________________________________________________________

The SQL works in SSMS, but the Private Data does not get updated in Switch.

Thanks

[Padawan]

How are you storing the result in private data?

Personally, I would create a database Switch variable to get the result of your SQL query and store it in private data via the private data property in a folder in Switch. If something goes wrong this way, then I would expect errors in the messages. Do you see something like that?

[gabrielp]

You likely have the Switch database module/configurators which I'm not familiar with but I figured I'd share this script which uses ODBC and has several ways to save results as private data. You can save a single result or a series result as private data. You also can save the result as a dataset https://github.com/open-automation/switch-sql-query