Skip to content
The Chat Archive
gpt-4o-mini-2024-07-18· OpenAI WildChat-4.8M (AI2)

( echo open ftp.yourserver.com echo username echo password echo get !brandFile!…

  1. User

    ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx!brandFile! echo get !departmentFile! D:\map_drive\wprx!departmentFile! echo get !productFile! D:\map_drive\wprx!productFile! echo bye ) > ftp_commands.txt ftp -n -s:ftp_commands.txt

    make this script in exec xp_cmdshell

  2. Assistant

    To convert your batch script into an xp_cmdshell call in SQL Server (T-SQL), we need to format the commands correctly and ensure they can be executed through SQL Server's xp_cmdshell. Here's how you can do it:

    DECLARE @ftpCommands NVARCHAR(MAX)
    DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt'  -- Replace with actual brand file name
    DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt'  -- Replace with actual department file name
    DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt'  -- Replace with actual product file name
    
    -- Construct the FTP command script
    SET @ftpCommands = 
        'echo open ftp.yourserver.com' + CHAR(13) +
        'echo username' + CHAR(13) +
        'echo password' + CHAR(13) +
        'echo get ' + @brandFile + ' D:\map_drive\wprx\' + @brandFile + CHAR(13) +
        'echo get ' + @departmentFile + ' D:\map_drive\wprx\' + @departmentFile + CHAR(13) +
        'echo get ' + @productFile + ' D:\map_drive\wprx\' + @productFile + CHAR(13) +
        'echo bye'
    
    -- Create a temporary command file
    DECLARE @ftpFile NVARCHAR(255) = 'D:\ftp_commands.txt'
    
    EXEC xp_cmdshell 'echo ' + @ftpCommands + ' > ' + @ftpFile
    
    -- Execute the FTP command
    EXEC xp_cmdshell 'ftp -n -s:' + @ftpFile
    

    Breakdown of the Code:

    1. Variable Declarations: Sets up variables for your file names and FTP commands.
    2. Construct FTP Command: Templates the FTP command using string concatenation.
    3. Create Command File: Sources the entire command passing it to xp_cmdshell to create a command file.
    4. Execute FTP Command: Calls FTP using the generated command script.

    Important Notes:

    • Replace ftp.yourserver.com, username, and password with actual values.
    • Ensure that the xp_cmdshell is enabled on your SQL Server instance. This is often disabled by default due to security considerations.
    • Review file path access permissions for SQL Server, ensuring it can write to D:\ and run FTP commands.
    • This code may require permissions as it uses xp_cmdshell, which has security implications. Always follow best practices regarding permissions in SQL Server.
  3. User

    can i do like set @ftpcommand = ' ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx!brandFile! echo get !departmentFile! D:\map_drive\wprx!departmentFile! echo get !productFile! D:\map_drive\wprx!productFile! echo bye ) > ftp_commands.txt ftp -n -s:ftp_commands.txt '

    exec xp_cmdshell @ftpcommand
    
  4. Assistant

    Your approach to using xp_cmdshell with the @ftpcommand variable is mostly correct in terms of structure, but there are a few adjustments needed to make it work properly within SQL Server. Primarily, you cannot use batch syntax such as !variable! for variable substitution inside a SQL Server xp_cmdshell command. Instead, you'll directly build the string with the SQL Server variables.

    Here’s how you can set it up:

    DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt'  -- Replace with actual brand file name
    DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt'  -- Replace with actual department file name
    DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt'  -- Replace with actual product file name
    
    DECLARE @ftpCommands NVARCHAR(MAX)
    
    -- Construct the full FTP command
    SET @ftpCommands = 
    '(
        echo open ftp.yourserver.com
        echo username
        echo password
        echo get ' + @brandFile + ' D:\map_drive\wprx\' + @brandFile + '
        echo get ' + @departmentFile + ' D:\map_drive\wprx\' + @departmentFile + '
        echo get ' + @productFile + ' D:\map_drive\wprx\' + @productFile + '
        echo bye
    ) > D:\ftp_commands.txt
    ftp -n -s:D:\ftp_commands.txt
    '
    
    -- Execute the command
    EXEC xp_cmdshell @ftpCommands
    

    Explanation:

    1. Variable Declarations: You define your file names as NVARCHAR variables.
    2. Building Command: The FTP commands are constructed using concatenation. Each instance of the @brandFile, @departmentFile, and @productFile is directly included in the string.
    3. Executing the Command: The complete batch command is executed with xp_cmdshell.

    Important Considerations:

    • Ensure that the paths are accessible to the SQL Server service.
    • Make sure that SQL Server has the necessary permissions to execute xp_cmdshell and that it is enabled.
    • Distributing sensitive information (like FTP passwords) is a security risk; avoid hardcoding them if possible.
    • Use appropriate error handling and logging in production scripts to capture failures in executing commands.
  5. User

    echo ( DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name

    DECLARE @ftpCommands NVARCHAR(MAX)

    -- Construct the full FTP command SET @ftpCommands = '( echo open ftp.yourserver.com echo username echo password echo get ' + @brandFile + ' D:\map_drive\wprx' + @brandFile + ' echo get ' + @departmentFile + ' D:\map_drive\wprx' + @departmentFile + ' echo get ' + @productFile + ' D:\map_drive\wprx' + @productFile + ' echo bye ) > D:\ftp_commands.txt ftp -n -s:D:\ftp_commands.txt ' -- Execute the command EXEC xp_cmdshell @ftpCommands ) > ftp_commands.sql

    correct it if i need create file bat file

  6. Assistant

    To create a .bat file from within SQL Server using xp_cmdshell, you need to structure the command correctly and ensure that the commands are written properly to the batch file. Below is a modified version of your script that constructs a .bat file and then executes it:

    1. We'll create a batch file (ftp_script.bat) that contains the FTP commands.
    2. The execution of the created batch file will happen after its creation.

    Here’s the corrected script:

    DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt'  -- Replace with actual brand file name
    DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt'  -- Replace with actual department file name
    DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt'  -- Replace with actual product file name
    
    DECLARE @ftpCommands NVARCHAR(MAX)
    
    -- Construct the full FTP command
    SET @ftpCommands = 
    '@echo off
    echo open ftp.yourserver.com
    echo username
    echo password
    echo get ' + @brandFile + ' D:\map_drive\wprx\' + @brandFile + '
    echo get ' + @departmentFile + ' D:\map_drive\wprx\' + @departmentFile + '
    echo get ' + @productFile + ' D:\map_drive\wprx\' + @productFile + '
    echo bye
    ' 
    
    -- Create the .bat file in a specific location
    DECLARE @batFile NVARCHAR(255) = 'D:\ftp_script.bat'
    
    -- Write FTP commands to the batch file
    EXEC xp_cmdshell 'echo ' + REPLACE(@ftpCommands, '''', '''''') + ' > ' + @batFile
    
    -- Execute the batch file
    EXEC xp_cmdshell @batFile
    

    Explanation:

    1. Create FTP Commands: The commands are prefixed with @echo off to suppress command echoing when the batch file runs.
    2. Escaping Single Quotes: When building the command string for xp_cmdshell, any single quotes in T-SQL strings need to be doubled up to be interpreted correctly.
    3. Write to Batch File: The constructed FTP commands are echoed into ftp_script.bat using xp_cmdshell.
    4. Execute the Batch File: The batch file is executed immediately after being created.

    Important Considerations:

    • Ensure you have write permissions for the D:\ directory or whichever location you're using.
    • Make sure xp_cmdshell is enabled and utilized cautiously since it presents security implications.
    • Double-check that the FTP details (username, password, server name) are secure and managed properly.
    • It’s generally a good idea to add error handling when executing commands or writing files, especially in production environments.
  7. User

    i mean will create echo ( DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name

    DECLARE @ftpCommands NVARCHAR(MAX)

    -- Construct the full FTP command SET @ftpCommands = '( echo open ftp.yourserver.com echo username echo password echo get ' + @brandFile + ' D:\map_drive\wprx' + @brandFile + ' echo get ' + @departmentFile + ' D:\map_drive\wprx' + @departmentFile + ' echo get ' + @productFile + ' D:\map_drive\wprx' + @productFile + ' echo bye ) > D:\ftp_commands.txt ftp -n -s:D:\ftp_commands.txt ' -- Execute the command EXEC xp_cmdshell @ftpCommands ) > ftp_commands.sql to file ftpcommand.sql by using echo command in dos

  8. Assistant

    To create a .sql file that includes your SQL statements using the echo command in a Command Prompt batch script (or using xp_cmdshell in SQL Server), you'll need to format the command properly. Let's construct a script that does this.

    We'll create a .sql file that contains the SQL code you've provided, preserving the format exactly as you want it. Here is how you can do that:

    1. Create a .sql file.
    2. Use the echo command to write the SQL code into that file.

    Here's a complete sample for you:

    DECLARE @sqlCommands NVARCHAR(MAX)
    
    SET @sqlCommands = 
    'echo DECLARE @brandFile NVARCHAR(255) = ''YourBrandFile.txt''  -- Replace with actual brand file name
    echo DECLARE @departmentFile NVARCHAR(255) = ''YourDepartmentFile.txt''  -- Replace with actual department file name
    echo DECLARE @productFile NVARCHAR(255) = ''YourProductFile.txt''  -- Replace with actual product file name
    
    echo DECLARE @ftpCommands NVARCHAR(MAX)
    
    echo -- Construct the full FTP command
    echo SET @ftpCommands = 
    echo ''(
    echo     echo open ftp.yourserver.com
    echo     echo username
    echo     echo password
    echo     echo get '' + @brandFile + '' D:\map_drive\wprx\'' + @brandFile + ''
    echo     echo get '' + @departmentFile + '' D:\map_drive\wprx\'' + @departmentFile + ''
    echo     echo get '' + @productFile + '' D:\map_drive\wprx\'' + @productFile + ''
    echo     echo bye
    echo '') > D:\ftp_commands.txt
    echo ftp -n -s:D:\ftp_commands.txt
    echo EXEC xp_cmdshell @ftpCommands
    '
    
    -- Specify the output file path
    DECLARE @outputFile NVARCHAR(255) = 'D:\ftp_commands.sql'
    
    -- Create the command to output the SQL code to the file
    EXEC xp_cmdshell 'echo ' + REPLACE(@sqlCommands, '''', '''''') + ' > ' + @outputFile
    

    Key Points:

    • Echo with Proper Escaping: Each line is echoed and includes appropriate escaping. Use double single quotes in the string to denote a literal single quote in SQL.
    • Output File Path: This example writes to D:\ftp_commands.sql. Make sure you have permission to write to this path.
    • Executing the Command: The final command constructs and executes the xp_cmdshell command to echo the SQL script into the specified file.

    Important Considerations:

    • Make sure xp_cmdshell is enabled on your SQL Server instance and that you understand the security implications of using it.
    • Validate that the SQL Server process has sufficient permissions to write to the specified directory.
    • This will create a plaintext .sql file. Ensure the contents do not expose sensitive information, such as FTP credentials.
  9. User

    run in cmd batch file, not in sql

  10. Assistant

    If you want to create a batch file that generates another .sql file with specific content directly in the Windows Command Prompt, you can use echo commands in a .bat file to generate that file.

    Here is a step-by-step guide on how to do this:

    1. Create a batch file (e.g., create_sql_file.bat).
    2. Write the echo commands in the batch file to generate the desired SQL content.

    Batch File Content

    Here is the content you would put into create_sql_file.bat:

    @echo off
    (
        echo DECLARE @brandFile NVARCHAR(255) = ''YourBrandFile.txt''  -- Replace with actual brand file name
        echo DECLARE @departmentFile NVARCHAR(255) = ''YourDepartmentFile.txt''  -- Replace with actual department file name
        echo DECLARE @productFile NVARCHAR(255) = ''YourProductFile.txt''  -- Replace with actual product file name
        echo.
        echo DECLARE @ftpCommands NVARCHAR(MAX)
        echo.
        echo -- Construct the full FTP command
        echo SET @ftpCommands = 
        echo ''(
        echo     echo open ftp.yourserver.com
        echo     echo username
        echo     echo password
        echo     echo get '' + @brandFile + '' D:\map_drive\wprx\'' + @brandFile + ''
        echo     echo get '' + @departmentFile + '' D:\map_drive\wprx\'' + @departmentFile + ''
        echo     echo get '' + @productFile + '' D:\map_drive\wprx\'' + @productFile + ''
        echo     echo bye
        echo '') > D:\ftp_commands.txt
        echo ftp -n -s:D:\ftp_commands.txt
        echo EXEC xp_cmdshell @ftpCommands
    ) > D:\ftp_commands.sql
    

    Instructions to Create and Run the Batch File

    1. Open Notepad:

      • Start Notepad or any text editor of your choice.
    2. Copy the Content:

      • Copy the provided batch script content above and paste it into the text editor.
    3. Save the File:

      • Save the file as create_sql_file.bat. Make sure to change the "Save as type" dropdown to "All Files" in Notepad so it doesn't save it as a .txt file.
    4. Run the Batch File:

      • Open Command Prompt (cmd).
      • Navigate to the directory where create_sql_file.bat is saved.
      • Execute the command: create_sql_file.bat.
    5. Check for Output:

      • After running the batch file, check D:\ftp_commands.sql for the generated SQL content.

    Notes

    • Make sure you have write permissions to D:\ or modify the path to a location where you have permission.
    • The @echo off command prevents the commands from being printed to the console, making the output cleaner.
    • Each echo statement outputs its content to D:\ftp_commands.sql, creating the desired SQL file.
  11. User

    rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do ( set "storeCode=%%i" echo Processing store code: !storeCode! pause

    rem Step 2: Transform store code to pth12345
    set "transformedCode=pth!storeCode!"
    echo Transformed store code to: !transformedCode!
    pause
    
    rem Step 3: Check if the pth12345 file exists
    if exist "!transformedCode!" (
        echo Found the file: !transformedCode!
        echo Skipping to next store code.
        pause
        goto :next
    )
    
    rem Step 4: Ping pth12345
    ping -n 1 "!transformedCode!" > nul 2>&1
    if errorlevel 1 (
        echo !transformedCode! is offline. Stopping for this store code.
        pause
        goto :next
    )
    
    rem Fetch yesterday's date using osql
    osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
    echo Fetched yesterday's date.
    pause
    
    rem Check if the date.dat file was created
    if not exist date.dat (
        echo Failed to retrieve date from the database. Exiting.
        pause
        goto :end
    )
    
    rem Read the date from the file and format it
    set /p date=<date.dat
    echo Date retrieved: %date%
    set day=%date:~0,10%   rem Get the full date string "YYYY-MM-DD"
    pause
    
    rem Extract components
    set yyyy=%day:~0,4%    rem Year
    set mm=%day:~5,2%      rem Month
    set dd=%day:~8,2%      rem Day
    echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd!
    pause
    
    rem Construct the file names
    set "brandFile=BRAND_!mm!!dd!.!storeCode!"
    set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
    set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
    
  12. Assistant

    Your batch script effectively processes a list of store codes from st.txt, transforms them, checks the existence of related files, pings the transformed codes, retrieves yesterday's date from a database, and extracts date components. There are, however, a few improvements to make the script work correctly in a batch file, especially while using setlocal for delayed expansion and ensuring proper handling of file operations.

    Here’s the complete script, with improvements and necessary completion up to file creation:

    @echo off
    setlocal enabledelayedexpansion
    
    rem Step 1: Loop through each store code in st.txt
    for /f "usebackq delims=" %%i in ("st.txt") do (
        set "storeCode=%%i"
        echo Processing store code: !storeCode!
        pause
    
        rem Step 2: Transform store code to pth12345
        set "transformedCode=pth!storeCode!"
        echo Transformed store code to: !transformedCode!
        pause
    
        rem Step 3: Check if the pth12345 file exists
        if exist "!transformedCode!" (
            echo Found the file: !transformedCode!
            echo Skipping to next store code.
            pause
            goto :next
        )
    
        rem Step 4: Ping pth12345
        ping -n 1 "!transformedCode!" > nul 2>&1
        if errorlevel 1 (
            echo !transformedCode! is offline. Stopping for this store code.
            pause
            goto :next
        )
    
        rem Fetch yesterday's date using osql
        osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
        echo Fetched yesterday's date.
        pause
    
        rem Check if the date.dat file was created
        if not exist date.dat (
            echo Failed to retrieve date from the database. Exiting.
            pause
            goto :end
        )
    
        rem Read the date from the file and format it
        set /p date=<date.dat
        echo Date retrieved: !date!
        set day=!date!   rem Get the full date string "YYYY-MM-DD"
        pause
    
        rem Extract components
        set yyyy=!day:~0,4!    rem Year
        set mm=!day:~5,2!      rem Month
        set dd=!day:~8,2!      rem Day
        echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd!
        pause
    
        rem Construct the file names
        set "brandFile=BRAND_!mm!!dd!.!storeCode!"
        set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
        set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
        
        rem Sample output of filenames to verify they are constructed properly
        echo Created files: !brandFile!, !departmentFile!, !productFile!
        pause
    
        :next
    )
    
    :end
    echo Done processing all store codes.
    endlocal
    

    Explanation of Important Parts:

    1. Enable Delayed Expansion:

      • setlocal enabledelayedexpansion is necessary for using !variable! syntax inside for loops.
    2. Constructed Store Codes and Filenames:

      • As you loop through each store code, the script constructs filenames based on the retrieved date. Make sure the filenames and paths you need are formatted as per your requirements.
    3. Error Checks:

      • The script includes error checking after operations such as file existence check and the database query to ensure stability.
    4. Next Label:

      • The goto :next statement is included to skip to the next iteration of the loop if a file already exists or if a ping fails.
    5. Pause Commands:

      • The pause commands are retained for debugging to observe the output step-by-step. You can remove them for production use.
    6. Control Flow:

      • The script correctly uses the :next label to continue processing each store code without stopping the entire script.

    Make sure to replace placeholders and variables (YourBrandFile.txt, YourDepartmentFile.txt, etc.) with the appropriate logic to suit your needs as you further develop the script.

  13. User

    @echo off setlocal EnableDelayedExpansion

    :loop rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do ( set "storeCode=%%i" echo Processing store code: !storeCode! pause

    rem Step 2: Transform store code to pth12345
    set "transformedCode=pth!storeCode!"
    echo Transformed store code to: !transformedCode!
    pause
    
    rem Step 3: Check if the pth12345 file exists
    if exist "!transformedCode!" (
        echo Found the file: !transformedCode!
        echo Skipping to next store code.
        pause
        goto :next
    )
    
    rem Step 4: Ping pth12345
    ping -n 1 "!transformedCode!" > nul 2>&1
    if errorlevel 1 (
        echo !transformedCode! is offline. Stopping for this store code.
        pause
        goto :next
    )
    
    rem Fetch yesterday's date using osql
    osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
    echo Fetched yesterday's date.
    pause
    
    rem Check if the date.dat file was created
    if not exist date.dat (
        echo Failed to retrieve date from the database. Exiting.
        pause
        goto :end
    )
    
    rem Read the date from the file and format it
    set /p date=<date.dat
    echo Date retrieved: %date%
    set day=%date:~0,10%   rem Get the full date string "YYYY-MM-DD"
    pause
    
    rem Extract components
    set yyyy=%day:~0,4%    rem Year
    set mm=%day:~5,2%      rem Month
    set dd=%day:~8,2%      rem Day
    echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd!
    pause
    
    rem Construct the file names
    set "brandFile=BRAND_!mm!!dd!.!storeCode!"
    set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
    set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
    echo Constructed file names: !brandFile!, !departmentFile!, !productFile!
    pause
    
    rem Step 5: Run the osql command and check result for the specific product pattern
    set "result="  rem Initialize result variable to empty
    
    for /f "delims=" %%j in ('osql -UTRANSF -PFSNART -S!transformedCode! -Q "exec xp_cmdshell 'dir d:\FTPRoot\transfIN\PRODUCT_!mm!!dd!.!storeCode!'"') do (
        echo Checking for PRODUCT file: %%j
        echo %%j | findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" >nul
        if not errorlevel 1 (
            set "result=PRODUCT_!mm!!dd!.!storeCode!"
        )
    )
    pause
    
    rem Step 6: Check result
    if defined result (
        echo Found result: !result!
        echo Result indicates true for the presence of PRODUCT pattern for store code !storeCode!.
        pause
        goto :next
    )
    
    echo No relevant result found for store code !storeCode!. Continuing to download.
    pause
    
    rem Step 7: FTP download
    set @ftpcommand = '
    (
        echo open ftp.yourserver.com
        echo username
        echo password
        echo get !brandFile! D:\map_drive\wprx\!brandFile!
        echo get !departmentFile! D:\map_drive\wprx\!departmentFile!
        echo get !productFile! D:\map_drive\wprx\!productFile!
        echo bye
    ) > ftp_commands.txt
    ftp -n -s:ftp_commands.txt
    '
    
    exec xp_cmdshell @ftpcommand
    
    
    
    rem Step 8: Copy files to the pth12345 folder
    (
        echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761'
        echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y'
        echo exec xp_cmdshell 'net use x: /d /y' 
    ) > transfer2store.sql
    
    osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql
    echo Copied files complete for store code !storeCode!
    pause
    
    :next
    

    )

    rem Compare st.txt and st_result.txt fc /b st.txt st_result.txt > nul 2>&1 if errorlevel 1 ( echo st.txt and st_result.txt are different. Restarting loop. timeout /t 60 /nobreak > nul goto loop ) else ( echo st.txt and st_result.txt are the same. Stopping program. )

    :end echo Script finished processing all store codes. pause endlocal

    in step 7 i need create .sql and run that sql file

  14. Assistant

    To modify your batch script to create an SQL file that contains the FTP commands and then execute that SQL file using osql, we will need to ensure that the construction of the SQL command is done properly.

    Adjustments to Step 7:

    1. Create the SQL file with FTP commands: Instead of trying to set a variable with the SQL commands, we'll write them directly to a .sql file.
    2. Use osql to execute the SQL file: After creating the FTP commands file, we'll execute it with osql.

    Here's the updated section of your batch script, specifically focusing on Step 7:

    rem Step 7: Create the SQL file for FTP commands
    (
        echo declare @ftpcommand nvarchar(max) = ''
        echo (
        echo     open ftp.yourserver.com
        echo     username
        echo     password
        echo     get !brandFile! D:\map_drive\wprx\!brandFile!
        echo     get !departmentFile! D:\map_drive\wprx\!departmentFile!
        echo     get !productFile! D:\map_drive\wprx\!productFile!
        echo     bye
        echo ) 
        echo > ftp_commands.txt
        echo ftp -n -s:ftp_commands.txt
    ) > ftp_commands.sql
    
    rem Execute the SQL file using osql
    osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_commands.sql
    

    Full Context of Step 7 in Your Script:

    Here’s how the complete Step 7 would look in the broader context of your script:

    rem Step 7: Create the SQL file for FTP commands
    (
        echo declare @ftpcommand nvarchar(max) = ''
        echo (
        echo     open ftp.yourserver.com
        echo     username
        echo     password
        echo     get !brandFile! D:\map_drive\wprx\!brandFile!
        echo     get !departmentFile! D:\map_drive\wprx\!departmentFile!
        echo     get !productFile! D:\map_drive\wprx\!productFile!
        echo     bye
        echo ) > ftp_commands.txt
        echo ftp -n -s:ftp_commands.txt
    ) > ftp_commands.sql
    
    rem Execute the SQL file using osql
    osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_commands.sql
    
    pause  rem Optional to pause and let you see the output
    

    Important Notes:

    1. Properly Create the SQL File: The FTP commands are written to ftp_commands.sql. This ensures the commands are sent as SQL statements.
    2. Execute with osql: Using osql allows execution as intended. Be sure that osql is properly configured in your environment.
    3. Escaping and Formats: Ensure that special characters inside the batch file are handled correctly. Usually, quotes need to be doubled, but in this layout, we are simply echoing lines so it remains readable.
    4. Error Handling: Further improvement could be made with error checking after executing the osql command to handle any issues gracefully.

    Final Adjustment for Deletion

    If desired, you might want to clean up by deleting ftp_commands.sql and ftp_commands.txt after execution if they are no longer needed:

    del ftp_commands.sql
    del ftp_commands.txt
    

    Entire Batch File at Step 7

    You can replace the provided part into your entire script where Step 7 occurs. Make sure that the flow of the program remains intact and looks good with proper validations and error checks according to your needs.

  15. User

    merge step 5, 6, 7 by step file sql check file first not run step 7

  16. Assistant

    To merge Steps 5, 6, and 7 while also ensuring that Step 7 (creating and executing the SQL file for FTP commands) only runs if the relevant product file is found, you'll need to organize the flow of your batch script as follows:

    Updated Steps Overview:

    1. Step 5 will check for the presence of the product file and set a variable to indicate whether it was found.
    2. Step 6 will check the result variable.
    3. If the result is defined (indicating the product file is found), Step 7 for creating and executing the FTP commands will run. If not, it will skip this step.

    Here’s how the merged steps can look in your existing script:

    Updated Batch Script

    @echo off
    setlocal EnableDelayedExpansion
    
    :loop
    rem Step 1: Loop through each store code in st.txt
    for /f "usebackq delims=" %%i in ("st.txt") do (
        set "storeCode=%%i"
        echo Processing store code: !storeCode!
        pause
    
        rem Step 2: Transform store code to pth12345
        set "transformedCode=pth!storeCode!"
        echo Transformed store code to: !transformedCode!
        pause
    
        rem Step 3: Check if the pth12345 file exists
        if exist "!transformedCode!" (
            echo Found the file: !transformedCode!
            echo Skipping to next store code.
            pause
            goto :next
        )
    
        rem Step 4: Ping pth12345
        ping -n 1 "!transformedCode!" > nul 2>&1
        if errorlevel 1 (
            echo !transformedCode! is offline. Stopping for this store code.
            pause
            goto :next
        )
    
        rem Fetch yesterday's date using osql
        osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
        echo Fetched yesterday's date.
        pause
    
        rem Check if the date.dat file was created
        if not exist date.dat (
            echo Failed to retrieve date from the database. Exiting.
            pause
            goto :end
        )
    
        rem Read the date from the file and format it
        set /p date=<date.dat
        echo Date retrieved: !date!
        set day=!date!   rem Get the full date string "YYYY-MM-DD"
        pause
    
        rem Extract components
        set yyyy=!day:~0,4!    rem Year
        set mm=!day:~5,2!      rem Month
        set dd=!day:~8,2!      rem Day
        echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd!
        pause
    
        rem Construct the file names
        set "brandFile=BRAND_!mm!!dd!.!storeCode!"
        set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
        set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
        echo Constructed file names: !brandFile!, !departmentFile!, !productFile!
        pause
    
        rem Step 5: Run the osql command and check result for the specific product pattern
        set "result="  rem Initialize result variable to empty
    
        for /f "delims=" %%j in ('osql -UTRANSF -PFSNART -S!transformedCode! -Q "exec xp_cmdshell 'dir d:\FTPRoot\transfIN\PRODUCT_!mm!!dd!.!storeCode!'"') do (
            echo Checking for PRODUCT file: %%j
            echo %%j | findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" >nul
            if not errorlevel 1 (
                set "result=PRODUCT_!mm!!dd!.!storeCode!"
            )
        )
        pause
    
        rem Step 6: Check result
        if defined result (
            echo Found result: !result!
            echo Result indicates true for the presence of PRODUCT pattern for store code !storeCode!.
            pause
    
            rem Step 7: Create the SQL file for FTP commands
            (
                echo declare @ftpcommand nvarchar(max) = ''
                echo (
                echo     open ftp.yourserver.com
                echo     username
                echo     password
                echo     get !brandFile! D:\map_drive\wprx\!brandFile!
                echo     get !departmentFile! D:\map_drive\wprx\!departmentFile!
                echo     get !productFile! D:\map_drive\wprx\!productFile!
                echo     bye
                echo ) > ftp_commands.txt
                echo ftp -n -s:ftp_commands.txt
            ) > ftp_commands.sql
    
            rem Execute the SQL file using osql
            osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_commands.sql
    
            pause  rem Optional to pause and let you see the output
        ) else (
            echo No relevant result found for store code !storeCode!. Continuing to download.
            pause
        )
    
        rem Step 8: Copy files to the pth12345 folder
        (
            echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761'
            echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y'
            echo exec xp_cmdshell 'net use x: /d /y' 
        ) > transfer2store.sql
    
        osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql
        echo Copied files complete for store code !storeCode!
        pause
    
        :next
    )
    
    rem Compare st.txt and st_result.txt
    fc /b st.txt st_result.txt > nul 2>&1
    if errorlevel 1 (
        echo st.txt and st_result.txt are different. Restarting loop.
        timeout /t 60 /nobreak > nul
        goto loop
    ) else (
        echo st.txt and st_result.txt are the same. Stopping program.
    )
    
    :end
    echo Script finished processing all store codes.
    pause
    endlocal
    

    Summary of Changes:

    • Combined Steps: Steps 5, 6, and 7 are combined, where the script checks for the presence of the product file and only proceeds to create and execute the FTP command file if the product file exists.
    • Use of Simplified Logic: Utilized the if defined result to determine if the product was found.
    • Removed Redundant Code: Only the necessary parts are executed based on the presence of the product file.
    • Pause for Debugging: Optional pauses are retained for debugging purposes, allowing you to see each step's output.

    This restructured script will help maintain a clear and efficient flow through the necessary steps, ensuring that the FTP command is only executed when the required conditions are met.

  17. User

    @echo off setlocal EnableDelayedExpansion cls rem Create or clear st_result.txt at the beginning echo. > st_fail.txt

    :loop cls rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do call :process "%%i" goto :compare

    :process set "storeCode=%~1"

    rem Step 2: Transform store code to PTH12345
    set "transformedCode=PTH!storeCode!"
    echo.
    echo Processing code: !transformedCode!
    
    rem Step 3: Check if !storeCode! already exists in st_result.txt
    findstr /x /c:"!storeCode!" st_fail.txt > nul
    if  errorlevel 1 (
        echo Found !transformedCode! in st_fail.txt.
    )
    
    rem Step 4: Ping PTH12345
    ping -n 1 "!transformedCode!" > nul 2>&1
    if errorlevel 1 (
        echo !transformedCode! is offline. Stopping for this store code.
        echo !storeCode! >> st_fail.txt & echo Try later
        goto :next
    )
    
    rem Step 5: Get Date
    osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "set nocount on; select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
    if errorlevel 1 (
        echo Failed to retrieve date. Check OSQL command.
        goto :next
    )
    
    set /p day=<date.dat
    set mm=!day:~6,2!
    set dd=!day:~9,2!
    del date.dat
    set "brandFile=BRAND_!mm!!dd!.!storeCode!"
    set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
    set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
    
    rem Step 6: Run the osql command and check result for the specific product pattern
    set "result="
    
    (
    echo exec xp_cmdshell 'dir d:\FTPRoot\transfIN\!productFile!'
    ) > check_product.sql
    
    osql -UTRANSF -PFSNART -S!transformedCode! -i check_product.sql > result.dat
    del /q check_product.sql > nul
    
    rem Check if result.dat contains the product filename
    findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" result.dat > nul
    if not errorlevel 1 (
        set "result=PRODUCT_!mm!!dd!.!storeCode!"
    )
    
    rem Clean up the output file
    del result.dat
    
    rem Step 7: Check result
    if defined result (
        echo Found !result!
        echo Next Store
        rem echo !storeCode! >> st_fail.txt & type st_fail.txt & pause
        goto :next
    )
    
    rem If not found, proceed to download
    echo Not Found !result!
    rem (
    rem echo user hotrn hotrn
    rem echo cd ..
    rem echo cd ..
    rem echo cd Aldata/LIFE/HEAD/SAV/DOWN/POS
    rem echo get !productFile! D:\map_drive\wprx\!productFile!
    rem echo get !departmentFile! D:\map_drive\wprx\!departmentFile!
    rem echo get !brandFile! D:\map_drive\wprx\!brandFile!
    rem echo bye
    rem ) > ftpcmd.dat
    rem echo ftp -n -s:ftpcmd.dat 10.4.9.128
    rem START /B cmd /c "ftp -n -s:ftpcmd.dat 10.4.9.128 > NUL 2>&1"
    rem del ftpcmd.dat
    rem echo Downloaded files to D:\map_drive\wprx\
    
    rem Step 7: Create the SQL file for FTP commands
    (
    echo declare @ftpcommand nvarchar(max) = ''
    echo (
    echo     user hotrn hotrn
    echo     cd Aldata/LIFE/HEAD/SAV/DOWN/POS
    echo     get !brandFile! d:\FTPRoot\TransfIN\!brandFile!
    echo     get !departmentFile! d:\FTPRoot\TransfIN\!departmentFile!
    echo     get !productFile! d:\FTPRoot\TransfIN\!productFile!
    echo     bye
    echo ) 
    echo > ftpwprx.dat
    echo ftp -n -s:ftpwprx.dat
    ) > ftp_wprx.sql
    type ftp_wprx.sql & pause
    rem osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_wprx.sql
    del /q ftp-wprx.sql
    
    rem Step 8: Copy files to the PTH12345 folder
    (
        echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761'
        echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y'
        echo exec xp_cmdshell 'net use x: /d /y'
    ) > transfer2store.sql
    
    echo osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql
    echo Copied files complete for store code !storeCode!
    del /q transfer2store.sql > nul
    
    rem Caution: Ensure you want to delete all files in the D:\map_drive\wprx directory.
    del /q D:\map_drive\wprx\* > nul
    
    :next
    exit /b 0
    

    :compare rem Step 9: Compare st.txt and st_result.txt fc /b st.txt st_result.txt > nul 2>&1 if errorlevel 1 ( rem echo st.txt and st_result.txt are different. Restarting loop. timeout /t 10 /nobreak goto loop ) else ( echo st.txt and st_result.txt are the same. Stopping program. )

    :end echo Script finished processing all store codes. pause endlocal

    change process check fail to file and try again

  18. Assistant

    To modify your batch script so that it handles failed attempts to process store codes correctly—by marking them in st_fail.txt and allowing for retrying later—you can implement a loop to check for failed store codes after processing them. If a store code is found to have failed, it gets recorded in st_fail.txt, and you'll need to check that file before processing the next loop of store codes.

    Updated Script

    Here's a revised version of your script. This version captures failed codes, retries processing them, and ensures that those entries are handled in st_fail.txt.

    @echo off
    setlocal EnableDelayedExpansion
    cls
    rem Create or clear st_fail.txt at the beginning
    echo. > st_fail.txt
    echo. > st_result.txt  rem Create or clear result file to track processed stores
    
    :loop
    cls
    rem Step 1: Loop through each store code in st.txt
    for /f "usebackq delims=" %%i in ("st.txt") do call :process "%%i"
    goto :compare
    
    :process
        set "storeCode=%~1"
        
        rem Step 2: Transform store code to PTH12345
        set "transformedCode=PTH!storeCode!"
        echo.
        echo Processing code: !transformedCode!
    
        rem Step 3: Check if !storeCode! already exists in st_fail.txt
        findstr /x /c:"!storeCode!" st_fail.txt > nul
        if errorlevel 1 (
            echo Found !transformedCode! in st_fail.txt.
        )
    
        rem Step 4: Ping PTH12345
        ping -n 1 "!transformedCode!" > nul 2>&1
        if errorlevel 1 (
            echo !transformedCode! is offline. Stopping for this store code.
            echo !storeCode! >> st_fail.txt & echo Try later
            goto :next
        )
    
        rem Step 5: Get Date
        osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "set nocount on; select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
        if errorlevel 1 (
            echo Failed to retrieve date. Check OSQL command.
            echo !storeCode! >> st_fail.txt & echo Try later
            goto :next
        )
        
        set /p day=<date.dat
        set mm=!day:~6,2!
        set dd=!day:~9,2!
        del date.dat
        set "brandFile=BRAND_!mm!!dd!.!storeCode!"
        set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
        set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
    
        rem Step 6: Run the osql command and check result for the specific product pattern
        set "result="
        
        (
        echo exec xp_cmdshell 'dir d:\FTPRoot\transfIN\!productFile!'
        ) > check_product.sql
    
        osql -UTRANSF -PFSNART -S!transformedCode! -i check_product.sql > result.dat
        del /q check_product.sql > nul
    
        rem Check if result.dat contains the product filename
        findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" result.dat > nul
        if not errorlevel 1 (
            set "result=PRODUCT_!mm!!dd!.!storeCode!"
        )
    
        rem Clean up the output file
        del result.dat
    
        rem Step 7: Check result
        if defined result (
            echo Found !result!
            echo Next Store
            echo !storeCode! >> st_result.txt  rem Record the successful store code
            goto :next
        )
    
        rem If not found, proceed to download
        echo Not Found !result!
    
        rem Step 7: Create the SQL file for FTP commands
        (
        echo declare @ftpcommand nvarchar(max) = ''
        echo (
        echo     user hotrn hotrn
        echo     cd Aldata/LIFE/HEAD/SAV/DOWN/POS
        echo     get !brandFile! d:\FTPRoot\TransfIN\!brandFile!
        echo     get !departmentFile! d:\FTPRoot\TransfIN\!departmentFile!
        echo     get !productFile! d:\FTPRoot\TransfIN\!productFile!
        echo     bye
        echo ) 
        echo > ftp_wprx.dat
        echo ftp -n -s:ftp_wprx.dat
        ) > ftp_wprx.sql
        type ftp_wprx.sql & pause
        del /q ftp_wprx.sql
    
        rem Step 8: Copy files to the PTH12345 folder
        (
            echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761'
            echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y'
            echo exec xp_cmdshell 'net use x: /d /y'
        ) > transfer2store.sql
        
        echo osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql
        echo Copied files complete for store code !storeCode!
        del /q transfer2store.sql > nul
    
        rem Caution: Ensure you want to delete all files in the D:\map_drive\wprx directory.
        del /q D:\map_drive\wprx\* > nul
    
        :next
        exit /b 0
    
    :compare
    rem Step 9: Compare st.txt and st_fail.txt
    fc /b st.txt st_fail.txt > nul 2>&1
    if errorlevel 1 (
        echo st.txt and st_fail.txt are different. Restarting loop.
        timeout /t 10 /nobreak
        goto loop
    ) else (
        echo st.txt and st_fail.txt are the same. Stopping program.
    )
    
    :end
    echo Script finished processing all store codes.
    pause
    endlocal
    

    Key Changes Implemented:

    1. Error Handling:

      • If the OSQL command fails to retrieve the date, the store code is recorded in st_fail.txt.
    2. Record Successful Codes:

      • A successful store code is added to st_result.txt for tracking purposes.
    3. Retry Logic and Failures:

      • When the original store code fails due to issues like offline status or date retrieval failure, it retries the failed store codes by checking st_fail.txt.
    4. Cleanup:

      • The script ensures temporary files such as result.dat and others are deleted once they are no longer needed.
    5. Output Visibility:

      • The relevant output is printed to the console to observe the flow of processing.

    This updated script is better structured for retrying failed store codes, providing a mechanism to ensure that anything that fails will be handled appropriately and can be addressed in a future run.