Skip to main content
  • EN

Is there a way to view the Message from SQL/SSMS after running a SQLExecutor on a stored procedure?

Context:

We have stored procedures written for importing and validating records. The validating stored procedure (which runs other individual stored procedures) returns a log/report of any issues with more of a textual format than a table.

When attempting to expose the output message from SSMS in the SQLExecutor, the only option you have is to expose specific attributes. This doesn't work with the log format mentioned previously.

I've reviewed other forums related to this topic where others have had to go outside of the norm to accommodate this, but I think this should be addressed within FME to relay messaging from SQLExecutor from the

Related Forums:

https://knowledge.safe.com/questions/82948/sql-executor-run-a-procedure.html

 

Here is the type of message displayed in SSMS from the validation stored procedure.

Hello @kaylin.austin

 

Are you able to share your Stored Procedure? As I'm curious how exactly you are returning the 'log report' if you will. Sometimes, you do have to select the value that was returned. I've outlined an example below:

Declare @LogReport VARCHAR(1024)
EXEC <Stored Procedure>, @LogReport OUTPUT
Select 'column1'

The attribute you would then expose on the output of the SQLExecutor would be 'column1' this is due to not inserting the variable into anything, so it is just the default value.

Are you able to try the above?

Hello @kaylin.austin

 

Are you able to share your Stored Procedure? As I'm curious how exactly you are returning the 'log report' if you will. Sometimes, you do have to select the value that was returned. I've outlined an example below:

Declare @LogReport VARCHAR(1024)
EXEC <Stored Procedure>, @LogReport OUTPUT
Select 'column1'

The attribute you would then expose on the output of the SQLExecutor would be 'column1' this is due to not inserting the variable into anything, so it is just the default value.

Are you able to try the above?

Hello @trentatsafe

I'm fine with sharing stored procedures in a chat or more secure/one-on-one platform, but not in a public forum.

I've attempted to use your method in two ways:

1. The stored procedure requires parameters to be passed. So I used:

Declare @LogReport VARCHAR(1024)
EXEC  sp_PreValidate_All] @ImportName='$(CLSTEST_ImportName)', @databasename='$(CLSTEST_DBCreate)', @LogReport OUTPUT
Select 'column1'

2. The stored procedure without passing parameters:

Declare @LogReport VARCHAR(1024)
EXEC msp_PreValidate_All], @LogReport OUTPUT
Select 'column1'

Both returned errors of possible malformed statement. In #1 I also changed ", @LogReport OUTPUT" to "AS @LogReport OUPUT" with no success.

I've just downloaded FME 2020.1 and receiving an error in the beginning of my workflow though so I'm going to need to review and attempt to resolve it. I haven't had an error in previous versions. The topic at hand is at the very end of this particular workflow.

Hello @kaylin.austin

 

Are you able to share your Stored Procedure? As I'm curious how exactly you are returning the 'log report' if you will. Sometimes, you do have to select the value that was returned. I've outlined an example below:

Declare @LogReport VARCHAR(1024)
EXEC <Stored Procedure>, @LogReport OUTPUT
Select 'column1'

The attribute you would then expose on the output of the SQLExecutor would be 'column1' this is due to not inserting the variable into anything, so it is just the default value.

Are you able to try the above?

Ok, rerunning with 2019.2, I do not have any errors leading up to the SQLExecutor.  I'm not the author of the stored procedure, but I've been informed that it's a concatenated string.  I've tried many different variations of what you've provided me with no success. 

Hello @trentatsafe

I'm fine with sharing stored procedures in a chat or more secure/one-on-one platform, but not in a public forum.

I've attempted to use your method in two ways:

1. The stored procedure requires parameters to be passed. So I used:

Declare @LogReport VARCHAR(1024)
EXEC rsp_PreValidate_All] @ImportName='$(CLSTEST_ImportName)', @databasename='$(CLSTEST_DBCreate)', @LogReport OUTPUT
Select 'column1'

2. The stored procedure without passing parameters:

Declare @LogReport VARCHAR(1024)
EXEC rsp_PreValidate_All], @LogReport OUTPUT
Select 'column1'

Both returned errors of possible malformed statement. In #1 I also changed ", @LogReport OUTPUT" to "AS @LogReport OUPUT" with no success.

I've just downloaded FME 2020.1 and receiving an error in the beginning of my workflow though so I'm going to need to review and attempt to resolve it. I haven't had an error in previous versions. The topic at hand is at the very end of this particular workflow.

Hi @kaylin.austin

Do you mind submitting a case here then, that way we can take this offline? We can post back once we figure out best solution.

 

 

Case submission can be handled here: https://www.safe.com/support/report-a-problem/

Reply


Community Stats

30,186
Posts
114,572
Replies
37,850
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.