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 appreciatedactually i just solved my own problem:

at the end of the stored procedure i selected the columns i wanted to return and that did it.

select @.RQSodfil,@.BulkRan,@.BulkFor,@.BulkJit,@.ID,@.ErrorNum

i also just did a regular sql statement where i called the EXEC command to run my stored procedure.

EXEC CreateTempTables @.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0'

hopefully if someone else has the same problem i've had they can see what i did.

Help ! How do i refer to the deleted table into a trigger statement ?

Hi there,

Could somebody post some simple example how one can refer to
a column in the 'deleted' temporary table within a trigger definition ?
Should the 'deleted' and 'inserted' temp tables be declared, or they are
'implied' ?

I keep having this msg when trying to create a trigger
"The column prefix 'deleted' does not match with a table name or alias name
used in the query"

Thanks for your inputs,
Eddy.[posted and mailed, please reply in news]

Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Could somebody post some simple example how one can refer to
> a column in the 'deleted' temporary table within a trigger definition ?
> Should the 'deleted' and 'inserted' temp tables be declared, or they are
> 'implied' ?

They are implied. You just use them.

> I keep having this msg when trying to create a trigger
> "The column prefix 'deleted' does not match with a table name or alias
> name used in the query"

May I guess that you are using dynamic SQL? Code executed in EXEC() or
sp_executesql is not part of the trigger, but constitutes a scope on
its own.

A practical workaround is to say:

SELECT * INTO #deleted FROM deleted
SELECT * INTO #inserted FROM inserted

first in the trigger.

This can even be useful in a trigger that does not use dynamic SQL,
particular if multi-row operations are common, because you can get
better performance. The virtual tables inserted and deleted are
not very efficient when they get some size.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Thanks Erland,
> I was trying to refer to a column in the deleted temp table with
> 'deleted.column_name'...
> My sql server does not like this !

Since you never posted the code to the trigger that gave you the
error message, I cannot say what your problem really was. Judging from
the code you post now, may you tried with:

> UPDATE DemographicData SET Lastupdate = getdate()
> WHERE ID = deleted.ID

which is not legal, but that has nothing to do with inserted/deleted,
but with the fact that you must name a table in a query in a FROM
clause or after INSERT/DELETE/UPDATE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp