Exec Stored Procedure Error
Article ID: 74984
Updated On:
Products
CA Test Data Manager (Data Finder / Grid Tools)
Issue/Introduction
In attempting to use DataPainter function 'execsqlproc' I am receiving a DB error. I am not sure if this is a simple syntax error on my side or a limitation between SQLServer and Datamaker in the need to declare at time of execution. If running directly in DBViz, the call is:
declare @out_seq int exec GetTitleRow 'P' ,@out_seq output select @out_seq
In Datamaker I am passing in:
@execsqlproc(PTDM_Internal,GetTitleRow,Silo,IN,'P',out_seq,OUT,,out_seq)@
and I'm receiving this error:
Profile: TDM_Internal Title: MF SQL Conn#16 - TDM_Internal Database Warning Message: SQLSTATE = 37000 [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot use the OUTPUT option when passing a constant to a stored procedure.
Transaction Error Code: -1 Database Error Code: 179
CA Test Data Manager - Datamaker Details: GUI Version: 4.4.0.37 (2/2/2018 09:55:56) REP Version: 3.2K
Transaction Details:
PB DBMS driver: odbc
ODBC Driver: sql server native client 10.0
ODBC DBMS Name: sql server
Server DBMS: SQL Server (sql server)
Server Version: 10.50.2500
Current System: PROWINSQLC01
Current Database: TDM_Internal
Database: User ID: gtrep_tdod
DBParm: PBNewSPInvocation='Yes'
AutoCommit: false
ii_identifier_case = 3
ib_current_schema_only = false
ib_db2 = false
ib_db2_400 = false
ib_extra_fkeys = false
ib_fkey_capable = true
ib_new_fkeys = false
ib_pkey_capable = true
ib_single_schema = false
ib_unicode = true
ib_user_views = false
Error message from Datamaker is 'Error in data - Failed calling EXECUTE DYNAMIC my_procedure.. transaction returned an error-1' The Stored Proc is: create procedure GetTitleRow @Silo varchar(1), @out_seq int OUTPUT AS begin Select top 1 @out_seq = seq from dbo.TitleVaultvins where Silo = @Silo and Vin_Used = 'N' order by Seq end begin Update dbo.TitleVaultVins set Vin_Used = 'Y' where Silo = @Silo and SEQ = @out_seq end return @out_seq
declare @out_seq int exec GetTitleRow 'P' ,@out_seq output select @out_seq
In Datamaker I am passing in:
@execsqlproc(PTDM_Internal,GetTitleRow,Silo,IN,'P',out_seq,OUT,,out_seq)@
and I'm receiving this error:
Profile: TDM_Internal Title: MF SQL Conn#16 - TDM_Internal Database Warning Message: SQLSTATE = 37000 [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot use the OUTPUT option when passing a constant to a stored procedure.
Transaction Error Code: -1 Database Error Code: 179
CA Test Data Manager - Datamaker Details: GUI Version: 4.4.0.37 (2/2/2018 09:55:56) REP Version: 3.2K
Transaction Details:
PB DBMS driver: odbc
ODBC Driver: sql server native client 10.0
ODBC DBMS Name: sql server
Server DBMS: SQL Server (sql server)
Server Version: 10.50.2500
Current System: PROWINSQLC01
Current Database: TDM_Internal
Database: User ID: gtrep_tdod
DBParm: PBNewSPInvocation='Yes'
AutoCommit: false
ii_identifier_case = 3
ib_current_schema_only = false
ib_db2 = false
ib_db2_400 = false
ib_extra_fkeys = false
ib_fkey_capable = true
ib_new_fkeys = false
ib_pkey_capable = true
ib_single_schema = false
ib_unicode = true
ib_user_views = false
Error message from Datamaker is 'Error in data - Failed calling EXECUTE DYNAMIC my_procedure.. transaction returned an error-1' The Stored Proc is: create procedure GetTitleRow @Silo varchar(1), @out_seq int OUTPUT AS begin Select top 1 @out_seq = seq from dbo.TitleVaultvins where Silo = @Silo and Vin_Used = 'N' order by Seq end begin Update dbo.TitleVaultVins set Vin_Used = 'Y' where Silo = @Silo and SEQ = @out_seq end return @out_seq
Cause
syntax errors.
Environment
TDM 4.4
MS SQL server
MS SQL server
Resolution
There are two things wrong with the calling of the stored procedure: The stored procedure is missing the schema name. For example:
@execsqlproc(Pordersdsmless,dbo.GetTitleRow,Silo,IN,'5',out_seq,OUT,,out_seq)@
Please use double quotes. For example:
@execsqlproc(Pordersdsmless,dbo.GetTitleRow,Silo,IN,"5",out_seq,OUT,,out_seq)@
@execsqlproc(Pordersdsmless,dbo.GetTitleRow,Silo,IN,'5',out_seq,OUT,,out_seq)@
Please use double quotes. For example:
@execsqlproc(Pordersdsmless,dbo.GetTitleRow,Silo,IN,"5",out_seq,OUT,,out_seq)@
Additional Information
Video URL on how to open a Support Case - https://communities.ca.com/videos/5898-demo-how-to-open-a-support-case
You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.
To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html.
You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.
To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html.
Feedback
Yes
No
Powered by
