I have generated a class using the linked procedure wizard however I can't get it to work if the datatype of one of the parameters is VARCHAR(MAX). It works fine if I change it to say VARCHAR(500) and rerun the stored procedure wizard. I get the following error returned.: **ErrorMsg: SQLState: (07002) NativeError: \[0] Message: [Microsoft\]\[ODBC SQL Server Driver\]COUNT field incorrect or syntax error** Can anyone see what I am doing wrong? I think it is to do with the way I am using the %Stream.GlobalCharacter? Any help is much appreciated. Class generated by linked procedure wizard:
Include %occInclude /// Class dbo.spTIETest [ Not ProcedureBlock ] { ClassMethod spTIETest(ObjectName As %String(MAXLEN=200), NamespaceName As %String(MAXLEN=50), CacheVersion As %String(MAXLEN=500), RawData As %Stream.GlobalCharacter) As %Integer [ ProcedureBlock = 1, ReturnResultsets, SqlName = spTIETest, SqlProc ] { if '$isobject($g(%sqlcontext)) s %sqlcontext=##class(%Library.ProcedureContext).%New() s RETURNVALUE=$g(RETURNVALUE) s ObjectName=$g(ObjectName) s NamespaceName=$g(NamespaceName) s CacheVersion=$g(CacheVersion) s RawData=$g(RawData) s hdbc=$$GetConnection^%apiGTW("TestTIEUserProd") if hdbc="" { set %msg=$$$ERROR($$$GTWCConnectionError),%msg=$$GetErrorText^%apiOBJ(%msg) goto setError } s QHandle=$zf(-5,%SQLGateway,5,hdbc) if QHandle="" { set %msg=$$$ERROR($$$GTWCAllocationError),%msg=$$GetErrorText^%apiOBJ(%msg) goto setError } s err=$zf(-5,%SQLGateway,$s(%SQLGateway("-uv"):37,1:3),QHandle,"{?=call dbo.""spTIE_InsertTIE_OBJECT_EXPORT""(?,?,?,?)}") i err g getError s err=$zf(-5,%SQLGateway,62,QHandle,1,4,1,4,10,0,10) s err=$zf(-5,%SQLGateway,62,QHandle,2,1,1,12,200,0,200) s err=$zf(-5,%SQLGateway,9,QHandle,$lb(ObjectName),2) i err g getError s err=$zf(-5,%SQLGateway,62,QHandle,3,1,1,12,50,0,50) s err=$zf(-5,%SQLGateway,9,QHandle,$lb(NamespaceName),3) i err g getError s err=$zf(-5,%SQLGateway,62,QHandle,4,1,1,12,500,0,500) s err=$zf(-5,%SQLGateway,9,QHandle,$lb(CacheVersion),4) i err g getError s err=$zf(-5,%SQLGateway,62,QHandle,5,1,$s(%SQLGateway("-uv")&&$zbitget($zversion(0),1):-8,1:1),12,0,0,-2) s err=$zf(-5,%SQLGateway,4,QHandle) i err=99 s lerr=$zf(-5,%SQLGateway,55,QHandle) s err=$li(lerr) i err=99 s err=$$setStream^%apiGTW(QHandle,RawData,0,1) i err g getError s %ROWCOUNT=$zf(-5,%SQLGateway,31,QHandle) set tConnection = ##class(%Library.SQLGatewayConnection).%New() set tConnection.DLLHandle = %SQLGateway, tConnection.ConnectionHandle = hdbc set rrs = ##class(%SQL.Gateway.ODBCResultSet).%New(tConnection,QHandle) while $Isobject(rrs) { if rrs.%GetMetadata().columnCount do %sqlcontext.AddResultSet(##class(%ResultSet.Static).%New(rrs)) set rrs = tConnection.getNextResultSet(QHandle) } s RETURNVALUE=$li($zf(-5,%SQLGateway,22,QHandle,1)) setError ; If ($g(%sqlcontext)'=$$$NULLOREF) { Set %sqlcontext.%SQLCODE = $g(err) Set %sqlcontext.%Message = $g(%msg) Set %sqlcontext.%ROWCOUNT = $g(%ROWCOUNT) w "ErrorMsg: "_%sqlcontext.%Message,! } s err=$zf(-5,%SQLGateway,16,QHandle) QUIT $g(RETURNVALUE) getError ; set %msg=$li($zf(-5,%SQLGateway,12,hdbc,QHandle)) goto setError } }Code I am using to call the method from terminal:
Set OBJECT = "UHSM.LPI.LinkedTable.LPIGPPractice" Set NAMESPACE = "WYTTEST" Set CACHEVERSION = "Cache for Windows (x86-64) 2017.2.1 (Build 801U) Wed Dec 6 2017 09:07:51 EST" Set RAWDATA = ##class(%Stream.GlobalCharacter).%New() Do RAWDATA.Write("RawData") Set sc = ##class(dbo.spTIETest)."spTIETest"(OBJECT,NAMESPACE,CACHEVERSION,.RAWDATA)Thanks, Graham