Hi,

I have upgraded a database to SQL 2000 from SQL7. I have a VB 6.0 application developed by a 3rd party (i.e. no source code) that uses this database.

The app uses ADODB for ODBC conncetivity.

My problem is that when the app calls the following stored procedure

exec dp_repCrestSearch N'01 Jan 2001', N'01 Jan 2003', '0307295', 'OAT02KOB1859%', -1
this works fine in the existing SQL7 database, however, nothing is returned when I run it SQL2000

I turned on SQL Profiler and found that the following was occurring when the SP was executed

exec dp_repCrestSearch N'01 Jan 2001

All the other parameters have been dropped. I used a network analyser to see what was in the packet sent from the workstation to the Server and all the data was correct.

I would appreciate it if anybody could provide some insight to this.

Thanks in advance

Can you post your store procedure?

Jaime E. Maccou

Here you go

CREATE procedure dp_repCrestSearch @start varchar(15),
                    @end varchar(15),
                    @sedol varchar(7),
                    @Ref varchar(20),
                    @abs varchar(20),
                    @debug int = 0
as
begin

set nocount on

declare @retVal int,
    @sql varchar(255),
    @where varchar(255),
    @rows int

    select @sql = '', @where = ''

    select @sql = "select 1 'tranid', 1 'id', itemNo,settleDate,settleDateTime,subDepot,subDepot Acc,ISIN,sedol,externalRef,internalRef,DC,qty,netQ ty,credit,debit,cpty,balanceType,escrowAgent,escro wAccId,dropNo,IntendSettDate from crest "

    if charindex('%',@sedol) > 0 and datalength(@sedol) > 1
    begin
        select @where = " sedol LIKE '" + @sedol + "' "
    end
    else if @sedol <> '%'
    begin
        select @where = " sedol = '" + @sedol + "' "
    end

    if charindex('%',@ref) > 0 and datalength(@ref) > 1
    begin
        if @where <> '' select @where = @where + ' AND '
        select @where = @where + " (externalref LIKE '" + @ref + "' OR internalref LIKE '" + @ref + "') "
    end
    else if @ref <> '%'
    begin
        if @where <> '' select @where = @where + ' AND '
        select @where = @where + " (externalref = '" + @ref + "' OR internalref = '" + @ref + "') "
    end

    if convert(decimal(16,4),@abs) >= 0
    begin
        if @where <> '' select @where = @where + ' AND '
        select @where = @where + " qty = " + @abs + " "
    end

    if @where <> '' select @where = @where + ' AND '
    select @where = " WHERE " + @where + " settledate between '" + @start + "' AND '" + @end + "' "

    if @debug <> 0 select @sql
    if @debug <> 0 select @where

    if @debug <> 5 exec (@sql + @where + ' order by settledate')
    select @retVal = @@error, @rows = @@rowcount

    drop table #rep

    return @retVal
end

GO