Help Getting Parameters Back From A Stored Procedure

Hello everyone,





I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.





--I've been getting the following error in visual basic 6 when i try to call this:


run-time error '-2147217900 (80040e14)': syntax access violation





--This is the error you get when you try to just run the code in query analyzer:


[Microsoft][ODBC SQL Server Driver]Syntax error or access violation





--what code i was trying to use (in query analyzer):


{call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a', @BulkJit = 'a', @ID = '0', @RQSodfilFlag = '1', @BulkRanFlag = '0', @BulkForFlag = '0', @BulkJitFlag = '0', @DeleteFlag = '0', @ErrorNum = '0')}





--code that i was trying to use in vb 6:


Public Sub TemporaryTables( _


ByVal bytRQSodfilFlag As Byte, _


ByVal bytBulkRanFlag As Byte, _


ByVal bytBulkForFlag As Byte, _


ByVal bytBulkJitFlag As Byte, _


ByVal bytDeleteFlag As Byte, _


ByVal cnPlant As String)





Dim objConn As ADODB.Connection


Dim objCmd As ADODB.Command


Dim objRQSodfil As Parameter


Dim objBulkRan As Parameter


Dim objBulkFor As Parameter


Dim objBulkJit As Parameter


Dim objParamID As Parameter


Dim objRQSodfilFlag As Parameter


Dim objBulkRanFlag As Parameter


Dim objBulkForFlag As Parameter


Dim objBulkJitFlag As Parameter


Dim objDeleteFlag As Parameter


Dim objErrorNum As Parameter


Dim intErrorNum As Integer





' setup command variable


Set objCmd = New ADODB.Command


Set objConn = New ADODB.Connection


objConn.Open cnPlant


objCmd.CommandText = "EXEC CreateTempTables"


objCmd.CommandType = adCmdStoredProc


objCmd.ActiveConnection = objConn





' setup parameters


Set objRQSodfil = objCmd.CreateParameter("@RQSodfil", adVarChar, adParamInputOutput, 20, "a")


objCmd.Parameters.Append objRQSodfil


Set objBulkRan = objCmd.CreateParameter("@BulkRan", adVarChar, adParamInputOutput, 20, "a")


objCmd.Parameters.Append objBulkRan


Set objBulkFor = objCmd.CreateParameter("@BulkFor", adVarChar, adParamInputOutput, 20, "a")


objCmd.Parameters.Append objBulkFor


Set objBulkJit = objCmd.CreateParameter("@BulkJit", adVarChar, adParamInputOutput, 20, "a")


objCmd.Parameters.Append objBulkJit


Set objParamID = objCmd.CreateParameter("@ID", adChar, adParamInputOutput, 2, 0)


objCmd.Parameters.Append objParamID


Set objRQSodfilFlag = objCmd.CreateParameter("@RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)


objCmd.Parameters.Append objRQSodfilFlag


Set objBulkRanFlag = objCmd.CreateParameter("@BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)


objCmd.Parameters.Append objBulkRanFlag


Set objBulkForFlag = objCmd.CreateParameter("@BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)


objCmd.Parameters.Append objBulkForFlag


Set objBulkJitFlag = objCmd.CreateParameter("@BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)


objCmd.Parameters.Append objBulkJitFlag


Set objDeleteFlag = objCmd.CreateParameter("@DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)


objCmd.Parameters.Append objDeleteFlag


Set objErrorNum = objCmd.CreateParameter("@ErrorNum", adInteger, adParamInputOutput, , 0)


objCmd.Parameters.Append objErrorNum





' execute command


Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)





' find returned parameters


gstrRQSodfilName = rsTableInfo.Fields("@RQSodfil")


gstrBulkRanName = rsTableInfo.Fields("@BulkRan")


gstrBulkForName = rsTableInfo.Fields("@BulkFor")


gstrBulkJitName = rsTableInfo.Fields("@BulkJit")


gstrID = rsTableInfo.Fields("@ID")


intErrorNum = rsTableInfo.Fields("@ErrorNum")





End Sub





any help would be appreciated