0

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

5
  • 1
    The documentation states the command string must be varchar(8000) or nvarchar(4000), not MAX.
    – Dan Guzman
    Feb 7, 2023 at 19:08
  • Thank you, that was it but now a new error generates. Feb 8, 2023 at 6:11
  • Your SQL statement includes line breaks. The entire 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.
    – Dan Guzman
    Feb 8, 2023 at 10:29
  • excuse me but i didn't get it, what do you mean by "The entire bcp command must be on one line"? the line breaks are on the @query. Feb 8, 2023 at 14:05
  • Run the bcp command from a Windows command prompt and it will be evident. Type bcp "SELECT Country, Year, Commodity_Group, MCS.main_commercial_species, presentation, preservation, volume_kg and press enter.
    – Dan Guzman
    Feb 8, 2023 at 14:28

2 Answers 2

2

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.

1
  • I have now changed the BCP_Command from MAX to 8000, but it generates me something like an error. i have updated my question above. Thanks Feb 8, 2023 at 6:12
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.