DSXchange

Hi,
We have a developer who exceutes the following fine as an SQl statement in query tools.
###############################################
BEGIN
SET NOCOUNT ON;

DECLARE @EventIDRecordType VARCHAR(26);
DECLARE @Name VARCHAR(256);
DECLARE @Members VARCHAR(3000);
DECLARE @Current_EventIDRecordType VARCHAR(26);

DECLARE @tmptbl TABLE (EventID VARCHAR(18), RecordType VARCHAR(8), Members VARCHAR(3000));
DECLARE mbr_cursor cursor FOR
SELECT eventid+recordtype, Name
FROM dbo.PruforceEventAttendee
ORDER BY 1, 2;


OPEN mbr_cursor;
FETCH NEXT FROM mbr_cursor INTO @EventIDRecordType, @Name;

SET @Current_EventIDRecordType = ' ' ;
SET @Members = '' ;

WHILE @@FETCH_STATUS = 0
BEGIN
IF @EventIDRecordType != @Current_EventIDRecordType
BEGIN
IF @Current_EventIDRecordType != ' '
INSERT INTO @tmptbl
VALUES(substring(@Current_EventIDRecordType,1,18), substring(@Current_EventIDRecordType,19,8), @Members);
SET @Current_EventIDRecordType = @EventIDRecordType;
SET @Members = RTRIM(@Name);
END
ELSE
SET @Members = @Members + ', ' + RTRIM(@Name);

FETCH NEXT FROM mbr_cursor INTO @EventIDRecordType, @Name;
END

INSERT INTO @tmptbl
VALUES(substring(@Current_EventIDRecordType,1,18), substring(@Current_EventIDRecordType,19,8), @Members);

CLOSE mbr_cursor;
DEALLOCATE mbr_cursor;

SELECT * FROM @tmptbl;
END

###############################################
When he pastes this in the user defined SQl field in the ODBC enterprise edition he receives the error:

main_program: [IBM (DataDirect OEM) ][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'OFF'.

Followed by:
xfmCombineAttendees: Error when checking operator: Could not find input field "EventID".

We ahve never tried this type of SQl before and wondering if this should work.

Thanks - - John

My profile is showing as Server but this is a parallel job.

You can edit your post and correct that.

Are you sure you're referring to the correct procedure? After all, the one you posted does not contain "OFF".

That's what I found to be odd...there is no "OFF" in the User defined SQl. I have created my own version of the job to verify.