September 29, 2010 at 10:33 am
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, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, @state = NULL" queryout "C:\\temp\test.cs
v" -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.
Thanks for the help
October 1, 2010 at 11:48 am
FYI, this seemed a simple enough solution. However, with the lack of information out there I have abandoned this method and am doing the export via SSRS. Only took 5 minutes or so to create the report layout.
No idea why it didn't occur to me earlier.
Thanks for looking at the post. It looks like quite a few were willing to help if they could.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply