Msg 214, Level 16, State 201, Procedure master..xp_cmdshell, Line 1 Procedure expects parameter 'command_string' of type 'varchar'

Question

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

Answer 1

You can't use varchar(max) with xp_cmdshell. When you change the

DECLARE @bcp_command VARCHAR(MAX);

to

DECLARE @bcp_command VARCHAR(8000);

It should work.

Answer 2

Your real solution here is not to use the insecure and difficult-to-use xp_cmdshell. You are far better off doing this in Powershell.

$rows = Invoke-SqlCmd -Query "SELECT DISTINCT country FROM Fishery_Landings;" -ServerInstance "SomeInstanceHere";

$file = "C:\Users\Erevos\Desktop\@country.csv";
$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';
";

foreach ($row in $rows)
{
    $thisQuery = $query.Replace("@country", $row["country"].Replace("'", "''"));
    $thisFile = $file.Replace("@country", $row["country"]);
    bcp "$thisQuery" queryout $thisFile -c -T -S SomeInstanceHere;
}

You can execute this on schedule using SQL Server Agent, which supports Powershell scripts.