USE [Fishery]
GO
/****** Object: StoredProcedure [dbo].[generate_csv_files] Script Date: 2/11/2023 4:34:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[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(8000);
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\Project Development\CSV_Files' + @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 above procedure is created to generate CSV files.
but an error arrises.
i have enabled TCPIP and Named Pipes provider but still
SQLState = 08001, NativeError = 53
Error = [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = 08001, NativeError = 53
Error = [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
NULL