i am using SQL Server Management Studio 2018 I have created a stored procedure where i want to generate CSV Files
CREATE PROCEDURE generate_csv_files
AS
BEGIN
SET NOCOUNT ON;
DECLARE @country VARCHAR(MAX);
DECLARE @file_name VARCHAR(MAX);
DECLARE @query VARCHAR(MAX);
DECLARE @bcp_command VARCHAR(MAX);
DECLARE cursor1 CURSOR FOR
SELECT DISTINCT country
FROM Fishery_Landings;
OPEN cursor1;
FETCH NEXT FROM cursor1 INTO @country;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @file_name = 'C:\Users\Erevos\Desktop\' + @country + '.csv';
SET @query = 'SELECT Country, Year, Commodity_Group, MCS.main_commercial_species, presentation, preservation, volume_kg
FROM Fishery_Landings FL
JOIN Commodity_Group CG on FL.CGID = CG.CGID
JOIN Main_Commodity_Species MCS on MCS.MCSID = FL.MCSID
WHERE country = ''' + @country + '''';
SET @bcp_command = 'bcp "' + @query + '" queryout ' + @file_name + ' -c -T -S ' + @@SERVERNAME;
EXEC master..xp_cmdshell @bcp_command;
FETCH NEXT FROM cursor1 INTO @country;
END;
CLOSE cursor1;
DEALLOCATE cursor1;
END;
The error i am getting:
Msg 214, Level 16, State 201, Procedure master..xp_cmdshell, Line 1 [Batch Start Line 1] Procedure expects parameter 'command_string' of type 'varchar'.
What i've done
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
How do i bypass this error?
Update After Changing the VARCHAR(MAX) to VARCHAR(8000) This error produce:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values][-G Azure Active Directory Authentication] [-h "load hints"] [-x generate xml format file] [-d database name] [-K application intent] [-l login timeout] NULL
bcp
command must be on one line. I'll add that creating files directly from T-SQL is a hack. It would be better do this in application code or script.bcp "SELECT Country, Year, Commodity_Group, MCS.main_commercial_species, presentation, preservation, volume_kg
and press enter.