stored procedure which returns a recordset

[ExtraD]

hi,

i have the follwoing issue. i created a stored procedure which returns a recordset:

CREATE PROCEDURE [dbo].[prc_getFleetCostData2]
@pCostID AS VARCHAR(100)
, @pLocatNr AS INT
, @pLocatTb AS VARCHAR(10)
, @pDate AS VARCHAR(10)
, @pAutoInc AS INT
, @pUnitType AS VARCHAR(4)
AS

DECLARE @sql VARCHAR(4000)

-- Retrieve in which envirionment the sp is (live/test)
DECLARE cEnv CURSOR FOR
SELECT Envirionment
FROM dbo.environment

DECLARE @Env AS VARCHAR(50)
OPEN cEnv
FETCH NEXT FROM cEnv INTO @Env
SELECT @Env = RTRIM(@Env)

-- Build sql query
SET @sql = 'SELECT fc.*, ft.*, ts.tr_currency' +
' FROM Order07' + @Env + '.dbo.FleetCost fc, Order07' + @Env + '.dbo.FleetCostType ft, Unitstat' + @Env + '.dbo.Transporter ts' +
' Where fc.fc_fccostid = ft.fcid_recnr' +
' AND fc.fc_from <= CONVERT(DATETIME, ''' + @pDate + ''', 105) AND fc.fc_till >= CONVERT(DATETIME, ''' + @pDate + ''', 105)' +
' AND CAST(fc.fc_fccostid AS CHAR) IN (' + @pCostID + ') ' +
' AND fc.fc_locatnr = ' + CAST(@pLocatNr AS VARCHAR(100)) + 'AND fc.fc_locattb = ''' + @pLocatTb + '''' +
' AND fc.fc_locatnr = ts.tr_FlPrPrFl_recnr AND fc.fc_locattb = ts.tr_type' +
' AND fc.fc_unittype = ''' + @pUnitType + '''' +
' AND fc.fc_fccostid NOT IN (SELECT fc.fc_fccostid' +
' FROM Order07' + @Env + '.dbo.FleetCost fc, Order07' + @Env + '.dbo.FleetCostType ft, Unitstat' + @Env + '.dbo.Transporter ts' +
' Where fc.fc_fccostid = ft.fcid_recnr' +
' AND fc.fc_from <= CONVERT(DATETIME, ''' + @pDate + ''', 105) AND fc.fc_till >= CONVERT(DATETIME, ''' + @pDate + ''', 105)' +
' AND CAST(fc.fc_fccostid AS CHAR) IN (' + @pCostID + ') ' +
' AND fc.fc_autoinc = ' + CAST(@pAutoInc AS VARCHAR(100)) +
' AND fc.fc_locatnr = ' + CAST(@pLocatNr AS VARCHAR(100)) + ' AND fc.fc_locattb = ''' + @pLocatTb + '''' +
' AND fc.fc_locatnr = ts.tr_FlPrPrFl_recnr AND fc.fc_locattb = ts.tr_type)'

EXECUTE (@sql)

-- Release cursor
CLOSE cEnv
DEALLOCATE cEnv

SELECT * FROM fleetcost

GO

----------------
My call from vb6 is as following:

Dim cmd As ADODB.Command
Dim rs2 As ADODB.Recordset
Dim param As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnSQLFY
cmd.CommandText = "prc_getFleetCostData2"
cmd.CommandType = adCmdStoredProc

Set param = cmd.CreateParameter("@pCostID", adVarChar, adParamInput, 100)
param.Value = "'''3'''"
'param.Value = "'''1'', ''2'''"
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@pLocatNr", adBigInt, adParamInput)
param.Value = tFleetID
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@pLocatTb", adVarChar, adParamInput, 10)
param.Value = tFleetType
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@pDate", adVarChar, adParamInput, 10)
param.Value = rp.DeliveryDate
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@pAutoInc", adBigInt, adParamInput)
param.Value = tMainAutoinc
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@pUnitType", adVarChar, adParamInput, 4)
param.Value = tunittype
cmd.Parameters.Append param


Set rs2 = New ADODB.Recordset
rs2.CursorLocation = adUseServer
rs2.Open cmd, , adOpenStatic, adLockReadOnly, adCmdStoredProc

Dim rsFleetCost As ADODB.Recordset
Set rsFleetCost = New ADODB.Recordset
Set rsFleetCost = rs2


--------------------
but my result in vb6 is always EOF and in the sql query analyzer he returns me a set.

could somebody tell me what i'm doing wrong????

THANX

 

[gmmastros]

You need to set nocount on, like this...

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[prc_getFleetCostData2] 
          @pCostID [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](100)
        , @pLocatNr [COLOR=blue]AS[/color] [COLOR=blue]INT[/color]
        , @pLocatTb [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10)
        , @pDate [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10)
        , @pAutoInc [COLOR=blue]AS[/color] [COLOR=blue]INT[/color]
        , @pUnitType [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](4)
[COLOR=blue]AS[/color]
[!]SET NOCOUNT ON[/!]

[COLOR=blue]DECLARE[/color] @sql [COLOR=blue]VARCHAR[/color](4000)

[COLOR=green]-- Retrieve in which envirionment the sp is (live/test)[/color]

The rest of your code here....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom