No columns defined in rowset

Hello Experts,
I have a stored procedure. I used ADO to called this stored procedure. In
runtime, it says "No columns defined in rowset".
Here is my stored procedure:
========================================
========
CREATE PROCEDURE [sp_MigrateAccount]
@.i_vchSessionID VARCHAR(50),
@.o_intRetVal int OUTPUT
AS
-- Variables for error handling.
DECLARE @.intError int
DECLARE @.insAcct_error int
DECLARE @.insAcctSum_error int
DECLARE @.insAddr_error int
DECLARE @.drop_error int
DECLARE @.intRecordErrorCounter int
-- Temporary storage
DECLARE @.chSourceAccountID char(12)
DECLARE @.vchSourceAccount char(128)
DECLARE @.vchSourceMainPhone char(32)
DECLARE @.vchSourceFax char(32)
DECLARE @.chSourceUserfield5 char(80)
DECLARE @.m_cintMigrateSucceed int
DECLARE @.m_cintRecordDataError int
DECLARE @.m_cintInsertToRealTableError int
DECLARE @.m_cintDuplicateKeyError int
SET NOCOUNT ON
-- Assign value to constants
SET @.m_cintMigrateSucceed = 0
SET @.m_cintRecordDataError = 2
SET @.m_cintInsertToRealTableError = 3
SET @.m_cintDuplicateKeyError = 7
-- Perform data validation --
-- Check for duplicate ACCOUNTID
SET @.intRecordErrorCounter = 0
SELECT @.intRecordErrorCounter = COUNT (a1.ACCOUNTID)
FROM sysdba.ACCOUNT AS a1
WHERE a1.ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID)
IF (@.intRecordErrorCounter > 0) -- There exists at least one validation
error.
BEGIN
-- Update ErrorColumn in the source table. --
-- 1 is an arbitrary value for the ErrorColumn.
-- If ErrorColumn is NULL or 0, this record has no error.
UPDATE sysdba.ACCOUNT_TEMP
SET ErrorColumn = @.m_cintDuplicateKeyError
WHERE (SessionID = @.i_vchSessionID)
AND ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT)
-- No need to update the destination table, thus we quit the SP
-- Note that the error number used by SQL Server is greater than 0.
SET @.intError = @.m_cintDuplicateKeyError
GOTO Quit_SP
END
-- Up to this point, the data validation is passed.
-- We can insert record into the destination table. now.
BEGIN TRAN dataMigration
-- Insert records to the ACCOUNT table
-- People from newsgroup said that you are required to
-- enter uppercase Account Name into ACCOUNT_UC field.
INSERT INTO ACCOUNT
(ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC)
SELECT ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC
FROM sysdba.ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID
SET @.insAcct_error = @.@.ERROR
IF (0 = @.insAcct_error) BEGIN
COMMIT TRAN dataMigration
END
ELSE
BEGIN
IF (0 <> @.insAcct_error)
-- Const m_cintInsertToRealTableError = 3
SET @.intError = @.m_cintInsertToRealTableError
ROLLBACK TRAN dataMigration
END
Error_Handler:
Quit_SP:
IF (@.intError IS NULL)
-- No value assigned to @.intError in the
-- body of this stored procedure.
-- Thus, migrate data is successful.
-- Const m_cintMigrateSucceed = 0
SET @.o_intRetVal = @.m_cintMigrateSucceed
ELSE
SET @.o_intRetVal = @.intError -- Failure encountered.
RETURN
GO
========================================
=
Here is my VBScript that used ADO to call the store procedure:
========================================
===
' :
' ADO database connection....
' :
With ADOCmd
.CommandText = "sp_MigrateAccount"
.CommandType = adCmdStoredProc
strInputText = i_strSessionID
lngParamLen = Len(strInputText)
If lngParamLen <= 0 Then
lngParamLen = 1
End If
Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
adParamInput, lngParamLen, strInputText)
.Parameters.Append ADOParam
Set ADOParam = .CreateParameter("@.o_intRetVal", adInteger,
adParamOutput)
.Parameters.Append ADOParam
Set .ActiveConnection = objCon
.Execute
lngRetVal = .Parameters("@.o_intRetVal").Value
End With
' :
' :
' :
'=======================================
==
The ".Execute" statement caused the "No columns defined in rowset".
If I try the following statement:
.Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Or,
.Execute , , adExecuteNoRecords
I cannot get the output value (@.o_intRetVal) from the stored procedure.
May I know how to solve this problem? Thanks a lot.
SammySa wrote:
> Hello Experts,
> I have a stored procedure. I used ADO to called this stored
> procedure. In runtime, it says "No columns defined in rowset".
That does not sound like an ADO error. It sounds more like a T-SQL error.
ADO errors usually refer to recordsets, fields and records. "rowset" makes
it sound
like a T-SQL error.
Have you verified that this procedure runs correctly in Query Analyzer? Like
this:
declare @.ret int
exec sp_MigrateAccount 'some session id',@.ret output
select @.ret [Output Value]

> Here is my stored procedure:
> ========================================
========
> CREATE PROCEDURE [sp_MigrateAccount]
Nothing to do with your error, but using "sp_" for a non-system stored
procedure can cause a slight performance dip, since the query engine treats
all procedures beginning with this prefix as system procedures, looking for
them first in Master, and only looking for them in the current database when
it fails to find them in Master. If you mistakenly name your procedure the
same as a real system procedure, you've got a knotty debugging problem ahead
of you ...

> @.i_vchSessionID VARCHAR(50),
> @.o_intRetVal int OUTPUT
> AS
> -- Variables for error handling.
> DECLARE @.intError int
<snip>
> SET NOCOUNT ON
I doubt this is the problem, but I generally make this the first statement
in my procedures - before any other statement, even DECLARE's.

>
<snip>
> ========================================
=
>
> Here is my VBScript that used ADO to call the store procedure:
> ========================================
===
> ' :
> ' ADO database connection....
> ' :
> With ADOCmd
> .CommandText = "sp_MigrateAccount"
> .CommandType = adCmdStoredProc
> strInputText = i_strSessionID
> lngParamLen = Len(strInputText)
> If lngParamLen <= 0 Then
> lngParamLen = 1
> End If
> Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
> adParamInput, lngParamLen, strInputText)
> .Parameters.Append ADOParam
> Set ADOParam = .CreateParameter("@.o_intRetVal",
> adInteger, adParamOutput)
> .Parameters.Append ADOParam
> Set .ActiveConnection = objCon
> .Execute
> lngRetVal = .Parameters("@.o_intRetVal").Value
> End With
> ' :
> ' :
> ' :
> '=======================================
==
> The ".Execute" statement caused the "No columns defined in rowset".
> If I try the following statement:
> .Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Should be:
.Execute , , (adCmdStoredProc + adExecuteNoRecords)
HTH,
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.sql