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
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
Resolution
There are two things wrong with the calling of the stored procedure: The stored procedure is missing the schema name. For example: