BCP Function sequence error when running proc with parameters

I have been trying to create a stored procedure that uses dynamic SQL to assemble a bcp command to export data from a parameterized stored procedure to a csv file.

Was originally getting the host data file access issue that I think was filesystem permissions.

Now I consistantly get the fillowing error when I run the proc:

SQLState = S1010, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Function sequence error

The proc code is as follows:

ALTER PROCEDURE [dbo].[sp_MakeMarketingListExports] 
        @includeInService varchar(1) = NULL,
        @includeMLM varchar(1) = NULL,
        @includeQuoteNoodle varchar(1) = NULL,
        @netective varchar(1) = NULL,
        @cyberChex varchar(1) = NULL,
        @agentsAdvantage varchar(1) = NULL,
        @quoteNoodle varchar(1) = NULL,
        @mlmListSubscriber varchar(1) = NULL,
        @state varchar(10) = NULL,
        @mailerID varchar(10) = NULL,
        @filePath varchar(250)
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @command varchar(500);

        SET @command = 'bcp "exec InternalML..sp_SelectMarketingListMembers '
                                + '@includeInService = ' + COALESCE(@includeInService, 'NULL') + ', '
                                + '@includeMLM = ' + COALESCE(@includeMLM, 'NULL') + ', '
                                + '@includeQuoteNoodle = ' + COALESCE(@includeQuoteNoodle, 'NULL') + ', '
                                + '@netective = ' + COALESCE(@netective, 'NULL') + ', '
                                + '@cyberChex = ' + COALESCE(@cyberChex, 'NULL') + ', '
                                + '@agentsAdvantage = ' + COALESCE(@agentsAdvantage, 'NULL') + ', '
                                + '@quoteNoodle = ' + COALESCE(@quoteNoodle, 'NULL') + ', '
                                + '@mlmListSubscriber = ' + COALESCE(@mlmListSubscriber, 'NULL') + ', '
                                + '@state = ' + COALESCE(@state, 'NULL') + '"'
                                + ' queryout ' + COALESCE(@filePath, 'NULL')
                                + ' -c -T -S ' + @@servername

        SELECT @command;

        EXEC xp_cmdshell @command;
END

I use the SELECT @command to get the assembled command for analysis.

Have had the parameter names for the calle proc included so the command came out like:

bcp "exec InternalML..sp_SelectMarketingListMembers @includeInService = NULL, @includeMLM = NULL, @includeQuoteNoodle = NULL, @netective = NULL, @cyberChex = NULL, @agentsAdvantage = NULL, @quoteNoodle = NULL, @mlmListSubscriber = NULL, @state = NULL" queryout C:\temp\test.csv -c -T -S SPKD18

Also did it without the parameter names with the same Function sequenc error message:

bcp "exec InternalML.dbo.sp_SelectMarketingListMembers NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @state = NULL" queryout "C:\\temp\test.csv" -c -T -S SPKD18

Any insight on why this is not working? BTW, I have run the bcp via the proc and copied it to a command prompt with the same results. The problem is that I can't find much on the internet about this error, probably because it is so generic.

I can successfully run the InternalML.dbo.sp_SelectMarketingListMembers proc with the parameters directly in SSMS Query window so am pretty sure it has to do with bcp.

Thanks for the help

ansaurus

tags:

views:

answers:

BCP Function sequence error when running proc with parameters

related questions